May 30
Impersonation on an ADO.NET SqlConnection
ADO.NET lets you create a database connection to SQL Server using either a SQL login or a Windows login with the credentials your program is running under. What if you want to impersonate another Windows user?
Given that you have the users' password and appropriate privleges and some effort, at runtime you can slip another users credentials into an ADO.NET connection.
...Or you can let SMO (SQL Server Managemement objects) do that work for you.
The ServerConnection object from the Microsoft.SqlServer.Management.Common namespace gives you a bit more flexibility in how to make your connection. A SeverConnection will login to SQL Server with Windows credentials if you set the ConnectAsUser property to true.
An ADO.NET SqlCommand cannot use a ServerConnection directly, but ServerConnection does have a property named SqlConnectionObject that returns a SqlConnection.
Below is some example console application that shows using a ServerConnection to execute an ADO.NET SqlCommand with an arbitrary Windows identity.
Note that ServerConnection.Connect() does not send the name and password to SQL Server for authentication, the name and password are authenticated on the machine that executes the Connect() method.This limits the utility of this technique somewhat, but in a later post I will look at a use case where this technique is pretty handy.
Of course this code is doing no error checking and glosses over some of the differences between a ServerConnection and a SqlConnection that is constructed directly.
To use this example you will have to add a reference to Microsoft.SqlServer.ConnectionInfo.
using
System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
namespace AutoConnect
{
class Program
{
static void Main(string[] args)
{
ServerConnection serverConnection = new ServerConnection();
SqlCommand cmd =
new SqlCommand("SELECT SUSER_NAME()", // get login name
serverConnection.SqlConnectionObject);
serverConnection.ConnectAsUser = true;
serverConnection.ConnectAsUserName = args[0];
serverConnection.ConnectAsUserPassword = args[1];
serverConnection.Connect();
Console.WriteLine(cmd.ExecuteScalar());
}
}
} Program run in a command shell...
C:>Connect "MiniDan", "P@ssw0rd"
CANOPUS5\MiniDan
http://www.SqlServiceBroker.com