SQLConnection Connection String Integrated Security Force user

[saadabc]

I have a VB .NET program that's using a SQLConnection object to connect to a SQL Server database. The connection string is using Windows Integrated Security.

The connection string is this:

"PERSIST SECURITY INFO=FALSE;INTEGRATED SECURITY=SSPI;DATA SOURCE=SAC_DB6_SRV;INITIAL CATALOG=RM97Mason;CONNECTION TIMEOUT=30"

so it's just connecting with the currently logged in Windows user SAC\bob for e.g. I want to force it to connect with a certain Windows user SAC\exchange everytime. How do I make that happen?


Thanks.

 

[NoCoolHandle]

Code impersonation.....
Try the following code.. Edit the red values...
(this was taken from a webform, but should work in winforms)

Import System.Web.Security
Import System.Security.Principal

[green]class etc....[/green]

    Dim LOGON32_LOGON_INTERACTIVE As Integer = 2
    Dim LOGON32_PROVIDER_DEFAULT As Integer = 0
    Dim impersonationContext As WindowsImpersonationContext
    

Declare Function LogonUserA 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 Integer

Declare Auto Function DuplicateToken Lib "advapi32.dll" ( _
                        ByVal ExistingTokenHandle As IntPtr, _
                        ByVal ImpersonationLevel As Integer, _
                        ByRef DuplicateTokenHandle As IntPtr) As Integer

Declare Auto Function RevertToSelf Lib "advapi32.dll" () As Long
Declare Auto Function CloseHandle Lib "kernel32.dll" (ByVal handle As IntPtr) As Long


Public Sub Page_Load(ByVal s As Object, ByVal e As EventArgs)
        If impersonateValidUser([red]"SomeAccount", "SomeDomain", "SomePassword"[/red]) Then
            'Insert your code that runs under the security context of a specific user here.
            Dim c As New SqlClient.SqlConnection("server=[red]SomeServer[/red];database=northwind;integrated security=sspi")
            c.Open()
            Dim m As New SqlClient.SqlCommand("Select suser_sname()", c)
            Dim r As SqlClient.SqlDataReader
         
            r = m.ExecuteReader()
            While r.Read
                Response.Write(r.Item(0).ToString())
            End While
            undoImpersonation()
        Else
            'Your impersonation failed. Therefore, include a fail-safe mechanism here.
        End If
End Sub

Private Function impersonateValidUser(ByVal userName As String, _
ByVal domain As String, ByVal password As String) As Boolean

    Dim tempWindowsIdentity As WindowsIdentity
    Dim token As IntPtr = IntPtr.Zero
    Dim tokenDuplicate As IntPtr = IntPtr.Zero
    impersonateValidUser = False

        If RevertToSelf() <> 0 Then
            If LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, token) <> 0 Then
                If DuplicateToken(token, 2, tokenDuplicate) <> 0 Then
                    tempWindowsIdentity = New WindowsIdentity(tokenDuplicate)
                    impersonationContext = tempWindowsIdentity.Impersonate()
                    If Not impersonationContext Is Nothing Then
                        impersonateValidUser = True
                    End If
                End If
            End If
        End If
    If Not tokenDuplicate.Equals(IntPtr.Zero) Then
        CloseHandle(tokenDuplicate)
    End If
    If Not token.Equals(IntPtr.Zero) Then
        CloseHandle(token)
    End If
End Function

Private Sub undoImpersonation()
    impersonationContext.Undo()
End Sub

 

[chrissie1]

[red]Warning be very carefull with impersonation.[/red]

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[saadabc]

I'll try your method NoCoolHandle. it looks really cool.

Wouldn't the following also work.


"PERSIST SECURITY INFO=FALSE;INTEGRATED SECURITY=False;DATA SOURCE=SAC_DB6_SRV;INITIAL CATALOG=RM97Mason;CONNECTION TIMEOUT=30;User=SAC\exchange;Password=password"


Setting the Integrated Security property of the Connection String to False (instead of SSPI) and then specifying the username and password.

 

[NoCoolHandle]

As per setting it to false.

No. When you set it to false, you are telling SQL that it will take care of login validation. Then you pass it a "Windows" UserName and Password. Problem is, Windows never gets the combo, so the user is never validated as SQL doesn't have the user/password combo in SysXlogins.

As per..Christiaan

Be carefull with impresonatoin. I really should only be used when all other options have been tried.

An alternative might be to use "applicaion roles" inside sqlserver. Applicaion Roles can be used to allow a login to do something while not allowing the login to do something.

So.. what you do is set up all the procs that your app needs. You then set the app role to having permission to use it, then the applicaion calls sp_setapprole before it needs to execute that code.

Also.. COM+ /Transaction Server

It has been a while since I used Approles, so you might want to do a search on them in BOL, but I think they might help here.


Rob

 

[NoCoolHandle]

Interesting sidenote (that I just found out about)

the account that you are logged on to needs to have
"act as part of the operating system"
permissions for impersonation to work.

Probably not something that every user has, so.....

you might want to look at the alternatives (again I think that "applicaion roles" might just be a good option here)

Rob