Configuring Linked Servers for Delegation

  • Article

SQL Server and Windows can be configured to enable a client connected to an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as delegation. Under delegation, the instance of SQL Server to which a Windows user has connected by using Windows Authentication impersonates that user when communicating with another instance. Security account delegation is required for distributed queries when a self-mapping is used for a specific login against a specific linked server.

Delegation Requirements

To illustrate the requirements for delegation, consider the following scenario: A user logs on to a client computer that connects to a server that is running an instance of SQL Server, SQLSERVER1. The user wants to run a distributed query against a database on a linked server, SQLSERVER2. This scenario, in which one computer connects to another computer to connect to a third computer, is called a double hop.

Note

A linked server must be configured for delegation even when a client application, including SQL Server Management Studio, that establishes a connection to a server is on the same computer as the instance of SQL Server that is being queried.

Requirements for the Client

  • The Windows authenticated login of the user must have access permissions to SQLSERVER1 and SQLSERVER2. 
  • The user Active Directory property, Account is sensitive and cannot be delegated, must not be selected.
  • The client computer must be using TCP/IP or named pipes network connectivity.

Requirements for the First/Middle Server (SQLSERVER1)

  • The server must have an SPN registered by the domain administrator.

  • The account under which SQL Server is running must be trusted for delegation.

  • The server must be using TCP/IP or named pipes network connectivity.

  • The second server, SQLSERVER2, must be added as a linked server. This can be done by running the sp_addlinkedserver stored procedure. For example:

    EXEC sp_addlinkedserver 'SQLSERVER2', N'SQL Server'

  • The linked server logins must be configured for self mapping. This can be done by running the sp_addlinkedsrvlogin stored procedure. For example:

    EXEC sp_addlinkedsrvlogin 'SQLSERVER2', 'true'

Requirements for the Second Server (SQLSERVER2)

  • If using TCP/IP network connectivity, the server must have an SPN registered by the domain administrator.
  • The server must be using TCP/IP or named pipes network connectivity.

See Also

Concepts

Linking Servers

Other Resources

sp_addlinkedserver (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Added that named pipes network connectivity can also be used to configure linked servers for delegation.

17 July 2006
New content:
  • Added a Note that states a linked server must be configured for delegation even when a client application that establishes a connection to a server is on the same computer as the instance of SQL Server that is being queried.