Digitally Signing a SQL Stored Procedure

Problem

This tip introduces the basics of digital signatures based on self-signed certificates for selected T-SQL objects (stored procedures, functions, and triggers) with T-SQL samples. Learn how to detect what happens to a digital signature when a stored procedure is accidentally altered by a database administrator or intentionally modified by a bad actor.

Solution

In this tip, you will see demonstrations and descriptions of the steps to:

• Create a self-signed certificate with the create certificate statement.
• Add a signature to a stored procedure based on the self-signed certificate with the add signature statement.
• Verify if the object (stored procedure) has a valid digital signature by inspecting the sys.crypt_properties view.

You can add a signature to an object, such as a stored procedure, with either a certificate or an asymmetric key. This tip illustrates how to create and use self-signed certificates for adding signatures to a stored procedure.

Self-signed certificates are issued by a SQL Server instance, but CA certificates are issued by widely trusted entities, such as Let’s Encrypt, GlobalSign, and DigiCert. Self-signed certificates, such as those demonstrated in this tip, are meant for development, testing, and tutorial environments. CA certificates are meant for production environments where a high degree of trust is required.

This tip focuses on T-SQL examples for implementing and managing digital signatures and will be presented for:

• Creating a certificate that enables the addition of a digital signature to a stored procedure, function, or trigger object
• Adding a digital signature to a stored procedure object
• Querying the sys.crypt_properties view for examining which objects have signatures created by a certificate
• Demonstrating how to assess whether a SQL Server object’s code has changed since it was last signed
• Determining if a SQL Server object is digitally signed

Example Stored Procedure

The following script excerpt runs in the Cryptography database, but you can use any database you prefer. It is recommended to run the code in any database besides the master database. It is a good practice to avoid populating the master database with user-defined objects. This is because the master database holds server-wide information that is critical to the proper operation of SQL Server.

After the USE statement, the script creates a fresh copy of the MyProcedure stored procedure in the dbo schema. Next, the script invokes an EXEC statement for the stored procedure to display the string returned by the SELECT statement in the stored procedure.

After the EXEC statement, the script invokes a SELECT statement for the sys.crypt_properties view. The SELECT statement in the example below returns cryptographic properties for the dbo.MyProcedure object so long as the object has a row in the sys.crypt_properties view.

The next SELECT statement joins the sys.objects and sys.schemas views to return properties for the MyProcedure stored procedure, including schema name, object name, object_id value, and the type of object.

The script concludes with the output from a CASE statement that reflects whether the MyProcedure stored procedure is signed or not signed.

Use Cryptography
GO
 
-- create and run a demo stored proc
-- check to see if the procedure is signed
 
-- conditionally drop and create the stored proc
drop procedure if exists dbo.MyProcedure;
go
 
create procedure dbo.MyProcedure
as
begin
   select 'This is my demo procedure.'
end
go
 
-- run the stored procedure
exec dbo.MyProcedure
 
-- properties of signed object
select *
from sys.crypt_properties
where major_id = OBJECT_ID('dbo.MyProcedure') AND class = 1;
 
-- identifiers for signed object
select  s.name AS SchemaName, o.name AS ObjectName, o.object_id, o.type_desc AS ObjectType
from sys.objects o
join sys.schemas s ON s.name = SCHEMA_NAME(o.schema_id)
where o.name = 'MyProcedure' and s.name = 'dbo';
 
-- is dbo.MyProcedure signed
select
case
   when
   (select major_id
   from sys.crypt_properties
   where major_id = OBJECT_ID('dbo.MyProcedure')) is not null
then 'dbo.MyProcedure is signed'
else 'dbo.MyProcedure is unsigned'
end

Here is the output from the preceding script. It is comprised of four results sets:

1. The first results set is from the SELECT statement inside the demonstration’s stored procedure.
2. The second results set is empty because there are no cryptographic properties yet for the MyProcedure stored procedure.
3. The third results set shows selected properties for the MyProcedure stored procedure from the join of the sys.objects and sys.schemas views.
4. Finally, the fourth results set indicates that the MyProcedure is not digitally signed yet.

Digitally Sign a Stored Procedure and Verify the Signature

The script in the preceding section shows how to create a sample stored procedure, but it does not digitally sign the stored procedure. The section also inspects the sys.crypt_properties view to verify if there is a signature attached (perhaps by another code segment) to the stored procedure.

This section starts by creating a self-signed certificate named MyCert, and then uses the certificate to digitally sign the stored procedure created in the preceding section. After signing the stored procedure, three SELECT statements perform additional inspections of the sys.crypt_properties view as well as the join of sys.objects and the sys.schemas views.

-- create a fresh version of the MyCert certificate
-- add a signature to dbo.MyProcedure with the certificate
-- and verify properties of signed object
 
-- create a fresh version of MyCert certificate
 
if exists (select * from sys.certificates where name = 'MyCert')
begin
    drop certificate MyCert;
end;
 
-- create self-signed certificate
create certificate MyCert
with subject = 'My Certificate for Signing Database Objects';
 
-- add signature to dbo.MyProcedure
add signature to dbo.MyProcedure
by certificate MyCert;
 
-- properties of signed object
select class_desc, major_id, crypt_type_desc, thumbprint, crypt_property
from sys.crypt_properties
where major_id = OBJECT_ID('dbo.MyProcedure') AND class = 1;
 
-- properties for signed object
select s.name AS SchemaName, o.name AS ObjectName, o.object_id, o.type_desc AS ObjectType
from sys.objects o
join sys.schemas s ON s.name = SCHEMA_NAME(o.schema_id)
where o.name = 'MyProcedure' and s.name = 'dbo';
 
-- is dbo.MyProcedure signed
select
case
   when
   (select major_id
   from sys.crypt_properties
   where major_id = OBJECT_ID('dbo.MyProcedure')) is not null
then 'dbo.MyProcedure is signed'
else 'dbo.MyProcedure is unsigned'
end

Here are the results

Here are the three results sets returned by the script in this section:

1. Displays five column values from the sys.crypt_properties view for the MyProcedure stored procedure. In the preceding section, there was no indication of any sys.crypt_properties for the stored procedure. This is because the stored procedure was not digitally signed, and it therefore had no row of column values in the sys.crypt_properties view.
   • The class_desc column value identifies what type of SQL Server class the MyProcedure stored procedure is. As you can see, the results set indicates that MyProcedure is either an object or a column.
   • The major_id column value is the object_id from the sys.objects view for the digitally signed object.
   • The crypt_type_desc column value is a description of the cryptographic operation performed on the stored procedure object.
   • The thumbprint is a hexadecimal representation of the hash for the certificate used to sign the SQL Server class (the stored procedure object in this example).
   • The crypt_property column value is a representation of the signature applied to the stored procedure. While it is a binary value internally, it appears as a hexadecimal value below.
2. Displays four columns from the join of the sys.schemas and sys.objects views for the stored procedure in this example.
3. Indicates whether the stored procedure is signed or not. For the script excerpt in this section, the results set reveals the stored procedure (dbo.MyProcedure) is signed.

After the Stored Procedure Is Altered, the Signature Is Removed

The commentary for the first script segment in this tip confirms that stored procedures are not automatically signed when you create them. The second script segment in this tip illustrates the steps for adding a fresh signature to a stored procedure based on a self-signed certificate. The script for this section demonstrates what happens to the signature for a digitally signed stored procedure after it is altered.

The following script starts with an alter procedure statement for the dbo.MyProcedure stored procedure. By comparing the alter procedure statement in this script to the create procedure statement in the first script, you can see that change modifies the stored procedure string output from “This is my demo procedure.” to “This is my altered demo procedure.” An exec statement after the alter procedure statement displays the modified string output.

The next three select statements are the same as in the script within the preceding section:

1. The first SELECT statement shows the column values in the sys.crypt_properties view for the altered stored procedure.
2. The second SELECT statement displays selected column values for the stored procedure from the join of the sys.objects and sys.schemas views.
3. The third SELECT statement uses a case expression to determine if the stored procedure is signed.

-- what happens to its signature after dbo.MyProcedure is altered
 
-- create/alter must be the first statement in a query batch
 
-- alter dbo.MyProcedure
alter procedure dbo.MyProcedure
as
begin
-- original string output is commented out
-- select 'This is my demo procedure.'
 
-- altered string output replaces original string output
   select 'This is my altered demo procedure.'
end
go
 
-- run the stored procedure
exec dbo.MyProcedure
 
-- properties of signed object
select class_desc, major_id, crypt_type_desc, thumbprint, crypt_property
from sys.crypt_properties
where major_id = OBJECT_ID('dbo.MyProcedure') AND class = 1;
 
-- identifiers for signed object
select  s.name AS SchemaName, o.name AS ObjectName, o.object_id, o.type_desc AS ObjectType
from sys.objects o
join sys.schemas s ON s.name = SCHEMA_NAME(o.schema_id)
where o.name = 'MyProcedure' and s.name = 'dbo';
 
-- is dbo.MyProcedure signed
select
case
   when
   (select major_id
   from sys.crypt_properties
   where major_id = OBJECT_ID('dbo.MyProcedure')) is not null
then 'dbo.MyProcedure is signed'
else 'dbo.MyProcedure is unsigned'
end

Here are the results

Here are the results sets from the preceding script. The two most important results sets are the second and the fourth ones:

• The second results set reveals that there are no rows in the sys.crypt_properties view for the MyProcedure stored procedure in the dbo schema.
• The fourth results set indicates why there are no rows in the view. It is because the stored procedure is unsigned. The signature for the stored procedure is removed by the alter procedure statement at the beginning of the preceding script. When an object, such as a SQL stored procedure, is modified after it is signed, SQL Server removes the signature record for the object in the sys.crypt_properties view.

Summary

Digital signatures represent a way of validating the authenticity of the code for a SQL Server object. Whenever it is important for you to know with certainty that the code for an object has not changed (either accidentally or on purpose), then digital signatures represent one means of verifying the code. This is because there are no rows for the object in the sys.crypt_properties view unless the object was signed by a signature administrator or the author of the code. If an object’s code is altered after a signature is applied to the object, then SQL Server removes the object’s signature row in the sys.crypt_properties view. You can use the create certificate and the add signature statements to digitally sign SQL stored procedures.

Next Steps

The next step after examining the code samples in this tip is to verify the value of digital signatures. Take a look at your stored procedures, functions, and triggers in your work environment. This is especially true if you recently encountered issues with unauthorized changes to SQL Server objects in your work environment.

Check out these additional TSQL and Security articles