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

Comments (20)

  1. Sahil Malik says:

    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

  2. Sahil Malik says:

    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

  3. MSDNArchive says:

    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

  4. MSDNArchive says:

    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

  5. 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/

  6. MSDNArchive says:

    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

  7. Hi Jian,

    Thanks for the interesting post. I’ve also seen some strangeness when using impersonation in conjunction with EXECUTE AS, depending on whether the database is marked as TRUSTWORTHY. If it is, the server’s system account is impersonated in all cases except if EXECUTE AS CALLER is specified. If the database is not marked TRUSTWORTHY, the WindowsIdentity property is null.

    To me this seems like a strange way to have implemented impersonation. I would rather see WindowsIdentity always be null if EXECUTE AS is used for anyone but the caller–I can’t see any reason to impersonate as the system account (does that actually do anything? isn’t the CLR routine effectively already impersonated as that account if you don’t explicitly impersonate?)

    I would be very interested in your comments on this and why it was designed that way…

    Thanks!

  8. Ben says:

    Jian,

    I have tried your code and am having little success.  When I execute this code as a SqlProcedure I get the following exception:

    Msg 6522, Level 16, State 1, Procedure SqlExecRemote, Line 0

    A .NET Framework error occurred during execution of user defined routine or aggregate ‘SqlExecRemote’:

    System.InvalidOperationException: Data access is not allowed in this context.  Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

    System.InvalidOperationException:

      at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)

      at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)

      at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)

      at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()

      at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()

      at Microsoft.SqlServer.Server.SqlContext.get_Pipe()

      at StoredProcedures.SqlExecRemote()

    Here is the code that I am using.

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using Microsoft.SqlServer.Server;

    using System.Security.Principal;

    public partial class StoredProcedures

    {

    [Microsoft.SqlServer.Server.SqlProcedure(Name="SqlExecRemote")]

    public static void SqlExecRemote()

    {

    WindowsImpersonationContext impersonatedUser = null;

    try

    {

    impersonatedUser = SqlContext.WindowsIdentity.Impersonate();

    using (SqlConnection con = new SqlConnection("Server=RemoteServer;Initial Catalog=master;Integrated Security=true"))

    {

    con.Open();

    SqlCommand com = new SqlCommand("select * from sysobjects",con);

    com.CommandType = CommandType.Text;

    SqlContext.Pipe.Send(com.ExecuteReader());

    }

    }

    catch (SqlException ex)

    {

    throw ex;

    }

    finally

    {

    if (impersonatedUser != null)  impersonatedUser.Undo();

    }

    }

    };

    When I comment out the Impersonate() call, then the procedure works just fine.  Any tips?

    Thanks,

    Ben

  9. MSDNArchive says:

    Hi Ben,

    The error you are seeing is because the

    SqlContext.Pipe.Send(com.ExecuteReader());

    call is actually inside the impersonation context. SqlContext.Pipe.Send is an inproc operation, it can’t run when it is inside the impersonation context. If you do a

    impersonatedUser.Undo();

    before calling SqlContext.Pipe.Send to send the reader to the pipe, it will work. I admit that the exception message is not clear enough to help for diagnosing the problem.

    Thanks,

    Jian

  10. Ben says:

    Worked like a charm.  Thanks!!

  11. Ben says:

    One last question (I hope).  When calling the procedure from a query window local to the SQL Server Instance, I get expected results from the procedure. But when I connect to the Instance from a remote client I get the following error:

    Msg 10312, Level 16, State 49, Procedure SqlExec, Line 0

    .NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

    The procedure is installed with "EXECUTE AS CALLER" which seems to be the problem.  When I ALTER PROC to change EXECUTE AS OWNER or EXECUTE AS ‘username’ then I can execute the procedure remotely as you would expect.  But the desired behavior is to always assume the context of the CALLER, and I just cant seem to get it to work.

    The exception is thrown at the Con.Open() statement in my previous example after making the change you recommended.

    I am not sure whether this is a delegation problem or a bug in this implementation, but the only variable that I have changed since the previous example is connecting remotely.

    -Ben

  12. MSDNArchive says:

    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

  13. Ben says:

    Yes to your question about putting the Undo method in the finally statement.  

    -Ben

  14. MSDNArchive says:

    Hi Ben, I tried your scenario to call the CLR stored proc from a remote client, and I got an exception on the Con.Open() with the following message:

    A .NET Framework error occurred during execution of user defined routine or aggregate ‘p_TESTNAME’:

    System.Data.SqlClient.SqlException: Login failed for user ‘NTAUTHORITYANONYMOUS LOGON’.

    This is expected because I can’t delegate the impersonation to the other server under my domain setting. I didn’t see the message that you are seeing.

    And for the question of why impersonation only works under "Execute As Caller", this is what the current SQL Server 2005’s design. No impersonation is allowed for Execute As User, Owner and Self.

    Thanks,

    Jian

  15. Ben says:

    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

  16. MSDNArchive says:

    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

  17. John says:

    Hello Jian,

    Nice post but I have a wierd little problem so I though you might be able to help me out.

    What I am trying to do is write a little function that returns the current user’s SID. My code is like this

    Return SqlContext.WindowsIdentity.User.ToString

    which fails with the same exception as above

    (Invalid op exception) but with a different trace that shows that the runtime cant get the windowsidentity:

    at Microsoft.SqlServer.Server.SqlContext.get_WindowsIdentity()

    Thanks

    John

  18. John says:

    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