SQL Server Notes

SQL Server Service Principal Name Notes

Examples of Common SPN Formats for 2005 and prior:

• Note: Many products will create both SPN records for a single instance.
• Default / named instance: MSSQLSvc/server:1433
• Default / named instance: MSSQLSvc/server.domain.com:1433

Examples of Common SPN Formats for 2008 and later:

Note: The TCP port isn't required. The SPN may not have a port for instances configured with Named Pipes or Shared Memory.

However, all formats are supported and may be used by vendors.

• Default instance: MSSQLSvc/server
• Default instance: MSSQLSvc/server.domain.com
• Named instance: MSSQLSvc/server:InstanceName
• Named instance: MSSQLSvc/server.domain.com:InstanceName
• Optional format: MSSQLSvc/server:1433
• Optional format: MSSQLSvc/server.domain.com:1433

Example of Commands to Create SPN Entries:

• setspn -s MSSQLSvc/server DOMAIN\SQLServiceAccount
• setspn -s MSSQLSvc/server:1433 DOMAIN\SQLServiceAccount
• setspn -s MSSQLSvc/server:InstanceName DOMAIN\SQLServiceAccount
• setspn -s MSSQLSvc/server.domain.com DOMAIN\SQLServiceAccount
• setspn -s MSSQLSvc/server.domain.com:1433 DOMAIN\SQLServiceAccount
• setspn -s MSSQLSvc/server.domain.com:InstanceName DOMAIN\SQLServiceAccount
  
  

References

• https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-2017

SQL Server Browser Notes

Microsoft states that, "Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4.

When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance."

The following fields can be parse from the SQL Server browser UDP responses:

• Instance Name
• IP Address
• TCP
• Version
• Clustered Status

Reference

• https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service?view=sql-server-2017