Logo
programming4us
programming4us
programming4us
programming4us
Windows XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
 
Windows Server

SQL Server 2008 : Managing Backups - Using Encryption

7/13/2011 9:14:23 AM
When you are using encryption in SQL Server, it is extremely important to back up any encryption keys and certificates and store them in a secure offsite location. For example, if you are using transparent data encryption, you cannot restore the encrypted database on a new system without having the encryption key. There is no back door. You can't attach the data files or restore the database, period. There are three objects you should back up when using encryption in SQL Server: the service master key, the database master key, and any certificates used for encryption.

The service master key is the root encryption key for the entire instance of SQL Server. SQL Server automatically creates the service master key the first time it is needed to encrypt another key. You should back up the service master key and keep it and the encryption password in a secure offsite location. The code to back up the service master key is shown in Listing 1.

Example 1. Code to Backup the Service Master Key
BACKUP SERVICE MASTER KEY
TO FILE = 'c:\service_master_key'
ENCRYPTION BY PASSWORD = '3ncrypti0nP@$$w0rd'
GO

The next thing you need to back up is the database master key. SQL Server uses the database master key to encrypt other keys and certificates within the database. If the key is accidently deleted, SQL Server will not be able to decrypt any objects created using that key. Therefore, you should back up the database master key and store it in a secure offsite location. You should also keep a local copy for immediate use. If you encrypted the database master key using a password, you will first have to open the key before backing it up. If you encrypted the database master key using the service master key, you do not have to open the key prior to backing it up. The code to back up the database master key for the master database is shown in Listing 2.

Example 2. Code to Back Up the Database Master Key
USE master
GO
--Open the master key (This is not required if the master key
-- is encrypted using the service master key.)
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'M@$t3r_K3y_3ncrypti0nP@$$w0rd '

--Back up the database master key
BACKUP MASTER KEY
TO FILE = 'c:\database_master_key'
ENCRYPTION BY PASSWORD = '3ncrypti0nP@$$w0rd'
GO

Finally, you need to back up any certificates and store them in a secure offsite location. The code in Listing 3 shows the syntax used to back up a certificate along with the private key.

Example 3. Code to Back Up a Certificate and the Private Key
USE master
GO

BACKUP CERTIFICATE TestCert
TO FILE = 'C:\TestCert.cer'
WITH PRIVATE KEY
(FILE = 'C:\TestCertKey.pvk',
ENCRYPTION BY PASSWORD = '3ncrypti0nP@$$w0rd');
GO
Other -----------------
- SQL Server 2008 : Managing Backups - Backups from T-SQL
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 4)
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 3) - What to Keep in Mind When Tuning
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 2) - Create, Execute, and Analyze
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 1) - Plan Your Tests
- Windows Small Business Server 2011 : Planning the Network Infrastructure (part 2)
- Windows Small Business Server 2011 : Planning the Network Infrastructure (part 1)
- Introducing Windows Small Business Server 2011
- Microsoft Dynamics GP 2010 : Streamlining payables processing by prioritizing vendors
- Microsoft Dynamics GP 2010 : Gaining visibility with Horizontal Scroll Arrows
 
 
Video tutorials
- How To Install Windows 8 On VMware Workstation 9

- How To Install Windows 8

- How To Install Windows Server 2012

- How To Disable Windows 8 Metro UI

- How To Change Account Picture In Windows 8

- How To Unlock Administrator Account in Windows 8

- How To Restart, Log Off And Shutdown Windows 8

- How To Login To Skype Using A Microsoft Account

- How To Enable Aero Glass Effect In Windows 8

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
programming4us programming4us
PS4 game trailer XBox One game trailer
WiiU game trailer 3ds game trailer
 
programming4us
Women
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone