Provide feedback
Saved searches
Use saved searches to filter your results more quickly
Sign up
Appearance settings
Introduction
This article explains how to verify and register Service Principal Names (SPN) for SQL Server Authentication with Kerberos Connections. Kerberos Authentication is a widely accepted network authentication Protocol. It is used to provide a highly secure method to authenticate Windows users.
What is an SPN?
MSDN Describes Service Principal Name (SPN) as:- “SPN is the name by which a client uniquely identifies an instance of 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. Before the Kerberos authentication service can use an SPN to authenticate a service, the SPN must be registered on the account object that the service instance uses to log on. When a client wants to connect to a service, it locates an instance of the service, composes an SPN for that instance, connects to the service, and presents the SPN for the service to authenticate.” Source MSDN Article: Service Principal Names
It is always recommended to run SQL Server Services under a Domain User Account which has minimal permissions. If you are looking for different ways to secure SQL Server within your environment then read the following “SQL Server Security Best Practices” article.
TSQL Query to verify SQL Server/Windows Authentication scheme used by SQL Server Connection
Execute the below TSQL Query to verify authentication used by SQL Server Connections.
USE master
GO
SELECT auth_scheme FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
GOExpected Results
SQL – When SQL Server authentication is used
NTLM – When NTLM authentication is used
KERBEROS – When KERBEROS authentication is used
Prerequisites when configuring SQL Server to use Kerberos Authentication
- 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.
Different Ways to Verify SPN has been successfully registered for SQL Server Authentication with Kerberos Connections
- Using SETSPN Command Line Utility
- Using Active Directory Service Interfaces Editor (ADSIEdit.msc)
Verify SPN has been successfully registered Using SETSPN Command Line Utility
In Command Line enter the following command: setspn -L <Domain\SQL Service Account Name> and press enter. Next, you need to look for registered ServicePrincipalName to ensure that a valid SPN has been created for the SQL Server.
Registered ServicePrincipalNames for CN=SQLServiceAccountName,OU=SQL,OU=Service Accounts,OU=Admin Roles,DC=SGP,DC=mytechmantra,DC=com:Error Message: When SPN is not configured correctly for SQL Server Service
If SPN is not configured correctly then you will see the below mentioned error message in command line.
FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0x00000525
Could not find account ServiceAccountVerify SPN has been successfully registered by reading SQL Server Error Log
If SPN is not registered successfully for the SQL Server Service then you will see the below mentioned warning message within the SQL Server Error Logs. You can search for the same in SQL Server Error Log file using the filtering option which is available in Log File Viewer.
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies. How to manually create a domain user Service Principle Name (SPN) for the SQL Server Service Account
A Domain Administrator can manually set the SPN for the SQL Server Service Account using SETSPN.EXE utility. However, to create the SPN, one must use the can use the NetBIOS name or Fully Qualified Domain Name (FQDN) of the SQL Server. SPN must be created for both the NetBIOS name and the FQDN. In case if you are creating for a Clustered SQL Server then specify the virtual name of the SQL Server Cluster as the SQL Server computer name. It is assumed that you are running SQL Server on the default port which is 1433. If you have configured to use SQL Server under a different port then specify that port number.
Create SPN for NetBIOS name of SQL Server
setspn –a MSSQLSvc/<computer name>:1433 <Domain\SQL Server Account>Create SPN for the FQDN of the SQL Server
setspn -a MSSQLSvc/:1433How to Automatically register a Service Principle Name (SPN) for the SQL Server Service Account
If you wish to register SPN for SQL Server Account Automatically then refer the following Microsoft Knowledge Base Article titled “How to use Kerberos authentication in SQL Server”.
Microsoft Kerberos Configuration Manager for SQL Server
Microsoft of recently released a downloaded utility name “Microsoft Kerberos Configuration Manager for SQL Server” which is a diagnostic tool.
This tool will help DBAs to troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Analysis Services, and SQL Server Reporting Services.
We would recommend you to download and install this tool to resolve SPN related issue on your servers.
Download Link: Microsoft® Kerberos Configuration Manager for SQL Server®
Trending SQL Server Tips
- SQL Delete Duplicate Rows from a SQL Table in SQL Server
- FORMAT SQL Server Dates Using FORMAT Function in SQL Server
- SQL Server SELECT DISTINCT Clause T-SQL Tutorial with Examples
References
- How to Configure an SPN for SQL Server Site Database Servers
- Register a Service Principal Name for Kerberos Connections
- How to troubleshoot the “Cannot generate SSPI context” error message
- How to use Kerberos authentication in SQL Server
- Download Microsoft Kerberos Configuration Manager for SQL Server – A diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server and SQL Server Reporting Services
Ashish Mehta
Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.
In this article, we’ll be talking about identity management in Windows Server 2016. Specifically, we will be talking about SPNs (Service Principal Names) and how wonderful they are.
First of all, an SPN is like an alias for an AD object, which can be a Service Account, User Account or Computer object, that lets other AD resources know which services are running under which accounts and creates associations between them in Active Directory.
There are several ways to check which SPNs are assigned to an object. One is through Active Directory Users and Computers and the other is using the command line.
View SPNs in Active Directory
To be able to see the SPNs using Active Directory Users and Computers, you need to have Advanced Features enabled in the console by going to the View menu. After enabling it, go to the desired AD object, choose Properties and go to the Attribute Editor tab:
Then look for the attribute servicePrincipalName and click Edit. Here you will see a list of all the SPNs and also the ability to add SPNs.
The other way is to use the setspn –l in a command prompt to view the SPNs for that specific object.
We can also add other SPNs to this object, depending on what the object is hosting, which type of service and so forth.
Create SPN in Active Directory
Let’s say we have a new service and we want to add an SPN, so that other AD resources can find out which server is hosting that service and with which user it’s authenticating.
First, let’s create a service account in Active Directory.
New-ADServiceAccount -Name MSA-syslab-1 -RestrictToSingleComputer
Now, we will associate the Managed Service Account to our server.
Add-ADComputerServiceAccount -Identity rmc-syslab-1 -ServiceAccount MSA-syslab-1
Next, let’s install that service account on the server.
Install-ADServiceAccount MSA-syslab-1
Finally let’s create our generic service.
New-Service -Name GENSERV -BinaryPathName C:\Windows\System32\notepad.exe
Now we use the setspn –s command that creates and SPN and uses the –s switch to make sure a duplicate SPN does not exist.
setspn -s GENSERV/rmc-syslab-1.rmcsyslab.com rmcsyslab\MSA-syslab-1
The command adds the service GENSERV hosted on rmc-syslab-1 running under the MSA user MSA-syslab-1. Now what’s left is to configure the service to run under the MSA account.
As you can see under the Log On tab in the service properties, I configured the MSA account and left the password blank, since we know that MSA passwords are managed by Windows.
Now let’s check the Service Account and see which SPN has been added to it.
As you can see, it has the SPN of GENSERV/rmc-syslab-1.rmcsyslab.com since this user logs on and authenticates that service.
Well, that is about it on SPNs for now. Please keep in mind that SPNs are very sensitive. You should only dive into this if there is an issue or if you are creating some custom service.
Thank you for your time and I hope this article was interesting to you. Enjoy!
After SCOM installation there will always be some hiccups to solve. Lets start with two. First one is long time friend of SCOM and second one is related to SCOM 2016 installation. If you open SCOM console most likely you will see this error:
Data Access Service SPN Not Registered
The System Center Data Access service failed to register an SPN. A domain admin needs to add MSOMSdkSvc/scom2016 and MSOMSdkSvc/scom2016.contoso.com to the servicePrincipalName of CN=SCOM2016,OU=servers,DC=contoso,DC=com
From times of SCOM 2012 management servers can run on multiple computers for redundancy and workload offload. Before there was only one management server and usual place were SPN (Service Principal Name) was added was its computer account. Today we use domain user account for running this service on multiple servers and SPN should be placed there. So lets list SPN for account we use. We use setspn command.
C:\Windows\system32>setspn -l contoso\scomcdas
Registered ServicePrincipalNames for CN=scomcdas,DC=contoso,DC=com:
We see there is no SPN registered for this account because this account does not have rights to do that
If you run this on computer account we get next result
C:\Windows\system32>setspn –l contoso\scom2016
Registered ServicePrincipalNames for CN=SCOM2016,OU=servers,DC=contoso,DC=com:
MSOMHSvc/SCOM2016
MSOMHSvc/scom2016.contoso.com
WSMAN/scom2016
WSMAN/scom2016.contoso.com
TERMSRV/SCOM2016
TERMSRV/scom2016.contoso.com
RestrictedKrbHost/SCOM2016
HOST/SCOM2016
RestrictedKrbHost/scom2016.contoso.com
HOST/scom2016.contoso.com
So lets add SPN for our account. You do this with commands
setspn -A MSOMSdkSvc/SERVERNAME DOMAIN\USERNAME setspn -A MSOMSdkSvc/SERVERNAME.fqdn.name DOMAIN\USERNAME
C:\Windows\system32>setspn -s MSOMSdkSvc/scom2016.contoso.com contoso\scomcdas
Checking domain DC=contoso,DC=com
Registering ServicePrincipalNames for CN=scomcdas,DC=contoso,DC=com
MSOMSdkSvc/scom2016.contoso.com
Updated object
Repeat the same with NETBIOS name setspn -s MSOMSdkSvc/scom2016 contoso\scomcdas
If you list you SPN on domain account it should look like this:
C:\Windows\system32>setspn -l contoso\scomcdas
Registered ServicePrincipalNames for CN=scomcdas,DC=contoso,DC=com:
MSOMSdkSvc/scom2016
MSOMSdkSvc/scom2016.contoso.com
SDK will be healthy now
The EXECUTE permission was denied on the object ‘sp_help_jobactivity’, database ‘msdb’, schema ‘dbo’.
New features, new problems. This is second error you will receive on SCOM 2016 installation only. If you did all by the book and you click Maintenance Schedule you will get this error.
You will see it also in Operations Manager event log even if you didn’t try to use Meintenence schedule.
What to do? This is purely missing permissions of SCOM sdk account on SQL server. so to solve this one install SQL Management Studio if you didn’t already and give you SCOM SDK account next permissions on msdb database.
Error is fixed now and you will not see it in the future.
Article describing registration of Active Directory Service Principal Names (SPN) and troubleshooting.
Registration of SPN’s have 2 most common used variants:
- Registration of SPN to specific Computer Object (this method is used when service is not running under AD domain account like SQL Server, etc.)
- Registration of SPN to specific Domain Account (this method is used when service is running under AD domain account like SQL Server, etc.)
Registration of SPN to specific Computer Object
Error log message with wrong kerberos authentication because of missing SPN for “MSSQLSvc” service running under local account “NT Service\MSSQL$DB01” on a server “DBSRV01.contoso.corp” using Instance “DB01”:
SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated. Service Account: NT Service\MSSQL$DB01 Missing SPNs: MSSQLSvc/DBSRV01.contoso.corp:DB01 Misplaced SPNs:
To FIX this problem logon to server/DC which is in same domain like server DBSRV01.contoso.corp and run following command from CMD/Powershell:
setspn -s "MSSQLSvc/DBSRV01.contoso.corp:DB01" "DBSRV01"
Description of command:
- setspn.exe -s – add arbitrary SPN after verifying no duplicates exist
- “MSSQLSvc/DBSRV01.contoso.corp:DB01” – register SPN for service with name MSSQLSvc on server DBSRV01.contoso.corp running Instance with name DB01.
- “DBSRV01” – register SPN to this AD Computer Object.
Registration of SPN to specific Domain Account
Error log message with wrong kerberos authentication because of missing SPN for “MSSQLSvc” service running under domain account “CONTOSO\SVC_SQL03” on a server “DBSRV03.contoso.corp” using Instance running on port “1433” and on computer object:
SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated. Service Account: CONTOSO\SVC_SQL03 Missing SPNs: MSSQLSvc/DBSRV03.contoso.corp, MSSQLSvc/DBSRV01.contoso.corp:1433 Misplaced SPNs: Duplicate SPNs:
To FIX this problem logon to server/DC which is in same domain like server DBSRV03.contoso.corp and run following command from CMD/Powershell:
setspn -s "MSSQLSvc/DBSRV03.contoso.corp" "CONTOSO\SVC_SQL03" setspn -s "MSSQLSvc/DBSRV03.contoso.corp:1433" "CONTOSO\SVC_SQL03"
Description of command:
- setspn.exe -s – add arbitrary SPN after verifying no duplicates exist
- “MSSQLSvc/DBSRV03.contoso.corp” – register SPN for service with name MSSQLSvc on server DBSRV03.contoso.corp running Instance on Computer Object.
- “MSSQLSvc/DBSRV03.contoso.corp:1433” – register SPN for service with name MSSQLSvc on server DBSRV03.contoso.corp running Instance on port 1433.
- “CONTOSO\SVC_SQL03” – register SPN to this AD Account.
Troubleshooting
- The operation failed because SPN value provided for addition/modification is not unique forest-wide.
Same SPN value already exists in a domain during registration of SPN. We need to find duplicate and remove it. Start powershell, import AD module (Import-Module ActiveDirectory) and start search query:
Search query for AD User Account:
Get-ADUser -Filter {serviceprincipalname -like "MSSQLSvc/DBSRV03.contoso.corp:1433"}
Search query for Computer Account:
Get-ADComputer -Filter {serviceprincipalname -like "MSSQLSvc/DBSRV01.contoso.corp:DB01"}