Solved

Executing database queries in different user contexts.

Posted on 2003-10-24
5
270 Views
Last Modified: 2010-04-16
I am writing a MSSQL2000 database-aware application that is, among other things, going to run queries.  The database in question will have security settings in different places, and the query must be executed from the user context of a given user / group.  Users and groups refer to local system accounts or Active Directory accounts.  Assume the former.

Herein lies my problem.  I don't know how to begin to execute the query under the context of an arbitrary user.  How do I execute code in an arbitrary user's context?  Does the SqlConnection have to be created in that context, or just the query command?

Any solutions that don't use unmanaged code would be much preferred!  Thanks.

-Jahava
0
Comment
Question by:Jahava
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:zrh
ID: 9618817
As far as I know you'll have to recreate the connection to change users.
Are you using ASP.NET or Windows forms?
The impersonation method to use is different for each.
ZRH
0
 
LVL 2

Author Comment

by:Jahava
ID: 9619578
I am using a standard Windows service, which, I assume, lies on the Windows Forms side.

-Jahava
0
 
LVL 6

Expert Comment

by:zrh
ID: 9631501
Solution 1:  (Sorry about the unmanaged code, but don't know how else to do it)
--------------------------------------------------------------------------------
1) Setup the connection string to include Integrated Security=SSPI.

2) Setup the users (domain\username or machine\username) in the database with permissions needed.

3) Include in the class in question:
    [DllImport("advapi32.dll", SetLastError=true)]
    public static extern bool LogonUser(String lpszUsername, String lpszDomain, String lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);

4) Include this in assembly in question:
    [assembly:SecurityPermissionAttribute(SecurityAction.RequestMinimum, UnmanagedCode=true)]
    [assembly:PermissionSetAttribute(SecurityAction.RequestMinimum, Name = "FullTrust")]

5) Include this method:
    public bool ChangeIdentity(string username, string domain, string password) {
            IntPtr tokenHandle = IntPtr.Zero, dupeTokenHandle = IntPtr.Zero;
            const int LOGON32_PROVIDER_DEFAULT = 0;
            const int LOGON32_LOGON_INTERACTIVE = 2;
            const int SecurityImpersonation = 2;

            // Call LogonUser to obtain a handle to an access token.
            bool ok = LogonUser(username, domain, password, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref tokenHandle);

            if (!ok) {
                // Failed to get user token handle...
                return false;
            }

            ok = DuplicateToken(tokenHandle, SecurityImpersonation, ref dupeTokenHandle);
            if (!ok) {
                CloseHandle(tokenHandle);
                // Failed to get duplicate token handle...    
                return false;
            }
           
            // The token that is passed to the following method must
            // be a primary token in order to use it for impersonation.
            WindowsIdentity.Impersonate(dupTokenHandle);
           
            // Free the tokens.
            if (tokenHandle != IntPtr.Zero) CloseHandle(tokenHandle);
            if (dupeTokenHandle != IntPtr.Zero) CloseHandle(dupeTokenHandle);
           
            return true; // Success
            Note: You may find returning the old user context more or less helpful.
    }

6) Call the above method with needed user details before executing the query.

NOTE: The above method is based off the one in the msdn documentation under WindowsIdentity.Impersonate().

Solution 2:
---------------------------------------------------------------------
Use mixed mode authentication, and setup a sql database user for each access role you need.  Then use
those to access the database instead.

Hope that helps,
ZRH
0
 
LVL 2

Author Comment

by:Jahava
ID: 9634113
Yeah, ZRH, that is something about what I expected.  Thanks for the reply.

Because I to want to multithread this application such that each Thread might be able to call a database query under a different user context, does the Impesonate function change the user access of the current Thread, or of the entire AppDomain?  In that case, would I have to create a new AppDomain for each threaded impersonation?

Also, how would this demonstration change with respect to Active Directory?  Does the LogonUser function return an access token for an Active Directory user just as it would a local windows user?

-Jahava
0
 
LVL 6

Accepted Solution

by:
zrh earned 125 total points
ID: 9636739
I think (not absolutely positive) that this changes the principal for the current thread.
You may have to set the Thread.CurrentPrincipal property for the respective threads if your doing a multithreaded app though.
Also, the above should work for active directory users too.  (Don't have AD where I'm at now so can't test it, but it should work)

If you think you can use this method i'll look into it some more for you...

ZRH
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now