SQL Connection as a differnt user

Before connecting to the database you can impersonate another NT user and the connection will be made in that users context.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemsecurityprincipalwindowsidentityclasstopic.asp

You will need to know or prompt for the NT users password.

Ah, this is dangerous. NEVER permit the user to enter any unvalidated information into the ConnectionString. This is an easy pathway to SQL Injection attacks.

I've been lobbying the Visual Studio people for a decade to expose an easy way to impersonate a specific user. One approach that I discuss in my book is to use a RUN AS approach that executes the development tool (Visual Studio) or the executable itself as a specified user. We talk about how to setup a shortcut that uses this alternate name to make it easy to retest.

hth

Imports System

Imports System.Runtime.InteropServices

Imports System.Security.Principal

Imports System.Security.Permissions

Imports Microsoft.VisualBasic

Public Class LoginForm1

Protected Friend canceled As Boolean = False

Friend Impersonating As Boolean = False

' TODO: Insert code to perform custom authentication using the provided username and password

' (See http://go.microsoft.com/fwlink/?LinkId=35339).

' The custom principal can then be attached to the current thread's principal as follows:

' My.User.CurrentPrincipal = CustomPrincipal

' where CustomPrincipal is the IPrincipal implementation used to perform authentication.

' Subsequently, My.User will return identity information encapsulated in the CustomPrincipal object

' such as the username, display name, etc.

Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click

Me.OK.Enabled = False

Me.Cursor = Cursors.WaitCursor

'Me.Hide()

Dim tokenHandle As New IntPtr(0)

Dim dupeTokenHandle As New IntPtr(0)

Try

Dim userName, domainName As String

' Get the user token for the specified user, domain, and password using the

' unmanaged LogonUser method.

' The local machine name can be used for the domain name to impersonate a user on this machine.

domainName = txtDomain.Text

userName = txtUsername.Text

Const LOGON32_PROVIDER_DEFAULT As Integer = 0

'This parameter causes LogonUser to create a primary token.

Const LOGON32_LOGON_INTERACTIVE As Integer = 2

tokenHandle = IntPtr.Zero

' Call LogonUser to obtain a handle to an access token.

Dim returnValue As Boolean = LogonUser(userName, domainName, txtPassword.Text, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, tokenHandle)

'Set the flag on the form t osee if they are impersonating

Impersonating = returnValue

If False = returnValue Then

Dim ret As Integer = Marshal.GetLastWin32Error()

'dim errorMessage As String = String.Format("LogonUser failed with error code : {0}", ret)

'messageBox.Show(errorMessage, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)

Throw New System.ComponentModel.Win32Exception(ret)

Return

End If

Dim success As String

If returnValue Then success = "Yes" Else success = "No"

Dim successMessage As String = "Did LogonUser succeed? " + success

'successMessage += vbCrLf + "Value of Windows NT token: " + tokenHandle.ToString()

' Check the identity.

successMessage += vbCrLf + "Before impersonation: " + WindowsIdentity.GetCurrent().Name

' Use the token handle returned by LogonUser.

Dim newId As New WindowsIdentity(tokenHandle)

Dim impersonatedUser As WindowsImpersonationContext = newId.Impersonate()

' Check the identity.

successMessage += vbCrLf + "After impersonation: " + WindowsIdentity.GetCurrent().Name

'TODO Uncomment this line if you want to continue using the user

' Stop impersonating the user.

'impersonatedUser.Undo()

' Check the identity.

successMessage += vbCrLf + "Application will run as: " + WindowsIdentity.GetCurrent().Name

MessageBox.Show(successMessage, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

' Free the tokens.

'Marsdata.Show()

If Not System.IntPtr.op_Equality(tokenHandle, IntPtr.Zero) Then

CloseHandle(tokenHandle)

End If

Catch ex As Exception

If Me.txtUsername.Text = "" Then

MessageBox.Show(("Exception occurred. " + ex.Message), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)

MessageBox.Show(("Please provide a username & Password"), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

MessageBox.Show(("Exception occurred. " + ex.Message), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)

End If

End Try

Me.Close()

End Sub

Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click

canceled = True

Me.Close()

End Sub

Private Declare Auto Function LogonUser Lib "advapi32.dll" (ByVal lpszUsername As [String], _

ByVal lpszDomain As [String], ByVal lpszPassword As [String], _

ByVal dwLogonType As Integer, ByVal dwLogonProvider As Integer, _

ByRef phToken As IntPtr) As Boolean

<DllImport("kernel32.dll")> _

Public Shared Function FormatMessage(ByVal dwFlags As Integer, ByRef lpSource As IntPtr, _

ByVal dwMessageId As Integer, ByVal dwLanguageId As Integer, ByRef lpBuffer As [String], _

ByVal nSize As Integer, ByRef Arguments As IntPtr) As Integer

End Function

Public Declare Auto Function CloseHandle Lib "kernel32.dll" (ByVal handle As IntPtr) As Boolean

Public Declare Auto Function DuplicateToken Lib "advapi32.dll" (ByVal ExistingTokenHandle As IntPtr, _

ByVal SECURITY_IMPERSONATION_LEVEL As Integer, _

ByRef DuplicateTokenHandle As IntPtr) As Boolean

End Class

Imports System

Imports System.Runtime.InteropServices

Imports System.Security.Principal

Imports System.Security.Permissions

Imports Microsoft.VisualBasic

Public Class LoginForm1

Protected Friend canceled As Boolean = False

Friend Impersonating As Boolean = False

' TODO: Insert code to perform custom authentication using the provided username and password

' (See http://go.microsoft.com/fwlink/?LinkId=35339).

' The custom principal can then be attached to the current thread's principal as follows:

' My.User.CurrentPrincipal = CustomPrincipal

' where CustomPrincipal is the IPrincipal implementation used to perform authentication.

' Subsequently, My.User will return identity information encapsulated in the CustomPrincipal object

' such as the username, display name, etc.

Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click

Me.OK.Enabled = False

Me.Cursor = Cursors.WaitCursor

'Me.Hide()

Dim tokenHandle As New IntPtr(0)

Dim dupeTokenHandle As New IntPtr(0)

Try

Dim userName, domainName As String

' Get the user token for the specified user, domain, and password using the

' unmanaged LogonUser method.

' The local machine name can be used for the domain name to impersonate a user on this machine.

domainName = txtDomain.Text

userName = txtUsername.Text

Const LOGON32_PROVIDER_DEFAULT As Integer = 0

'This parameter causes LogonUser to create a primary token.

Const LOGON32_LOGON_INTERACTIVE As Integer = 2

tokenHandle = IntPtr.Zero

' Call LogonUser to obtain a handle to an access token.

Dim returnValue As Boolean = LogonUser(userName, domainName, txtPassword.Text, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, tokenHandle)

'Set the flag on the form t osee if they are impersonating

Impersonating = returnValue

If False = returnValue Then

Dim ret As Integer = Marshal.GetLastWin32Error()

'dim errorMessage As String = String.Format("LogonUser failed with error code : {0}", ret)

'messageBox.Show(errorMessage, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)

Throw New System.ComponentModel.Win32Exception(ret)

Return

End If

Dim success As String

If returnValue Then success = "Yes" Else success = "No"

Dim successMessage As String = "Did LogonUser succeed? " + success

'successMessage += vbCrLf + "Value of Windows NT token: " + tokenHandle.ToString()

' Check the identity.

successMessage += vbCrLf + "Before impersonation: " + WindowsIdentity.GetCurrent().Name

' Use the token handle returned by LogonUser.

Dim newId As New WindowsIdentity(tokenHandle)

Dim impersonatedUser As WindowsImpersonationContext = newId.Impersonate()

' Check the identity.

successMessage += vbCrLf + "After impersonation: " + WindowsIdentity.GetCurrent().Name

'TODO Uncomment this line if you want to continue using the user

' Stop impersonating the user.

'impersonatedUser.Undo()

' Check the identity.

successMessage += vbCrLf + "Application will run as: " + WindowsIdentity.GetCurrent().Name

MessageBox.Show(successMessage, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

' Free the tokens.

'Marsdata.Show()

If Not System.IntPtr.op_Equality(tokenHandle, IntPtr.Zero) Then

CloseHandle(tokenHandle)

End If

Catch ex As Exception

If Me.txtUsername.Text = "" Then

MessageBox.Show(("Exception occurred. " + ex.Message), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)

MessageBox.Show(("Please provide a username & Password"), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

MessageBox.Show(("Exception occurred. " + ex.Message), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)

End If

End Try

Me.Close()

End Sub

Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click

canceled = True

Me.Close()

End Sub

Private Declare Auto Function LogonUser Lib "advapi32.dll" (ByVal lpszUsername As [String], _

ByVal lpszDomain As [String], ByVal lpszPassword As [String], _

ByVal dwLogonType As Integer, ByVal dwLogonProvider As Integer, _

ByRef phToken As IntPtr) As Boolean

<DllImport("kernel32.dll")> _

Public Shared Function FormatMessage(ByVal dwFlags As Integer, ByRef lpSource As IntPtr, _

ByVal dwMessageId As Integer, ByVal dwLanguageId As Integer, ByRef lpBuffer As [String], _

ByVal nSize As Integer, ByRef Arguments As IntPtr) As Integer

End Function

Public Declare Auto Function CloseHandle Lib "kernel32.dll" (ByVal handle As IntPtr) As Boolean

Public Declare Auto Function DuplicateToken Lib "advapi32.dll" (ByVal ExistingTokenHandle As IntPtr, _

ByVal SECURITY_IMPERSONATION_LEVEL As Integer, _

ByRef DuplicateTokenHandle As IntPtr) As Boolean

End Class

Once the connection is open, changing the impersonated user account will not change the credentials under which the connection is operating. To change the connection's user, use the SQL "setuser" command. Be aware, however, that the change is permanent; you can use "setuser" to change back, but only if the new user also has permission to use the command (which basic users will not).

Yes, you need to impersonate the other user prior to opening the connection (i.e. before calling connection.Open).

If you are using integrated Windows authentication in SQL server it should happen automatically.