# 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: . ## Create the SPNs It is recommended to use the Kerberos Configuration Manager utility. This can be downloaded from [here](https://www.microsoft.com/en-us/download/details.aspx?id=39046). {% hint style="info" %} 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. {% endhint %} 1. 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: 1. Navigate to **C:\Program Files\Microsoft\Kerberos** Configuration Manager for SQL Server and launch **KerberosConfigMgr.exe**. 2. Click **Connect** from the menu and then click **Connect** without entering any info. 3. Select the **SPN** tab. 4. Scroll all the way to the right and notice the Required SPN and Status columns. {% hint style="info" %} These next steps must be done with a Domain Admin account. They do not have to be executed on the database server. {% endhint %} 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 the following steps when not using Kerberos Configuration Manager: 1. Open an administrator command prompt and type: ```shell Setspn -l \ ``` For example, executing this command will return the following: ```shell C:\Users\administrator>setspn -l \sqlservice Registered ServicePrincipalNames for CN=SQLService,OU=Accounts,DC=,DC=lab: MSSQLSvc/SQLSERVER..lab:1433 MSSQLSvc/SQLSERVER..lab ``` {% hint style="info" %} These next steps must be done with a Domain Admin account. They do not have to be executed on the database server. {% endhint %} 1. Create the SPNs: ```shell setspn -S MSSQLSvc/ \ setspn -S MSSQLSvc/ \ setspn -S MSSQLSvc/: \ ``` 2. Rerun the following command and confirm the SPNs exist correctly: ```shell setspn -l \ ``` * 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 {% hint style="info" %} 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](https://learn.microsoft.com/en-us/windows-server/administration/install-remote-server-administration-tools?tabs=server-manager%2Cdesktop-experience\&pivots=windows-server-2022). {% endhint %} After the SPNs have been setup: 1. Open Active Directory Users and Computers. 2. Find the service account. 3. Select **Properties** and select the **Attribute Editor** tab. 4. 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 by running the following command: `setspn -L KAIBAB\AdaptivaSQLServer` ![](https://1238239958-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzBcNZqjCKqgdpA83eD4o%2Fuploads%2Fgit-blob-20f2a8976b760733b61bcf855f3a46bcfe411e34%2Fverify-spns-command-terminal.png?alt=media) ## 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. {% hint style="info" %} These next steps must be done with a Domain Admin account. {% endhint %} 1. Open **Active Directory Users and Computers**. 2. Select the **Accounts** folder. 3. Find the SQL Service account used right-click and select **Properties**. ![](https://1238239958-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzBcNZqjCKqgdpA83eD4o%2Fuploads%2Fgit-blob-7e514b304d936803b5fbfa4dc97345fc80929b4a%2Fselect-account.png?alt=media) 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**. ![](https://1238239958-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzBcNZqjCKqgdpA83eD4o%2Fuploads%2Fgit-blob-ce3caf2a7286ca65d5aae869f6674f686e58df83%2Fadd-services.png?alt=media) ![](https://1238239958-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzBcNZqjCKqgdpA83eD4o%2Fuploads%2Fgit-blob-8a5f04c518d1bad8e82492b12f5fd0841d1a4a0c%2Fserver-properties-delegation.png?alt=media) 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 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 enter the following: Linked server: `` Select **SQL Server**. 3. 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. 4. On the **Server Options** page, set the **Collation Compatible** field to **True**. ![](https://1238239958-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzBcNZqjCKqgdpA83eD4o%2Fuploads%2Fgit-blob-972c3b0941468488bc68e58ad706a721f30cbcc7%2Fimage1.png?alt=media) 5. 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.