On servers without an SPN, we sometimes see the following kinds of intermittent connectivity errors:
“SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.”
|
Config
Item
|
Default
Value
|
Standard
|
|
SPN for SQL Servers running under domain accounts |
Not created (because domain accounts lack permissions to create & register SPNs, by default) |
All SQL servers running under a domain account must have an SPN
created and registered in Active Directory, for the domain account under
which the MsSQLserver service is running.
|
What is SPN?
A service principal name (SPN) is the name by which a client
uniquely identifies an instance of a service. 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. The benefit of having an SPN is that
it allows the system to use Kerberos authentication along with NTLM. In general,
Kerberos(Kerberos is a network authentication protocol provides a highly secure method to authenticate client and server entities (security principals) on a network.) is known to perform better and is more secure than NTLM.
To reduce the possibility of network connectivity errors, all SQL
servers running under a domain account must have an SPN created and registered
in Active Directory, for the domain account under which the MsSQLserver service
is running.
How to create SPN ?
Window Server Team responsible to create it-
·
To create an SPN for the NetBIOS name of the SQL Server use the
following command:
C:\> setspn –A MSSQLSvc/<SQL Server
computer name or Virtual name of SQL Server>:1433 <Domain\Account>
· To create an SPN for
the FQDN of the SQL Server use the following command:
C:\> setspn -A MSSQLSvc/<SQL Server
FQDN>:1433 <Domain\Account>
How to verify registered SPN ?
To view registered SPNs on a server for the domain account under
which SQL Server is running, we can use the Setspn command as follows (the
output below is the simplified output for just one named SQL instance your on server
C:\> setspn -U -L Redmond\_backup
For more information about SPNs see the following links: MSDN important Links-
- How to Configure an SPN for SQL Server Site Database Servers
- Registering a Service Principal Name for Kerberos Connections
- How to use SPNs when you configure Web applications that are hosted on Internet Information Services Service Principal Names (SPNs)
- Service Principal Names (SPNs) SetSPN
Please comment below if know more about SPN - Jainendra Verma
No comments:
Post a Comment