Impersonation inside SQLCLR Stored Procedure [Jian Zeng]

In SQL Server 2005, we now have the ability to write managed (or CLR) code inside a Stored Procedure. This implies that you now have the capability to connect to a remote or the local SQL Server with ADO.NET via the System.Data assembly. When doing so with intergrated authentication, you normally will specify 'integrated security = true' in the connection string. Since you are inside SQLCLR, the credentials used to connect, will be the NT account from which SQL Server service is running. If the SQL Server was started as an account that doesn’t have the permission to access the remote server, you will get an error message that will indicate the login failure when executing the SQLCLR Stored Procedure. For example, if the service is running as Network Service account, you will get an error message that saying something like

"A .NET Framework error occurred during execution of user defined routine or aggregate 'p_TESTNAME': System.Data.SqlClient.SqlException: Login failed for user ‘NT AUTHORITY\NETWORK SERVICE'.”

This might not be what you want. Most of the times you want to use the NT user account that is currently logged in that SQL Server box rather than the SQL Server service account. How can you do that? SqlContext.WindowsIdentity property is what you need. When you call SqlContext.WindowsIdentity inside SQLCLR, it will return the WindowsIdentity token of the user that logs in the machine. Then you will have to impersonate that user before the data access connection is being called. The following is the code snippet to show you how to do it:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Security.Principal;
public class c_TESTNAME
{
public static void p_TESTNAME ()
{
WindowsIdentity newId = SqlContext.WindowsIdentity;
WindowsImpersonationContext impersonatedUser = newId.Impersonate();

         try {
using (SqlConnection conn = new SqlConnection("Server=RemoteServer;Integrated Security =true"))
{
conn.Open();
//Do something…
conn.Close();
}
}
finally {
impersonatedUser.Undo();
}
}
}

Notice that I wrap the database connection code inside a try..finally block. This will make sure that the user’s context always gets reverted. Otherwise the execution of the SQLCLR stored procedure will complain that the thread NT token was not reverted.

The above code works fine with one limitation. When we changed the user’s context by impersonating a different user inside SQLCLR, you can’t do any inproc data access. You will get an error when you try to open a connection with “context connection = true”. The inproc data access is only allowed after you call WindowsImpersonationContex.Undo() method.

Jian Zeng, ADO.Net

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights