다음을 통해 공유

How to grant ReadServicePrincipalName and WriteServicePrincipalName rights to SQL Server service start-up account without using ADSDIEDIT tool.

  • 아티클

I hope you probably know how to fix below different authentication related errors that can occur while connecting to SQL Server with Windows Integrated Authentication.

 

1. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

2. Login Failed for user 'NT Authority\ANONYMOUS' LOGON

3. Login Failed for user ' ', the user is not associated with a trusted SQL Server connection

4. Cannot Generate SSPI Context

5. SSPI handshake failed with error code 0xc0000413 while establishing a connection with integrated security; the connection has been closed.

 

The focus of this post is not to discuss above different errors and their resolutions because they have already been covered by some very good resources below.

 

1. How to use Kerberos authentication in SQL Server - https://support.microsoft.com/kb/319723

  1. Kerberos Authentication in Windows Server 2003 - https://www.microsoft.com/windowsserver2003/technologies/security/kerberos/default.mspx
  2. Understanding Kerberos and NTLM authentication in SQL Server Connections - https://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx
  3. Using Kerberos with SQL Server - https://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
  4. Kerberos Authentication and SQL Server 2008 - https://technet.microsoft.com/en-us/library/cc280744.aspx
  5. Understanding Kerberos and NTLM authentication in SQL Server Connections - https://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx
  6. Microsoft SQL Server Tips & Tricks (Kerberos Authentication in SQL Server 2005) - https://blogs.msdn.com/sqlserverfaq/archive/2008/05/06/use-of-kerberos-authentication-in-sql-server-2005.aspx
  7. Service Principal Names - https://msdn.microsoft.com/en-us/library/ms677949.aspx

 

If you go through the above articles, you will find that for most of the above errors, the resolution will involve correcting or setting SQL Server SPNs which can be done using SetSPN utility. Instead of setting SPNs manually, you may want to give ReadServicePrincipalName and WriteServicePrincipalName rights to SQL Server service start-up account so that it can register and de-register SQL Server SPNs on its own whenever the SQL Server service is started and stopped. As the above articles describe, these rights can be granted from ADSIEDIT tool. But recently I got a question where I was asked if there is a way other than ADSIEDIT tool to perform this operation. Well, here is the answer.

Below is an easy command-line way to perform this operation. This can be done over the command prompt using utility called ‘dsacls’ instead of ‘SetSPN’. Below is the command and need to be run with domain admin rights (as was always required for setting/modifying/deleting SPNs).

 

dsacls < DomainName_of_Service_Account > /G SELF:RPWP;"servicePrincipalName"

 

To set the permissions, the above command assigns the SELF account Read/Write servicePrincipalName. The permissions are applied onto this object only on the service account object.

               

/G - Grant

RPWP - Read Property Write Property

The Dsacls command-line tool is included when you install Windows Server 2003 Support Tools from the product CD or from below Microsoft Download Center.

Windows Server 2003 Service Pack 2 32-bit Support Tools - https://www.microsoft.com/downloads/details.aspx?FamilyID=96a35011-fd83-419d-939b-9a772ea2df90&DisplayLang=en

For more information about how to install Windows Support Tools from the product CD, check below.

Install Windows Support Tools - https://technet.microsoft.com/en-us/library/cc755948(WS.10).aspx

For more information on Dsacls, below are the articles.

How to Use Dsacls.exe in Windows Server 2003 and Windows 2000 - https://support.microsoft.com/kb/281146

· Dsacls Overview - https://technet.microsoft.com/en-us/library/cc755938(WS.10).aspx

 

 

NOTE - This is in no way recommended to do on a SQL Cluster Instance. We do not recommend to allow the auto update of the SPN in a SQL cluster. In Stand Alone it is okay to do this procedure when using a domain account for the SQL Server Service. Do not need to do this if SQL is running under system since system already has the write SPN privilege.

 

Author : Deepak (MSFT) , SQL Developer Engineer , Microsoft

Reviewed by : Evan (MSFT) , SQL Escalation Services, Microsoft

Comments

  • Anonymous
    January 10, 2010
    The comment has been removed

  • Anonymous
    January 10, 2010
    Thanks! Yes, as mentioned in the post itself, the command need to be executed with domain admin rights (as was always required for setting/modifying/deleting SPNs).

  • Anonymous
    March 16, 2010
    Hi Derek, Can you explain why this is not recommended for SQL clusters? I can't seem to find any conclusive documentation on best practice for setting this up with multiple intsances on clusters. In my case we have a 3 SQL instance, 4 nodes cluster running SQL 2008 on Win 2008. Any help or direction would be appreciated, Simon

  • Anonymous
    March 18, 2010
    It is not recommended on a Cluster due to time gap between node failover and DC replication - when one node goes offline it will send request to delete the SPN, however there is a chance that AD will not be updated before second node will be brought online. Second node will receive information that SPN still exists and will start without creating request to register the SPN. After DC replication will be completed SPN will be deleted and connectivity will be broken.