SQL server linked server using ODBC - windows vs SQL login

Question

We have an ODBC connection to Netsuite. It uses a CA certificate. I can create a linked server using the MSDASQL provider and it works fine, but only for SQL server logins. For windows logins, behavior is not consistent. Some accounts can use the remote server and some get an error : OLE DB provider "MSDASQL" for linked server "NETSUITE" returned message "[NetSuite][ODBC 64bit driver][64bit Client]Problem obtaining trusted CA certificates from Truststore".

Answer 1

in the end, it turned out to be the truststore setting in the odbc.ini. When the odbc connector is created this setting is set to "system". Changing it to the actual certificate path did the trick.

Answer 2

Hi @Mordechai Danielov ,

For Windows logins, the login must be a valid login on the linked server, make sure that the Windows identify has been granted access to the SQL server database and for not work login, check the user credentials you should use to connect to the database

And check the local server login to remote server login mappings

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I don't know anything about Netsuite, but generally I would not expect self-mapping with Windows authentication to work with any other data source than SQL Server. Actually, even getting self-mapping to work with SQL login is a small feat.

You often have to resort to set up a proxy login, so that connection to the remote data source is done through that proxy. That proxy can be common for all users (not very secure) or be set up per user (maintenance nightmare).