Using windows auth and alternate credentials for sql server

[TS]

Hi, I am trying to figure out how to use connection pooling and a single set
of credentials to access a sql server using integrated windows security. I
am trying to do what I do in asp.net using the impersonation tag where I set
the user I want to impersonate to a single user account. My application
handles security so that if I can use the app, I can hit the DB.

So say I have an authenticated user that is using my app, I want to be able
to only have 1 user account that has access to the DB instead of one for
every user who uses the app.

thanks

 

[Ying-Shen Yu[MSFT]]

Hello,

The WindowsIdentity class is useful to represent a user. Before doing
that,you need to call the LogonUser API to get the token of the user with
its username and password. Basically, you may take following steps to do
it:

1. Get the Username and Password from the TextBoxs on the form
2. Get the security token of specific user with LogonUser API via PInvoke
3. Create a temp WindowsIdentity with the token

If you want to impersonate that user to access some resources are only
available to him, you need to take the following two additional steps:

4. Impersonate the specific user with the temp WindowsIdentity
5. Undo the Impersonation

You may refer to the following article to get some sample codes:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q306158

For your convenience, I also attached a sample code here:

using System;
using System.Runtime.InteropServices;
using System.Security.Principal;
using System.Security.Permissions;

[assembly:SecurityPermissionAttribute(SecurityAction.RequestMinimum,
UnmanagedCode=true)]
public class Class1
{
[DllImport("advapi32.dll")]
public static extern bool LogonUser(String lpszUsername, String
lpszDomain, String lpszPassword,
int dwLogonType, int dwLogonProvider, out int phToken);

public static void Main(string[] args)
{
// The Windows NT user token.
int token1;

// Get the user token for the specified user, machine, and password
using the unmanaged LogonUser method.
bool loggedOn = LogonUser(
// User name.
"bob",
// Machine name, you may get current machine name using
Environment.MachineName
"AARDVARK",
// Password.
"coffee",
// Logon type = LOGON32_LOGON_NETWORK_CLEARTEXT.
3,
// Logon provider = LOGON32_PROVIDER_DEFAULT.
0,
// The user token for the specified user is returned here.
out token1);

Console.WriteLine("LogonUser called");

// Call GetLastError to try to determine why logon failed if it did
not succeed.
int ret = Marshal.GetLastWin32Error();

Console.WriteLine("LogonUser Success? " + loggedOn);
Console.WriteLine("NT Token Value: " + token1);
if (ret != 0) Console.WriteLine("Error code (126 == \"Specified
module could not be found\"): " + ret);

//Starting impersonation here:
Console.WriteLine("\n\nBefore impersonation:\n");
WindowsIdentity mWI1 = WindowsIdentity.GetCurrent();
Console.WriteLine(mWI1.Name);
Console.WriteLine(mWI1.Token);

IntPtr token2 = new IntPtr(token1);

Console.WriteLine("\n\nNew identity created:\n");
WindowsIdentity mWI2 = new WindowsIdentity(token2);
Console.WriteLine(mWI2.Name);
Console.WriteLine(mWI2.Token);

// Impersonate the user.
WindowsImpersonationContext mWIC = mWI2.Impersonate();

Console.WriteLine("\n\nAfter impersonation:\n");
WindowsIdentity mWI3 = WindowsIdentity.GetCurrent();
Console.WriteLine(mWI3.Name);
Console.WriteLine(mWI3.Token);

// Revert to previous identity.
mWIC.Undo();

Console.WriteLine("\n\nAfter impersonation is reverted:\n");
WindowsIdentity mWI4 = WindowsIdentity.GetCurrent();
Console.WriteLine(mWI4.Name);
Console.WriteLine(mWI4.Token);
}
}


Best regards,

Ying-Shen Yu [MSFT]
Microsoft Community Support
Get Secure! - www.microsoft.com/security

This posting is provided "AS IS" with no warranties and confers no rights.
This mail should not be replied directly, please remove the word "online"
before sending mail.

 

[TS]

Why is it so easy to do impersonation in asp.net and so much harder for a
windows app?

Hello,

The WindowsIdentity class is useful to represent a user. Before doing
that,you need to call the LogonUser API to get the token of the user with
its username and password. Basically, you may take following steps to do
it:

1. Get the Username and Password from the TextBoxs on the form
2. Get the security token of specific user with LogonUser API via PInvoke
3. Create a temp WindowsIdentity with the token

If you want to impersonate that user to access some resources are only
available to him, you need to take the following two additional steps:

4. Impersonate the specific user with the temp WindowsIdentity
5. Undo the Impersonation

You may refer to the following article to get some sample codes:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q306158

For your convenience, I also attached a sample code here:

using System;
using System.Runtime.InteropServices;
using System.Security.Principal;
using System.Security.Permissions;

[assembly:SecurityPermissionAttribute(SecurityAction.RequestMinimum,
UnmanagedCode=true)]
public class Class1
{
[DllImport("advapi32.dll")]
public static extern bool LogonUser(String lpszUsername, String
lpszDomain, String lpszPassword,
int dwLogonType, int dwLogonProvider, out int phToken);

public static void Main(string[] args)
{
// The Windows NT user token.
int token1;

// Get the user token for the specified user, machine, and password
using the unmanaged LogonUser method.
bool loggedOn = LogonUser(
// User name.
"bob",
// Machine name, you may get current machine name using
Environment.MachineName
"AARDVARK",
// Password.
"coffee",
// Logon type = LOGON32_LOGON_NETWORK_CLEARTEXT.
3,
// Logon provider = LOGON32_PROVIDER_DEFAULT.
0,
// The user token for the specified user is returned here.
out token1);

Console.WriteLine("LogonUser called");

// Call GetLastError to try to determine why logon failed if it did
not succeed.
int ret = Marshal.GetLastWin32Error();

Console.WriteLine("LogonUser Success? " + loggedOn);
Console.WriteLine("NT Token Value: " + token1);
if (ret != 0) Console.WriteLine("Error code (126 == \"Specified
module could not be found\"): " + ret);

//Starting impersonation here:
Console.WriteLine("\n\nBefore impersonation:\n");
WindowsIdentity mWI1 = WindowsIdentity.GetCurrent();
Console.WriteLine(mWI1.Name);
Console.WriteLine(mWI1.Token);

IntPtr token2 = new IntPtr(token1);

Console.WriteLine("\n\nNew identity created:\n");
WindowsIdentity mWI2 = new WindowsIdentity(token2);
Console.WriteLine(mWI2.Name);
Console.WriteLine(mWI2.Token);

// Impersonate the user.
WindowsImpersonationContext mWIC = mWI2.Impersonate();

Console.WriteLine("\n\nAfter impersonation:\n");
WindowsIdentity mWI3 = WindowsIdentity.GetCurrent();
Console.WriteLine(mWI3.Name);
Console.WriteLine(mWI3.Token);

// Revert to previous identity.
mWIC.Undo();

Console.WriteLine("\n\nAfter impersonation is reverted:\n");
WindowsIdentity mWI4 = WindowsIdentity.GetCurrent();
Console.WriteLine(mWI4.Name);
Console.WriteLine(mWI4.Token);
}
}


Best regards,

Ying-Shen Yu [MSFT]
Microsoft Community Support
Get Secure! - www.microsoft.com/security

This posting is provided "AS IS" with no warranties and confers no rights.
This mail should not be replied directly, please remove the word "online"
before sending mail.

 

[TS]

Why is it so hard to do in win forms and so easy in asp.net? - its a simple
tag in config file.

Hello,

The WindowsIdentity class is useful to represent a user. Before doing
that,you need to call the LogonUser API to get the token of the user with
its username and password. Basically, you may take following steps to do
it:

1. Get the Username and Password from the TextBoxs on the form
2. Get the security token of specific user with LogonUser API via PInvoke
3. Create a temp WindowsIdentity with the token

If you want to impersonate that user to access some resources are only
available to him, you need to take the following two additional steps:

4. Impersonate the specific user with the temp WindowsIdentity
5. Undo the Impersonation

You may refer to the following article to get some sample codes:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q306158

For your convenience, I also attached a sample code here:

using System;
using System.Runtime.InteropServices;
using System.Security.Principal;
using System.Security.Permissions;

[assembly:SecurityPermissionAttribute(SecurityAction.RequestMinimum,
UnmanagedCode=true)]
public class Class1
{
[DllImport("advapi32.dll")]
public static extern bool LogonUser(String lpszUsername, String
lpszDomain, String lpszPassword,
int dwLogonType, int dwLogonProvider, out int phToken);

public static void Main(string[] args)
{
// The Windows NT user token.
int token1;

// Get the user token for the specified user, machine, and password
using the unmanaged LogonUser method.
bool loggedOn = LogonUser(
// User name.
"bob",
// Machine name, you may get current machine name using
Environment.MachineName
"AARDVARK",
// Password.
"coffee",
// Logon type = LOGON32_LOGON_NETWORK_CLEARTEXT.
3,
// Logon provider = LOGON32_PROVIDER_DEFAULT.
0,
// The user token for the specified user is returned here.
out token1);

Console.WriteLine("LogonUser called");

// Call GetLastError to try to determine why logon failed if it did
not succeed.
int ret = Marshal.GetLastWin32Error();

Console.WriteLine("LogonUser Success? " + loggedOn);
Console.WriteLine("NT Token Value: " + token1);
if (ret != 0) Console.WriteLine("Error code (126 == \"Specified
module could not be found\"): " + ret);

//Starting impersonation here:
Console.WriteLine("\n\nBefore impersonation:\n");
WindowsIdentity mWI1 = WindowsIdentity.GetCurrent();
Console.WriteLine(mWI1.Name);
Console.WriteLine(mWI1.Token);

IntPtr token2 = new IntPtr(token1);

Console.WriteLine("\n\nNew identity created:\n");
WindowsIdentity mWI2 = new WindowsIdentity(token2);
Console.WriteLine(mWI2.Name);
Console.WriteLine(mWI2.Token);

// Impersonate the user.
WindowsImpersonationContext mWIC = mWI2.Impersonate();

Console.WriteLine("\n\nAfter impersonation:\n");
WindowsIdentity mWI3 = WindowsIdentity.GetCurrent();
Console.WriteLine(mWI3.Name);
Console.WriteLine(mWI3.Token);

// Revert to previous identity.
mWIC.Undo();

Console.WriteLine("\n\nAfter impersonation is reverted:\n");
WindowsIdentity mWI4 = WindowsIdentity.GetCurrent();
Console.WriteLine(mWI4.Name);
Console.WriteLine(mWI4.Token);
}
}


Best regards,

Ying-Shen Yu [MSFT]
Microsoft Community Support
Get Secure! - www.microsoft.com/security

This posting is provided "AS IS" with no warranties and confers no rights.
This mail should not be replied directly, please remove the word "online"
before sending mail.

 

[Ying-Shen Yu[MSFT]]

Hi,

That's because the ASP.NET runtime is doing this for you behind the scenes.
Since you're writing an application of your own, you would have to handle
this yourself.

By the way, impersonation only impersonates the calling thread, so if there
are several threads needs impersonation, you need call
WindowsIdentity.Impersonate method on each thread.

Thanks!

Best regards,

Ying-Shen Yu [MSFT]
Microsoft Community Support
Get Secure! - www.microsoft.com/security

This posting is provided "AS IS" with no warranties and confers no rights.
This mail should not be replied directly, please remove the word "online"
before sending mail.

 

[TS]

thanks, that makes sense.

So to be sure I understand you, everybody that wants to maximize
performance, by pooling connections to a database and keeping only one login
to that database instead of one for every user of app, have to do the steps
you outlined previously?

 

[Ying-Shen Yu[MSFT]]

Hi,

Yes, you need do impersonation if your DB server authentication is using
Windows Integrated Security.
Please feel free to reply this thread, if you have anything unclear about
this issue.

Best regards,

Ying-Shen Yu [MSFT]
Microsoft Community Support
Get Secure! - www.microsoft.com/security

This posting is provided "AS IS" with no warranties and confers no rights.
This mail should not be replied directly, please remove the word "online"
before sending mail.