SPNs and delegation

In order to support moving to dedicated SQL Servers or SQL Always On Availability Groups, you will need to configure SPNs (Service Principle Name) and Kerberos delegations in your environment. This article is helpful to understand the principles of SQL Server connectivity: http://support.microsoft.com/kb/2443457.

Create the SPNs

For Windows 2019 or earlier, we recommend using the Kerberos Configuration Manager utility, which can be downloaded from Microsoft. This tools is not supported beyond Windows 2019 and SQL Server 2019.

This utility may not work if the SQL Server is in a different domain than the SQL Service accounts or if there are many groups added to the local Administrators group.

Use Kerberos Configuration Manager to set SPN's

Only supported on Windows 2019 and SQL Server 2019 or earlier.

Use the following steps in Kerberos Configuration Manager.

1. Log into the SQL Database server using an account in the local Administrators group.

2. Install the Kerberos Configuration Manager utility.

3. Navigate to C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server and launch KerberosConfigMgr.exe.

4. Click Connect from the menu and then click Connect without entering any info.

5. Select the SPN tab.

6. Scroll all the way to the right and notice the Required SPN and Status columns.

These next steps must be done with a Domain Admin account. They do not have to be executed on the database server.

1. If able to modify the service accounts, click on Fix All (or do them individually) otherwise, click Generate All, enter a file name and provide that script to an administrator with the appropriate permissions.

2. After the fix script(s) has been run, to confirm the SPNs have been created successfully, click on Refresh. Scroll to the right to confirm the status is Good for all rows.

Use setspn command-line tool to set SPN's

Use the setstpn command line tool to create the SPN's.

1. Log into the SQL Database server using an account in the local Administrators group.

2. Open an administrative command prompt and type:

   setspn -L <domain>\<serviceaccount>

   For example, executing this command will return the following:

   C:\Users\administrator>setspn -L <domain>\sqlservice
   Registered ServicePrincipalNames for CN=SQLService,OU=Accounts,DC=<DOMAIN>,DC=lab:
   MSSQLSvc/SQLSERVER.<DOMAIN>.lab:1433
   MSSQLSvc/SQLSERVER.<DOMAIN>.lab

These next steps must be done with a Domain Admin account. They do not have to be executed on the database server.

1. Create the SPNs:

   setspn -S MSSQLSvc/<NetBiosName> <domain>\<serviceaccount>
   
   setspn -S MSSQLSvc/<FQDN> <domain>\<serviceaccount>
   
   setspn -S MSSQLSvc/<FQDN>:<port> <domain>\<serviceaccount>

2. Rerun the following command and confirm the SPNs exist correctly:

   setspn -L <domain>\<serviceaccount>

   • If using with SQL Always On Availability Groups, then repeat the above steps on database server #2. This should ALSO be done with the AG Listener.

Verify SPN setup

Remote Server Administration Tools (RSAT) must be installed in order to use Active Directory Users and Computers. For more information, please see Microsoft's documentation guide: Install and manage Remote Server Administration Tools in Windows.

After the SPNs have been setup:

1. Open Active Directory Users and Computers.

2. Click View | Advanced Features. This is required to see the Attribute Editor property of the service account object.

3. Select the Accounts folder.

4. Right-click the service account and select Properties.

5. Select the Attribute Editor tab. This is only visible with the Advanced Features view enabled.

6. Find the servicePrincipalName property and open it.

Confirm the SPNs are listed for the server(s) FQDN both with and without the port number.

You can also use the setspn command line tool to view these attributes by running the following command:

setspn -L KAIBAB\AdaptivaSQLServer

Delegate Kerberos authentication

Because the SQL Server is on a different server than the Adaptiva Server it is required to setup Kerberos trust delegation. The following steps cannot be done if the SPNs have not been setup correctly.

These next steps must be done with a Domain Admin account.

1. Open Active Directory Users and Computers.

2. Select the Accounts folder.

3. Right-click the service account and select Properties.

   
4. Select the Delegation tab.

   • If SPNs have not been setup correctly, then this tab will not be available.

5. Select Trust this user for delegation to specified services only and select Use Kerberos only.

6. Click on Add....

7. Click on Users or Computers....

8. Enter the Service account name and click Check Names then click OK.

9. In the list of Available services, select all the entries with MSSQLSvc for the SQL Database server as appropriate and click OK.

   

   You must select the entries for them to be added.

   
10. Click OK to close the Properties box.

11. Log on to the SQL Server(s) and using SQL Server Configuration Manager, restart the SQL Server service (or Restart the server).

Confirm the Configuration

Once the SPNs and the Kerberos delegations have been configured, use SQL Management Studio to confirm the connection properties (Kerberos Configuration Manager can also be used):

1. Click on the Delegation tab to confirm the delegations have been setup correctly

   • The details will state - No obvious delegation issues.

2. Log onto a different server than SQL database server that has SQL Management Studio installed.

3. Connect to the remote SQL Database server Connect > Database Engine > enter the server name.

4. Open a New Query Window on the remote database server and Execute the following query:

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

5. The result back should be Kerberos.

Create the Linked Servers

Ensure that TCP/IP connections are enabled in SQL Configuration Manager.

When the Adaptiva database is on a different SQL Server then the ConfigMgr database, the Linked Servers must be manually created.

1. In SQL Management Studio with a connection to the ConfigMgr SQL database server, expand Server Objects, Linked Servers

2. Right-click on Linked Servers and click New Linked Server.

3. On the New Linked Server page, enter the following:

   Linked server: <AdaptivaSQLServerFQDN>

   Select SQL Server.

4. On the Security page, select Be made using the login's current security context.

   The account used must NOT have the setting enabled. Account is sensitive and cannot be delegated.

5. On the Server Options page, set the Collation Compatible field to True.

   
6. Click OK.

   • If SQL Always On Availability Groups are being used, repeat the above steps connected to database server #2 and connected to the Availability Group Listener.

Now repeat the above steps connecting to the Adaptiva SQL database server and creating a Linked Server to the ConfigMgr SQL Server FQDN.

Test the Linked Servers

It is important to confirm the linked servers are able to access the data on the other server.

1. In SQL Server Management Studio, expand Server Objects, Linked Servers, FQDN of the other server, catalogs.

2. Notice the list of databases returned from the other server.

3. Expand the appropriate database and confirm that tables and views can be seen.