Home » Blog » CERTPROPERTY – Digging Deeper into SQL Server Certificates

CERTPROPERTY – Digging Deeper into SQL Server Certificates

2014-05-12 - Cryptography, General, Security

Introduction

SQL Server certificates come with a variety of properties. To retrieve some of these properties, you can use the built-in CERTPROPERTY function.

CERTPROPERTY Example

The CERTPROPERTY function takes two parameters. The first parameter is the certificate_id of the certificate that you are interested in. The certificate needs to exist in the current database. To get the certificate_id for it, you can for example use the built-in CERT_ID function. The following example will make use of this option.

The second parameter of the CERTPROPERTY function is the name of the property you want to read. The following example select statements show all possible property names:

[sql] SELECT CERTPROPERTY(CERT_ID('ACertificate'),'Expiry_Date') AS Expiry_Date,
CERTPROPERTY(CERT_ID('ACertificate'),'Start_Date') AS Start_Date,
CERTPROPERTY(CERT_ID('ACertificate'),'Issuer_Name') AS Issuer_Name,
CERTPROPERTY(CERT_ID('ACertificate'),'Cert_Serial_Number') AS Cert_Serial_Number
SELECT CERTPROPERTY(CERT_ID('ACertificate'),'Subject') AS Subject,
CERTPROPERTY(CERT_ID('ACertificate'),'SID') AS SID,
CERTPROPERTY(CERT_ID('ACertificate'),'String_SID') AS String_SID
[/sql]

The only reason this example uses two separate select statements instead of one is to make the output fit on one screenshot. Well — almost:

Interestingly, there is currently no way to retrieve the name of a certificate, neither with the CERTPROPERTY function nor with a dedicated CERT_NAME function. The Expiry_Date, Start_Date, Issuer_Name and Cert_Serial_Number properties return just those values. The Subject is worth an article on its own. For now, it should suffice to mention that it is the same value that you have to specify when you create a new certificate in SQL Server. The SID is used by SQL Server to link the certificate to a server principal when the latter is created based on the certificate. The String_SID finally is that same SID value, expressed in a special string format.

Why not sys.certificates?

You might have noticed that all of these values are also part of the sys.certificates catalog view. Both were introduced together in SQL Server 2005 so it seems the SQL Server team just wanted to give us options on how to get to this information.

Summary

The built-in CERTPROPERTY function provides an additional way to get to some of the properties of a particular certificate. All properties accessible through this function are also exposed in the sys.certificates catalog view.