SQL Server 2008 Open Master Key Error Upon Physical Server Change Over

SQL Server 2008 Open Master Key error upon physical server change over

The database master key is encrypted using the server master key, which is specific to the machine where SQL Server is installed. When you move the database to another server, you lose the ability to automatically decrypt and open the database master key because the local server key will most likely be different. If you can't decrypt the database master key, you can't decrypt anything else that depends on it (certificates, symmetric keys, etc).

Basically, you want to re-encrypt the database master key against the new server key, which can be done with this script (using admin privileges):

-- Reset database master key for server (if database was restored from backups on another server)
OPEN MASTER KEY DECRYPTION BY PASSWORD = '---your database master key password---'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

Note that when you create a database master key, you should always provide a password as well so that you can open the key using the password in the scenario where the service master key cannot be used - hopefully you've got that password stored somewhere!

Alternatively, you can restore a backup of the database master key - but you need one that was created for the target server, not the source server.

If you haven't got either a backup or a password, then I'm not sure you will be able to recover the encrypted data on the new server, as you will have to drop and recreate the database master key with a new password, which will kill any dependent keys and data.

Restoring SQL Server Database - Master Key Not Opening

The problem is the SMK has changed (since the machine has changed). There's an article explaining it here. Just export and import the SMK -- bearing in mind that any encrypted data in your copied-to system will be unreadable.

MSDN articles:

• Backing up the SMK
• Restoring the SMK

Please create a master key in the database or open the master key in the session before performing this operation

Fixed.

Referenced: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine

This paragraph gave it away:

The copy of the DMK stored in the master system database is silently updated whenever the DMK is changed. However, this default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of the ALTER MASTER KEY statement. A DMK that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.

Ran the following on my secondary nodes.

1. Drop Certificate...
2. Drop master key
3. Create master key...
4. Create certificate from file...

Arrived at the solution after checking this.

--on primary, output: master 
select name from sys.databases where is_master_key_encrypted_by_server=1

--on secondary, output: nothing...
select name from sys.databases where is_master_key_encrypted_by_server=1

So I figured if I could get the master key to be encrypted by default by the service master key then this would automate the decryption.

--on secondary
drop certificate [BackupCertWithPK]
drop master key

--Skipped restore master key from file.
--Instead, I ran create master key with password.
create master key encryption by password = 'MyTest!Mast3rP4ss';

--verify by open/close.
open master key decryption by password = 'MyTest!Mast3rP4ss';
close master key;

--proceed to restore/create cert from file.
create cerfiticate [BackupCertWithPK] 
from file = '\\FS1\SqlBackups\SQL1\Donot_delete_SQL1-Primary_BackupCertWithPK.cer' 
with private key (file = '\\FS1\SqlBackups\SQL1\Donot_delete_SQL1-Primary_BackupCertWithPK.key' , decryption by password = 'key_Test!prim@ryP4ss') ; 

After this ran the above select again.

--on secondary, output: master, now there was hope again!
select name from sys.databases where is_master_key_encrypted_by_server=1

Finally, I re-ran my backup job with options set for Verify and Encryption successfully. Verify step did not fail nor prompted to open/close the master key.

The following simply worked as intended without needing to open/close the master key.

RESTORE VERIFYONLY FROM DISK = '\\FS1\SqlBackups\SQL01\SystemDbs\msdb_backup_2017_09_22_171915_6346240.bak' WITH FILE = 1, NOUNLOAD, NOREWIND;

Wohooo! Mission accomplished.

Cannot open Symmetric key by certificate as SQL user account

You need to have the control permission on the certificate:

GRANT CONTROL ON CERTIFICATE::myCert TO sqlUser;
GO

And grant references permission on the key:

GRANT REFERENCES ON SYMMETRIC KEY::some_Key_01 TO sqlUser;
GO

AzureDevOps - Azure SQL Server deployment Problems and Solution

Problem 1

##[error]System.Management.Automation.ParentContainsErrorRecordException: *** Deployment cannot continueAn error occurred during deployment plan generation.

Cause

Target platform of SQL server on which you are trying to deploy your dacpac is different than target platform of dacpac file.

Possible solutions

1. Change target platform in your dacpac project as same as target platform of SQL server.
   

2. Add /p:AllowIncompatiblePlatform=true argument as additional argument to WinRm SQL server task.

Problem 2

##[error]Publishing to database 'testdb' on server 'ajyatest.database.windows.net'. Initializing deployment (Start) Initializing deployment (Failed) *** Could not deploy package. Unable to connect to master or target server 'testdb'. You must have a user with the same password in master or target server 'testdb'.

Cause

Wrong username or password to SQL server.

Possible solutions

Check if username and password are right for SQL server.
[You can verify username password using SSMS]

Problem 3

##[error]System.Management.Automation.RuntimeException: No resource found with serverName ajyatest1, serverType Microsoft.Sql/servers in subscription <SubscriptionId>. Specify the correct serverName/serverType and try again.

Cause

Wrong SQL server name is wrong.

Possible solutions

Check if SQL server with same name exist on Azure Portal.

Problem 4

Server name 'ajyatest1' is not in the right format. Use FQDN format like 'yyy.database.windows.net'

Cause

SQL server as input in not being provided as FQDN.

Possible solutions

As mentioned in error itself provide SQL server in format 'yyy.database.windows.net'

Problem 5

##[error]System.Net.WebException: The remote server returned an error: (404) Not Found. 

Cause

Check for endpoint URL for creating firewall rules. Verify if URL for endpoint is current.

Possible solutions

Check what’s wrong with endpoint. How user have created endpoint.

Problem 6

##[error]Start IP address of firewall rule cannot exceed End IP address.

Cause

End IP is smaller than Start IP for creating firewall rule.

Possible solutions

IP address for start IP should be smaller than end IP address.

Problem 7

##[error]Invalid value provided for parameter: startIpAddress

Cause

IP address is not in right format.

Possible solutions

IP address for start IP should be smaller than end IP address.

Problem 8

##[error] Named pipes provider, error: 40 – could not open a connect to SQL. 
Possible suggestions

Suggestions

For onPrem SQL server
1. SQL server name should be in right format server\instance.
2. Verify connection string.
3. Check if Named Pipes (NP) is being enabled on the SQL instance.
4. Is Remote connection enabled.

  For Azure SQL server 

1. It could cause as per application throttled.
2. Try to give azure SQL server name with port 1433. E.g. servername.database.windows.net,1433;

Problem 9

How to configure timeout for SQLPackage.exe ?

Solution

/p:CommandTimeout=1200  /TargetTimeout: 1200 

Problem 10

Does Azure SQL Database deployment with AAD integrated authentication supported on hosted agent?

Solution

No, AAD integrated authentication tries to login to SQL server with same user with which Agent is running. Since hosted agent user won't have permission on the SQL server because of security reasons.Hence it will fail. In other words, AAD integrated authencation is not supported for Hosted agent. Thought you can use Active Directory - Password authentication.

Problem 11

Azure SQL Database Deployment - Imported Invoke-Sqlcmd doesn't support connectionString

Solution
Imported Invoke-Sqlcmd doesn't support connectionString. Install SQLServer PS module.

Problem 12

SqlPackage.exe argument /p:BackupDatabaseBeforeChanges=true fails on Azure

Solution

/p:BackupDatabaseBeforeChanges=true options is for on-premise databases. That option doesn’t apply to Azure since the backup system is completely different.

Problem 13

Azure SQL Publish - SQL Script File - Not able to pass secret value as Variable

Solution

SQL script file supports secret variables. Make sure you are passing arguments for variables as follow.

Invoke-Sqlcmd -ServerInstance "test.database.windows.net" -Database "Testdb" -Username "test"  -Password ******  -Inputfile "C:\test.sql" -variable "MYVAR1='$(variablevalue1)'", "MYVAR2='String2'" -ConnectionTimeout 120

Debug tools and suggestions

Documentation for SQLPackage.exe arguments

SQL Package arguments

Configure diagnostics logs for SQLPackage.exe

/diagnostics:true

Get debug logs from the task

Set release definition variable system.debug with value true

SqlCMD arguments

SqlCmd arguments

Compare SqlCmd and Invoke-SqlCmd arguments

SqlCmd vs Invoke-SqlCmd arguments

Help with Service Broker error message

The error refers to a user in the database hosting your Service Broker service (select name from sys.database_principals where principal_id = 5).

In addition to exporting a certificate to the target server, you also need to import target server's certificate, associate it with a user and create a remote service binding to tell Service Broker which local user represents the remote service. The following 2 articles should help you: Service Broker Dialog Security and Securing a dialog with certificates.

---

Related Topics