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

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:

[sql] CREATE CERTIFICATE ATestCertificate
WITH SUBJECT = 'A Test Certificate';
[/sql]

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:

[sql] CREATE CERTIFICATE ATestCertificate
ENCRYPTION BY PASSWORD = '**********'
WITH SUBJECT = 'A Test Certificate';
[/sql]

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:

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

Checking if a Certificate Exists

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

[sql] 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';
[/sql]

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

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.