Using Encryption

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL database in Microsoft Fabric

In SMO, the service master key is represented by the ServiceMasterKey object. This is referenced by the ServiceMasterKey property of the Server object. It can be regenerated by using the Regenerate method.

The database master key is represented by the MasterKey object. The IsEncryptedByServer property indicates whether or not the database master key is encrypted by the service master key. The encrypted copy in the master database is automatically updated whenever the database master key is changed.

It is possible to drop service key encryption using the DropServiceKeyEncryption method and encrypt the database master key with a password. In that situation, you will have to explicitly open the database master key before accessing private keys that it has secured.

When a database is being attached to an instance of SQL Server, you must either supply the password for the database master key or execute the AddServiceKeyEncryption method to make an unencrypted copy of the database master key available for encryption with the service master key. This step is recommended to avoid the need to explicitly open the database master key.

The Regenerate method regenerates the database master key. When the database master key is regenerated, all the keys that have been encrypted with the database master key are decrypted, and then encrypts them with the new database master key. The DropServiceKeyEncryption method removes the encryption of the database master key by the service master key. AddServiceKeyEncryption causes a copy of the master key to be encrypted using the service master key and stored in both the current database and in the master database.

In SMO, certificates are represented by the Certificate object. The Certificate object has properties that specify the public key, the name of the subject, period of validity, and information about the issuer. Permission to access the certificate is controlled by using the Grant, Revoke and Deny methods.

Example

For the following code examples, you will have to select the programming environment, programming template and the programming language to create your application. For more information, see Create a Visual C# SMO Project in Visual Studio .NET.

Adding a Certificate in Visual C#

The code example creates a simple certificate with an encryption password. Unlike other objects, the Create method has several overloads. The overload used in the example creates a new certificate with an encryption password.

{  
            //Connect to the local, default instance of SQL Server.   
            {  
                Server srv = new Server();  
  
                //Reference the AdventureWorks2022 database.   
                Database db = srv.Databases["AdventureWorks2022"];  
  
                //Define a Certificate object variable by supplying the parent database and name in the constructor.   
                Certificate c = new Certificate(db, "Test_Certificate");  
  
                //Set the start date, expiry date, and description.   
                System.DateTime dt;  
                DateTime.TryParse("January 01, 2010", out dt);  
                c.StartDate = dt;  
                DateTime.TryParse("January 01, 2015", out dt);  
                c.ExpirationDate = dt;  
                c.Subject = "This is a test certificate.";  
                //Create the certificate on the instance of SQL Server by supplying the certificate password argument.   
                c.Create("pGFD4bb925DGvbd2439587y");  
            }  
        }   

Adding a Certificate in PowerShell

The code example creates a simple certificate with an encryption password. Unlike other objects, the Create method has several overloads. The overload used in the example creates a new certificate with an encryption password.

# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2022  
CD \sql\localhost\default\databases  
$db = get-item AdventureWorks2022  
  
#Create a certificate  
  
$c = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Certificate -argumentlist $db, "Test_Certificate"  
$c.StartDate = "January 01, 2010"  
$c.Subject = "This is a test certificate."  
$c.ExpirationDate = "January 01, 2015"  
  
#Create the certificate on the instance of SQL Server by supplying the certificate password argument.  
$c.Create("pGFD4bb925DGvbd2439587y")  
  

See Also

Using Encryption Keys