Backwards

I'm using visual studio 2005 and SQL 2000, trying to connect to a sql
database as a differnent user.

Here is my current connection string that is using my own NT login:

Dim message As String = "Data Source=" +
Me.ComboBox1.Text.ToString() + ";Initial Catalog=Store;Integrated
Security=SSPI"
Dim sql As New SqlClient.SqlConnection(message)

sql.Open()

I have access to this database and the connection establishes fine.
However i dont have access to certain Stored Procedure's on this
database and these run through a differnt NT user.

I dont have access to a SQL user name and password so i cant hardcode
the "sa" login instead.

Does anyone know if i can change my connection string above to use
another windows/NT login

Awaiting reply,
Andy




Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

Andreas Johansson

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.






Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

Steve Hale - MSFT

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.






Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

William Vaughn

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






Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

kayuWoody

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






Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

Backwards

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






Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

Backwards

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






Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

Backwards

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






Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

Vayse_Dev

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





Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

CommonGenius.com

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




Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

Vayse_Dev

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





Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

CommonGenius.com

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




Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

Vayse_Dev

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.





Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

CommonGenius.com

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




Re: .NET Framework Data Access and Storage SQL Connection as a differnt user

Vayse_Dev

Thanks, that worked.

Hurrah! Smile