Connecting to SQL server using different NT domain credentials
ASP.NET has a nice Impersonation feature that allows server-based web applications to use alternate credentials to perform network activities. Unfortunately, it does not extend into Windows “thick” clients; until now.
My scenario was to read an Excel file from a local and/or shared folder with one set of credentials, and then a Sql Server with a different set of Domain credentials.
I came across this article which uses a VB.NET class for the “thick-client” impersonation, and has the capability to be started and stopped. All you need to do is wrap the actual SqlConnection.Open method within the impersonation Start/Stop and all is well.
Dim imp As New RunAs_Impersonator
Try
imp.ImpersonateStart("DOMAIN", "userId", "password")
If sqlConnection.State = ConnectionState.Closed Then
sqlConnection.Open()
End If
imp.ImpersonateStop()
Catch ex As Exception
Console.WriteLine("Error opening sql connection--" + ex.Message)
End Try
' THEN ALL USES OF THE SQLCONNECTION OBJECT WILL BE TRUSTED
sqlCommand.CommandText = "SELECT * FROM myTABLE"
sqlCommand.Connection = sqlConnection
Dim sqlReader As SqlDataReader = sqlCommand.ExecuteReader()
.