Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 1 down vote accepted
  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
    
share|improve this answer
    
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. –  lush May 29 '13 at 19:25
    
@lush Refer to this blogs.msdn.com/b/farukcelik/archive/2008/01/02/… –  Kin 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. –  lush 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. –  lush 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 May 29 '13 at 20:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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