First published on MSDN on Jan 06, 2010
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 -
http://support.microsoft.com/kb/319723
-
Kerberos Authentication in Windows Server 2003 -
http://www.microsoft.com/windowsserver2003/technologies/security/kerberos/default.mspx
-
Understanding Kerberos and NTLM authentication in SQL Server Connections -
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authent...
-
Using Kerberos with SQL Server -
http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
-
Kerberos Authentication and SQL Server 2008 -
http://technet.microsoft.com/en-us/library/cc280744.aspx
-
Understanding Kerberos and NTLM authentication in SQL Server Connections -
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authent...
-
Microsoft SQL Server Tips & Tricks (Kerberos Authentication in SQL Server 2005) -
http://blogs.msdn.com/sqlserverfaq/archive/2008/05/06/use-of-kerberos-authentication-in-sql-se...
-
Service Principal Names -
http://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 -
http://www.microsoft.com/downloads/details.aspx?FamilyID=96a35011-fd83-419d-939b-9a772ea2df90&D...
For more information about how to install Windows Support Tools from the product CD, check below.
Install Windows Support Tools -
http://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 -
http://support.microsoft.com/kb/281146
· Dsacls Overview -
http://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
