I always forget the syntax for setting SPN on the SQL Server service account.
So, I thought I’d write it down….
In the examples below, the account is called SvcSQLAccount in the DOMAIN domain, we are using port 1433 and the FQN is SERVER1.DOMAIN.COM
setspn -A MSSQLSvc/SERVER1.DOMAIN.COM:1433 DOMAIN\SvcSQLAccount
setspn -A MSSQLSvc/SERVER1.DOMAIN.com DOMAIN\SvcSQLAccount
setspn -A MSSQLSvc/SERVER1:1433 DOMAIN\SvcSQLAccount
setspn -A MSSQLSvc/SERVER1 DOMAIN\SvcSQLAccount
Check if it is ok by running:
Setspn -L DOMAIN\SvcSQLAccount
Kerberos authentication offers the following advantages over NTLM authentication:
- Mutual authentication. When a client uses the Kerberos v5 protocol for authentication with a particular service on a particular server, Kerberos provides the client with an assurance that the service is not being impersonated by malicious code on the network.
- Delegation support. Servers that use Kerberos authentication to authenticate clients can impersonate those clients and use the client’s security context to access network resources.
- Performance. Kerberos authentication offers improved performance over NTLM authentication.
- Simplified trust management. Networks with multiple domains no longer require a complex set of explicit, point-to-point trust relationships.
- Interoperability. Microsoft’s implementation of the Kerberos protocol is based on standards-track specifications recommended to the Internet Engineering Task Force (IETF). As a result, the implementation of the protocol in Windows 2000 lays a foundation for interoperability with other networks where Kerberos version 5 is used for authentication.