Good Morning everyone, Today will talk about How SQL Server works with Kerberos authentication? How to troubleshoot issues related to Kerberos in SQL Server? Kerberos authentication is very robust topic and complex, but implementing in SQL Server successfully isn’t...:)
When SQL Server starts, the account that is running the SQL Server Services tries to register a SPN (Service Principal Name) in Active Directory. The account that is running SQL Server Services requires “Read/Write ServicePrincipalNames” permission in AD. If successfully registered, an SPN for the SQL Server service is now mapped to the service account for SQL Server. Whether it is failed or success, you can verify by following below steps:
- In SQL Server Management Studio, browse to the SQL Server Error Log.
- Find the section of the log for the period SQL Server was last restarted.
- You should see an error message indicating whether SPN registration was successful or not.
If Success
If Failed
To view, create or delete SPN’s, there’s the Setspn command line utility. To get any use out of it, you need to know a couple of things about what you’re checking, such as the service account for your service, domain details, etc.
Check by which service account SQL Server services are Running
To Check follow below steps:
- Launch SQL Server Configuration Manager on the server running SQL Server.
- Select SQL Server Services and look at the ‘Log On As’ column for the SQL Server service.
In my case, the ‘TESTACC’ user account in the ‘TESTDOM’ domain is running the SQL Server services. This is the account we have to check when looking to see if there’s a mapped SPN. It may be that your SQL Server services are running under a local machine account, in which case you need to use the machine’s hostname when doing the SPN lookup.
List the SPN’s mapped to a principal
To list the SPN’s mapped to the ‘TESTDOM\TESTACC’ account:
- Open a DOS window
- Run setspn -L domain\account or setspn -L hostname, e.g.
What can you infer from this output? As per the Setspn technet article, the SPN format is: ServiceClass/HostName:Port/ServiceName
So, these SPN’s are for the MSSQLSvc service, running on the MYSERVER and MYSERVER2 hosts (FQDN) on ports 4444/44445 respectively. As well as the port number, the SQL Server instance name is enumerated by the command listing.
In my case, the service account running SQL Server had enough permissions in AD to register the SPN itself. But, if you get the failure message in your SQL Server Error Log, an administrator will have to manually register the SPN’s.
To manually register an SPN, you again use the setspn command line utility:
- Open a DOS window
- Run setspn -S serviceclass/host:port/servicename, e.g
When you use the -S switch, it first checks whether an SPN already exists in AD for the service. If so, you get an error stating that a duplicate SPN exists. If there is a duplicate, check its syntax is correct, and if needed use setspn -D to delete it, then recreate it as above.
Finally the conclusion for SQL Server, Service Principle Names (SPNs) are unique identifiers for services and identify the account’s type of service. If an SPN is not configured for a service, a client account will be unable to authenticate to the servers using Kerberos. You need to be a domain administrator to add an SPN, which can be added using the SetSPN utility.
SQL Server & its related features, the following SPNs need to be registered. Here are the formats to SETSPN:
--SQL Server Service SETSPN -S mssqlsvc/servername:1433 Domain\SQL
For named instances, or if the default instance is running under a different port, then the specific port number should be used.
--Reporting Services Service SETSPN -S http/servername Domain\SSRS SETSPN -S http/servername.domain.com Domain\SSRS
The SPN should be set for the NETBIOS name of the server and the FQDN. If you access the reports using a host header or DNS alias, then that should also be registered.
SETSPN -S http/www.reports.com Domain\SSRS
--Analysis Services Service SETSPN -S msolapsvc.3/servername Domain\SSAS
After Setup the SPN's, Instance must be restarted for the changes to take effect.
One thing you should consider regarding the SQL Server Serivce Account permission to get the SPN register itself whenever SQL Services restart and that is Enable Delegation (With Trust this user to delegation specific Services only - Kerberos Only)
For More SQL related issues refer this Article
For more details regarding Kerberos with SQL Server Reporting Services refer this Document
When SQL Server starts, the account that is running the SQL Server Services tries to register a SPN (Service Principal Name) in Active Directory. The account that is running SQL Server Services requires “Read/Write ServicePrincipalNames” permission in AD. If successfully registered, an SPN for the SQL Server service is now mapped to the service account for SQL Server. Whether it is failed or success, you can verify by following below steps:
- In SQL Server Management Studio, browse to the SQL Server Error Log.
- Find the section of the log for the period SQL Server was last restarted.
- You should see an error message indicating whether SPN registration was successful or not.
If Success
If Failed
To view, create or delete SPN’s, there’s the Setspn command line utility. To get any use out of it, you need to know a couple of things about what you’re checking, such as the service account for your service, domain details, etc.
Check by which service account SQL Server services are Running
To Check follow below steps:
- Launch SQL Server Configuration Manager on the server running SQL Server.
- Select SQL Server Services and look at the ‘Log On As’ column for the SQL Server service.
In my case, the ‘TESTACC’ user account in the ‘TESTDOM’ domain is running the SQL Server services. This is the account we have to check when looking to see if there’s a mapped SPN. It may be that your SQL Server services are running under a local machine account, in which case you need to use the machine’s hostname when doing the SPN lookup.
List the SPN’s mapped to a principal
To list the SPN’s mapped to the ‘TESTDOM\TESTACC’ account:
- Open a DOS window
- Run setspn -L domain\account or setspn -L hostname, e.g.
What can you infer from this output? As per the Setspn technet article, the SPN format is: ServiceClass/HostName:Port/ServiceName
So, these SPN’s are for the MSSQLSvc service, running on the MYSERVER and MYSERVER2 hosts (FQDN) on ports 4444/44445 respectively. As well as the port number, the SQL Server instance name is enumerated by the command listing.
In my case, the service account running SQL Server had enough permissions in AD to register the SPN itself. But, if you get the failure message in your SQL Server Error Log, an administrator will have to manually register the SPN’s.
To manually register an SPN, you again use the setspn command line utility:
- Open a DOS window
- Run setspn -S serviceclass/host:port/servicename, e.g
When you use the -S switch, it first checks whether an SPN already exists in AD for the service. If so, you get an error stating that a duplicate SPN exists. If there is a duplicate, check its syntax is correct, and if needed use setspn -D to delete it, then recreate it as above.
Finally the conclusion for SQL Server, Service Principle Names (SPNs) are unique identifiers for services and identify the account’s type of service. If an SPN is not configured for a service, a client account will be unable to authenticate to the servers using Kerberos. You need to be a domain administrator to add an SPN, which can be added using the SetSPN utility.
SQL Server & its related features, the following SPNs need to be registered. Here are the formats to SETSPN:
--SQL Server Service SETSPN -S mssqlsvc/servername:1433 Domain\SQL
For named instances, or if the default instance is running under a different port, then the specific port number should be used.
--Reporting Services Service SETSPN -S http/servername Domain\SSRS SETSPN -S http/servername.domain.com Domain\SSRS
The SPN should be set for the NETBIOS name of the server and the FQDN. If you access the reports using a host header or DNS alias, then that should also be registered.
SETSPN -S http/www.reports.com Domain\SSRS
--Analysis Services Service SETSPN -S msolapsvc.3/servername Domain\SSAS
After Setup the SPN's, Instance must be restarted for the changes to take effect.
One thing you should consider regarding the SQL Server Serivce Account permission to get the SPN register itself whenever SQL Services restart and that is Enable Delegation (With Trust this user to delegation specific Services only - Kerberos Only)
For More SQL related issues refer this Article
For more details regarding Kerberos with SQL Server Reporting Services refer this Document
No comments:
Post a Comment