I have established a linked server and am attempting to query a database to which I have a SQL Server username and password, however I can't find the syntax for specifying the userid/pw in the query and get the following error:
Msg 18452, Level 14, State 1, Line 1
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
I know from http://msdn.microsoft.com/en-us/library/ms175537(v=sql.105).aspx that windows authentication is preferred, but it is a vendor database and not up to me.
Also from that link: "you must set up a local login mapping from a login that uses Windows Authentication to a specific login on the linked server that is not a Windows authenticated login. In this case, the remote login uses SQL Server Authentication if the linked server is an instance of SQL Server."
I am not an Windows admin on the local machine and not any kind of admin on the linked machine. Is there no other way?
Best Answer
Open the properties of your linked server. Select Security, and click the "be made using this security context" radio button. Put your SQL Server login in this section. Clear out any logins listed in the box above "Local server login to remote server login mappings", and try again.