none
How do you implement programmatic impersonation with LINQ? RRS feed

  • Question

  • All,

    I am attempting to use Windows Integrated Authentication in my ASP.NET application to access our SQL Server backend.  I am stumped on how to do this only for the LINQ database access.  I need the rest of my ASP.NET application to run in the context of the default NetworkService account.

    How do you get LINQ to open its SQLConnection using impersonation?  Are there any protected methods you can access or events to intercept when the connection is opened?

    Monday, June 15, 2009 6:34 PM

All replies

  • E-Z way: Impersonate the user, open a connection, store it, and pass that connection to the L2S DataContext constructor.
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools, www.huagati.com/L2SProfiler
    Tuesday, June 16, 2009 3:14 PM
    Answerer
  • I wonder what would be the best way to scale that sort of solution.  I don't have very good knowledge of the internal management of the SQLConnection class.

    Would I be able to get away with one global application wide SQLConnection or would multiple requests clog the connection?

    If I create a new connection for each DataContext that I create, will I end up with a bunch of database connections that are left hanging open or would garbage collection be sufficient to control that situation?  OR, is there some sort of behind the scenes connection pooling that keeps the situation under control?

    Right now, I am thinking about subclassing DataContext and overriding the default, parameterless constructor so that it passes the open SQLConnection to the base DataContext class constructor as you suggest.  Then I would set the base class for my dbml generated DataContext to the subclassed DataContext with the already opened SQLConnection.

    Hopefully that would quickly introduce the impersonation in one and only one place and also trickle down to all the DataContext instances I've already programmed into my entire application.
    Tuesday, June 16, 2009 4:09 PM
  • The best way to do this is to have the connection pool service account which runs the web site be that global account which has access rights to the database. Then when the code access the database, the database knows about that user and allows access. That way the account information is safely placed away from the code and appropriate access is assured.

    Note I always have individual connection pools per web site for just this purpose; also it allows for cycling the pool without affecting other sites if needed. HTH

    William Wegerson (www.OmegaCoder.Com)
    • Proposed as answer by Thomas Koelle Thursday, May 19, 2011 1:37 PM
    Tuesday, June 16, 2009 4:51 PM
    Moderator
  • A global app-wide sql connection would be a bottleneck as it can only service one request at a time.

    You could do the impersonation and connection handling in the constructor (and dispose) of the DC, e.g:

    internal partial class SomeDataContext
    {
    private System.Data.SqlClient.SqlConnection _connection = null;
    
    public SomeDataContext(string connectString, string domainName, string userName, string password) 
        : this(connectString)
    {
        IntPtr logonToken = IntPtr.Zero;
        IntPtr dupeLogonToken = IntPtr.Zero;
        WindowsImpersonationContext impCtx = null;
        try
        {
            //logon as the user to be impersonated
            bool returnValue = LogonUser(userName, domainName, password, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref logonToken);
    
            //duplicate the logon token
            bool retVal = DuplicateToken(logonToken, SecurityImpersonation, ref dupeLogonToken);
    
            //impersonate
            WindowsIdentity impersonatedIdentity = new WindowsIdentity(dupeLogonToken);
            impCtx = impersonatedIdentity.Impersonate();
    
            //open the connection
            _connection = (System.Data.SqlClient.SqlConnection)this.Connection;
            _connection.Open();
        }
        catch
        {
            //bad, bad stuff happened.
            throw new SomethingBadHappenedException();
        }
        finally
        {
            //revert back to the running user
            if (impCtx != null)
            {
                impCtx.Undo();
            }
    
            //release logon tokens
            if (logonToken != IntPtr.Zero)
            {
                CloseHandle(logonToken);
            }
            if (dupeLogonToken != IntPtr.Zero)
            {
                CloseHandle(dupeLogonToken);
            }
        }
    }
    
    protected override void Dispose(bool disposing)
    {
        if (_connection != null && _connection.State == System.Data.ConnectionState.Open)
        {
            _connection.Close();
        }
        base.Dispose(disposing);
    }
    }
    Note: in real life you need to also check the return values from the LogonUser and DuplicateToken win apis - excluded from the sample above to keep it short...
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools, www.huagati.com/L2SProfiler
    Wednesday, June 17, 2009 3:54 AM
    Answerer
  • Do you have a solution for the impersonated db connect in LINQ 2 SQL?

     

    Thilo


    Thursday, May 12, 2011 10:16 AM