Edit

Share via

Impersonation and credentials for connections

  • Article

Applies to: SQL Server

In the SQL Server common language runtime (CLR) integration, using Windows Authentication is complex, but is more secure than using SQL Server Authentication. When using Windows 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's possible to map a CLR function to a proxy identity, so that its outbound connections have a different security context than the Windows service account.

In some cases, you might 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 Windows Authentication. After you obtain 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.

After you call SQLContext.WindowsIdentity.Impersonate, you can't access local data and you can't access system data. To access data again, you have to call WindowsImpersonationContext.Undo.

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

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;
}

Note

For information about behavior changes in impersonation, see Breaking changes to Database Engine features in SQL Server 2016.

Furthermore, if you obtained the Windows identity instance, by default you can't propagate that instance to another computer; Windows security infrastructure restricts that by default. However, there's a mechanism called delegation that enables propagation of Windows identities across multiple trusted computers. For more information about delegation, see Kerberos Protocol Transition and Constrained Delegation.

Related content