How to distribute digitally signed SQL modules
Digital signatures in SQL Server 2005 modules can be used to extend the privileges of the caller for the duration of the call. This feature enables the ability to create an application that can enable authorized callers to access resources (such as tables, symmetric keys, etc.) that otherwise would require highly escalated privileges.
While adding a signature when you have control of the certificate’s private key is really straightforward, but it may not be so obvious how to distribute an application that uses this feature without giving away the private key.
One possible approach is to create a certificate during the application installation process, sign the module and then delete the private key to prevent further usage. While this approach will allow the application to work (functionality speaking), it will effectively be using a certificate completely outside your control, and it will prevent to distinguish between the original signature and a new signature.
Another option is to extract the signature as binary and use the ADD SIGANTURE TO … WITH SIGNATURE syntax. You can create the module and sign it with the private key on your development environment, then to distribute the signature, you can extract it from sys.crypt_properties, for example:
SELECT object_name( cp.major_id ) as object_name, cer.name as cert_name, cp.crypt_property as signature
FROM
sys.crypt_properties cp,
sys.certificates cer
WHERE
cp.class = 1 AND
cp.thumbprint = cer.thumbprint
go
Something really important to remark when you use this feature is that the module has to be created on the target machine exactly (and I want to emphasize this) as it was created on your development environment. This includes empty lines, comments and spaces, both before and in the middle of the module creation.
Creating a script to extract the signature using only TSQL is not easy; the main reason being the little support for varbinary data type in TSQL language. Because of this I created a sample C# code that may be useful in this task.
One warning before showing this sample code, using EXECUTE AS feature in a siganed module will include the context SID as a mechanism to prevent undesired escalation of privileges. If you need to use these two features together, make sure you can control the SID mapped to the user.
Sample C# code
/***************************************************************************************
*
* This posting is provided "AS IS" with no warranties, and confers no rights.
*
* Authors: Raul Garcia
* Date: 05/29/2006
* Description:
*
* This is just a demo that can be used to extract the exact script used to create
* the module (from metadata) and extarct the script to add the siganture.
*
*
* (c) 2006 Microsoft Corporation. All rights reserved.
*
**************************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
namespace SqlServerHelper
{
public class SqlSignedModule
{
private string m_ModuleDefinition;
private string m_Schema;
private string m_ModuleName;
private string m_ExecuteAsSid;
private string m_CertName;
private string m_Warning;
private SqlConnection m_SqlConn;
private string m_sLastError;
private string m_SignatureDefinition;
private bool m_bSignatureRecovered;
/// <summary>
/// Returns a string with the last error message
/// in case there was any failure in any call
/// </summary>
public string LastError
{
get
{
return m_sLastError;
}
}
/// <summary>
/// It may be possible that the operation succeeds,
/// but yet there is some important information that
/// you may wnat to know.
/// This call returns null if there is no warning,
/// a string describing the situation otherwise
/// Right now it is only used to warn you in case
/// of detecting EXECUTE AS in the module
/// </summary>
public string WarningMessage
{
get
{
return m_Warning;
}
}
/// <summary>
/// Create a new SqlSignedModule object
/// </summary>
/// <param name="ConnectionString">The connection string to access the source database</param>
/// <param name="ModuleName">the signed module name (it assumes default schema)</param>
/// <param name="CertName">the signing certificate name</param>
public SqlSignedModule(string ConnectionString, string ModuleName, string CertName)
{
m_sLastError = "";
m_Warning = null;
m_bSignatureRecovered = false;
m_Schema = null;
m_ModuleName = ModuleName;
m_CertName = CertName;
m_SqlConn = new SqlConnection(ConnectionString);
}
/// <summary>
/// Create a new SqlSignedModule object
/// </summary>
/// <param name="ConnectionString">The connection string to access the source database</param>
/// <param name="schema">schema name</param>
/// <param name="ModuleName">the signed module name</param>
/// <param name="CertName">the signing certificate name</param>
public SqlSignedModule(string ConnectionString, string schema, string ModuleName, string CertName)
{
m_sLastError = "";
m_Warning = null;
m_bSignatureRecovered = false;
m_Schema = schema;
m_ModuleName = ModuleName;
m_CertName = CertName;
m_SqlConn = new SqlConnection(ConnectionString);
}
/// <summary>
/// Returns the module definition from metadata.
/// It will return null if the call fails.
/// </summary>
public string ModuleDefinition
{
get
{
m_bSignatureRecovered = GetSignatureByCert();
return m_ModuleDefinition;
}
}
/// <summary>
/// Returns the script to add the signature
/// It will return null if the call fails.
/// </summary>
public string Signature
{
get
{
m_bSignatureRecovered = GetSignatureByCert();
return m_SignatureDefinition;
}
}
private void VerifyExecuteAs(SqlInt32 sqlExecuteAsUid)
{
if (!sqlExecuteAsUid.IsNull)
{
m_Warning = @"Module has been marked with an Execute as context. Signed modules include the execute as information (SID) as part of the signature. The precalculated signature will be invalid if the SIDs don't match.";
}
else
{
m_Warning = null;
}
}
private bool GetSignatureByCert()
{
bool bRetVal = false;
SqlCommand oSqlCmd = null;
try
{
oSqlCmd = m_SqlConn.CreateCommand();
SqlParameter schema_name = oSqlCmd.CreateParameter();
SqlParameter module_name = oSqlCmd.CreateParameter();
SqlParameter cert_name = oSqlCmd.CreateParameter();
SqlParameter module_def = oSqlCmd.CreateParameter();
SqlParameter signature = oSqlCmd.CreateParameter();
SqlParameter sig_def = oSqlCmd.CreateParameter();
SqlParameter ExecuteAs = oSqlCmd.CreateParameter();
schema_name.DbType = DbType.String;
schema_name.IsNullable = true;
schema_name.ParameterName = @"schemaname";
schema_name.Direction = ParameterDirection.Input;
if( m_Schema != null && m_Schema.Length > 0 )
{
schema_name.Value = m_Schema;
}
else
{
schema_name.Value = SqlString.Null;
}
module_name.DbType = DbType.String;
module_name.IsNullable = false;
module_name.ParameterName = @"m_ModuleName";
module_name.Direction = ParameterDirection.Input;
module_name.Value = m_ModuleName;
cert_name.DbType = DbType.String;
cert_name.IsNullable = false;
cert_name.ParameterName = @"m_CertName";
cert_name.Direction = ParameterDirection.Input;
cert_name.Value = m_CertName;
module_def.DbType = DbType.String;
module_def.IsNullable = true;
module_def.ParameterName = @"m_ModuleDefinition";
module_def.Direction = ParameterDirection.Output;
module_def.Size = Int32.MaxValue;
ExecuteAs.DbType = DbType.Int32;
ExecuteAs.IsNullable = true;
ExecuteAs.ParameterName = @"ExecuteAs";
ExecuteAs.Direction = ParameterDirection.Output;
ExecuteAs.Size = Int32.MaxValue;
sig_def.DbType = DbType.String;
sig_def.IsNullable = true;
sig_def.ParameterName = @"sig_def";
sig_def.Direction = ParameterDirection.Output;
sig_def.Size = Int32.MaxValue;
signature.DbType = DbType.Binary;
signature.IsNullable = true;
signature.ParameterName = @"signature";
signature.Direction = ParameterDirection.Output;
signature.Size = Int32.MaxValue;
oSqlCmd.Parameters.Add(schema_name);
oSqlCmd.Parameters.Add(module_name);
oSqlCmd.Parameters.Add(cert_name);
oSqlCmd.Parameters.Add(module_def);
oSqlCmd.Parameters.Add(signature);
oSqlCmd.Parameters.Add(sig_def);
oSqlCmd.Parameters.Add(ExecuteAs);
oSqlCmd.CommandText =
@"
declare @Aux nvarchar(max)
declare @object_id int
SET @Aux = quotename( @m_ModuleName )
if( @schemaname is not null )
SET @Aux = quotename( @schemaname ) + N'.' + @Aux
SET @object_id = object_id( @Aux )
SELECT @ExecuteAs = execute_as_principal_id, @m_ModuleDefinition = definition FROM sys.sql_modules where object_id = @object_id
SELECT @signature = cp.crypt_property FROM sys.crypt_properties cp, sys.certificates cer
WHERE cp.major_id = @object_id AND cp.class = 1 AND cp.thumbprint = cer.thumbprint AND cer.name = @m_CertName
SET @sig_def = N'ADD SIGNATURE TO ' + @Aux + N' BY CERTIFICATE ' + quotename( @m_CertName ) + ' WITH SIGNATURE = ';";
oSqlCmd.Connection.Open();
oSqlCmd.ExecuteNonQuery();
SqlString sqlstrSig_def = (SqlString)sig_def.SqlValue;
SqlString sqlstrModule_def = (SqlString)module_def.SqlValue;
SqlBinary sqlbinSignature = (SqlBinary)signature.SqlValue;
SqlInt32 sqlExecuteAsUid = (SqlInt32)ExecuteAs.SqlValue;
VerifyExecuteAs( sqlExecuteAsUid );
// Now let's get the signature and module definition
if( !sqlbinSignature.IsNull && !sqlstrModule_def.IsNull && !sqlstrSig_def.IsNull )
{
string strSignature = @"0x";
byte[] baSignatureBin = sqlbinSignature.Value as byte[];
for (int i = 0; i < baSignatureBin.Length; i++)
{
strSignature = strSignature + string.Format("{0:X2}", baSignatureBin[i]);
}
//Terminate the
m_ModuleDefinition = module_def.Value as string;
m_SignatureDefinition = string.Format(@"{0}{1}", sig_def.Value as string, strSignature);
bRetVal = true;
}
else
{
if( sqlstrModule_def.IsNull )
{
m_sLastError = string.Format(@"Unable to access the definition of {0}.{1}. Make sure it is not an encrypted module", m_Schema, m_ModuleName );
}
else if( sqlbinSignature.IsNull )
{
m_sLastError = string.Format(@"Unable to access the signature on module {0}.{1} by certificate {2}.", m_Schema, m_ModuleName, m_CertName);
}
else
{
m_sLastError = string.Format(@"Unexpected error while trying ot access the signature on module {0}.{1} by certificate {2}.", m_Schema, m_ModuleName, m_CertName);
}
}
}
catch (SqlException e)
{
m_sLastError = string.Format( @"[SQL ERROR].[ErrorCode:{0}][Message:{1}]", e.ErrorCode, e.Message );
}
catch (Exception e)
{
m_sLastError = string.Format( @"[Unexpected Excpetion].[Source:{0}][Message:{1}]", e.Source, e.Message );
}
finally
{
if (oSqlCmd != null)
{
if( oSqlCmd.Connection.State != ConnectionState.Broken
&& oSqlCmd.Connection.State != ConnectionState.Closed )
{
oSqlCmd.Connection.Close();
}
}
}
if( !bRetVal )
{
m_ModuleDefinition = null;
m_SignatureDefinition = null;
}
return bRetVal;
}
}
}