CREATE CERTIFICATE – How to create a Self-Signed Certificate in SQL Server

2014-04-28 - Cryptography, Encryption Hierarchy, General, Security, Series

Introduction

With the CREATE CERTIFICATE T-SQL statement you can create a self-signed certificate. Certificates are asymmetric encryption keys that are signed by a certificate authority. SQL Server does not provide a way to create or request such an externally signed certificate (but they can be imported). However, when working with certificates in SQL Server we often do not need any outside validation. In those cases, it is appropriate to create a self-signed certificate.

CREATE CERTIFICATE Example

The CREATE CERTIFICATE statement in its simplest form has the following format:

CREATE CERTIFICATE ATestCertificate 
  WITH SUBJECT = 'A Test Certificate';

ATestCertificate is the name for the certificate. . Subject is a field defined by the X.509 standard and certificates created by this command follow that standard. In SQL Server, the subject can be up to 128 characters long. This is a SQL Server restriction, the X.509 standard itself allows for longer subjects.

The private key for SQL Server generated certificates is always a 1024-bit key for the RSA encryption algorithm. SQL Server supports imported certificate key lengths from 384 to 4096 bits but can only generate this one key length for certificates.

Database Master Key vs. Password Protected Certificates

Certificates created with the CREATE CERTIFICATE statement as described in the previous section are protected by the database master key. To be able to execute that statement, the database master key has to exist. If the database master key does not exist or if it cannot be opened, the CREATE CERTIFICATE statement will fail.

Alternatively, if you do not want the private key to be protected by the database master key, you can provide a password using the ENCRYPTION BY PASSWORD clause:

CREATE CERTIFICATE ATestCertificate 
  ENCRYPTION BY PASSWORD = '**********' 
  WITH SUBJECT = 'A Test Certificate';

A certificate can only be protected by either a password or the database master key. If a password is specified during creation, that same password is required every time the private key needs to be accessed.

Certificate Expiration Dates

The CREATE CERTIFICATE statement allows you to specify a date range during which the new certificate is valid. However, only the Service Broker checks those dates. Any other SQL Server functionality that uses certificates simply ignores these fields.

The expanded CREATE CERTIFICATE syntax that includes those dates is:

CREATE CERTIFICATE ATestCertificate 
  ENCRYPTION BY PASSWORD = '**********' 
  WITH SUBJECT = 'A Test Certificate',
  START_DATE = '2014-04-24',
  EXPIRY_DATE = '2015-04-23';

Checking if a Certificate Exists

You can check if a certificate exists already for example by using the sys.certificates catalog view:

SELECT C.name,C.certificate_id,C.pvt_key_encryption_type_desc,C.subject,C.start_date,C.expiry_date
  FROM sys.certificates AS C
 WHERE C.name = 'ATestCertificate';

The output of this statement includes the valid date range as well as the certificate's subject:

CREATE CERTIFICATE in action.

Summary

CREATE CERTIFICATE can be used to create a self-signed certificate in SQL Server. The private key of such a certificate can be protected either by a password or by the database master key.

Encryption Hierarchy Series

This article is part of the Encryption Hierarchy series. Check out The SQL Server Encryption Hierarchy for an overview of the hierarchy and a list of all posts that are part of the series.

Categories: Cryptography, Encryption Hierarchy, General, Security, Series
Tags: , , , , ,

5 Responses to CREATE CERTIFICATE – How to create a Self-Signed Certificate in SQL Server

  1. Pingback: How to create a Self-Signed Certificate in SQL Server | Senior DBA

  2. wilyoke says:

    Hi,
    May I know after created the self-signed certificate in SQL server, how should I use it with my Windows Application?
    Thanks.

  3. @sqlity says:

    wilyoke There are many things that you can do with a certificate in SQL Server and describing all of them does not really fit in a comment here. You can for example http://sqlity.net/en/2441/create-symmetric-key/. 

    Maybe you can describe the problem you are trying to solve?

  4. wilyoke says:

    Hi Sebastian,
    I have a stand alone application in Client Server which will connect to a Database Server when it is running. I would need to make the full interaction start from Client Server to the Database Server are protected.
    May I know how should I configure at my application/Client Server and Database Server ?
    Because I don’t know what is the following step to perform after running the create certificate script.
    Thanks.

  5. @sqlity says:

    wilyoke The type of certificate this article covers is stored within a database and its reach is confined to the SQL Server instance. What you are looking for is to protect the communication between the instance and the client. For that you need a different certificate. More info on that you can find here: http://msdn.microsoft.com/en-us/library/ms191192.aspx

Leave a Reply