tiistai 27. huhtikuuta 2010

Creating external connection to same database server from inside SQLCLR

I recently had this need to create external connection apart from context connection within SQL CLR procedure. Of course I could have made connection string as parameter but that just didn't feel right. I wanted to connect to another database in same server with same credentials than current connection.
First problem here is to get even the name of current SQL Server because when using context connection SqlConnection.DataSource is null. Name of local server can be obtained with little SQL executed within context connection:
var getServerNameCommand = ContextConnection.CreateCommand();
getServerNameCommand.CommandText = "SELECT @@SERVERNAME;";
var serverName = (string) getServerNameCommand.ExecuteScalar(); 
To get database name we can use the same trick:

var getDatabaseNameCommand = ContextConnection.CreateCommand();
getDatabaseNameCommand.CommandText = "SELECT db_name()";
var databaseName = (string) getDatabaseNameCommand.ExecuteScalar();
Now we can create the another connection but problem is that CLR inside SQL Server is running actually as part of SQL Servers process. This means that if we try to create a connection with integrated security it will try to connect as SQL Server process. Luckily we can access to the identity of current user through SqlContext.WindowsIdentity. To create connection string I use SqlConnectionStringBuilder.
var builder = new SqlConnectionStringBuilder();
builder.IntegratedSecurity = true;
builder.InitialCatalog = databaseName;
builder.DataSource = serverName;
var impersonationContext = SqlContext.WindowsIdentity.Impersonate();
var connection = new SqlConnection(builder.ToString());
// Impersonation has to be undone otherwise you will get exception:
// ".NET Framework execution was aborted. The UDP/UDF/UDT did not revert
// thread token."                 
That's pretty much it. This might not work if you use SQL Server Authentication instead of integrated one. Deployed assembly also has to have EXTERNAL_ACCESS or UNSAFE permission to access SqlContext.WindowsIdentity.