How to work with a certificate in SQL Server

How to work with a certificate in SQL Server

This SQL command adds a certificate to a SQL Server database.

To perform this action, you need to have the CREATE CERTIFICATE permission on the database. It's important to note that only Windows logins, SQL Server logins, and application roles can own certificates. Groups and other roles are not allowed to own certificates.

-- Create a self-signed certificate
CREATE CERTIFICATE selfsigned
   ENCRYPTION BY PASSWORD = 'selfsigned2439587y'  
   WITH SUBJECT = 'Testing self-signed Records',
   EXPIRY_DATE = '20251031';

-- Restoring the certificate involves using the same process as creating a new certificate.
create certificate MySalaryCert
from file = N'c:\\Encryption\\MyCerti.cer'
with private key
  ( file = N'c:\\Encryption\\MyCerti.pvk'
  , decryption by password = N'hiMaNshu#paTel#dBa'
   );

CREATE CERTIFICATE MySalaryCert  
    FROM EXECUTABLE FILE = 'c:\\MySalaryCert.dll';  
GO

CREATE ASSEMBLY MySalaryCert  
    FROM 'c:\\MySalaryCert.dll'   
    WITH PERMISSION_SET = SAFE;  
GO  
CREATE CERTIFICATE MySalaryCert FROM ASSEMBLY MySalaryCert;  
GO

-- The following query displays the details of the certificate, including the public and private key information:
SELECT
  name,
  certificate_id,
  pvt_key_encryption_type_desc,
  subject,
  expiry_date,
  start_date,
  thumbprint,
  pvt_key_last_backup_date
FROM sys.certificates;

For more information, refer to the following resources:

Microsoft Docs: CREATE CERTIFICATE (Transact-SQL)

MSSQLTips: How to Configure SSL Encryption in SQL Server