Solved

Executing database queries in different user contexts.

Posted on 2003-10-24
5
272 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Server Error 11 58
Video Player 11 23
C# 2008 (3.5 Framework) An object reference is required for the non-static field, method, or property 3 30
Expression Evaluater 3 26
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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