Linked server to remote sql server failed

Question

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.

Answer 1

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):