Solved

Executing database queries in different user contexts.

Posted on 2003-10-24
5
268 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
Comment Utility
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
Comment Utility
I am using a standard Windows service, which, I assume, lies on the Windows Forms side.

-Jahava
0
 
LVL 6

Expert Comment

by:zrh
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 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

9 Experts available now in Live!

Get 1:1 Help Now