Sql-server – Linked server to remote sql server failed

linked-serversql server

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.

Best Answer

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

Related Solutions

Sql-server – Local login impersonation not working with a linked server

The error you have is unrelated to logins as such.

This is caused when SQL Server tries to "pass through" the NT login token to the remote server. It doesn't have permission to pass the token through. The remote server looks for this because the local servers connects with Integrated Security.

You need "Security Account Delegation" to be configured for the local server.

As for the default mapping...

What MSDN says is that if you have "MyDomain\bob" locally then you have an "virtual" entry "stored" locally called "MyDomain\bob". There is no reconciliation of the 2 servers between the 2 servers. This is the same as running this:

EXEC sp_addlinkedsrvlogin 'MyLinkedServer', @useself = 'TRUE';

Note that "MyDomain\MyGroup" is not mapped: only discrete NT users and SQL logins. Not NT Groups.

This is all described in "Security for Linked Servers"

The default mapping for a linked server configuration is to emulate the current security credentials of the login. This kind of mapping is known as self-mapping. When a linked server is added by using sp_addlinkedserver, a default self-mapping is added for all local logins. If security account delegation is available and the linked server supports Windows Authentication, self-mapping for the Windows authenticated logins is supported.

...

If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, self-mapping will not work for logins that use Windows Authentication.

Sql-server – Can’t create linked server SQL Server 2005

As one colleague pointed out, there's no SQL Server 2003 version. I suppose you're speaking about MSSQL 2000. Which was somehow related in time with VS 2003 :-).

As you're working with instances on the same server, do you have the service "SQL Server Browser" started and running?

Why you need it:

"The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints

Best Answer

Related Solutions

Sql-server – Local login impersonation not working with a linked server

Sql-server – Can’t create linked server SQL Server 2005

Related Question