How to Implement Kerberos Constrained Delegation with SQL Server 2008

![logo-sql08.gif](images/Ee191523.image001.gif "logo-sql08.gif")

Writer: Kevin McDonnell

Technical Reviewers: Greg Campbell, Jesus Dougan, Jivko Dobrev, Dan Benediktson

Applies to: SQL Server 2008

Summary: This paper discusses the steps required for a database administrator and Active Directory administrator to implement Kerberos constrained delegation with SQL Server 2008. I will also cover some new Service Principal Names (SPNs) available for SQL Server 2008 as well as some new methods to obtain Kerberos connections with the SQL Server Native Client (SNAC).

Introduction

If you’re interested in allowing users to use Windows® Authentication across multiple SQL Servers, then Kerberos Constrained Delegation is a feature in Active Directory® Domain Services (AD DS) that you’ll want to configure. This article is intended for SQL Server database administrators (DBAs) and Active Directory administrators. DBAs often need to configure linked servers, to enable server-to-server communication, and enable remote execution of queries, all under the context of the original Windows client credentials.

Security account delegation and Kerberos constrained delegation in Active Directory Domain Services allow you to control the movement of users Windows credentials across servers. The Windows Server® 2003 operating system leverages the Service-for-User-to-Proxy (S4UProxy) Kerberos extension to provide Kerberos Constrained Delegation. This paper will outline several methods to accomplish Constrained Delegation with the Microsoft® SQL Server® 2008 database software. This paper is not a primer on Active Directory Domain Services or Kerberos authentication.

More information on Kerberos can be found here:

https://msdn.microsoft.com/en-us/library/aa378747(VS.85).aspx

Security Account Delegation vs. Kerberos Constrained Delegation

Windows 2000 Server first allowed domain administrators to configure Kerberos unconstrained delegation. The only problem with Kerberos unconstrained delegation is that it doesn’t let you control what services can be delegated. In other words, all services would be permitted to delegate their client credentials using any protocols to another server, and not just the Kerberos protocol and client credentials required for SQL Server.

Windows Server 2003 allows you to control what protocols and for what services user credentials can be delegated. The Windows Server 2003 domain controller must be at Functional Level 2 in order to take advantage of Kerberos constrained delegation. Therefore, Kerberos constrained delegation gives the Active Directory administrator more control over what services and what protocols can be delegated.

Windows Authentication: NTLM or Kerberos

Windows Authentication is the preferred method for users to authenticate to SQL Server. Windows clients that have been authenticated on a domain can establish Windows Authenticated connections either using NTLM or Kerberos. In an Active Directory environment, Kerberos authentication is always attempted first. If Kerberos authentication cannot be made, the client will attempt to use NTLM authentication. The client or user is unaware of whether a successful Windows authenticated connection to SQL Server was made using NTLM or Kerberos, you have two options: the user can examine the Kerberos tickets they have using KLIST or for SQL Server 2005 and SQL Server 2008, or you can query the sys.dm_exec_connections dynamic management view (DMV).

SQL Server 2008 Client Network Communications Changes

For SQL Server 2000 and SQL Server 2005, client connections that use the Named Pipes protocol will always result in NTLM authenticated connections. In order for SQL Server 2000 and SQL Server 2005 client connections to successfully negotiate Kerberos connections, they must use the TCP network library. The reason that Kerberos works only while using TCP for SQL Server 2000 and SQL Server 2005 clients is that the Service Principal Name registered for SQL Server includes the port number the server is listening on.

SQL Server clients can control which network library is used for each connection by either prefacing the server name with “tcp:” or “np:”, or “lpc:” in the connection string or by using the SQL Server Configuration Manager, SQL Server Native Client Configuration tool to create an alias that specifies TCP or named pipes. A SQL Server client alias writes an entry in the client machine’s registry containing the server name and the preferred protocol used to connect. Each time a new client connection is made, this registry key is first checked to see if there is a preferred interprocess communication (IPC). The registry key that we store SQL Server client aliases is:

HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Preparing Clients to Authenticate with Kerberos

When the SQL Server service starts, an attempt to register the Service Principal Name (SPN) is attempted. If the account starting SQL Server doesn’t have permission to register a SPN in Active Directory Domain Services, this call will fail and a warning message will be logged in the Application event log as well as the SQL Server error log. By default only the following accounts have permission to register Service Principal Names: (Local System, Network Service, Domain Admin).

Why is this important? In order to use Kerberos constrained delegation, we must be able to authenticate using Kerberos, have the Service Principal Names set, and have Kerberos constrained delegation configured.

Local System, Network Service, and Domain User are all valid choices for starting the SQL Server service. However, Local System is not recommended because it grants greater privileges than required to start the service. Microsoft’s recommendation is to start the SQL Server service using a low privileged Domain User account. However, Network Service could also be used under certain circumstances.

The problem with running SQL Server 2008 under Network Services from a security perspective is the ability to isolate it from other services running under Network Service. If another service running under Network Service was compromised, the attacker could gain access to SQL Server as well.

With SQL Server 2008 running on Windows Server 2008 or the Windows Vista® operating system, we now have per-services service security IDs (SIDs) that are used to access the operating system and SQL Server resources. More information about setting up service accounts for SQL Server and per-services SIDs can be found here:

https://msdn.microsoft.com/en-us/library/ms143504.aspx#Service_SID

However, certain features such as SQL Server clustered services can’t run under Network Service. Also, a domain administrator account should never be used to start a service.

It’s important to also note that default instances of SQL Server will listen on port 1433. SQL Server named instances by default listen on a dynamic port. If a SPN is set on a named instance with a particular port number, and later the port number changes after a service restart, the existing SPN will be invalid for that instance. To avoid this situation, the DBA can assign a static port to the named instance, or the Active Directory administrator can grant the service account the right to write the Service Principal Name attribute.

With SQL Server 2008, Named instances still by default listen on a dynamic port; however, you can register the SPN by using the instance name to avoid locking the port number with the SPN.

For Kerberos authenticated connections over TCP, we still require referencing the port number for a default instance. We can also now register the SPN for a SQL Server 2008 named instance by referencing the instance name. This avoids the problem of a named instance changing the listen on port number, which invalidates a previous SPN set using the older syntax.

Also, from either ODBC or OLE DB we have a new optional SPN dialog box that will enable the client to specify either a user account or machine account and obtain a Kerberos connection, even if the SPN for the SQL Server service account hasn’t been set. However, this additional connection string, keyword, cannot be used in Kerberos constrained delegation scenarios, which I will cover later.

Sample ODBC Connection String: (Machine account is specified, because the SQL Server service is running under Network Service)

[ODBC]

DRIVER=SQL Server Native Client 10.0

UID=kevin

ServerSPN=MySQL2008Machine$@MyDomain.com

WSID=Workstation

APP=Microsoft® Windows® Operating System

Trusted_Connection=Yes

SERVER=MySQL2008Machine

Sample OLE DB Provider String: (domain account used to start the SQL Server service is specified, because the service is running as a domain user)

[oledb]

; Everything after this line is an OLE DB initstring

Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID=""

;Initial Catalog=master;Data Source=SQL2008;

Initial File Name="";Server SPN=MySQLService@MyDomain.com

Note that in an ODBC connection string the keyword is “ServerSPN” and in OLE DB the string is

“Server SPN”: There is no space in the ODBC connection attribute.

Service Principal Name

To change the SPN, you can use either the SetSPN.exe tool or ADSIEdit. You must be logged in as a user with domain administrator privileges. You can verify if the SPN has been set for SQL Server by running:

SetSPN – L <service account that starts sql>

If SQL Server is starting under the Network Service account, run this:

; SetSPN –L hostname.

The original format of the SPN for SQL Server is:

MSSQLSvc/FQDN:Port

The new SPN formats for SQL Server 2008 are:

 MSSQLSvc/FQDN

For named instances the format is:

MSSQLSvc/FQDN:InstanceName

For more information, see Registering a Service Principal Name (https://msdn.microsoft.com/en-us/library/cc280459.aspx) and Service Principal Name (SPN) Client Connections (https://msdn.microsoft.com/en-us/library/ms191153.aspx).

SQL Server 2005 and SQL Server 2008 make it easy to determine whether your client connections were made using NTLM or Kerberos authentication. After your client connection has been established, run the following query:

select session_id,auth_scheme,net_transport,client_net_address from sys.dm_exec_connections where
@@SPID = session_id

If you want to see all client connections, just run:

select session_id,auth_scheme,net_transport,client_net_address from sys.dm_exec_connections

New Improvements with Kerberos in SQL Server 2008 Native Client (SNAC)

The new SNAC client that comes with SQL Server 2008 offers some new opportunities to help the client establish Kerberos authentication to the SQL Server.

If the SPN is not set for the startup account for SQL Server, the client can present an optional SPN keyword to the server using ODBC or OLE DB. If SQL Server is running under a domain user account and the SPN for the service account is not set, you cannot authenticate using Kerberos unless the following Server SPN keyword/connection attribute is configured:

Server SPN = user@domain

Where user@domain = the account being used to start the SQL Server service.

If SQL Server is running under the Network Service account, the machine account can be specified in the optional Server SPN keyword:

Server SPN = machine$@domain

To allow named pipes and shared memory, Kerberos authenticated connections, the Domain Admin must register:

MSSQLSvc/FQDN and/or MSSQLSvc/FQDN:InstanceName

Also, with named instances, because the Domain Admin is registering the instance name and not a specific port number, the SPN never invalidates if the port number changes. This makes managing named instance SPNs much easier.

The new SPN format allows a client to even make a shared memory, Kerberos authenticated connection if you specify : user@domain in the ODBC, Server SPN connection attribute, where user@domain is the name of the service account that is starting the SQL Server service.

If the SPN is set in Active Directory Domain Services, the client can optionally also pass the following: (FQDN = fully qualified domain name)

Server SPN = MSSQLSvc:FQDN:port

Or

Server SPN = MSSQLSvc:FQDN

Server SPN = MSSQLSvc:FQDN:Instance

However, the new Server SPN keyword/connection attributes can’t be used with any of the SQL Server client tools. The reason is that System.data.dll does not support these new keywords. SQL Server Management Studio and all the SQL Client tools use System.data.dll.

Also, you can’t rely on a user supplied SPN and configure Kerberos constrained delegation scenarios like a linked server. The Delegation tab in Active Directory Users and Computers is only available if the Server SPN is registered. (Figure 1 and Figure 2) So while the new Server SPN attribute is available to make single hop Kerberos connections easier, in order to configure Kerberos constrained delegation, the SQL Server SPN’s must be registered in Active Directory Domain Services first.

Figure 1: A domain account called “NoSPN” where the Service Principal Name is not set. Note that there is no Delegation tab in the dialog box.

Figure 2: The domain user account SQLSvcBoulder is used to start the SQL Server service on the Boulder server and the SPNs for SQL Server have been set. The Delegation tab, where the administrator can configure constrained security delegation, is displayed.

The DBA Dilemma – A Linked Server Connection using Windows Authentication

The DBA typically creates a linked server to establish a relationship between two distinct instances of SQL Server. When you create a linked server via the user interface, in the Security section of the linked server, you can specify the authentication to use. If you click Be made using the login’s current security context, connections to the linked server will be authenticated using the client’s credentials (Figure 3).

Figure 3: Security settings for a linked server

The DBA and Active Directory Administrator Working Together

When a DBA needs to configure a linked server and have the client’s credentials authenticate to the backend SQL Server using Windows Authentication, we’ll need to authenticate using Kerberos, and configure Kerberos constrained delegation. The common error received when either security account delegation or Kerberos constrained delegation is not configured correctly is:

Login failed for user NT AUTHORITY\ANONYMOUS LOGON

This paper will walk through various configuration options that the DBA and Active Directory administrator can configure to accomplish end-to-end Windows Authenticated connections through a SQL Server linked server connection. The following options will be discussed when configuring constrained security delegation.

The DBA and Active Directory Administrator Working Together

The following three servers will be used to document the necessary changes to enable Kerberos constrained delegation with SQL Server 2008. These three servers all belong to a fictitious domain called HOL169.local. The three servers are in three different regions of the US. The Sammamish server is in Sammamish, WA. The Boulder Server is in Boulder, CO and the Philly server is in Philadelphia, PA. Two domain users on the hol169 domain, Kyle and Jeff will retrieve data from the Adventureworks2008 sample database, which only exists on the Philly server, but will go through a configured linked server on the Boulder server to retrieve it:

  • Sammamish – SQL Server 2008 on the 64-bit edition of Windows Server 2008 (only acting as a client machine)
  • Boulder – SQL Server 2008 on the 64-bit edition of Windows Server 2008
  • Philly – SQL Server 2008 on the 64-bit edition of Windows Server 2008

Scenario: Enable a Linked Server on Boulder so that Sammamish users can authenticate to the Philly Server using Windows Authentication. The users logged on to the Sammamish computer will connect to the Boulder SQL Server and retrieve data from the Philly SQL Server.

The following VBscript code can be used to test for a successful or failed connection and retrieve results from the Adventureworks2008 Sample database on the Philly server. Save this code to a file called “Connectphilly.vbs”.

Test Code Used in Scenario

‘ConnectPhilly.vbs
Dim ConnectionString,conn,rs,x,output
ConnectionString = "Provider=SQLNCLI10.1;Data Source=Boulder;Integrated Security=SSPI;Initial Catalog = sports;Server SPN="
Set conn = CreateObject( "ADODB.Connection" )
conn.Open ConnectionString
msgbox "Connection Succeeded"
Set rs = CreateObject("ADODB.recordset")
rs.open "select BusinessEntityID,Name from Philly.Adventureworks2008.Sales.vStoreWithContacts where BusinessEntityID < 300",conn
set output = wscript.stdout
    While NOT rs.EOF
                for each x in rs.Fields
                  output.write x.value & " "
                next
                rs.MoveNext
        output.writeline
    Wend
rs.Close
conn.Close
To verify that you can establish a Kerberos connection to the first server, (Boulder) you can use the following code: (The reason that I’m providing this sample is so that you can experiment with passing the new Server SPN strings that are not possible to do with the SQL Server Client tools.)
‘ConnectBoulder.vbs
Dim ConnectionString,conn,rs,x,output
ConnectionString = "Provider=SQLNCLI10.1;Data Source=Boulder;Integrated Security=SSPI;Initial Catalog = master;Server SPN="
Set conn = CreateObject( "ADODB.Connection" )
conn.Open ConnectionString
msgbox "Connection Succeeded"
Set rs = CreateObject("ADODB.recordset")
rs.open "select session_id,auth_scheme,net_transport,client_net_address from sys.dm_exec_connections where @@spid = session_id",conn
set output = wscript.stdout
    While NOT rs.EOF
       for each x in rs.Fields
              output.write x.value & " "
       next
       rs.MoveNext
        output.writeline
    Wend
rs.Close
conn.Close

Tools Used for Troubleshooting

  1. Install Network Monitor on the client machine (Sammamish) and the first SQL Server we connect to (Boulder). Network Monitor allows you to trace the connections on the network and verify that we are attempting a Kerberos connection. In a TCP connection to SQL Server, you will first see a three-way TCP handshake to the SQL Server, followed by a connection to the Domain Controller over port 88 to request a Kerberos ticket. (TGS)
  2. Use the sample code ConnectBoulder.vbs to verify that you can obtain a Kerberos authenticated connection to the first SQL Server. This will run the DMV listed in item 4 below.
  3. KLIST – a command-line utility available from a DOS prompt on Windows Server 2008 that can display the Kerberos tickets, and purge them if necessary.
  4. Query the system DMV sys.dm_exec_connections on SQL Server. Find the spid associated with your connection and review the auth_scheme column to determine if Kerberos was used to authenticate. (Keep in mind that this DMV will only contain information while the connection is still open at the server.) The permission “VIEW SERVER STATE” is required to query this DMV.
  5. Enable Kerberos event logging. For more information, see Article 262177, (https://support.microsoft.com/default.aspx?scid=kb;EN-US;262177), in the Microsoft Knowledge Base.

Active Directory Administrator Steps for Sample Scenario

  1. Create two domain user accounts used to test scenario. (Hol169\Kyle and Hol169\Jeff)
  2. Create two domain user accounts used to start the MSSQLServer service. First, create Hol169\SQLSvcBoulder for the Boulder SQL Server.
  3. Next, create Hol169\SQLSvcPhilly for the Philly SQL Server.

DBA Setup Steps for Sample Scenario

  1. Create a linked server definition on the Boulder SQL Server.
  2. Create logins Hol169\Kyle and Hol169\Jeff, which can connect to both Boulder and Philly SQL Servers.
  3.  Install the sample database Adventureworks2008 on the Philly server and grant appropriate permissions to Kyle and Jeff (Sample scripts are provided below.)
  4. Verify that Kyle and Jeff can successfully establish trusted connections to both the Boulder server and the Philly server.
  5. On the Boulder server, grant View Server State permission to Kyle and Jeff. This will enable them to run ConnectBoulder.vbs.
  6. .Grant SELECT permission for the Sales.vStoreWithContacts view in the Adventureworks2008 sample database to Jeff and Kyle.

Note: The View Server State permission granted on the Boulder server is not required for the linked server to work. It’s only required to query the sys.dm_exec_connections DMV to verify the method the client used to authenticate the connection. The script ConnectBoulder.vbs queries this DMV.

Here is the Transact-SQL script required to grant permission to Kyle and Jeff to the Adventureworks2008 database and permission to select from the Sales.Store table:

USE [Adventureworks2008]
GO
CREATE USER [HOL169\Jeff] FOR LOGIN [HOL169\Jeff]
GO
USE [Adventureworks2008]
GO
ALTER USER [HOL169\Jeff] WITH DEFAULT_SCHEMA=[Sales]
GO
USE [Adventureworks2008]
GO
CREATE USER [HOL169\Kyle] FOR LOGIN [HOL169\Kyle]
GO
USE [Adventureworks2008]
GO
ALTER USER [HOL169\Kyle] WITH DEFAULT_SCHEMA=[Sales]
GO
grant select on Sales.vStoreWithContacts to [hol169\Kyle]
grant select on Sales.vStoreWithContacts to [hol169\Jeff]

Here is the Transact-SQL script that creates the linked server on the Boulder server:

GO
EXEC master.dbo.sp_addlinkedserver @server = N'PHILLY', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PHILLY', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'PHILLY', @locallogin = NULL , @useself = N'True'
GO

The following server configurations will be used.

Scenario 1: Both remote servers have SQL Server service running under Network Service account.

Computer Sammamish Boulder Philly

SQL Server service account

Acting as client

Network service

Network service

SPN set

N/A

Auto

Auto

Constrained Delegation set

N/A

Yes

No

Table 1

Action Items for the Active Directory Domain Administrator for Scenario 1

  1. To configure constrained delegation, open Active Directory Users and Computers.
  2. In Computer Container, right-click Boulder, and then click Properties, because SQL Server is running under the network service account.
  3. Click the Delegation tab.
  4. Click Trust this user for delegation to specified services only.
  5. Click Use Kerberos Only.
  6. Click Add, and then click Users and Computers.
  7. Type the name of the Philly server, Click OK, and then select both MSSQLSvc services, Click OK.
  8. The lower part of the dialog box should appear with the entries shown below; click OK:
Service type User or Computer Port

MSSQLSvc

PHILLY.hol169.local

1433

MSSQLSvc

PHILLY.hol169.local

Table 2: Services, to which this account can present delegated credentials:

Action Items for the DBA for Scenario 1

  1. Configure a linked server on the Boulder server to connect to the Philly service with the security set to Be made using the login’s current security context.
  2. Enable auditing for successful and failed logins.
  3. Restart the SQL Server service.
  4. Execute ConnectPhilly.vbs. To execute the sample code, go to a DOS command window and execute: cscript ConnectPhilly.vbs 
  5. ConnectPhilly.vbs should execute and return data back.
  6. Review the SQL Server error log to verify that a trusted Windows connection made was from Kyle or Jeff.

You should see the following results if the ConnectPhilly.vbs query was successful.

C:\>cscript connectPhilly.vbs
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.
292 Next-Door Bike Store
294 Professional Sales and Service
296 Riders Company
298 The Bike Mechanics
Computer Sammamish Boulder Philly

Service account

Acting as client

Hol169\SQLSvcBoulder

Hol169\SQLSvcPhilly

SPN Set

N/A

Yes

Yes

Constrained Delegation set

N/A

Yes

No

Table 3

Action Items for Active Directory Domain Administrator for Scenario 2:

  1. Create the following SPNs in Active Directory Domain Services: MSSQLSvc/Boulder.hol169.local:1433; MSSQLSvc/Boulder.hol169.local; MSSQLSvc/Philly.hol169.local:1433; MSSQLSvc/Philly.hol169.local
  2. You can use Setspn.exe to do this, using the following syntax: setspn –a MSSQLSvc/Boulder.hol169.local:1433 SQLSvcBoulder
  3. To configure constrained delegation, open Active Directory Users and Computers.
  4. In the User Container, click SQLSvcBoulder.
  5. Right-click, click Properties, and then click the Delegation tab.
  6. Click Trust this user for delegation to specified services only.
  7. Click Use Kerberos Only.
  8. Click Add, and then click Users and Computers.
  9. Type the name of the service running the Philly server (SQLSvcPhilly), click OK and then select both MSSQLSvc services.
  10. Click OK.

The lower part of the dialog box should appear with the following entries (see figure 4):

Service type User or computer Port

MSSQLSvc

Philly.hol169.local

1433

MSSQLSvc

Philly.hol169.local

Table 4: Services to which this account can present delegated credentials:

Figure 4: SQLSvcBoulder Properties dialog box

Reviewing the properties of the SQLSvcBoulder account using ADSIEdit will show you that the msDS-AllowedtoDelegateTo properties are now populated with the service account MSSQLSvc/Philly.hol169.local and MSSQLSvc/Philly.hol169.local. (Figure 5)

Figure 5: Multi-valued String Editor

Action Items for DBA for Scenario 2:

  1. If you did not do so in the previous scenario, configure a linked server on Boulder to connect to the Philly service with the security set to Be made using the login’s current security context.
  2. Enable auditing for successful and failed logins.
  3. Restart the SQL Server service on both servers.
  4. Execute the script ConnectPhilly.vbs. To execute the sample code, go to a DOS command window and execute: cscript ConnectPhilly.vbs 
  5. ConnectPhilly.vbs should execute and return data back. 
  6. Review the SQL Server error log to verify that the connection made was from Kyle or Jeff.

You should see the following results if the ConnectPhilly.vbs query was successful.

C:\>cscript connectPhilly.vbs
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.
292 Next-Door Bike Store
294 Professional Sales and Service
296 Riders Company
298 The Bike Mechanics

Conclusion

SQL Server 2008 offers new SPN options to register SQL Server 2008 SPNs, which can simplify the configuration of Kerberos constrained delegation. The new SPN formats MSSQLSvc/FQDN and MSSQLSvc/FQDN:Instance Name enable clients to authenticate using Kerberos over named pipes and shared memory. The new instance format allows the SPN to remain valid even if the port number changes.

There are also new ServerSPN keywords for ODBC and OLE DB connections in the new SQL Server Native Client (SNAC) which allow the client to establish Kerberos connection to SQL Server 2008. The new connection attribute “Server SPN” enables the client application to specify the service account in a UPN format user@domain if the SQL Server is running under a domain user account. If the SQL Server is running under the network service account, the client can send the machine account machine$@domain as the “Server SPN”.

However, because Kerberos constrained delegation requires an SPN to be set in active directory, a client connection cannot rely solely on passing the “Server SPN” to authenticate across a SQL Server linked server using Windows Authentication. The preferred method of configuring SQL Server service accounts is to use a Domain User account.

Configuring linked servers in SQL Server with Windows Authentication does take some effort on behalf of the Active Directory administrator and the SQL Server DBA. Having the knowledge of the required steps to enable Kerberos constrained delegation for the DBA and the Active Directory administrator will simplify the process.

For more information:

https://www.microsoft.com/sqlserver/: SQL Server Web site

https://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter

https://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter 

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

  • Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
  • Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release.

Send feedback.