SQL Connection as a differnt user

SQL Connection as a differnt user

• eisebs

  But whats the point of this code then

  Or do you mean that the impersonation should take change before the connection is made If so, how do I do that

  Thanks

  
  

• DHLennon

  I'm in a similar situation. Im writing an in house asp app to query a database. Due to not being restricted to windows authentication my app cant open the db directly, so i wrote a script this i pass in the server name and the sql to execute, it in turn generates a text file which the asp app reads to populate its pick lists. Im hoping that the asp app can use the runas command to invoke the script.

  I'll find out soon.

  Thanks

  Woody

  
  
  

• Ronak

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

• x0ras

  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

  
  
  

• Mohamedwaly

  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

  
  
  

• Coatesy

  Thanks, that worked.

  Hurrah!

  
  

• Volodimir

  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.

  
  
  

• endtje

  Thanks for the code.

  It compiles for me fine, and seems to logon a user correctly.

  I'm still confused though, on how to use the impersonated user. Say I wanted to run a SQL Command as the impersonated user - how could I do that.

  I guess I need to set My.User.CurrentPrincipal to something. Any demo code showing use

  Thanks

  
  

• Michelle Cheng

  I'm a bit stuck on that part.

  After

  Dim impersonatedUser As WindowsImpersonationContext = newId.Impersonate()

  How do I use impersonatedUser to call my sql commands

  Must I change some setting in My.User

  Some sample code would be great, as this is driving me nuts.

  Thanks.

  
  

• Roshan Sathaar

  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).
  
  

• janiv

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

• Javedkomal

  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

  
  
  

• Rodrigo Pires

  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

  
  
  

• RGabo

  Without changing your code you should be able to use runas.exe to run your application as the other account. For example you can use:

  runas /user:domain\username cmd

  to open a command window under the other credentials and then execute the application within that command window. You need to know the domain and user name for the other account (or the local user name on the machine) and the password for the other account. If this is not an option then you can try the previous suggestion to impersonate the other account.

  
  
  
• SQL Connection as a differnt user