Restoring the Database which has encrypted columns

Scenario:

I tried restoring a database from server1 to server 2.

But when I tried accessing the encrypted data on restored database of server2, I got the following error while opening the encryption key to read the encrypted data, something like this:

“Unable to open the key for encryption. First open the Master Key.”

3 methods to resolve this type of error:

Decrypt and Encrypt the Data

1.) Decrypt the Encrypted data on original database of server1.

2.) Transfer the data from server1 to server2.

3.) Drop the Encryption keys, Certificates and Master key on restored database of Server2:

Select * from sys.certificates

select * from sys.symmetric_keys

Drop SYMMETRIC KEY CCC

Drop Certificate CERTAdventureWorks

Drop Master Key

4.) Recreate the Master_Key, Certificate and Encryption keys with the same name as it was befor so that no code of the restored database needs to be done on server2:

CREATE MASTER KEY ENCRYPTION

BY PASSWORD = 'zxcvbnm’

create certificate CERTAdventureWorks with Start_date = '07/20/2011',

Expiry_date = '07/20/2012',

subject = 'Generic encryption certificate for AdventureWorks database'

CREATE SYMMETRIC KEY CCC

WITH ALGORITHM = TRIPLE_DES ENCRYPTION

BY CERTIFICATE CERTAdventureWorks

5.)Encrypt the data and store using the above keys on server2 restored database.

Add Master Key encryption with Service key (If you know the password of Master Key):

On Server2 Restored Database:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'abcde'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY

Backup the Service key on Server1 and Restore it to Server2:

On Server1:

BACKUP SERVICE MASTER KEY TO FILE = 'E:\Certificate\S_Master_Key' ENCRYPTION BY PASSWORD = 'fghijkl'

On Server2:

RESTORE SERVICE MASTER KEY FROM FILE = 'E:\Certificate\S_Master_Key' DECRYPTION BY PASSWORD = 'fghijkl'

In few cases you might have to do the steps of approach 2 after performing the approach 3 steps.

SQL Server 2005 – Mirroring using Witness Server

Principal Database: SQL Server 2005 Standard edition

Mirror Database: SQL Server 2005 Standard edition

Witness Server: SQL Server 2005 Express edition

Setup the Principal and Mirror databases as explained in my previous blog post: http://masoodhashim.blogspot.com/2011/07/sql-server-mirroring-step-by-step.html

Not to include a witness server make sure that the port through which you want the endpoint of witness server is open and has connectivity.

Follow these steps to setup the witness:

Step 1: Create encryption key, certificate and end-points on Witness Instance

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'

GO

CREATE CERTIFICATE HOST_WITT_cert

WITH SUBJECT = 'HOST_WITT certificate',

START_DATE = '07/06/2011'

GO

CREATE ENDPOINT End_Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE HOST_WITT_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4,

ROLE = Witness

)

GO

BACKUP CERTIFICATE HOST_WITT_cert

TO FILE = 'E:\certificate\HOST_WITT_cert.cer'

GO

Step 2: Create login, user and associate certificate with user on Principal Instance

/*

* Execute this against the Principal Instance. The HOST_WITT_cert.cer

* needs to be copied on the Principal Server.

*/

USE MASTER

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'

GO

CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login

GO

CREATE CERTIFICATE HOST_WITT_cert

AUTHORIZATION HOST_WITT_user

FROM FILE = 'E:\certificate\HOST_WITT_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]

GO

Step 3: Create login, user and associate certificate with user on Mirror Instance

USE MASTER

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'

GO

CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login

GO

CREATE CERTIFICATE HOST_WITT_cert

AUTHORIZATION HOST_WITT_user

FROM FILE = 'E:\certificate\HOST_WITT_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]

GO

Step 4: Create login, user and associate certificate with user on Witness Instance

/*

* Execute this against the Witness Instance. The HOST_PRIN_cert.cer

* and HOST_MIRR_cert.cer needs to be copied on the Witness Server.

*/

USE MASTER

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'

GO

CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login

GO

CREATE CERTIFICATE HOST_PRIN_cert

AUTHORIZATION HOST_PRIN_user

FROM FILE = 'E:\certificate\HOST_PRIN_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'

GO

CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login

GO

CREATE CERTIFICATE HOST_MIRR_cert

AUTHORIZATION HOST_MIRR_user

FROM FILE = 'E:\certificate\HOST_MIRR_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]

GO

Step 5: Setup Mirroring

On Principal Instance:

ALTER DATABASE AdventureWorks

SET WITNESS = 'TCP://192.168.1.3:5024'

Problems faced: The automatic failover was not happening when I was switching off the Principal database server. Then I changed IP address to Fully Qualified Domain Name while setting up the witness server:

ALTER DATABASE AdventureWorks

SET WITNESS = 'TCP://Masood-PC:5024'

Make sure that the Host name is mapped to the IP address in the host file.

Different Scenarios Experimented

1.) Mirror is off. Principal made a change and went offline before Mirror came online. Mirrored DB not able to become principal.

In such cases, do the applications which use alternate connection string can point to alternate string if main connection string is not reachable??

2.) If Witness gets disconnected, and then Principal goes offline. Then Mirror does not become a principal. If you try to switch off mirroring, the mirror goes to restoring state but never online. Then go to step 4 solutions to get back mirroring.

3.) If Witness gets disconnected, and then Mirror goes offline. Then principal goes into recovery state. If you try to switch off mirroring, the Principal gets online. Then goto step 4 solution to get back mirroring.

4.) Principal goes offline, and then mirror becomes Principal. The new principal goes offline and when the old principal comes online it goes to restoring state. In such case set mirroring off

Error: The database 'AdventureWorks' is marked RESTORING and is in a state that does not allow recovery to be run.

And restore DB in recovery mode:

RESTORE database AdventureWorks WITH RECOVERY

Then to get back the Mirroring, Restore DB in Mirror and set partners in both principal and Mirror. Don’t forget to set witness in principal instance.

How to Enable Mirroring on any Server

Add the command –T1400 in the start-up parameters of the SQL Server. Do it as follows:

Go to Surface area configuration then to Services:

Add “;-T1400” at the end of start-up parameters and restart the server. In “;-T1400” is case sensitive as far as I have read. Use Capital ‘T’. This sets up the Trace Mode on for mirroring and it gets enabled.

SQL Server Database Mode From Suspect –> Emergency–> Normal

Normal –> Suspect

Scenario 1 :

1.) Begin Tran

2.) Insert Into DimCustomer

(

CustomerAlternateKey

)

Select top 10000 CustomerAlternateKey + 'AAAA' From DimCustomer

Make some inserts

3.) Take the Server offline

4.) Copy the log file somewhere and Delete the original log file

5.) Bring the Server online

SELECT DATABASEPROPERTYEX ('GETIT', 'STATUS') AS 'Status';

Result: Suspect

Scenario 2:

1.) Take the DB offline

2.) Detach DB

3.) Move data files to some other folder

4.) Create a new DB with same name and destination as original location that suspect DB was in.

5.)Take DB offline

6.) Replace new DB files with Suspect DB files.

7.) Bring DB online

8.) SELECT DATABASEPROPERTYEX ('GETIT', 'STATUS') AS 'Status';

Result: Suspect

Suspect –> Emergency

ALTER DATABASE GETIT SET EMERGENCY;

GO

Emergency -> Normal

ALTER DATABASE GETIT SET SINGLE_USER;

GO

DBCC CHECKDB (GETIT, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

GO

ALTER DATABASE GETIT SET Multi_USER;

GO

SELECT DATABASEPROPERTYEX ('GETIT', 'STATUS') AS 'Status';

GO

Result: Online

SQL Server Mirroring Step By Step – Without Witness Server

Step 1:

Backup and Restore:

Principal Server:

Backup the principal server. Full backup and Transaction log backup of principal server should be taken and restored at the mirror server with NORECOVERY.

Mirror Server:

RESTORE DATABASE [AdventureWorks] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AW.bak'

WITH FILE = 1, MOVE N'AdventureWorks_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Mir\AdventureWorks_Data.mdf',

MOVE N'AdventureWorks_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Mir\AdventureWorks_Log.ldf',

NORECOVERY, NOUNLOAD, STATS = 10

GO

RESTORE DATABASE [AdventureWorks] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AW1.trn'

WITH FILE = 1, MOVE N'AdventureWorks_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Mir\AdventureWorks_Data.mdf',

MOVE N'AdventureWorks_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Mir\AdventureWorks_Log.ldf',

NORECOVERY, NOUNLOAD, STATS = 10

GO

Problems Faced:

If recovering is not happening due to permission issues then the backup folder should be given NETWORK SERVICE account permission. I tried but I did not success so I changed the logon of Mirror Server to LOCAL SYSTEM under SQL SERVER CONFIGURATION MANAGER and I succeeded.

After the restore is done change the log on back to NETWORK SERVICE as it was before.

Step 2:

Make sure TCP\IP connection for both principal and mirror SQL SERVER is on from SQL SERVER CONFIGURATION MANAGER:

Check if the Port number for Clients is set to 1433(Default):

After Restoring, the Database in Mirror server should be in restoring state as it was restored in NORECOVERY mode:

Steps to Start Mirroring:

I used the following link to setup the mirror:

http://blogs.msdn.com/b/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

Step 1: Create encryption key, certificate and end-points on Principal Instance

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'

GO

CREATE CERTIFICATE HOST_PRIN_cert

WITH SUBJECT = 'HOST_PRIN certificate',

START_DATE = '07/05/2011'

GO

CREATE ENDPOINT End_Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE HOST_PRIN_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4,

ROLE = ALL

)

GO

BACKUP CERTIFICATE HOST_PRIN_cert

TO FILE = 'E:\certificate\HOST_PRIN_cert.cer'

GO

--Drop Endpoint End_Mirroring

--Drop Certificate HOST_PRIN_cert

--Drop Master Key

The Certificate gets created:

Step 2: Create encryption key, certificate and end-points on Mirror Instance

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'

GO

CREATE CERTIFICATE HOST_MIRR_cert

WITH SUBJECT = 'HOST_MIRR certificate',

START_DATE = '07/05/2011'

GO

CREATE ENDPOINT End_Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE HOST_MIRR_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4,

ROLE = ALL

)

GO

BACKUP CERTIFICATE HOST_MIRR_cert

TO FILE = 'E:\certificate\HOST_MIRR_cert.cer';

GO

The certificate gets created:

Step 3: Create login, user and associate certificate with user on Principal Instance

USE MASTER

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'

GO

CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login

GO

CREATE CERTIFICATE HOST_MIRR_cert

AUTHORIZATION HOST_MIRR_user

FROM FILE = 'E:\certificate\HOST_MIRR_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]

GO

--drop login HOST_MIRR_login

--drop user HOST_MIRR_user

--Select * from sys.certificates

--DROP CERTIFICATE HOST_MIRR_cert

Step 4: Create login, user and associate certificate with user on Mirror Instance

USE MASTER

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'

GO

CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login

GO

CREATE CERTIFICATE HOST_PRIN_cert

AUTHORIZATION HOST_PRIN_user

FROM FILE = 'E:\certificate\HOST_PRIN_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]

--GRANT CONNECT ON ENDPOINT::End_Mirroring TO [NT SERVICE\MSSQL$MACHA]

GO

--DROP CERTIFICATE HOST_PRIN_cert

--drop login COMMONT

--drop user HOST_PRIN_user

Problems Faced in Step 3 and 4:

I was getting permission errors. Make sure that both the certificates that we had created have the permissions to the SQL server instance logins..

Step 5: Setup Mirroring

/*

* Execute this against the Mirror Instance.

*/

ALTER DATABASE AdventureWorks

SET PARTNER = 'TCP://192.168.1.3:5022'

GO

/*

* Execute this against the Principal Instance.

*/

ALTER DATABASE AdventureWorks

SET PARTNER = 'TCP://192.168.1.3:5023'

GO

Problems Faced in Step 5:

It was saying the principal log is not big enough and some cycling errors.

Resolution:

Take log backup from principal and apply it at the mirror:

On the principal, I did:

BACKUP LOG ADVENTUREWORKS TO DISK = 'E:\DB_LOG.BAK'

On the mirror, I did:

RESTORE TRANSACTION ADVENTUREWORKS FROM DISK = 'E:\DB_LOG.BAK'

WITH LOG

Now both the databases are mirrored.

Mirroring Monitor:

You can check if both the databases are synchronized from Mirroring Monitor and also check the data flow rate from this:

Setting up The witness Server:

I had SQL server 2008 for both Principal and Mirror Databases. I tried installing SQL Server 2005 express edition to be used as a Witness server. But i failed miserably. I tries out all the Google answers to solve it. But finally i arrived at an article which said that the witness server should not be any lower versions than those of principal or mirror. So i tried Mirroring using Witness server between the SQL Server 2005 standard editions for both Principal and Mirror and SQL Server express edition for Witness server and i succeeded. Check this out in my next post :

http://masoodhashim.blogspot.com/2011/07/sql-server-2005-mirroring-using-witness.html