Linked Servers using SQL Authentication
-
I am working with a Linked Server (both servers are SQL2k SP3) and am running into the following error.
"Access to the remote server is denied because no login-mapping exists"
This error only appears when trying to access the linked server using a SQL Server account. When a domain account is used the query runs as expected.
The linked server is configured to use a SQL Account on the remote server to access the data. The account it maps to works fine as I can map the domain account to it and everything runs as expected.
I have tried mapping the SQL Server account that is running the query to an account on the remote server with no luck.
EXEC sp_addlinkedsrvlogin '<sql account name>', 'false', NULL, 'Test', 'test'
Am I missing something or is SQL Server unable to map a SQL Account to a Linked Server? All i can find in BOL and on MS site is that you should use Win Auth. when possible.
Any thoughts?
Thanks
-
If you are mapping a local account then you must login with sql security using the mapped local account specified, for the link to work otherwise you will get the error message.
If you want to use the link irrespective of the local login then remove the mapping and select 'Be made using this security context' and put the remote username and password in the two boxes.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for the help but figured out the problem.
The problem is that the developer created the linked server using the OLE DB driver. From what I can tell is that if you use the OLE DB driver to connect the Linked Server it won't allow you to map SQL Server Logins between both servers, only Integrated Security. I can't believe I didn't catch that, it always pays to step away from the problem for a few hours and look at it with fresh eyes.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply