Export (0) Print
Expand All

Using Encryption

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.

For the following code example, 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 Basic SMO Project in Visual Studio .NET and Create a Visual C# SMO Project in Visual Studio .NET.

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.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'Define a Certificate object variable by supplying the parent database and name in the constructor.
Dim c As Certificate
c = New Certificate(db, "Test_Certificate")
'Set the start date, expiry date, and description.
c.StartDate = DateValue("January 01, 2007")
c.Subject = "This is a test certificate."
c.ExpirationDate = DateValue("January 01, 2008")
'Create the certificate on the instance of SQL Server by supplying the certificate password argument.
c.Create("pGFD4bb925DGvbd2439587y")

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 AdventureWorks2012 database. 
                Database db = srv.Databases["AdventureWorks2012"];

                //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");
            }
        } 

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 AdventureWorks2012
CD \sql\localhost\default\databases
$db = get-item AdventureWorks2012

#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")
 
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft