Service Principal Name (SPN) Support in Client Connections
Beginning with SQL Server 2008, support for service principal names
(SPNs) has been extended to enable mutual authentication across all
protocols. In previous versions of SQL Server, SPNs were only supported for
Kerberos over TCP when the default SPN for the SQL Server instance was
registered with Active Directory.
SPNs are used by the authentication protocol to determine the account in
which a SQL Server instance runs. If the instance account is known, Kerberos
authentication can be used to provide mutual authentication by the client and
server. If the instance account is not known, NTLM authentication, which only
provides authentication of the client by the server, is used.
Currently, SQL Server Native Client performs the authentication lookup,
deriving the SPN from the instance name and network connection properties. SQL
Server instances will attempt to register SPNs on startup, or they can be
registered manually. However, registration will fail if there are insufficient
access rights for the account that attempts to register the SPNs.
Domain and computer accounts are registered automatically in Active
Directory. These can be used as SPNs, or administrators can define their own
SPNs. SQL Server makes secure authentication more manageable and reliable
by allowing clients to directly specify the SPN to use.
Note:
An SPN specified
by a client application is only used when a connection is made with Windows
integrated security.
To manually create a domain user
Service Principle Name (SPN) for the SQL Server service account
- Click Start, click Run, and then enter cmd in the Run dialog box.
- From the command line, navigate to Windows Server support tools installation directory. By default, these tools are located in the C:\Program Files\Support Tools directory.
- Enter a valid command to create the SPN. To create the SPN, you can use the NetBIOS name or the Fully Qualified Domain Name (FQDN) of the SQL Server. However, you must create an SPN for both the NetBIOS name and the FQDN.Note:When you create an SPN for a clustered SQL Server, you must specify the virtual name of the SQL Server Cluster as the SQL Server computer name.
- To create an SPN for the NetBIOS name of the SQL Server use the following command: setspn –A MSSQLSvc/<SQL Server computer name>:1433 <Domain\Account>
- To create an SPN for the FQDN of the SQL Server use the following command: setspn -A MSSQLSvc/<SQL Server FQDN>:1433 <Domain\Account>
- Verify that the command completed successfully by reviewing the command’s output for the updated object line.
To verify the domain user SPN is registered correctly using the SetSPN command
1.
Click
Start, click Run, and then enter cmd in the Run dialog box.
2.
From
the command line enter the following command: setspn –L <domain\SQL Service Account>
3.
Review
the registered ServicePrincipalName to ensure that a valid SPN has been
created for the SQL Server.
To verify the domain user SPN is registered correctly using the ADSIEdit MMC console
1.
Click
Start, click Run, and then enter adsiedit.msc to launch the ADSIEdit MMC console.
2.
If
necessary, connect to the site server's domain.
3.
In
the console pane, expand the site server's domain, expand DC=<server distinguished name>, expand CN=Users, and then right-click CN=<Service
Account User>. On
the context menu, click Properties.
4.
In
the CN=<Service
Account User> Properties dialog box, review the servicePrincipalName value to ensure that a valid SPN has been
created and associated with the correct SQL Server.
To change the SQL Server service account from local system to a domain user account
1.
Create
or select a domain or local system user account that will be used as the SQL
Server service account.
2.
Open SQL Server Configuration Manager.
3.
Click
SQL Server 2005
Services, and then
double click SQL Server<INSTANCE NAME>.
4.
On
the Log on tab, select This account, and then enter the user name and password for the
domain user account created in step 1 or click Browse to find the user account in Active Directory and then click Apply.
5.
Click
Yes on the Confirm Account Change dialog box to confirm the service account change
and restart the SQL Server Service.
6.
Click
OK after the service account has been
successfully changed.
Register a SPN for SQL Server
Authentication with Kerberos
When it comes to configuring your
SQL Servers to use Kerberos authentication there are a couple of prerequisites
that must be met. First, the clients and servers must be joined to a domain. If
they are joined, but they are in different domains then a two-way trust must be
setup between these domains. Secondly an SPN
must be successfully registered for the SQL Server service so that it can be
identified on the network. The first requirement is pretty easy to validate so
let's concentrate on the second one.
There are a few ways that we can
check if the SPN has been registered successfully. If your SQL Server instance
is running under a domain account (which is recommended) you can run the
following command to see the services that are registered. If there are no
services registered for this account you will get the error message below the
command.
C:\Users\test>setspn -l DOMAIN\SQLServiceAccount
FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0x00000525
Could not find account SQLServiceAccount
Alternatively, you can also use the SQL
Server error log to validate if the SPN has been registered successfully or
not. By either filtering within the SSMS
GUI or using xp_read_errorlog
we can search for the string below. If found we know the SPN did not register
successfully.
The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the
SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated
authentication to fall back to NTLM instead of Kerberos. This is an informational message.
Further action is only required if Kerberos authentication is required by authentication
policies.
Finally, you can contact your
system administrator and have them use the ADSIEdit MMC console to manually
check if the service is registered.
Now that we've identified the
issue we can go through a couple of different options that will allow us to
successfully register the SPN and use Kerberos authentication.
Option 1 - Register SPN automatically
To enable the SPN to be registered
automatically on SQL Server startup the service must be running under the
"Local System" or "Network Service" accounts (not
recommended), under a domain administrator account, or under an account that
has permissions to register an SPN. The permissions required for this are the
"Read servicePrincipalName" and "Write
servicePrincipalName" access control settings in the Active Directory
service. One thing that should be noted is granting these rights is not
recommended (see http://support.microsoft.com/kb/319723)
if SQL Server is clustered or if you have multiple domain controllers as
latency in Active Directory replication can cause connectivity issues with your
SQL Server instance.
Option 2 - Register SPN manually
To register an SPN manually we can use the
Microsoft provided Setspn.exe
utility. To be able to run this tool and register an SPN you need to be a
domain admin or have the appropriate privileges (defined above). One other
thing to note is that the -s option ensures that the SPN you are trying to
create is not already defined. Here are a couple examples. The first one is for
a default instance and the second is for a named instance.
setspn -s MSSQLSvc/myhost.redmond.microsoft.com DOMAIN\SQLServiceAccount
setspn -s MSSQLSvc/myhost.redmond.microsoft.com:instancename DOMAIN\SQLServiceAccount
Once you've picked and implemented one of
these options and if necessary restarted SQL Server you can establish a new
connection and run the following TSQL to check that you are now using Kerberos
authentication.
select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections
No comments:
Post a Comment