SPNs and delegation
With more companies moving to dedicated SQL Servers or SQL Always On Availability Groups, this section is intended to help get the SPNs (Service Principle Name) configured and Kerberos delegations set up. The guidelines in this article are also helpful: http://support.microsoft.com/kb/2443457.
Create the SPNs
It is recommended to use the Kerberos Configuration Manager utility. This can be downloaded from here.
NOTE: 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.
Log into the SQL Database server using an account in the local Administrators group
Optionally, but recommended, install the Kerberos Configuration Manager utility
Use the following steps in the Kerberos Configuration Manager
Navigate to C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server and launch KerberosConfigMgr.exe
Click Connect from the menu and then click Connect without entering any info
Select the SPN tab
Scroll all the way to the right and notice the Required SPN and Status columns
NOTE: These next steps must be done with a Domain Admin account. They do not have to be executed on the database server.
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.
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 the following steps when not using Kerberos Configuration Manager
Open an administrator 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
NOTE: These next steps must be done with a Domain Admin account. They do not have to be executed on the database server.
Create the SPNs:
Setspn -S MSSQLSvc/<NetBiosName\> <domain>\<serviceaccount> Setspn -S MSSQLSvc/<FQDN\> <domain>\<serviceaccount> Setspn -S MSSQLSvc/<FQDN\>:<port> <domain>\<serviceaccount>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.
After the SPNs have been setup, open Active Directory Users and Computers. Find the service account, select Properties and select the Attribute Editor tab. Find the servicePrincipalName property and open it. Confirm the SPNs are listed for the server(s) for both with and without the FQDN with port numbers.
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.
NOTE: These next steps must be done with a Domain Admin account.
Open Active Directory Users and Computers
Find the SQL Service account used and select Properties
Select the Delegation tab.
NOTE: If SPNs have not been setup correctly, then this tab will not be available
Select Trust this user for delegation to specified services only and select Use Kerberos only
Click on Add...
Click on Users or Computers...
Enter the Service account name and click Check Names, OK
In the list of Available services, select all the entries with MSSQLSvc for the SQL Database server as appropriate and click OK
Click OK to close the Properties box
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: Click on the Delegation tab to confirm the delegations have been setup correctly: The details will state: No obvious delegation issues.
Log onto a server Different than SQL database server, but that has SQL Management Studio installed.
Connect to the remote SQL Database server (Connect, Database Engine, enter the server name)
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=@@spidThe result back should be Kerberos
Create the Linked Servers
When the Adaptiva database is on a different SQL Server then the ConfigMgr database, the Linked Servers must be manually created.
In SQL Management Studio with a connection to the ConfigMgr SQL database server, expand Server Objects, Linked Servers
Right-click on Linked Servers and enter the following:
Linked server: <AdaptivaSQLServerFQDN>
Select SQL Server
On the Security page, select Be made using the login's current security context.
IMPORTANT: The account used must not have the setting enabled. Account is sensitive and cannot be delegated.
On the Server Options page, set the Collation Compatible field to True.
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.
In SQL Server Management Studio, expand Server Objects, Linked Servers, FQDN of the other server, catalogs
Notice the list of databases returned from the other server
Expand the appropriate database and confirm that tables and views can be seen.
Last updated
Was this helpful?