How to work with a certificate in SQL Server Himanshu Patel SQL Server Consultant developer & Administrator -

Adds a certificate to a database in SQL Server.

Requires CREATE CERTIFICATE permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.

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

--To restore the certificate, you perform the same procedure as the CREATE 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

--This will load both the public and private sides of the encryption key into SQL Server and you can view them with this DDL:
select
  name
, certificate_id
, pvt_key_encryption_type_desc
, subject
, expiry_date
, start_date
, thumbprint
, pvt_key_last_backup_date
 from sys.certificates

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-ver15

https://www.mssqltips.com/sqlservertip/3299/how-to-configure-ssl-encryption-in-sql-server/

Spread the love