Skip to content

Latest commit

 

History

History
89 lines (68 loc) · 6.14 KB

using-encryption.md

File metadata and controls

89 lines (68 loc) · 6.14 KB
Raw
title description author ms.author ms.date ms.service ms.topic helpviewer_keywords monikerRange
Using Encryption
Using Encryption
markingmyname
maghan
08/06/2017
sql
reference
database master key [SMO]
cryptography [SMO]
cryptography [SQL Server], SMO
encryption keys [SMO]
encryption [SQL Server], SMO
encryption [SMO]
certificates [SMO]
service master key [SMO]
=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Using Encryption

[!INCLUDE SQL Server ASDB, ASDBMI, ASDW]

In SMO, the service master key is represented by the xref:Microsoft.SqlServer.Management.Smo.ServiceMasterKey object. This is referenced by the xref:Microsoft.SqlServer.Management.Smo.Server.ServiceMasterKey%2A property of the xref:Microsoft.SqlServer.Management.Smo.Server object. It can be regenerated by using the xref:Microsoft.SqlServer.Management.Smo.ServiceMasterKey.Regenerate%2A method.

The database master key is represented by the xref:Microsoft.SqlServer.Management.Smo.MasterKey object. The xref:Microsoft.SqlServer.Management.Smo.MasterKey.IsEncryptedByServer%2A 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 xref:Microsoft.SqlServer.Management.Smo.MasterKey.DropServiceKeyEncryption%2A 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 [!INCLUDEssNoVersion], you must either supply the password for the database master key or execute the xref:Microsoft.SqlServer.Management.Smo.MasterKey.AddServiceKeyEncryption%2A 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 xref:Microsoft.SqlServer.Management.Smo.MasterKey.Regenerate%2A 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 xref:Microsoft.SqlServer.Management.Smo.MasterKey.DropServiceKeyEncryption%2A method removes the encryption of the database master key by the service master key. xref:Microsoft.SqlServer.Management.Smo.MasterKey.AddServiceKeyEncryption%2A 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 xref:Microsoft.SqlServer.Management.Smo.Certificate object. The xref:Microsoft.SqlServer.Management.Smo.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 xref:Microsoft.SqlServer.Management.Smo.Certificate.Create%2A 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 xref:Microsoft.SqlServer.Management.Smo.Certificate.Create%2A 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