Making Session Keys of SQL instances Identical in SQL Availability Group
We recently added a master key encrypted database on a two-nodes SQL Server AlwaysOn Availability Group. Whenever there is a failover, the master key gets dropped and we have to manually add the master key back to the database on a new primary node.
I would think the databases is fully synchronized and there won’t be any loss of configuration after a failover. I talked to my vendor and they said during a failover, the client will be using the signed Master Key from the previous instance, on a database with a differently signed Master Key. They said that Session Keys are different between the SQL instances running as part of the group and that we need to make sure that the session keys are identical.
I am not sure of what to make of that:
- Is there anything such as session keys in HA group?
- If so, can I make them identical for each node (SQL instances) in the
cluster?
sql-server availability-groups encryption
edited Mar 16 '17 at 18:01
Shawn Melton
14.3k43782
asked Mar 16 '17 at 14:14
PolDBQPolDBQ
113110
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
We recently added a master key encrypted database on a two-nodes SQL Server AlwaysOn Availability Group. Whenever there is a failover, the master key gets dropped and we have to manually add the master key back to the database on a new primary node.
I would think the databases is fully synchronized and there won’t be any loss of configuration after a failover. I talked to my vendor and they said during a failover, the client will be using the signed Master Key from the previous instance, on a database with a differently signed Master Key. They said that Session Keys are different between the SQL instances running as part of the group and that we need to make sure that the session keys are identical.
I am not sure of what to make of that:
- Is there anything such as session keys in HA group?
- If so, can I make them identical for each node (SQL instances) in the
cluster?
sql-server availability-groups encryption
edited Mar 16 '17 at 18:01
Shawn Melton
14.3k43782
asked Mar 16 '17 at 14:14
PolDBQPolDBQ
113110
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
We recently added a master key encrypted database on a two-nodes SQL Server AlwaysOn Availability Group. Whenever there is a failover, the master key gets dropped and we have to manually add the master key back to the database on a new primary node.
I would think the databases is fully synchronized and there won’t be any loss of configuration after a failover. I talked to my vendor and they said during a failover, the client will be using the signed Master Key from the previous instance, on a database with a differently signed Master Key. They said that Session Keys are different between the SQL instances running as part of the group and that we need to make sure that the session keys are identical.
I am not sure of what to make of that:
- Is there anything such as session keys in HA group?
- If so, can I make them identical for each node (SQL instances) in the
cluster?
sql-server availability-groups encryption
edited Mar 16 '17 at 18:01
Shawn Melton
14.3k43782
asked Mar 16 '17 at 14:14
PolDBQPolDBQ
113110
We recently added a master key encrypted database on a two-nodes SQL Server AlwaysOn Availability Group. Whenever there is a failover, the master key gets dropped and we have to manually add the master key back to the database on a new primary node.
I would think the databases is fully synchronized and there won’t be any loss of configuration after a failover. I talked to my vendor and they said during a failover, the client will be using the signed Master Key from the previous instance, on a database with a differently signed Master Key. They said that Session Keys are different between the SQL instances running as part of the group and that we need to make sure that the session keys are identical.
I am not sure of what to make of that:
- Is there anything such as session keys in HA group?
- If so, can I make them identical for each node (SQL instances) in the
cluster?
sql-server availability-groups encryption
sql-server availability-groups encryption
edited Mar 16 '17 at 18:01
Shawn Melton
14.3k43782
asked Mar 16 '17 at 14:14
PolDBQPolDBQ
113110
edited Mar 16 '17 at 18:01
Shawn Melton
14.3k43782
asked Mar 16 '17 at 14:14
PolDBQPolDBQ
113110
edited Mar 16 '17 at 18:01
Shawn Melton
14.3k43782
edited Mar 16 '17 at 18:01
Shawn Melton
14.3k43782
edited Mar 16 '17 at 18:01
Shawn Melton
14.3k43782
14.3k43782
asked Mar 16 '17 at 14:14
PolDBQPolDBQ
113110
asked Mar 16 '17 at 14:14
PolDBQPolDBQ
113110
asked Mar 16 '17 at 14:14
PolDBQPolDBQ
113110
113110
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
When you restore a database that has encrypted data to a different instance, with a different Service Master Key (SMK), the data won't decrypt properly because of SQL Server's Encryption Hierarchy. The "restore" of the master key you mention is really decrpyting the data using the Database Master Key (DMK) password and then re-ecrypting the data with the new instance's SMK:
OPEN Master Key Decryption By Password = N'<redacted>'-- Password used when creating DMK
ALTER Master Key ADD encryption by Service Master KEY
There are a number of ways around having to do this after every failover, but the way I prefer in this scenario is to backup a SMK from the current primary node in your Availability Group (AG), and restore that to all other Instances in said AG so your SMKs match throughout. Once this is done, you may need to run the above statement against each db that's not sitting in a read-only state on any Secondary Replicas so that any encrypted data will be readable after any future listener failover events. The whole point of having an AG is the ability to perform a quick and transparent failover, so a shared SMK shouldn't be considered a risk in my opinion.
This article goes over this and walks you through things in much more detail, but take some time to set this up so you can take full advantage of Availability Groups.
answered Mar 16 '17 at 14:23
John EisbrenerJohn Eisbrener
5,12611340
I will give it a try and update.
– PolDBQ
Mar 16 '17 at 18:14
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f167354%2fmaking-session-keys-of-sql-instances-identical-in-sql-availability-group%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
When you restore a database that has encrypted data to a different instance, with a different Service Master Key (SMK), the data won't decrypt properly because of SQL Server's Encryption Hierarchy. The "restore" of the master key you mention is really decrpyting the data using the Database Master Key (DMK) password and then re-ecrypting the data with the new instance's SMK:
OPEN Master Key Decryption By Password = N'<redacted>'-- Password used when creating DMK
ALTER Master Key ADD encryption by Service Master KEY
There are a number of ways around having to do this after every failover, but the way I prefer in this scenario is to backup a SMK from the current primary node in your Availability Group (AG), and restore that to all other Instances in said AG so your SMKs match throughout. Once this is done, you may need to run the above statement against each db that's not sitting in a read-only state on any Secondary Replicas so that any encrypted data will be readable after any future listener failover events. The whole point of having an AG is the ability to perform a quick and transparent failover, so a shared SMK shouldn't be considered a risk in my opinion.
This article goes over this and walks you through things in much more detail, but take some time to set this up so you can take full advantage of Availability Groups.
answered Mar 16 '17 at 14:23
John EisbrenerJohn Eisbrener
5,12611340
I will give it a try and update.
– PolDBQ
Mar 16 '17 at 18:14
add a comment |
When you restore a database that has encrypted data to a different instance, with a different Service Master Key (SMK), the data won't decrypt properly because of SQL Server's Encryption Hierarchy. The "restore" of the master key you mention is really decrpyting the data using the Database Master Key (DMK) password and then re-ecrypting the data with the new instance's SMK:
OPEN Master Key Decryption By Password = N'<redacted>'-- Password used when creating DMK
ALTER Master Key ADD encryption by Service Master KEY
There are a number of ways around having to do this after every failover, but the way I prefer in this scenario is to backup a SMK from the current primary node in your Availability Group (AG), and restore that to all other Instances in said AG so your SMKs match throughout. Once this is done, you may need to run the above statement against each db that's not sitting in a read-only state on any Secondary Replicas so that any encrypted data will be readable after any future listener failover events. The whole point of having an AG is the ability to perform a quick and transparent failover, so a shared SMK shouldn't be considered a risk in my opinion.
This article goes over this and walks you through things in much more detail, but take some time to set this up so you can take full advantage of Availability Groups.
answered Mar 16 '17 at 14:23
John EisbrenerJohn Eisbrener
5,12611340
I will give it a try and update.
– PolDBQ
Mar 16 '17 at 18:14
add a comment |
When you restore a database that has encrypted data to a different instance, with a different Service Master Key (SMK), the data won't decrypt properly because of SQL Server's Encryption Hierarchy. The "restore" of the master key you mention is really decrpyting the data using the Database Master Key (DMK) password and then re-ecrypting the data with the new instance's SMK:
OPEN Master Key Decryption By Password = N'<redacted>'-- Password used when creating DMK
ALTER Master Key ADD encryption by Service Master KEY
There are a number of ways around having to do this after every failover, but the way I prefer in this scenario is to backup a SMK from the current primary node in your Availability Group (AG), and restore that to all other Instances in said AG so your SMKs match throughout. Once this is done, you may need to run the above statement against each db that's not sitting in a read-only state on any Secondary Replicas so that any encrypted data will be readable after any future listener failover events. The whole point of having an AG is the ability to perform a quick and transparent failover, so a shared SMK shouldn't be considered a risk in my opinion.
This article goes over this and walks you through things in much more detail, but take some time to set this up so you can take full advantage of Availability Groups.
answered Mar 16 '17 at 14:23
John EisbrenerJohn Eisbrener
5,12611340
When you restore a database that has encrypted data to a different instance, with a different Service Master Key (SMK), the data won't decrypt properly because of SQL Server's Encryption Hierarchy. The "restore" of the master key you mention is really decrpyting the data using the Database Master Key (DMK) password and then re-ecrypting the data with the new instance's SMK:
OPEN Master Key Decryption By Password = N'<redacted>'-- Password used when creating DMK
ALTER Master Key ADD encryption by Service Master KEY
There are a number of ways around having to do this after every failover, but the way I prefer in this scenario is to backup a SMK from the current primary node in your Availability Group (AG), and restore that to all other Instances in said AG so your SMKs match throughout. Once this is done, you may need to run the above statement against each db that's not sitting in a read-only state on any Secondary Replicas so that any encrypted data will be readable after any future listener failover events. The whole point of having an AG is the ability to perform a quick and transparent failover, so a shared SMK shouldn't be considered a risk in my opinion.
This article goes over this and walks you through things in much more detail, but take some time to set this up so you can take full advantage of Availability Groups.
answered Mar 16 '17 at 14:23
John EisbrenerJohn Eisbrener
5,12611340
answered Mar 16 '17 at 14:23
John EisbrenerJohn Eisbrener
5,12611340
answered Mar 16 '17 at 14:23
John EisbrenerJohn Eisbrener
5,12611340
answered Mar 16 '17 at 14:23
John EisbrenerJohn Eisbrener
5,12611340
5,12611340
I will give it a try and update.
– PolDBQ
Mar 16 '17 at 18:14
add a comment |
I will give it a try and update.
– PolDBQ
Mar 16 '17 at 18:14
I will give it a try and update.
– PolDBQ
Mar 16 '17 at 18:14
I will give it a try and update.
– PolDBQ
Mar 16 '17 at 18:14
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f167354%2fmaking-session-keys-of-sql-instances-identical-in-sql-availability-group%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
