Service Principal Name (SPN) in SQL Server

https://www.sqldbachamps.
com
Praveen Madupu - +91 98661 30093
Sr SQL Server DBA, Dubai
[email protected]
Service Principal Name (SPN) in SQL Server is a unique identifier for a service instance, used to associate a
service with a domain account. SPNs are essential for enabling Kerberos authentication, which allows clients to
authenticate the SQL Server service without needing to pass passwords over the network.
Kerberos is a secure and efficient authentication protocol often used in environments where performance,
security, and Single Sign-On (SSO) are priorities. Properly configuring an SPN is necessary to enable this
authentication protocol in SQL Server.
Steps to Set Up an SPN in SQL Server
Here is a detailed, step-by-step process for setting up an SPN:
1. Pre-requisites
● Domain Account: The SQL Server service should be running under a domain account (not a local system
or network service account) because SPNs are registered in Active Directory.
● Permissions: The domain account used for running SQL Server must have permission to register SPNs
or the person configuring the SPN must have domain administrator rights.
● Fully Qualified Domain Name (FQDN): The SPN needs to be registered with the FQDN or IP address of
the server where SQL Server is installed.
https://www.sqldbachamps.com
2. Determine the SQL Server Service Account
To check the service account under which SQL Server is running:
● Open SQL Server Configuration Manager.

● Go to SQL Server Services.
● Look at the "Log On As" column to determine the domain account running the SQL Server service. This
will be in the format DOMAIN\account_name if the service is running under a domain account.
For example:
● DOMAIN\SQLServiceAccount
3. Identify the SQL Server Instance and Port
You need to know the SQL Server instance name and port number to configure the SPN. For the default instance,
SQL Server uses port 1433, but named instances may use dynamic ports.
● Check the Instance Name and Port:

○ For the default instance, use the hostname (or FQDN).
○ For a named instance, use HOSTNAME\INSTANCENAME.
[email protected]
○ To check the port used by the SQL Server, run the following query in SSMS:
SELECT local_net_address, local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
4. SPN Format
The SPN format for SQL Server differs depending on the protocol (MSSQLSvc) and whether you are using a
default or named instance. The two common formats are:
● Default Instance:
MSSQLSvc/FQDN:1433
MSSQLSvc/HOSTNAME:1433
● Named Instance:
MSSQLSvc/FQDN:InstancePort
MSSQLSvc/HOSTNAME:InstancePort
For example, if your SQL Server is running on SQLServer01 and it’s using port 1433 for a default instance, the
SPN would look like:
MSSQLSvc/SQLServer01:1433
MSSQLSvc/SQLServer01.domain.com:1433
If it’s a named instance SQLInstance01 running on port 1435, it would be:
MSSQLSvc/SQLServer01:1435
MSSQLSvc/SQLServer01.domain.com:1435
[email protected]
5. Set the SPN Using setspn Command
Use the setspn command to create, list, or delete SPNs. The syntax for creating SPNs is:
setspn -S MSSQLSvc/FQDN:Port Domain\SQLServiceAccount
a. SPN for a Default Instance (Port 1433)
Run the following command (replace placeholders with actual values):
setspn -S MSSQLSvc/SQLServer01:1433 DOMAIN\SQLServiceAccount
setspn -S MSSQLSvc/SQLServer01.domain.com:1433 DOMAIN\SQLServiceAccount
b. SPN for a Named Instance
For a named instance running on a specific port (e.g., 1435):
setspn -S MSSQLSvc/SQLServer01:1435 DOMAIN\SQLServiceAccount
setspn -S MSSQLSvc/SQLServer01.domain.com:1435 DOMAIN\SQLServiceAccount
● -S ensures that the SPN is not duplicated (it will fail if the SPN already exists).
● You can use -A instead of -S if you want to add the SPN without checking for duplicates, but this is not
recommended.
6. Verify the SPN
To verify that the SPN was set correctly, use the setspn -L command:
setspn -L DOMAIN\SQLServiceAccount
This will list all SPNs registered for the SQL Server service account. Ensure that the SPNs for both the FQDN and
the hostname are listed with the correct port numbers.
[email protected]
7. Granting the SQL Service Account Permissions to Register SPNs
If you do not want to manually set SPNs each time or want to automate it, you can grant the SQL Server service
account the permission to register its own SPNs dynamically.
a. Using Active Directory Users and Computers
1. Open Active Directory Users and Computers.

2. Locate the SQL Server service account (DOMAIN\SQLServiceAccount).
3. Right-click the account and select Properties.
4. Go to the Delegation tab.
5. Select Trust this user for delegation to any service (Kerberos only).
Alternatively, for a more secure configuration, you can specify services and allow delegation for those
specific services only.
b. Using Group Policy
You can use Group Policy to grant the service account permissions to register SPNs. Use the "Read/write
servicePrincipalName" permission for the SQL Server service account.
8. Troubleshooting
● Duplicate SPNs: Kerberos authentication fails if there are duplicate SPNs. Run the following command to
check for duplicate SPNs:
setspn -X
Kerberos Validation: To ensure that Kerberos authentication is being used, you can query the current
connections in SQL Server:
SELECT auth_scheme
If Kerberos is configured correctly, the result should display KERBEROS for the auth_scheme.
Removing SPNs: If you need to remove an incorrectly set SPN, use the setspn -D command:
setspn -D MSSQLSvc/SQLServer01:1433 DOMAIN\SQLServiceAccount

[email protected]
9. Test the Configuration
Once the SPN is set and verified, restart the SQL Server service and test Kerberos authentication:
● Use SSMS or another client tool to connect to the SQL Server instance.
● Run the query to check if the auth_scheme is KERBEROS:
SELECT auth_scheme
If KERBEROS is returned, Kerberos authentication is working successfully.
Summary
1. Ensure SQL Server runs under a domain account.

2. Determine the instance name and port.
3. Set SPNs for the SQL Server service using the setspn command.
4. Verify SPN registration using setspn -L.
5. Test Kerberos authentication using SQL Server queries.
Correctly setting up SPN enables Kerberos authentication in SQL Server, which improves security and supports
features like delegation and Single Sign-On (SSO).

Service Principal Name (SPN) in SQL Server

Uploaded by

Copyright:

Available Formats

Service Principal Name (SPN) in SQL Server

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Service Principal Name (SPN) in SQL Server

Uploaded by

Copyright:

Available Formats

https://www.sqldbachamps.

Steps to Set Up an SPN in SQL Server

Here is a detailed, step-by-step process for setting up an SPN:

To check the service account under which SQL Server is running:

● Open SQL Server Configuration Manager.

3. Identify the SQL Server Instance and Port

● Check the Instance Name and Port:

SELECT local_net_address, local_tcp_port

WHERE session_id = @@SPID;

If it’s a named instance SQLInstance01 running on port 1435, it would be:

setspn -S MSSQLSvc/FQDN:Port Domain\SQLServiceAccount

a. SPN for a Default Instance (Port 1433)

Run the following command (replace placeholders with actual values):

setspn -S MSSQLSvc/SQLServer01:1433 DOMAIN\SQLServiceAccount

setspn -S MSSQLSvc/SQLServer01.domain.com:1433 DOMAIN\SQLServiceAccount

b. SPN for a Named Instance

For a named instance running on a specific port (e.g., 1435):

setspn -S MSSQLSvc/SQLServer01.domain.com:1435 DOMAIN\SQLServiceAccount

6. Verify the SPN

a. Using Active Directory Users and Computers

1. Open Active Directory Users and Computers.

b. Using Group Policy

WHERE session_id = @@SPID;

setspn -D MSSQLSvc/SQLServer01:1433 DOMAIN\SQLServiceAccount

WHERE session_id = @@SPID;

If KERBEROS is returned, Kerberos authentication is working successfully.

1. Ensure SQL Server runs under a domain account.

You might also like