0

I have added a linked server that was working fine. After this I added a local login to within properties > security on the linked server. After doing this I get an error when trying to run a query using this link stating:

access to remote server is denied because no login-mapping exists sql

I tried removing the local login and although I can use the remove option and select OK if I load up the properties again the user remains. How can I permanently remove this user?

1 Answer 1

0

The issue is related to linked server connection security. If you do not map any logins with a specific SQL Server account, you can select the Be made using this security context option and specify a SQL Server account with the following syntax:

EXEC sp_addlinkedsrvlogin 'TEST1', 'false', 'userLocal', 'userRemote', 'remotePassword'

As alternative solution you can use the parameter @datasrc instead of @provstr. @dataSrc should works without setting the User ID

Example:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server', @datasrc=N'serverName\InstanceName' 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName', @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser', @rmtpassword = N'*****'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.