Sql-server – getting a “login failed” when creating this linked server

linked-serversql-server-2008

I'm creating a linked server from ServerA to ServerB, both of them are SQL Server 2008 servers. I need to create the linked server using a domain service account so that anyone will be able to utilize the linked server. ServerB only accepts Windows authentication, so I've added the service domain account to ServerB with proper permissions. I've verified the permissions by logging in with the service domain account to ServerB and everything looks great. But when I create a linked server on ServerA with the script below, I get a "login failed for user". Is this script incorrect, or am I missing something else?

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'ServerB', @catalog=N'MyDatabase'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'False',@locallogin=NULL,@rmtuser=N'mydomain\myuser',@rmtpassword='mypassword'
GO

Best Answer

  1. Log in to the serverA using "mydomain\myuser" credentials.

  2. Run below TSQL :

    EXEC master.dbo.sp_addlinkedserver 
                            @server = N'serverB', 
                            @srvproduct=N'', 
                            @provider=N'SQLNCLI', 
                            @datasrc=N'serverB', 
                            @catalog=N'MyDatabase',
                            @provstr='Integrated Security=SSPI;'
GO
EXEC sp_addlinkedsrvlogin 'serverB', true  
go

  3. Check if the "serverB" exists in sys.servers :

    select * from sys.servers

  4. Check if the "serverB" works from ServerA

    select * from serverB.master.dbo.sysdatabases
Related Question