dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Test-DbaSpn

This function will take in a -ComputerName parameter and return an array of objects that list all required service principal names (SPN) for each instance of SQL Server found on the target computer name. The SPN objects returned will also specify the following information:

  • The FQDN of the computer
  • The instance name of SQL Server
  • SQL Server product/version
  • The service account of the database engine
  • The required* SPN
  • Is the SPN already set for the service account?
  • Is the SQL Server instance clustered?
  • Is TCP/IP enabled for this instance?
  • What port number is this running on?
  • Are dynamic ports enabled for this instance’s TCP/IP protocol?
  • Any warnings about the instance
  • Any errors in discovering the instance

A note about required SPNs

For a given instance of SQL Server, a base SPN is always suggested:

MSSQLSvc/<FQDN> or MSSQLSvc/<FQDN>:<InstanceName>

If TCP/IP is enabled, then you should set an SPN for the port number SQL Server is listening on. For default instances, the SPN looks like this:

MSSQLSvc/<FQDN>:1433

For named instances listening on dynamic ports, an SPN can be set, but it is suggested that you do not since port numbers can change. That’s why the function will warn you about dynamic ports. It will still return a “required” SPN with a dynamic port listed, but you should use caution setting it.

Screenshots

You can even check your entire estate in just one line. Here, you can see us testing several instances in our lab.

Or use Test to help easily Set all required SPNs

Examples

Get a list of required SPNs for every instance of SQL Server on a remote computer

Test-DbaSpn -ComputerName sqlserverc -Credential (Get-Credential)

Get a list of required SPNs for every instance of SQL Server on a remote computer

Test-DbaSpn -ComputerName sqlserverc

Author

This command was created by Drew Furgiuele. You can find Drew tweeting about SQL Server and PowerShell on Twitter and blogging at port1433.com.

Get-Help

From PowerShell, execute Get-Help Test-DbaSpn -Detailed for more information on this function.

Source Code

Want to see the source code? View Test-DbaSpn.ps1 on GitHub
 

Related commands