Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to use module signing for SQL Server security


By:   |   Last Updated: 2014-09-18   |   Comments (1)   |   Related Tips: More > 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:

  1. Create a certificate
  2. Create a user and map to the certificate
  3. Sign the module
  4. Grant the permissions for the certificate user
  5. 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


Last Updated: 2014-09-18


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, January 15, 2015 - 10:16:07 AM - Thomas Franz Back To Top

If you add the certificate to the master database to allow server-level permissions (as ALTER ANY LOGIN in my case) you have to export the certificate and reimport it in the application database again (otherwise you could not sign the procedure):

USE master;
      BACKUP CERTIFICATE DemoCert
      TO FILE = 'c:\temp\DemoCert.cer'
      WITH PRIVATE KEY (FILE = 'c:\temp\DemoCert.pvk',
           ENCRYPTION BY PASSWORD = 'This1$AC0MPL3XPassWord',
           DECRYPTION BY PASSWORD = 'This1$AC0MPL3XPassWord');

USE SampleDB;
      CREATE CERTIFICATE DemoCert
      FROM FILE = 'c:\temp\DemoCert.cer'
      WITH PRIVATE KEY (FILE = 'DemoCert.pvk',
           ENCRYPTION BY PASSWORD = 'This1$AC0MPL3XPassWord',
           DECRYPTION BY PASSWORD = 'This1$AC0MPL3XPassWord');

Of course if you have your own cer / pvk-File already, you do not need to export it from the master db and can import the certificate to both databases.

For MUCH more information about procedure security / certificate signing (and a nice script that creates a one-time-signing certificate with a password unknown even to yourself) have a look at http://www.sommarskog.se/grantperm.html


Learn more about SQL Server tools