Is a certificate in use?

SQL 2005 brought several important security improvements in regard to previous versions, one of which is the Certificate. It has three purposes in SQL 2005:

• Authentication – subjects (persons, devices, services) are granted access to the server (e.g. through HTTP endpoints, as database principals) after a valid certification path has been verified;
• Module signatures – procedures, functions, triggers and assemblies can be signed with certificates; and
• Encryption – data and symmetric keys can be encrypted using certificates.

The principal benefit of certificates is the possibility of separating security management from data management one stage further. In previous versions security needed to be managed either on the data server itself or inside the Windows NT domain. In SQL 2005 after establishing a trust between the data provider and the certificate issuer, the latter becomes the actual security manager. The host trusts that the issuer has verified the identity of the certificate subjects and that the issuer has fully resolved any matters regarding the use of certificates – which certificates can be used by which subjects etc.

This post is dedicated to a very specific case regarding certificates: knowing whether a certificate is being used (and how).

The function below uses four catalog views to determine whether a certificate is in use in one or more of the following situations:

• a user in the current database that maps to a certificate (sys.database_principals);
• a symmetric key encrypted by a certificate (sys.key_encryptions); or
• a cryptographic property of a securable (e.g. module signatures – sys.crypt_properties).

One thing is missing, obviously – a test of whether a certificate has been used to encrypt data. At this time I haven't had any luck in discovering whether this information is stored at all. The real question is: should it be saved? Do the principles of data security allow the data server to track which values have been encrypted by which method and/or by which means? I believe not - 'tis the eternal conflict of security (inaccessibility) vs. accessibility (insecurity). It seems to be an unsupported feature – if knowing whether a certificate has been used to encrypt data is vital to your business you should design your own tracking solution.

While SQL 2005 will stop us with an error message if we decide to drop a certificate that is in use in one of the three situations mentioned above, we are free to drop a certificate if it's used solely to encrypt data.

Which makes it impossible to decrypt it afterwards.

Incidentally, dropping Symmetric keys which were used to encrypt data will also succeed – followed by an error message when trying to use (open) them afterwards. Obviously, the data can no longer be decrypted in this case as well.

Pretty much the same goes for Asymmetric keys, however, since they do not have to be opened prior to being used, no error is ever raised, only the data remains "permanently encrypted".

This may sound ridiculous, but on the other hand it's simply the case of the principles of data security (once again) prevailing over the principles of data accessibility. Which isn't all bad, actually.

I guess it's good practice to backup certificates.

But I'm drifting...

Anyway, here's the function:

create function dbo.fnGet_Certificate_IsUsed
 (
 @certificateName sysname
 )
returns tinyint
/*
Return value Meaning
null  Certificate by that name does not exist
0  Certificate by that name exists but is not used
1  Database User is mapped to certificate
2  Entities signed or encrypted by certificate
3  1 + 2
4  Securables associated to certificate
5  1 + 4
6  2 + 4
7  3 + 4
*/
as
begin
 declare @state tinyint 

 if (exists (
   select *
    from sys.certificates
    where (sys.certificates.[name] = @certificateName)
   ))
  begin
   set @state = 0

   if (exists (
     select *
      from sys.certificates
       inner join sys.database_principals
         on sys.database_principals.sid = sys.certificates.sid
      where (sys.certificates.[name] = @certificateName)
     ))
    begin
     set @state = @state + 1
    end

   if (exists (
     select *
      from sys.certificates
       inner join sys.key_encryptions
         on sys.key_encryptions.thumbprint = sys.certificates.thumbprint
      where (sys.certificates.[name] = @certificateName)
     ))
    begin
     set @state = @state + 2
    end

   if (exists (
     select *
      from sys.certificates
       inner join sys.crypt_properties
         on sys.crypt_properties.thumbprint = sys.certificates.thumbprint
      where (sys.certificates.[name] = @certificateName)
     ))
    begin
     set @state = @state + 4
    end
  end

 return @state
end
go

Return values (bitmap):

Value	Meaning
null	certificate does not exist
0	certificate exists but isn't used
1	a user is mapped to the certificate
2	one or more entities are signed or encrypted with the certificate
4	one or more modules are signed with the certificate

An example of use (after you've created the function in the AdventureWorks database):

use AdventureWorks
go

create certificate JethroTull
 encryption by password = 'JT4Ever'
 with subject = 'Jethro Tull fans'
go

create symmetric key JethroTullSymmetricKey
 with algorithm = des -- or any of the supported algorithms
 encryption by certificate JethroTull
go

create user Jethro
 for certificate JethroTull
go

select dbo.fnGet_Certificate_IsUsed('JethroTull') as CertificateState
go

ML

P.S. Coming soon: a function to list certificate dependencies.