I have a requirement, for service broker endpoints and queues, to automate the creation of a certificate on one server and load them up on another.
The manual way of doing this is something like this:
1. Use CREATE CERTIFICATE to create a self signed certificate, something like:
CREATE CERTIFICATE SomeCertificateA
WITH Subject = 'A.Server.Local',
START_DATE = '01/01/2006',
EXPIRY_DATE = '01/01/2010'
ACTIVE FOR BEGIN_DIALOG = ON;
2. Then use BACKUP CERTIFICATE to create a file for the certificate, something like:
BACKUP CERTIFICATE SomeCertificateA TO FILE = 'c:\share\SomeCertificateA.cer';
3. On the other server, that needs to talk to the first one using the certificate, you can then load it up from file, something like:
CREATE CERTIFICATE SomeCertificateA FROM FILE = '\\server1\share\SomeCertificateA.cer';
This works fine, but the problem is that you need file access to both servers. There is no way to select the certificate data as varbinary like this...
At first it seemed that I would have to create a service that ran on both servers that would send the files back and forth, but it smelled a bit funny. Besides I want to be able to copy certificates between servers by only having a SQL connection open - no file shares, no web access, no funny ports, just plain ol' ADO.NET.
Next option was to give SQL CLR a go. The idea being to call a SQL CLR function that would execute the BACKUP CERTIFICATE statement and return it as binary data to SQL. It works like a charm and the result listed below.
The first method is part of the SQL CLR project and generates a temporary filename, backs up the certificate to that temporary file and pops the raw data onto the pipe.
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetCertificateData(string certificateName)
{
string fileName = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
using (SqlConnection sqlConnection = new SqlConnection("context connection=true"))
{
sqlConnection.Open();
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "BACKUP CERTIFICATE " + certificateName + " TO FILE = '" + fileName + "'";
sqlCommand.ExecuteNonQuery();
}
FileStream fileStream = new FileStream(fileName, FileMode.Open);
SqlBytes certificateData = new SqlBytes(fileStream);
SqlMetaData[] sqlMetaData = new SqlMetaData[2];
sqlMetaData[0] = new SqlMetaData("CertificateName", SqlDbType.VarChar, certificateName.Length);
sqlMetaData[1] = new SqlMetaData("CertificateData", SqlDbType.VarBinary, certificateData.Length);
SqlDataRecord sqlDataRecord = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("CertificateName", SqlDbType.VarChar,certificateName.Length),
new SqlMetaData("CertificateData", SqlDbType.VarBinary,certificateData.Length)});
sqlDataRecord.SetSqlString(0, new SqlString(certificateName));
sqlDataRecord.SetSqlBytes(1, certificateData);
SqlContext.Pipe.SendResultsStart(sqlDataRecord);
SqlContext.Pipe.SendResultsRow(sqlDataRecord);
SqlContext.Pipe.SendResultsEnd();
}
The next method does the reverse...
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CreateCertificateFromData(string certificateName, byte[] certificateData)
{
string fileName = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
fileStream.Write(certificateData, 0, certificateData.Length);
fileStream.Close();
using (SqlConnection sqlConnection = new SqlConnection("context connection=true"))
{
sqlConnection.Open();
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "CREATE CERTIFICATE " + certificateName + " FROM FILE = '" + fileName + "'";
sqlCommand.ExecuteNonQuery();
}
}
That is pretty much all that you need. I'll leave it up to the reader to figure out the safety of the assemblies, CAS and other hoops that you may need to jump through.
To use it in .NET you simply call the stored procedures. To save a certificate to a local file...
public static void SaveCertificateToFile(SqlConnection sqlConnection, string certificateName, string fileName)
{
byte[] certificateData;
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "dbo.GetCertificateData";
sqlCommand.Parameters.AddWithValue("@certificateName", certificateName);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader != null && sqlDataReader.Read())
{
certificateData = (byte[]) sqlDataReader[1];
FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
fileStream.Write(certificateData, 0, certificateData.Length);
fileStream.Close();
sqlDataReader.Close();
}
}
To create a certificate from a byte array (which you can get from a filestream)...
public static void CreateCertificateFromData(SqlConnection sqlConnection, string certificateName, byte[] certificateData)
{
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "dbo.CreateCertificateFromData";
sqlCommand.Parameters.AddWithValue("@certificateName", certificateName);
sqlCommand.Parameters.AddWithValue("@certificateData", certificateData);
sqlCommand.ExecuteNonQuery();
}
}
There you go. A practical use of the CLR that overcomes a feature oversight in SQL Server.
Simon Munro