Unable to connect to other databases aside from Principal ODBC
Hello,
I currently have an ODBC connection to a SQL server with multiple databases that I use regularly. I have a principal connection "X1PRD", that I use regularly and it is working as should. The issue I am facing now is that I have uploaded a couple of tables to another database "X1REF" within the same SQL server and I am getting the error below. It is saying that "The server principal is not able to access the database "x1ref" under the current security context." I am not sure how to fix this. Any ideas?
Error:
Connector reply error: SQL##f - SQL State: 08004, Error Code: 916, Error Msg: [Microsoft][ODBC SQL Server Driver][SQL Server]The server principal is not able to access the database "m3ref" under the current security context.
This sounds like the Qlik service account or your user account doesn't have permission to access the 2nd database. If this is on-prem Sense, I'm guessing it's the service account. Check with your DBA on permissions (select, execute, etc). Or, if you are able to impersonate the service account, you can try on SSMS and see if you get the same error.
Hi! The message "The server principal is not able to access the database 'X1REF' under the current security context." indicates that the user account under which the ODBC connection is running does not have sufficient permissions to access the "X1REF" database.
Try to check user permissions in SQL Server Login to SQL Server Management Studio (SSMS): Use your SQL Server credentials to log in. Navigate to the 'X1REF' Database: In SSMS, expand the "Databases" node. Right-click on the "X1REF" database and select "Properties". Verify User Access: Go to the "Permissions" tab. Check if the user (the one used in your ODBC connection) has been granted sufficient permissions (like db_datareader, db_datawriter, or db_owner) If the user is not listed, add them and assign appropriate roles.
or map the user to the 'X1REF' Database User Mapping: In SSMS, expand the "Security" node and then "Logins". Find and right-click the login used in your ODBC connection, then select "Properties". Go to the "User Mapping" page. Check the box next to the "X1REF" database and ensure that the user has the appropriate roles (such as db_datareader, db_datawriter, or db_owner). Apply Changes: Click OK to save changes and exit.
Sometimes, if the default database for the login is set to "X1PRD", it might cause issues when accessing other databases. Set Default Database: Right-click on the login under "Security" -> "Logins" in SSMS. Select "Properties". In the "General" tab, change the default database to either "X1REF" or "master" (which usually has broader access).
