Delegation: What It Is And How To Set It Up

<div><h5>The Problem</h5> <p>You’re a security conscious DBA so you follow best practices; you run your SQL services as a domain account and have all your users and applications using Windows authentication. Then one of your developers comes to you and says he’s getting this error running a query that uses a linked server:</p> <blockquote> <p><font face="Courier New" color="#ff0000">Msg 18456, Level 14, State 1, Line 1 <br />Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.</font></p> </blockquote> <p>What they’re likely seeing is the result of something known as the “double-hop”:  A connection from Machine A to Machine B asks Machine B to access something on Machine C using the credentials originally provided by Machine A. In this scenario, Machine B using the credentials from Machine A is known as <em>impersonation </em>and Machine B’s ability to use impersonation to connect to Machine C is called <em>delegation. </em>The errors occur when delegation has not been properly configured. There are couple of ways this may manifest itself:</p> <ul> <li>Bulk inserting a file from a UNC share on another computer (e.g. \\servername\share\file.bcp) </li> <li>An Ad-hoc distributed query using a trusted connection </li> <li>Using a linked server that’s been configured with a trusted connection </li> </ul> <p> </p> <h5>The Solution</h5> <p>There are two ways to solve this problem – either use SQL authentication or configure delegation. Since this post is about fixing the double hop problem in a Windows auth environment I’ll save the debate on authentication methods for another time and focus on what it takes to get delegation working. Microsoft has a nice write-up on <a href="http://msdn.microsoft.com/en-us/library/aa905162(SQL.80).aspx" target="_blank">how to configure delegation</a> on MSDN. I’ve added to it by providing screenshots and my own comments.</p> <p>To use delegation, all computers must use Active Directory and have <a href="http://en.wikipedia.org/wiki/Kerberos_(protocol)" target="_blank">Kerberos</a> support enabled (Kerberos is the default authentication method in Windows 2000 and up, so this usually isn’t an issue). The following options in Active Directory must also be specified (which you get to through the Active Directory Users and Computers MMC snapin):</p> <ul> <li>The <strong>Account is sensitive and cannot be delegated</strong> check box must not be selected for the user requesting delegation. </li> </ul> <p><img alt="image" border="0" height="502" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_8okFvZITqffLTZChi6BZlw-HwgI-Xnh5B5hL45ElIK2PORqrQjhnzfVu9r9hSvN8ASomu2a41OyL-LNHuM02d07p1ii7dtZ-NYjFF_xaNIIJibd5rKlS8PKvWBlwfrjdZfq5QvABuvqr/?imgmax=800" style="border-top-width: 0px; display: block; border-left-width: 0px; float: none; border-bottom-width: 0px; margin-left: auto; margin-right: auto; border-right-width: 0px" title="image" width="404" /> </p> <p> </p> <ul> <li>The <strong>Account is trusted for delegation</strong> check box must be selected for the service account of SQL Server. This is a bit outdated; for Windows Server 2003 there’s a Delegation tab where you can choose the different levels of trust for delegation (note that the tab doesn’t show up until SQL Server is started using that account). For those ultra-tight on security you can specify services on a computer-by-computer basis…for the slightly more relaxed there’s the “any service” option<em>.</em> </li> </ul> <p><img alt="image" border="0" height="484" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtbMNlEaeBz8m22McHPwE4be3X5PLocBZNIl7IE-HoI4M9Dki_OGPL02VfbQmZZvWPgDOFH53K9lLpdahLfKVWUdla9bg9cr8fvUw8jUqN7qMOEEAwQx1mc-aOkYkpgWwc8oxwYI9HVe1t/?imgmax=800" style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" width="404" />  </p> <ul> <li>The <strong>Computer is trusted for delegation</strong> check box must be selected for the server running an instance of SQL Server. Same deal as the previous setting – there’s a tab for this with different options depending on how tight you need to control security. </li> </ul> <p><img alt="image" border="0" height="466" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRdYcosAEvcxaH3Vd7yRHnJzVQWoma6jhw4kCA6MHKfy-V3mm4wwstm8LTRjaWUQacDmYGgmA6TSLZwn9ajvnjLwNv1z89ZZ3SGBBup_GwCynoC1hoOcTEUYushyphenhyphenzQPAmI4l1p8Y1qAIVW/?imgmax=800" style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" width="404" /></p> <p> </p> <p>Next, your SQL Server service must have a <a href="http://msdn.microsoft.com/en-us/library/ms677949(VS.85).aspx" target="_blank">Service Principal Name</a> (SPN). This is essentially a unique name in Active Directory for an instance of a service on a computer. When SQL is run under a domain account a SPN needs to be manually created using the setspn utility found in the <a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en" target="_blank">Windows 2003 Support Tools</a>. The command for this utility amounts to something like this:</p> <blockquote> <p><font face="Courier New">setspn -A MSSQLSvc/[FULLY QUALIFIED DOMAIN NAME]:[PORT] [DOMAIN]\[LOGIN]</font></p> <p>For example:</p> <p><font face="Courier New">setspn -A MSSQLSvc/hq-dbadmin01.mydomain.com:1433 MYDOMAIN\sql.service</font></p> </blockquote> <p> </p> <p>Finally, there are a few things to do to make it all work:</p> <ul> <li>You have to restart your SQL Service after creating the SPN. </li> <li>For distributed queries or queries using a linked server where both servers are running as a domain account you must create a SPN for both servers </li> <li>You must use Kerberos for all of this to work. To check if you’re using Kerberos, after you’ve gone through all the steps above make a new connection and run the query below. It should return "KERBEROS” if everything’s set up right: </li> </ul> <blockquote> <p><font face="Courier New">select auth_scheme from sys.dm_exec_connections where </font><font face="Courier New"><a href="mailto:session_id=@@spid">session_id=@@spid</a></font><font face="Courier New"> </font></p> </blockquote> <p> </p> <p><strong>VISIT: </strong><a href="http://msdn.microsoft.com/en-us/library/aa905162(SQL.80).aspx" target="_blank">Administering SQL Server (SQL Server 2000): Security Account Delegation</a> <br /><strong><strong>VISIT</strong>:</strong> <a href="http://msdn.microsoft.com/en-us/library/ms677949(VS.85).aspx" target="_blank">MSDN Library: Service Principal Names</a> <br /><strong><strong>VISIT</strong>:</strong> <a href="http://en.wikipedia.org/wiki/Kerberos_(protocol)" target="_blank">Wikipedia: Kerberos</a> <br /><strong>DOWNLOAD:</strong> <a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en" target="_blank">Windows Server 2003 Service Pack 1 32-bit Support Tools</a></p> </div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Security SQL Server 2005 SQL Server 2008 Troubleshooting