Setting up Database mirroring in SQL Server 2008 using T-SQL when the database is encrypted using Transparent Data Encryption.


Consider the following Scenario, You are using SQL Server 2008 Enterprise edition RTM or higher version and you are Transparent database encryption (new feature introduce in SQL Server 2008) to encrypt the Database to be mirrored. The database encryption key has been encrypted using a server certificate and which is turn encrypted using master database key (usually a symmetric key). 


 


When trying to setup database mirror using SSMS you might get the below exception


 


<Exception>


TITLE: Database Properties


——————————


An error occurred while starting mirroring.


——————————


ADDITIONAL INFORMATION:


Alter failed for Database ‘<DB_NAME>’.  (Microsoft.SqlServer.Smo)


 


For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1787.0+((Katmai_RTM_QFE-CU).090107-2206+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476


——————————


Alter failed for DatabaseEncryptionKey ‘Microsoft.SqlServer.Management.Smo.ObjectKeyBase’.  (Microsoft.SqlServer.Smo)


For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1787.0+((Katmai_RTM_QFE-CU).090107-2206+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+DatabaseEncryptionKey&LinkId=20476


——————————


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


——————————


Database ‘<DB_NAME>’ cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)


For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1787&EvtSrc=MSSQLServer&EvtID=927&LinkId=20476


——————————


BUTTONS:


OK


——————————


</Exception>


 


It seems to be an issue with SSMS GUI. To workaround this problem you can use T-SQL to setup Database Mirroring in SQL Server 2008 of an encrypted database.



Here, I am going to explain the setup by step procedure on how to encrypt the database using TDE and then mirror the encrypted database using T-SQL.


 


On the Principal Site


======================


/* Create a Database Master Key in the Master Database which should be secured by a password. If you omit to specify the encryption mechanism (password) while creating the MASTER KEY, the database master key gets encrypted by SERVICE MASTER KEY (default behavior). In that scenario you might need to export the service master key and copy/import it on the mirror site.


 


Since the database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.


 


Information about the database master key is visible in the sys.symmetric_keys catalog view.


 


If the database master key already exists and not in use or use wants to change the encryption algorithm or key protection mechanism, either you can the alter the database master key or drop and recreate it.


 


In my Test Scenario, I’m dropping the existing master key and creating a new master key encrypted with password */


 


–Check if the master key already present. 


USE Master


go


select * from sys.symmetric_keys


 


–Drop the existing Master Key


Use MASETR


GO


DROP MASTER KEY


Go


 


–Create Master Key in Master Database


USE MASTER


GO


CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<TypeStrongPassword>’;


go


 


**Note :  TypeStrongPassword should remain same throughout the setup.


 


–Create Server Certificate in the Master Database encrypted with master key (created above) which would be used to create USER database encryption key.


 


USE Master;


GO


CREATE CERTIFICATE <MyDB_Mirror_Server_Cert> WITH SUBJECT = ‘SQL TDE CERT’


Go


 


*Note : Replace <MyDB_Mirror_Server_Cert> with the name of Certificate. You can specify any name of your choice. Also you can change the SUBJECT to a more meaningful description.


 


— Now in the User database, create a Database Encryption Key. In my test scenario, I’m dropping the existing Database Encryption Key if already exist and not in use.


— Information about  the database encryption keys is stored in sys.dm_database_encryption_keys.


 


USE <User Database>


go


DROP DATABASE ENCRYPTION KEY


go


CREATE DATABASE ENCRYPTION KEY


WITH ALGORITHM = AES_128


ENCRYPTION BY SERVER CERTIFICATE <MyDB_Mirror_Server_Cert>


GO


–Enabling Transparent Database Encryption for the USER Database


USE master;


GO


ALTER DATABASE <User Database> SET ENCRYPTION ON


GO


 


— Now Backup master key immediately


USE master;


OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘<TypeStrongPassword>’;


BACKUP MASTER KEY TO FILE = ‘<Full path and exportmasterkey filename>’


    ENCRYPTION BY PASSWORD = ‘<TypeStrongPassword>’;


GO


 


**Note: Replace <Full path and exportmasterkey filename> with full path and export file name. Also Replace <TypeStrongPassword> with the actual password use to encrypt the master key.


 


— Now Backup Server certificate as well


 


BACKUP CERTIFICATE <MyDB_Mirror_Server_Cert> TO FILE = ‘<Full path and export cert filename>’


    WITH PRIVATE KEY ( FILE = ‘<Full path and export filename _key>’ ,


    ENCRYPTION BY PASSWORD = ‘<TypeStrongPassword>’);


GO


 


— Perform Full database backup of the Principal database


 


 


On the Mirrored Site


====================


/* On Mirror Server, restore the master key from backup performed from principal site. Since the database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. Information about the database master key is visible in the sys.symmetric_keys catalog view.


 


If the database master key already exists and not in use, drop the existing database master key (if any) and restore it from backup taken from principal site.


 


In my Test Scenario, I’m dropping the existing master key and restoring the master key from backup taken from principal site */


 


use master


go


drop master key


go


RESTORE MASTER KEY


    FROM FILE = ‘ Full path and exportmasterkey filename>’


    DECRYPTION BY PASSWORD = ‘<TypeStrongPassword>’


    ENCRYPTION BY PASSWORD = ‘<TypeStrongPassword>’;


GO


 


— Create server certificate on the mirror site using the PRIVATE KEY backed up from principal site


 


USE Master;


GO


DROP CERTIFICATE <MyDB_Mirror_Server_Cert>


go


 


OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘<TypeStrongPassword>’


 


CREATE CERTIFICATE <MyDB_Mirror_Server_Cert>    


FROM FILE = ‘<Full path and export cert filename>’    


WITH PRIVATE KEY (FILE = ‘<Full path and export filename _key>’,    


DECRYPTION BY PASSWORD = ‘<TypeStrongPassword>’);


GO


 


— Restore the database from backup with NORECOVERY


 


RESTORE DATABASE <User Database>


   FROM disk = ‘C:\Program Files\Microsoft SQL Server\MSSQL10.x\MSSQL\Backup\<Backup_FileName>.bak’


   WITH NORECOVERY,


      MOVE ‘<Primary FileGroup>’ TO


‘C:\Program Files\Microsoft SQL Server\MSSQL10.y\MSSQL\DATA\<PrimaryDB_File>.mdf’,


      MOVE ‘<Logical File name of LogFile>’


TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10.y\MSSQL\DATA\<Log_File>.ldf’


 


Where x = Instance ID of the Principle Server and y = Instance ID of Mirror Server


 


— On the Mirrored Site, drop the existing mirroring endpoint and create the database mirroring endpoint as follows


 


DROP ENDPOINT <endpoint_mirroring>


 


CREATE ENDPOINT <endpoint_mirroring>


    STATE = STARTED


    AS TCP ( LISTENER_PORT = 7023 )


    FOR DATABASE_MIRRORING (ROLE=PARTNER);


GO


 


— Verify that the endpoint is properly configured and is in state “STARTED”


 


select * from sys.database_mirroring_endpoints


 


–On the primary site, drop the existing mirroring endpoint and create the database mirroring endpoint as follows


 


DROP ENDPOINT <endpoint_mirroring>


 


CREATE ENDPOINT <endpoint_mirroring>


    STATE = STARTED


    AS TCP ( LISTENER_PORT = 7022 )


    FOR DATABASE_MIRRORING (ROLE=PARTNER);


GO


 


— Verify that the endpoint is properly configured and is in state “STARTED”


 


select * from sys.database_mirroring_endpoints


 


— First set the principal server as partner on the mirror database


 


ALTER DATABASE <User Database> SET PARTNER = ‘TCP://<FQDN of the Principal Server>:7022’


 


 


— Now set the Mirror server as partner on the principal database


 


ALTER DATABASE <User Database> SET PARTNER = ‘TCP://<FQDN of the Mirror Server>:7023’


 



Now the DATABASE Mirroring has been successfully setup when using Transparent Data Encryption in SQL Server 2008.


You can test and verify failover works fine either using SSMS or the T-SQL command.



 


Gurwinderjit Singh


Tech Lead, Microsoft SQL Server

Comments (25)

  1. Jeff Strongo says:

    Hello,

    Thank you for the instructions above.

    At the final step:

    (– Now set the Mirror server as partner on the principal database)

    I receive the following error:

    Database mirroring error: status 15581, severity 16, state 0, string <database name>

    Do you know what causes this as everything up to that final point works correctly. Thank You.

  2. Gurwinderjit Singh says:

    Since the database on the principal site is encrypted state.

    1) check if you have opened the master key and the other key(s) (if any) you are using in TDE before issuing the Alter database statement on the principal site

    2) Also check the state of the Mirroring endpoint, it must be in state "STARTED".

  3. Glen says:

    Hi Gurwinderjit,

    I would like to THANK YOU for detailed step by step instructions above. It really worked for me, not only saved my time but made my life easy.

    Once again thank you very much. you guys really ROCK…!

    Glen

  4. Sri says:

    This is a good post. I have a question, how to find which is mirrored database or principal database using TSql command?

  5. Ryan says:

    Hello,

    I have this exact situation with a few differences.

    I have another database mirrored that doesent need TDE. I have that mirrored just fine.

    When I run the

    ALTER DATABASE <User Database> SET PARTNER = ‘TCP://<FQDN of the Principal Server>:5022’

    I get Error

    Msg 1416, Level 16, State 31, Line 1

    Database "Database" is not configured for database mirroring.

    Or through the GUI I get the usual error.

    I have used the same script to create the mirroring for the other database. And it runs.

    Help is very welcome!

    Thank you!

  6. Gurwinderjit Singh says:

    Ryan,

    Error 1416 : states that the mirror database has been recovered. Ensure that the mirrored database must be in Restoring state (NORECOVERY option)

    Run the below queries on the mirror server and verify the state

    –check the mirroring endpoint state

    select dme.endpoint_id, dme.name, dme.principal_id, dme.state_desc,

    dme.role_desc,dme.connection_auth_desc,dme.certificate_id, dme.encryption_algorithm_desc, te.port, te. ip_address

    from sys.database_mirroring_endpoints dme inner join sys.tcp_endpoints te on dme.endpoint_id = te.endpoint_id and te.type = 4

    — check the mirror database state

    select database_id, user_access_desc, state_desc, recovery_model_desc, is_broker_enabled, log_reuse_wait_desc

    from sys.databases where name = ‘<database_name>’

    In case, the mirror database is in ONLINE state, you need to again restore the database and the subsequent T-Log backups with NORECOVERY option before setting the mirroring partners

  7. Ryan says:

    Thanks for the reply,

    I have verified all of this info. Restored the database with NORECOVERY and T-Logs with NORECOVERY.

    The database on the mirror says (Restoring….)

    I have 3 other mirrored databases on these servers without TDE and they work fine. For setting up mirroring I have just run the script to ensure I am exicuting it the correct way.

    Still unsure of next steps.

    Thanks!

  8. Richard Moutlon says:

    Dear Gurwinderjit,

    I appreciate the detailed explanation of how to Mirror Encrypted databases.

    However, I am experiencing the same Database mirroring error that Jeff Strongo reported back on April 20:

      status 15581, severity 16, state 0, string <database name>

    I have checked the two items you gave in your follow-up email, but this has not cleared the error for me.

    I wondered whether any other information has surfaced about this error ?

    Appreciate any other information that you have.

    Thanks

  9. Gurwinderjit Singh says:

    Provide details of your environment in which you are trying to setup DB Mirroring with TDE database  which includes the following:

    1) SQL Server build of both the prinicipal and the mirrored Server

    2) Are both the Servers in same subnet / same domain

    3) Is there any firewall between these two servers.

    4) which step of the above script fails and with what error.

    5) how the database is encrypted, provide some details of encryption hierarchy being used.

    6) Are you using same both in both the mirroring endpoints or different ports. Also ensure that the port(s) are open in the firewall or the Antivirus settings (if any).

    7) have you verified if the mirroring endpoints are working fine or not.

  10. Richard Moulton says:

    Gurwinderjit,

    Sorry for my slow response to your questions.   I’ve been working on other projects in the meantime.

    1)  10.0.2531

    2)  Both servers are in the same subnet/domain

    3)  No firewall between servers

    4)  Final step.   Error is:  status 15581, severity 16, state 0, string <database name>

    5)  Database is encrypted using the exact instructions in your article

    6)  Both endpoints are 5022  (please note that mirroring of non-encrypted db works fine)

    7)  Mirroring end points are STARTED.

  11. Richard Moulton says:

    Gurwinderjit,

    I worked with Microsoft support to resolve my issue (REG:109103045827330).

    For mirroring to work for me, the following extra statements needed to be executed immediately after the Master Key was restored on the Mirror site:

      OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘<strong password>’

      ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

      go

    This was the only change that I needed to make to your instructions to fully resolve the "status 15581" error I was receiving.

  12. Ryan.xu says:

    here is another trick,configure mirroring first, then implemente TDE encryption:

    1. configure database mirroring first

    2. create master key on principal

    3. create certificate on principal

    4. suspend the mirroring between principal and mirroring server

    5.don’t resotre the master key to mirroring server, just create a master key on mirroring server, restore the certificate from principal server

    6. resume the mirroring between database.

    It works for me, if I restore the master key to mirroring server,run

    ALTER DATABASE <User Database> SET PARTNER = ‘TCP://<FQDN of the Principal Server>:5022’

    will get Error

    Msg 1416, Level 16, State 31, Line 1

    Database "Database" is not configured for database mirroring.

  13. AmruthaVarshiniJ says:

    Thank you for your comments!

  14. The steps posted by Ryan.xu are all that's necessary – restoring the database master key of the master database is an unnecessary step.

    All that's needed here is for the server to be able to decrypt the DEK (database encryption key) used by TDE to encrypt the database – to do that it just needs to have access in master to the certificate used to protect the DEK. The actual DbMK of the master database doesn't need to be identical – as long as the certificate is encrypted by it, all is fine.

  15. njain says:

    How do you set up mirroring between a work group server and a domain server?

    I tried all kijnd of solutions offered on the internet but had no luck. I am keep on getting following message:

    Error: 1418 013 Microsoft SQL Server 013 The server network address can not be reached or does not exist. Check the network address name and reissue the command

  16. Gurwinderjit says:

    @njain,

    Use "Pass Through Authentication" to setup mirroring between Domain and workgroup server.

    The error you are encountering indicates network problem, ensure that you are able to access the NETBIOS names from either servers. you can also try using the IP Address instead of FQDN while creating the mirroring endpoints.

  17. bf4385 says:

    Hopefully someone still reads this thread…

    I'm curious about setting up Mirroring including the third optional server as a "Witness Server".  Has anyone done this, what would be the edit to the above procedure?

  18. Neeraj Dwivedi says:

    Thanks Gurwinder, this is really nice article. It really helped me a lot.I was getting r=error"Database mirroring error: status 15581, severity 16, state 0, string <database name>", but the solution which Richard Moutlon gave fixed my issue. One more thing I wanted to add. I also had to setup witness server instance, but that was really simple. I just had to run "ALTER DATABASE DBNAME SET WITNESS =  'TCP://FQDN:port'" of witness server instance after creating endpoint on witness box.

  19. Abku says:

    Awesome step by step explanation to setup mirroring with TDE

  20. natebruneau@hotmail.com says:

    FYI, I was getting this error:

    Database mirroring error: status 9016, severity 16, state 0, string EncryptDB. But the answer posted earlier fixed my issue:

     OPEN MASTER KEY DECRYPTION BY PASSWORD = '<strong password>'

     ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

     Go

    On the Mirror server.

  21. Smit says:

    How and where do I put service account name (don't want to use my window account) to run the mirroring?

  22. Dom says:

    I have done the recovery of the Full DB .bak with NORECOVERY

    I have done the recovery of the log transaction .trn with NORECOVERY

    now on the principal and mirrored servers the DB si in "Recovering" status for an hour already … the DB is 4 Gb and the log .5 Gb …

    anything wrong?

    The mirror option is not available yet as when right clicking the DB and trying to select "mirror" it is going straight to "Properties"!!!

    Thanks,

    Dom

  23. Rohit R. says:

    Do we need to implement TED for database on Azure?