1

I have tried to create linked server to the remote sql server but when it failed with following error message.

The test connection to the linked server failed.

------------------------------ ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

I have followed the instructions from How do I specify a linked server to a remote database over tcp/ip?.

My config settings are

EXEC master.dbo.sp_addlinkedserver
@server     = N'TESTSERVER',
@srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
@provider   = N'SQLNCLI', 
@datasrc    = N'172.x.x.x';
-- Pair local and remote logins
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname  = N'TESTSERVER',
@useself     = N'False',
@locallogin  = N'Local_user_name',
@rmtuser     = N'Remote_user_name',
@rmtpassword = N'Remote_password';

I am able to connect the remote server instance. But it failed when I used that as a linked server in my Local instance.

Please help me resolve the error.

Thanks.

Update

I am able to connect by changing the setting from Be made using the login’s current security context to Be made using this security context

Please let me know why the latter works.

  • Did you try to login through "sql server authentication". – Md Haidar Ali Khan Aug 22 '17 at 5:22
  • @MdHaidarAliKhan I can able to connect remote instance using the same credentials. But it fails only in linked server. – User12345 Aug 22 '17 at 6:05
  • The code you posted does not correspond to the error you've got. Your code is the same as selecting in GUI Be made using this security context when you map all the local logins to remote SQL Server login with password. But the error you are getting says you tries to use Windowth Authentication, please update your post with the code that you've used when got this error – sepupic Aug 22 '17 at 6:28
  • When you say you connect successfully to the remote server (presumably in SSMS) what exact credentials are you supplying in the login window? .\Remote_user_name maybe? Or <ip_address>\Remote_user_name? And could you please provide the SQL Server version you are using (tag). – hot2use Aug 22 '17 at 6:28
2

As I can see from the your error messages you are connecting to the server from different domain. So your cuurent windows credentials cannot be used as Be made using the login’s current security context implies.

Option Be made using this security context implies that during authentication SQl Server will use the credentials specified in the text boxes (SQL Server authentication).

So the answer is - you can use the method Be made using this security context with the credentials you specified, or, alternatively you can specify how every local user will authenticate on the remote server (specifying that no authentication is made for any login not in the list above): enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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