Contents
What is a SQL Server SPN?
In simple terms, a SPN is a unique identifier for a Windows service and a service account running that service. SPNs are used for Kerberos authentication. Double hop issues are when you have a client connect to one SQL Server and that server needs to pull data from another SQL Server.
How do I get SPN for SQL Server?
In Command Line enter the following command: setspn -L > and press enter. Next, you need to look for registered ServicePrincipalName to ensure that a valid SPN has been created for the SQL Server.
How do I set up SPN?
The steps to follow to configure an SPN account for an application server are:
- Assign the SPN to the Active Directory account using the setspn command.
- Repeat this command for any number of SPN to the same account.
- Generate a keytab file for the user account.
How do I find missing SPN?
- Run the following command to remove the misplaced SPN: Command Syntax: setspn –D
- On the client machine, either logoff and log back in or clear the Kerberos ticket cache by running the following command: klist purge.
- Try reconnecting to SQL Server with your client application.
How do you create an SPN?
SPNs are registered for built-in accounts automatically. However, when you run a service under a domain user account, you must manually register the SPN for the account you want to use. To create an SPN, you can use the SetSPN command line utility.
Can a SPN be registered on SQL Server?
To enable the SPN to be registered automatically on SQL Server startup the service must be running under the “Local System” or “Network Service” accounts (not recommended), under a domain administrator account, or under an account that has permissions to register an SPN.
Do you need permission to create a SPN?
By default, the machine accounts have permission to modify themselves. If we change this over to a Domain User Account for the SQL Service account, things change a little. By default a Domain User does not have the permission required to create the SPN.
How to verify SPN for SQL Server Authentication?
Execute the below TSQL Query to verify authentication used by SQL Server Connections. All client and servers should be joined to a domain. If the clients and servers are in different domains then a two-way trust must be setup between domains. SPN must be successfully registered for the SQL Server Service to be identified on the network.
When do you need to register a SPN for a service?
If you install multiple instances of a service on computers throughout a forest, each instance must have its own SPN. For example, an SPN always includes the name of the host computer on which the service instance is running, so a service instance might register an SPN for each name or alias of its host.