Share via

Impersonation inside SQLCLR Stored Procedure [Jian Zeng]

  • Article

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

Comments

  • Anonymous
    January 25, 2006
    Jian,

    Thank you for this post.

    For the above to work, don't you have to be using Integrated Authentication in the first place? i.e. this concept won't work with SQL Server authentication.

    It's a bit implied, but thought it may make sense to clearly spell that out :)

    But I could be way off in my assumption anyway.

    SM
  • Anonymous
    January 25, 2006
    1 more comment :) -

    It would be nice to hear your views/best practices on SQLCLR assemblies being used over different schemas/windows auth registrations. If I'm dbo, and I register something using windows authentication - will you have to be careful of any issues executing it as a different user?

    SM
  • Anonymous
    January 25, 2006
    Sahil, Regarding the first part, you are right. For the above code to work, you will need to invoke the SQLCLR stored procedure with integrated security in the first place.

    HTH,
    Sushil Chordia
  • Anonymous
    January 25, 2006
    Thanks Sushil :)
  • Anonymous
    January 25, 2006
    Sahil, speaking of best practice, I haven’t seen anyone comparing the pros and cons of impersonation inside SQLCLR yet. But I think the SQLCLR routine developer has to aware that SQL Server service account is used when accessing external resources. Especially when the service account has higher privilege than the client user account and you want to limit the access, you might need to do impersonation.

    Thanks,
    Jian
  • Anonymous
    January 26, 2006
    As is mentioned above, if a connection using SQL Server authentication attempts to impersonate it will be given NULL instead of a token. However, an interesting thing is that if you are connected as sa (or another SQL Server login part of sysadmins) you will get a token; that of the account SQL Server is running as..

    Another interesting thing I noted is that if you want to access SqlContext.WindowsIdentity inside a function you must specify DataAccess=DataAccessKind.Read and/or SystemDataAccess=SystemDataAccessKind.Read. It works with either one, which seems a bit strange.

    http://www.hedgate.net/blog/2006/01/17/two-notes-about-impersonation-in-sqlclr/
  • Anonymous
    January 26, 2006
    Thanks Chris of getting into the detail of the SqlContext.WindoesIdentity behaviors!

    I think the reason you need to specify DataAccessKind.Read or SystemDataAccessKind.Read is that any access to SqlContext class needs that attribute to be specified.

    Thanks,
    Jian
  • Anonymous
    January 27, 2006
    The comment has been removed
  • Anonymous
    February 15, 2006
    The comment has been removed
  • Anonymous
    February 15, 2006
    The comment has been removed
  • Anonymous
    February 15, 2006
    Worked like a charm.  Thanks!!
  • Anonymous
    February 21, 2006
    The comment has been removed
  • Anonymous
    February 22, 2006
    Hi Ben,

    The error message you are seeing normally indicates that the impersonation is not undone before you exit the SQLCLR procedure. Did you put the impersonatedUser.Undo into the finally block? Maybe some exception happened that causes the Undo is not being called. But we still need to investigate why the exception happened first. I will let you know what I find out tomorrow.

    Thanks,
    Jian
  • Anonymous
    February 23, 2006
    Yes to your question about putting the Undo method in the finally statement.  

    -Ben
  • Anonymous
    February 24, 2006
    The comment has been removed
  • Anonymous
    February 24, 2006
    I am now getting the error that you cite.  Previously, I had commented out the Undo() method and hence the previous error.  My user account resides in a different domain than the DB Server instances that I am using in my test code.  Its clear that there is a delegation problem when using my account from DOMAIN1 while my database server instances reside in DOMAIN2.  After doing a little sniffing, its apparent that the DB Server that is performing the remote query on another instance is trying to authenticate my account and is using NTLM to do so.  When both accounts and servers reside in the same DOMAIN, then all is well; but when the CALLERS account resides in a different domain, then I am getting this error. DOMAIN1 and DOMAIN2 have a two way trust between eachother and I am told that the firewall allows KERBEROS authentication (though I will find out more on Monday).  Is there anything else that I can tell you about my environment that would be more helpful?

    -Ben
  • Anonymous
    March 15, 2006
    Hi Ben,

    Did you find out more about your firewall configuration? As far as I know, if KERBEROS is enable, you should be able to get around the delegate problem.

    Thanks,
    Jian
  • Anonymous
    April 21, 2006
    The comment has been removed
  • Anonymous
    April 21, 2006
    It seems I solved it on my own.

    I had to assign the SqlFunction attribute to my function like this:
    <Microsoft.SqlServer.Server.SqlFunction(systemdataaccess:=SystemDataAccessKind.Read)> _
       Public Shared Function CurrentUser() As String
       
       
  • Anonymous
    June 16, 2009
    PingBack from http://fixmycrediteasily.info/story.php?id=17180