Welcome back to another blog post on SQL Server concepts and ideas. In today’s article, we will be discussing the importance of managing Service Principal Names (SPNs) and how to use the SETSPN tool to accomplish this task.
SETSPN is a command line utility that is commonly used to manage SPNs for Kerberos authentication. It is a powerful tool that allows you to add, delete, and list SPNs for various accounts in Active Directory.
Before we dive into the details, it’s important to note that the actions you can perform with SETSPN depend on your rights within Active Directory. Some operations, such as adding or deleting an SPN, require membership in the Domain Admins group or delegated rights to modify the account to which the SPN is being added.
However, even if you don’t have these elevated permissions, you can still use the -L switch to list out all SPNs belonging to a particular account. This is particularly useful for DBAs and database developers who often don’t have domain admin access.
Let’s take a look at an example. If you want to see all services registered to your computer’s account, you can use the following command:
SETSPN -L BrianPC
This will display a list of registered SPNs for the account “BrianPC”. You might see SPN types like TERMSRV and HOST, which indicate the ability to RDP into the computer and the generic SPN for every computer in the domain, respectively.
But what if you want to check if you have the correct SPNs for your SQL Server instance? In this case, the account would be the service account under which SQL Server runs. For example:
SETSPN -L SQLSvc
This command will show you the registered SPNs for the “SQLSvc” account. You might see SPNs like MSSQLSvc/SQLServer01.contoso.com and MSSQLSvc/SQLServer01, which are specific to SQL Server instances.
So why should you care about managing SPNs? The answer is simple: it allows you to verify if the SPNs were set up correctly. Often, the creation of SPNs is delegated to the domain administration team, and as a DBA or database developer, you want to ensure that the SPNs are correctly configured for Kerberos authentication.
Using the SETSPN tool with the -L switch, you can easily check if the SPNs are set up correctly. Even if you no longer have domain admin privileges, you can still use SETSPN to verify the work done by the team responsible for setting up SPNs.
In addition to the -L switch, SETSPN also provides other useful switches for adding and deleting SPNs. For example, you can use the -A switch to add an SPN and the -D switch to delete an SPN. The syntax for these switches is as follows:
SETSPN -A SPN Account SETSPN -D SPN Account
For instance, if you want to add the SPNs for a SQL Server instance with the service account “SQLSvc”, you can use the following commands:
SETSPN -A MSSQLSvc/SQLServer01.contoso.com SQLSvc SETSPN -A MSSQLSvc/SQLServer01 SQLSvc
On the other hand, if you need to delete an SPN, you can use the -D switch with the appropriate SPN and account:
SETSPN -D MSSQLSvc/SQLServer01.contoso.com SQLSvc SETSPN -D MSSQLSvc/SQLServer01 SQLSvc
It’s important to note that the rights required to delete an SPN are the same as those needed to add one.
While there are other useful switches available in SETSPN, they may not be present in all versions of the tool. If you’re interested in exploring these additional switches, I recommend referring to the SETSPN documentation or using the command SETSPN -? to display the available options.
In conclusion, managing Service Principal Names (SPNs) is crucial for ensuring proper Kerberos authentication in SQL Server. The SETSPN tool provides a convenient way to add, delete, and list SPNs for various accounts. By using SETSPN, you can verify the correct setup of SPNs and ensure a secure and efficient SQL Server environment.