Impersonation and Credentials for Connections

In the Microsoft SQL Server common language runtime (CLR) integration, using Windows authentication is complex, but is more secure than using SQL Server Authentication. When using integrated authentication, keep in mind the following considerations.

By default, a SQL Server process that connects out to Windows acquires the security context of the SQL Server Windows service account. But it is possible to map a CLR function to a proxy identity, so that its outbound connections have a different security context than that of the Windows service account.

In some cases, you may want to impersonate the caller by using the SqlContext.WindowsIdentity property instead of running as the service account. The WindowsIdentity instance represents the identity of the client that invoked the calling code, and is only available when the client used integrated authentication. After you have obtained the WindowsIdentity instance, you can call Impersonate to change the security token of the thread, and then open ADO.NET connections on behalf of the client.

The following example shows how to impersonate the caller by using the SqlContext.WindowsIdentity property.

Visual C#

WindowsIdentity clientId = null;
WindowsImpersonationContext impersonatedUser = null;

clientId = SqlContext.WindowsIdentity;

// This outer try block is used to protect from 
// exception filter attacks which would prevent
// the inner finally block from executing and 
// resetting the impersonation.
try
{
   try
   {
      impersonatedUser = clientId.Impersonate();
      if (impersonatedUser != null)
         return GetFileDetails(directoryPath);
         else return null;
   }
   finally
   {
      if (impersonatedUser != null)
         impersonatedUser.Undo();
   }
}
catch
{
   throw;
}

For a more complete example of impersonation using the SqlContext.WindowsIdentity property, see the Impersonation Sample.

Furthermore, if you obtained the Microsoft Windows identity instance, by default you cannot propagate that instance to another computer; Windows security infrastructure restricts that by default. There is, however, a mechanism called "delegation" that enables propagation of Windows identities across multiple trusted computers. You can learn more about delegation in the TechNet article, "Kerberos Protocol Transition and Constrained Delegation".

See Also

Concepts

The SqlContext Object

Other Resources

Authentication Mode

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	Updated content: Re-wrote first two paragraphs for clarity. Added code example.