# 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: . ## Create the SPNs For Windows 2019 or earlier, we recommend using the Kerberos Configuration Manager utility, which can be downloaded from [Microsoft](https://www.microsoft.com/en-us/download/details.aspx?id=39046). This tools is not supported beyond Windows 2019 and SQL Server 2019. {% 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 %} ### 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. {% 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 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: ```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. 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` ![](/files/tzAEnW2Lc2pVoeeXbEqZ) ## 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. Right-click the service account and select **Properties**. ![](/files/M3zPE7OdeABb0VwkcUwe) 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**. ![](/files/dDgCxBvK16G4B6Tf88dT) You must select the entries for them to be added. ![](/files/nePN4z6a1rvlct5YTULF) 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: `` 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**. ![](/files/eguO2aXua8FB5210j52A) 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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.adaptiva.com/platform-install/additional-configuration/platform-spns-delegation.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.