How to set up a Kerberos Authentication Scenario with SQL Server Linked Servers

Let’s say you’ve got a SQL Server (KIRK) containing a linked server against another SQL Server (SCOTTY)  and you’re connecting the KIRK from remote client machine (SPOCK) using SQL Server Management Studio and receiving the error message below when you executed a query involving the linked server :

 “Error 18456: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON'” error message.

This error message indicates that the required credentials are not “forwarded” to the end SQL Server SCOTTY  . We call this issues as “Double hop” issues and the only way to get this to work is using Kerberos Authentication in the scenario.

We already have a KB article 319723 titled “How to use Kerberos Authentication in SQL Server” and explains the problem with an example which is having IIS in the middle. The picture is like that for the example in the KB article :

Client Machine (running Internet Explorer) –> IIS Machine (hosting ASP Pages) –> SQL Server

Though the idea is same, it’s a little bit different for our scenario. First of all, i need to give some more details about my environment :

• FARUKCDC is my Active Directory machine and my domain is farukcorp.com
• SPOCK.farukcorp.com is my client machine and it has got only SQL Server 2005 Client Tools to be able to use SQL Server Management Studio

The current logged on user to the machine is FARUKCORP\Spockuser

• KIRK.farukcorp.com is my SQL Server in the middle and it has SQL Server 2005 with a default instance listening on TCP 1433. The service account running the SQL Server Service is FARUKCORP\Kirkuser

The current logged on user to the machine is FARUKCORP\Kirkuser again.

FARUKCORP\kirkuser is also added to the local Administrators group on KIRK machine

• SCOTTY.farukcorp.com is my end SQL Server and it has SQL Server 2005 with a default instance listening on TCP 1433 again. The service account running the SQL Server is FARUKCORP\scottyuser

The current logged on user to the machine is FARUKCORP\scottyuser again

FARUKCORP\scottyuser is also added to the local Administrators group on SCOTT machine

 

Here’s the steps to reproduce the issue :

• 1) The FARUKCORP\Spockuser who is logged on to the machine runs Management Studio
• 2) Connects to SQL Server named KIRK
• 3) Tries to retrieve data from a linked server named “linkedserver” in KIRK
• 4) The “linked server” is linked to the SQL Server named SCOTTY
• 5) User executes the query below

 

select * from linkedserver.master.dbo.sysdatabases

 

and receives the error message :

 

“Error 18456: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON'”

 

Here are the steps that you’ll need to check/apply to get this Kerberos delegation scenario to work :

1.) Testing  the connectivity without Kerberos delegation :

Because we want the user logged on to the SPOCK machine to access the database in SCOTTY, we need to be sure that we can connect from

SPOCK à KIRK and SPOCK à SCOTTY. After being sure of that, we’ll set the delegation setting later

 

1.1.) SPOCKàKIRK connectivity test :

I granted access to FARUKCORP\Spockuser in KIRK SQL Server. After that, I opened Management Studio on SPOCK machine and see that I can connect to KIRK machine successfully.

 

1.2.) SPOCKàSCOTTY connectivity test :

I granted access to FARUKCORP\Spockuser in SCOTTY  SQL Server. After that, I opened Management Studio on SPOCK machine and see that I can connect to SCOTTY machine successfully.

 

2.) Setting the Kerberos Delegation for the computers (KIRK , SCOTTY):

I logged onto the FARUKCDC domain controller machine logged in with domain admin account and opened “Active Directory Users and Computers“. I followed the following steps to enable “Trust” for the computers :

• a. Under my domain farukcorp.com, I expanded Computers and right mouse click onto SCOTTY machine and selected Properties

• b. Switched to “Delegation” tab and selected “Trust this computer for delegation to any service (Kerberos only)“

• c. Followed the step b for the SCOTTY machine too :

 

3.)  Setting the SPNs for the computers (KIRK, SCOTTY) :

If the SQL Server Services was running with  LOCAL SYSTEM or NETWORK SERVICE account, the SQL Server Services would create the required for its services automatically. Because we’re running the SQL Servers services with our specified domain account (like FARUKCORP\Kirkuser and FARUKCORP\scottyuser) we need the set the SPNs manually. I followed the steps below to add the SPNs for the computers :

To download the SetSPN utility, visit the following Microsoft Web site: http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&displaylang=en&WT.mc_id=soc-n-[TR]-loc-[Services]-[farukc]

• a. Opened a command shell by typing “cmd” from the Start/Run menu

 

• b. I issued the command below and got the results for the HOST SPNs for KIRK :

 

C:\Documents and Settings\Administrator>setspn -l kirk

Registered ServicePrincipalNames for CN=KIRK,CN=Computers,DC=farukcorp,DC=com:

    HOST/KIRK

    HOST/KIRK.farukcorp.com

• c. I issued the command below and got the results for the HOST SPNs for SCOTTY:

 

C:\Documents and Settings\Administrator>setspn -l scotty

Registered ServicePrincipalNames for CN=SCOTTY,CN=Computers,DC=farukcorp,DC=com:

    HOST/SCOTTY

    HOST/SCOTTY.farukcorp.com

 

• d. I issued the command below and got the results for the HOST SPNs :

 

C:\Documents and Settings\Administrator>setspn -l spock

Registered ServicePrincipalNames for CN=SPOCK,CN=Computers,DC=farukcorp,DC=com:

    HOST/SPOCK

    HOST/SPOCK.farukcorp.com

 

• e. I got that the HOST SPNs are set but the MSSQLSvc SPNs are NOT set for the KIRK and SCOTTY computers

 

• f. I issued the commands below to add the MSSQLSvc SPN for KIRK machine :
• 1) To add:

C:\Documents and Settings\Administrator>setspn -A MSSQLSvc/KIRK.farukcorp.com farukcorp\kirkuser

Registering ServicePrincipalNames for CN=Kirkuser,CN=Users,DC=farukcorp,DC=com

        MSSQLSvc/KIRK.farukcorp.com

Updated object

 

• 2) To verify :

C:\Documents and Settings\Administrator>setspn -l farukcorp\kirkuser

Registered ServicePrincipalNames for CN=Kirkuser,CN=Users,DC=farukcorp,DC=com:

    MSSQLSvc/KIRK.farukcorp.com

 

• 3) To add :

C:\Documents and Settings\Administrator>setspn -A MSSQLSvc/KIRK.farukcorp.com:1433 farukcorp\kirkuser

Registering ServicePrincipalNames for CN=Kirkuser,CN=Users,DC=farukcorp,DC=com

        MSSQLSvc/KIRK.farukcorp.com:1433

Updated object

 

• 4) To verify :

C:\Documents and Settings\Administrator>setspn -l farukcorp\kirkuser

Registered ServicePrincipalNames for CN=Kirkuser,CN=Users,DC=farukcorp,DC=com:

    MSSQLSvc/KIRK.farukcorp.com:1433

    MSSQLSvc/KIRK.farukcorp.com

 

                       You need to see both of these yellow colored lines for farukcorp\kirkuser  user.

 

• g. I issued the same commands in step f, like below to add the MSSQLSvc SPN for SCOTTY machine :

 

• 1) To add:

C:\Documents and Settings\Administrator>setspn -A MSSQLSvc/SCOTTY.farukcorp.com:1433 farukcorp\scottyuser

Registering ServicePrincipalNames for CN=scottyuser,CN=Users,DC=farukcorp,DC=com

        MSSQLSvc/SCOTTY.farukcorp.com:1433

Updated object

 

• 2) To verify :

 

C:\Documents and Settings\Administrator>setspn -l farukcorp\scottyuser

Registered ServicePrincipalNames for CN=scottyuser,CN=Users,DC=farukcorp,DC=com:

    MSSQLSvc/SCOTTY.farukcorp.com:1433

 

• 3) To add :

C:\Documents and Settings\Administrator>setspn -A MSSQLSvc/SCOTTY.farukcorp.com farukcorp\scottyuser

Registering ServicePrincipalNames for CN=scottyuser,CN=Users,DC=farukcorp,DC=com

        MSSQLSvc/SCOTTY.farukcorp.com

Updated object

• 4) To verify :

C:\Documents and Settings\Administrator>setspn -l farukcorp\scottyuser

Registered ServicePrincipalNames for CN=scottyuser,CN=Users,DC=farukcorp,DC=com:

    MSSQLSvc/SCOTTY.farukcorp.com:1433

    MSSQLSvc/SCOTTY.farukcorp.com

You need to see both of these yellow colored lines for farukcorp\scottyuser user.

 

4.)  Setting the permissions to read/write servicePrincipalNames for the FARUKCORP\Kirkuser, FARUKCORP\scottyuser domain accounts :

I followed the steps below In my FARUKCDC Domain Controller machine :

1.	Click Start, click Run, type Adsiedit.msc, and then click OK.
2.	In the ADSI Edit snap-in, expand Domain [farukcorp.com], expand DC= farukcorp.com, expand CN=Users, right-click CN= Kirkuser, and then click Properties.
3.	In the CN= Kirkuser Properties dialog box, click the Security tab.
4.	On the Security tab, click Advanced.
5.	In the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries. If SELF is not listed, click Add, and then add SELF.
6.	Under Permission entries, click SELF, and then click Edit.
7.	In the Permission Entry dialog box, click the Properties tab.
8.	On the Properties tab, click This object only in the Apply onto list, and then make sure that the check boxes for the following permissions are selected under Permissions: Read servicePrincipalName Write servicePrincipalName
9.	Click OK three times, and then exit the ADSI Edit snap-in.

 

  10. Repeat the same 9 steps above for scottyuser also.

 

5.) Setting the Kerberos Delegation for the domain users running the SQL Server accounts (FARUKCORP\Kirkuser and FARUKCORP\scottyuser) :

Again FARUKCDC domain controller machine, logged in with domain admin account and opened “Active Directory Users and Computers“. I followed the following steps to enable “Trust this user for delegation to any service (Kerberos only)” for the users  this time:

 

• a. Under my domain farukcorp.com, I expanded Users and right mouse click onto Kirkuser user and selected Properties

• b. Switched to “Delegation” tab and selected “Trust this user for delegation to any service (Kerberos only)“”

 

• c. Followed the step b for the scottyuser user too :

 

6.) Setting up the linked server

 

I switched to the KIRK machine, logged on to the KIRK SQL Server Engine with Windows Authentication (using FARUKCORP\Kirkuser account)  and opened a SQL Query Window, typed the SQL statements below to set up the linked server :

 

• a. Drop the linked server if exists :

EXEC sp_dropserver ‘LinkedServer’

 

• b. Create the linked server named “LinkedServer” :
• c.

EXEC sp_addlinkedserver @server=’LinkedServer’,

                                    @srvproduct=”,

                                    @provider=’SQLNCLI’,

                                    @datasrc=’scotty.farukcorp.com’,

                                    @provstr=’Integrated Security=SSPI;’

 

• d. Add the login info :

EXEC sp_addlinkedsrvlogin ‘LinkedServer’, true

 

• e. Check if the “LinkedServer” exists in sys.servers :

select * from sys.servers

 

• f. Check if the “LinkedServer” works from here (You should be able to retrieve the sysdatabases info from SCOTTY) :

 

select * from linkedserver.master.dbo.sysdatabases

 

7.) Testing the double hop from SPOCK machine :

I logged into the SPOCK machine with FARUKCORP\spockuser, opened Management Studio and connected to KIRK SQL Server Database engine with current logged on user (FARUKCORP\spockuser)

Opened a query windows and executed the SQL Statement below :

select * from linkedserver.master.dbo.sysdatabases

and successfully received the results from the SPOCK machine.