Impersonation and Credentials for Connections
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 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 Windows 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.
After you call SQLContext.WindowsIdentity.Impersonate, you cannot access local data and you cannot access system data. To access data again, you have to call WindowsImpersonationContext.Undo.
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;
}
Note
For information about behavior changes in impersonation, see Breaking Changes to Database Engine Features in SQL Server 2014.
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".