2

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
2
  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
    
  • That works for when I'm logged in from mydomain\myuser, but what I failed to mention is that this is a service account. I need to be able to created the linked server which always uses the service account's credentials. – Christopher Garcia May 29 '13 at 19:25
  • @lush Refer to this blogs.msdn.com/b/farukcelik/archive/2008/01/02/… – Kin Shah May 29 '13 at 19:26
  • This is the weird part. I've been told I cannot use Kerberos from the project lead. Yesterday, he sat with me and added a linked server from one server to another and in the security tab, he used a domain\user and password combo for "Be made using this security context". And this solution worked, although I'm not sure how. – Christopher Garcia May 29 '13 at 19:29
  • When I try to add a linked server in the same manner he showed me yesterday, which I saw work, I get a "login failed, ServerB requires Windows authentication". So even though I'm using "mydomain\myuser" and password, ServerB is still thinking that I'm trying to use SQL Authentication though I'm not. – Christopher Garcia May 29 '13 at 19:32
  • @lush Service account wont work unless they are member of AD. Refer to Configuring Linked Servers for Delegation ==> msdn.microsoft.com/en-us/library/ms189580%28v=sql.100%29.aspx – Kin Shah May 29 '13 at 20:20

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.