How to use module signing for SQL Server security

By: Tibor Nagy | Comments (2) | Related: > Security

Problem

I heard that certain SQL Server security requirements can be fulfilled by module signing. Could you please explain the purpose of module signing and provide an example in SQL Server?

Solution

When it comes to security considerations, it is always recommended to give the lowest required access to the users in the SQL Server database. It is a usual practice to minimize the access and grant execute permissions only to views and stored procedures, instead of providing access to the underlying database tables and objects. This way the end users will require permissions only for a limited number of entry level objects which will access the underlying objects on behalf of the user (this is ownership chaining). An additional benefit is that the permission management is reduced to a smaller set of objects and the schema layout can be hidden from the end users.

However, some permissions cannot be inherited through ownership chaining like dynamic SQL and TRUNCATE TABLE permissions. The popular EXECUTE AS command can be used to impersonate users when executing stored procedures, but it changes the execution context and the audit log contains only the impersonated user, not the original caller of the stored procedure. Module signing has been introduced in SQL Server 2005 to overcome the ownership chaining issues and to provide a tool to impersonate users without changing the execution context.

We can sign the following module types:

Stored Procedures
Functions
Triggers
Assemblies

There are five steps of module signing:

Create a certificate
Create a user and map to the certificate
Sign the module
Grant the permissions for the certificate user
Grant permission to the module for the users/groups

You can import a key pair from certificate files with private key length between 384 and 4096 bits. Alternatively you can create a self-signed certificate with 1024 bits long private keys with SQL Server. The SQL Server follows the X.509 standard so the certificates will have standard Subject, Expiry Date, etc. fields. If you create the certificate in the master database then you can allow server-level permissions. If you create it in a user database then you can allow database-level permissions.

Please note that the certificate is invalidated when the module changes so you have to sign the module again after every code change. This is a useful protection against unauthorized modification of the signed modules.

SQL Server Module Signing Example

After reading the theoretical background, it is time to see how we can implement module signing in the SQL Server environment.

Scenario: We have the DemoCertSP stored procedure which selects data from the DemoSchema.DemoTable. DemoUser would like to run the stored procedure and we plan to grant this permission without any additional permission to the table. To achieve this goal, we will create the DemoCert certificate and a certificate account called DemoCertAccount. We sign the stored procedure with this certificate and grant the necessary rights to the certificate account. The last step is to grant execute permission on the stored procedure to DemoUser.

1A. We can create a certificate in the database by loading a key pair from certificate files:

USE SampleDB;
CREATE CERTIFICATE DemoCert    
    FROM FILE = 'C:\SQLDemo\Certificates\Demo.cer' 
    WITH PRIVATE KEY (FILE = 'C:\SQLDemo\Certificates\Demo.pvk', 
    DECRYPTION BY PASSWORD = 'This1$AC0MPL3XPassWord');
GO

1B. If we do not have a certificate to import then we can easily create a self-signed certificate (do not forget the password, it will be required for signing the module):

USE SampleDB;
CREATE CERTIFICATE DemoCert 
   ENCRYPTION BY PASSWORD = 'This1$AC0MPL3XPassWord'
   WITH SUBJECT = 'Demo Certificate Subject', 
   EXPIRY_DATE = '01/01/2015';
GO

2. Now we create a certificate account with the following statement:

USE SampleDB;
GO
CREATE USER DemoCertAccount
   FROM CERTIFICATE DemoCert;
GO

3. The next step is to sign the stored procedure with the certificate and the password:

USE SampleDB;
ADD SIGNATURE TO DemoCertSP 
   BY CERTIFICATE DemoCert
    WITH PASSWORD = 'This1$AC0MPL3XPassWord';
GO

4. The certificate account requires select permission on the table and execute permission on the stored procedure:

USE SampleDB;
GRANT SELECT 
   ON DemoSchema.DemoTable
   TO DemoCertAccount;
GO

GRANT EXECUTE 
   ON DemoCertSP 
   TO DemoCertAccount;
GO

5. Finally we grant execute permission on the stored procedure to the user who requested the permission:

GRANT EXECUTE 
   ON DemoCertSP 
   TO DemoUser;
GO

It is also possible to remove the signature from a module if it is no longer required:

USE SampleDB;
DROP SIGNATURE FROM DemoCertSP 
    BY CERTIFICATE DemoCert;
GO

Next Steps

Create a certificate and test the module signing in your test environment.
Read more about this topic on Microsoft Technet:

Additional information about the CREATE CERTIFICATE statement.
More detailed information about the ADD SIGNATURE statement.

You can find more articles in the Security category.
Read more tips by the author here

About the author

Tibor Nagy is a SQL Server professional in the financial industry with experience in SQL 2000-2012, DB2 and MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips