SQL Server to SQL Server linked server setup

From My understanding of this issue it's a "HOP" issue.

i.e. you are trying to use server A to relay your login details (with SSPI) to Server B.

In SQL Server 2005 they have added a whole load of security issues that make this harder than it should be. The words "Kerberos Authentication" will become the bain of most sys-admins/DBA's lives. It effectively is used for pass-through authentication.

Here are the basics of what you need. 1) The servers (A and B) need to be set-up in Active Directory(AD) with delegation for Kerberos enabled. (this is set through your active directory admin panel)

2) The service account that your SQL Servers run under need to have delegation enabled also (this is also set through your active directory admin panel). - if they are not running under a service account, you need to create one.

3) The Servers need to have SPN's defined for the instance and the HOST and the machine name. (Using a tool called SetSPN in the windows support tools)

Support Tools (SetSPN is in this set) http://www.microsoft.com/downloads/details.aspx?FamilyID=96a35011-fd83-419d-939b-9a772ea2df90&DisplayLang=en

(Overview of how to add an SPN) http://technet.microsoft.com/en-us/library/bb735885.aspx

4) You may need to set your DB to "trustworthy"

ALTER DATABASE SET trustworthy on

5) After you have all of this done restart your instances.

6) Then try create your linked server again.

Finally you can test your connection to SQL Server. This should work fine if you have it all configured correctly.

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=ServerB;Integrated Security=SSPI;'
    ).MASTER.dbo.syscolumns

This will tell you your connection authentication type.

select auth_scheme from sys.dm_exec_connections where session_id=@@SPID

You want to get 'KERBEROS' here and not 'NTLM'.

It's a slippy slope, KERBEROS and Pass-through delegation, stick with it and you will eventually figure it out.

References Kerberos http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

http://blogs.iis.net/brian-murphy-booth/archive/2007/03/09/the-biggest-mistake-serviceprincipalname-s.aspx

Other manifestations of the problem http://www.sqlservercentral.com/Forums/Topic460425-359-1.aspx

http://msdn2.microsoft.com/en-us/library/aa905162(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/ms189580.aspx

I hope this all helps.

You can also use SQL Server Management Studio (SSMS) to manage \ create linked servers as well if you're more comfortable with the GUI. To do so:

1. Launch SSMS and connect to one of the instances of SQL Server you want to link
2. Expand "Server Objects" in Object Explorer
3. Right click "Linked Servers" and choose "New Linked Server"
4. On the "New Linked Server" dialog, select "SQL Server" as the Server Type and enter the instance of SQL Server you'd like to link to.
5. On the "Security" page, select how users will authenticate from the current server to the linked server. You mentioned both servers are set up to use Windows Logins. If this is the case, under the section labeled "For a login not defined in the list above, connections will:" I would probably choose the option labeled "Be made using the Login's current security context".

Note that this assumes that users who have logins on server A also have logins on server B.

I'm going nuts with the same problem! I remember doing this with 2000 was always easy. I have been all over google and I can't get this to work. Exact same setup, both servers running on a domain account, Windows auth.

I'm trying to use named pipes instead of TCP and at least I get a different error:

EXEC sp_addlinkedserver 
    @server='statler', 
    @srvproduct='', 
    @provider='SQLNCLI', 
    @datasrc='np:statler', 
    @provstr='Integrated Security=SSPI'

-- Then I try this:
select net_transport, auth_scheme 
from statler.master.sys.dm_exec_connections 
where session_id=@@spid

/*

Getting closer, but still fails:

OLE DB provider "SQLNCLI" for linked server "statler" returned message 
    "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "statler" returned message 
    "An error has occurred while establishing a connection to the server. 
    When connecting to SQL Server 2005, this failure may be caused by the 
    fact that under the default settings SQL Server does not allow 
    remote connections.".
Msg 5, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [5]. 
OLE DB provider "SQLNCLI" for linked server "statler" returned message 
    "Invalid connection string attribute".

*/

This might have something to do with enabling names pipes, but I can connect via sqlcmd from server A to server B like this:

WALDORF:>  Sqlcmd.exe /E /Snp:statler

If I don't used named pipes, and just do:

New Linked Server
Server Type: SqlServer
Security: be made using the current login's security context

I get this:

Login failed for user NT AUTHORITY\ANONYMOUS LOGIN

[Edit] I started a discussion on Sql Server Central about this. Basically, you have to do some complicated configuration related to Kerberos delegation to get this to work.

http://www.sqlservercentral.com/Forums/Topic574262-146-1.aspx

I decided to just create a single, limited Sql Login account to handle the linked queries. I hate resorting to that, but it seems more secure than the changes you have to make to get it working with windows auth.

If you do a search on sp_addlinkedserver and sp_linkedservers, you get some examples. It is pretty straightforward to setup.

Also if you have SQL Manager, you can add with it's GUI.

Basically you need to link the two servers either by the SPs mentioned by Tim, or via GUI and then set the access rules (which is not even needed if you use Windows authentication on both servers).

I know this is supposed to be easy, but it's not working for me at all - I'm having security issues here. So I would like someone to spell out the steps for me.

I've done this in the past on SQL 2000 with no issues.

So you can link them, but cannot execute a query because of wrong accounts?

Does the windows user you try to use has rights to read data on both server?

Once I also had a problem because the "data access" property was set to false for some unknown reason.

Also try out what happens if you explicit set one user to another user for the link.

(These all can be done in SQL Manager.)

Tim has posted the exact steps that I had assumed were the correct ones. Step 5 is the security page. I select "Be made using the login's current security context".

When I click ok, I get the following error. I don't know why it's trying to use 'NT Authority\Anonymous login'. I'm logged into my workstation with my domain account which has all rights on both servers.

TITLE: Microsoft SQL Server Management Studio

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

------------------------------ ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

---

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3068&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

Try to do this while you are logged in locally to the server, if you do it from a remote machine you might not be sending the proper credentials.