If the login credentials are in the ODBC connection, which security connection setting to use?

Question

We have a linked PostgreSQL server and the remote login and password are parsed using the ODBC settings on the Windows server.

Under the Security tab in Linked Server Properties within SQL, which connection setting should be used please?

• Not be made
• Be made without using a security context
• Be made using the login's current security context
• Be made using this security context

If the last one, why would you want to pass the login credentials twice?

Answer 1

I have a linked MySQL server set up through an ODBC connector, so these settings may help you get somewhere. I found the setting that worked 100% of the time for me was selecting:
Be made using the login's current security context Then in the box above, click Add, then as your local login choose your default local SQL server user, or a specific SQL Server user, and un-check impersonate, then in the Remote User and Remote Password field enter your PostgreSQL credentials. I am not 100% sure of the actual logic behind this, but the way I see it is it's linking up your local SQL server user with your remote user.

If that doesn't work, then you could also try selecting Be made using this security context and entering your Remote password and Username there, but I found my previous suggestion to always yield the best results for me. Good luck!