Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Executing database queries in different user contexts.

Posted on 2003-10-24
5
Medium Priority
?
294 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 500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Screencast - Getting to Know the Pipeline
Suggested Courses

783 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