This the second in a series of post on Infor CRM connection issues. In the first post, we looked at an issue that can affect the Data Link used to connect to the database. This time, we are going to look at an issue with the database set up. You would normally only encounter this issue when initially setting up the database, though it occur later if someone change the SQL login setting for some reason.
As described in this post, the Infor CRM database has a sysdba user that owns the sysdba schema in the database. In order for CRM to access the database, a SQL login is created to map to the sysdba database user. This SQL login is usually also named sysdba, but you can use anything as long as it is linked to the sysdba database user.
The bit I want to highlight is the setup of the sysdba SQL login. In order for the connection to work, this login specifically needs to have only the public user role, and have Master as the default database. This are the default settings, so all that is required is to create the sysdba login and link it to the sysdba database user as described in the above post. If give the sysdba login additional roles, you will be unable to login to CRM. If you are testing with the Administrator program, you will get “Database login failed: Unspecified error”. However, if you test in the Data Link connection, it will say the connection is good.
I mention this as a troubleshooting issue because I have seen folks add SQL server roles to the sysdba account when troubleshooting some other issue. When this happens, it has no immediate effect, as they are already experiencing login issues, but then even when they get the original issue resolved they are unable to login because of the added roles to the sysdba login. So make sure not to add any roles beyond Public to the sysdba account, and consider checking to see if any roles have been added if you notice the Data Link tests as working even though no one can log in.