Using Transparent Data Encryption
Scenario
In this exercise, you will encrypt a database and access it from a client application to verify that encryption is
transparent to client applications. Client applications that use traditional data encryption must decrypt the data
before they can use it. This causes increased complexity for applications that use the encrypted data and slows development time. Transparent data encryption performs all encryption and decryption on the database system. This provides protection and does not require any additional work for application developers.
Tasks Detailed Steps Complete the following task on:
1. Configure Transparent Data Encryption
a. In SQL Server Management Studio, Open the Encrypt.sql script from the
C:\SQLHOLS\WhatsNewForDBAs\Starter\TDE folder, and then examine the code
it contains. The script includes Transact-SQL statements to:
• Create a master key with the password Pa$$w0rd.
• Create a certificate with the name of ServerCertificate and the subject Server level certificate.
• Create a database encryption key that uses the AES_128 algorithm and is
encrypted with the server certificate ServerCertificate.
• Alter the AdventureWorks database and set encryption on.
b. Execute the Transact-SQL query.
2. Access the encrypted database from a client application.
a. Start Microsoft Office Excel® 2007.
b. On the Data tab of the ribbon, in the Get External Data section, click From
Other Sources, and then click From SQL Server.
c. In the Server name box, type (local) and then click Next.
d. In the Select the database that contains the data you want drop-down box,
select AdventureWorks.
e. In the Connect to a specific table list, click Employee, and then click Next.
f. Click Finish, and then click OK to import the data.
g. Notice that SQL Server has transparently decrypted the data without any changes
to the client application.
h. Close Excel without saving changes.
Key Point: Transparent data encryption requires no configuration or programming on
the client application.
3. Back up the encrypted Adventure Works database.
a. In SQL Server Management Studio, in Object Explorer, in the connection to the
(local) database engine instance, expand the Databases folder, right-click
AdventureWorks, point to Tasks, and then click Back Up.
b. In the Destination section, click Remove to remove any existing backup destinations.
c. Click Add, type C:\SQLHOLS\AdventureWorksEncrypt.bak and then click OK.
d. Click OK to back up the database, and then click OK when the backup operation
has completed successfully.
4. Attempt to restore the
a. In Object Explorer, click Connect and then click Database Engine.
b. Connect to the (local)\Named server instance using Windows authentication. Be
careful not to connect to the default instance - we are going to attempt a restore to
Scenario
In this exercise, you will encrypt a database and access it from a client application to verify that encryption is
transparent to client applications. Client applications that use traditional data encryption must decrypt the data
before they can use it. This causes increased complexity for applications that use the encrypted data and slows development time. Transparent data encryption performs all encryption and decryption on the database system. This provides protection and does not require any additional work for application developers.
Tasks Detailed Steps Complete the following task on:
1. Configure Transparent Data Encryption
a. In SQL Server Management Studio, Open the Encrypt.sql script from the
C:\SQLHOLS\WhatsNewForDBAs\Starter\TDE folder, and then examine the code
it contains. The script includes Transact-SQL statements to:
• Create a master key with the password Pa$$w0rd.
• Create a certificate with the name of ServerCertificate and the subject Server level certificate.
• Create a database encryption key that uses the AES_128 algorithm and is
encrypted with the server certificate ServerCertificate.
• Alter the AdventureWorks database and set encryption on.
b. Execute the Transact-SQL query.
2. Access the encrypted database from a client application.
a. Start Microsoft Office Excel® 2007.
b. On the Data tab of the ribbon, in the Get External Data section, click From
Other Sources, and then click From SQL Server.
c. In the Server name box, type (local) and then click Next.
d. In the Select the database that contains the data you want drop-down box,
select AdventureWorks.
e. In the Connect to a specific table list, click Employee, and then click Next.
f. Click Finish, and then click OK to import the data.
g. Notice that SQL Server has transparently decrypted the data without any changes
to the client application.
h. Close Excel without saving changes.
Key Point: Transparent data encryption requires no configuration or programming on
the client application.
3. Back up the encrypted Adventure Works database.
a. In SQL Server Management Studio, in Object Explorer, in the connection to the
(local) database engine instance, expand the Databases folder, right-click
AdventureWorks, point to Tasks, and then click Back Up.
b. In the Destination section, click Remove to remove any existing backup destinations.
c. Click Add, type C:\SQLHOLS\AdventureWorksEncrypt.bak and then click OK.
d. Click OK to back up the database, and then click OK when the backup operation
has completed successfully.
4. Attempt to restore the
a. In Object Explorer, click Connect and then click Database Engine.
b. Connect to the (local)\Named server instance using Windows authentication. Be
careful not to connect to the default instance - we are going to attempt a restore to
No comments:
Post a Comment