Enabling TDE without breaking existing mirrorDatabase Mirroring with TDECan I send a string over TCP using...
Can a Tiny Servant be used as a messenger?
How can I be pwned if I'm not registered on the compromised site?
What's the best tool for cutting holes into duct work?
How do you make a gun that shoots melee weapons and/or swords?
Giving a talk in my old university, how prominently should I tell students my salary?
Split a number into equal parts given the number of parts
Is there a way to find out the age of climbing ropes?
Convert an array of objects to array of the objects' values
Are small insurances worth it
Is there a math equivalent to the conditional ternary operator?
“I had a flat in the centre of town, but I didn’t like living there, so …”
If nine coins are tossed, what is the probability that the number of heads is even?
What is brightness?
How does insurance birth control work in the United States?
Specific Chinese carabiner QA?
Where does the proton come in the reduction of NAD?
Deal the cards to the players
Should I use HTTPS on a domain that will only be used for redirection?
Can a space-faring robot still function over a billion years?
Caulking a corner instead of taping with joint compound?
Create chunks from an array
Has a sovereign Communist government ever run, and conceded loss, on a fair election?
I've given my players a lot of magic items. Is it reasonable for me to give them harder encounters?
Was it really inappropriate to write a pull request for the company I interviewed with?
Enabling TDE without breaking existing mirror
Database Mirroring with TDECan I send a string over TCP using T-SQL?SQL Server Database Mirroring : strange client application behavior upon failoverHow do I shrink the physical Transaction Log file when it's the principal in a mirror?Mirroring in SQL Server 2012Please help me resolve this SQL mirroring error which comes when I ‘Start’ the mirror. The network address can not be reached or does not existTruncate log file mirrored databaseSQL Server 2008 R2 Mirroring issuesSQL Server 2016 : mirroring between 2 VMsEnabling TDE on PRODUCTION Servers - Guidance , Suggestions and Testing TDE ways
I've been trying to enable TDE in a mirrored SQL Server 2008 R2 configuration. Enabling TDE is not so difficult, though when I enable TDE on the principal, the mirror database goes in to a suspended state.
There are a few good articles here and here as well as many others, but they all demonstrate enabling TDE on a mirrored configuration with importing the database in the mirror before enabling encryption. I've not yet found a way to simply turn on TDE in an existing mirrored system.
I've gone so far as to export the service master key and service keys from the Principal and import them in the mirror. I've tried transact-sql'ing mirroring in the hopes that what I was seeing in the GUI was erroneous due to some advanced mirroring things going on, with no success.
Is enabling TDE across an existing mirrored SQL Server setup possible?
sql-server sql-server-2008-r2 mirroring transparent-data-encryption
edited Dec 22 '14 at 10:35
Paul White♦
52.9k14281457
migrated from stackoverflow.com Dec 16 '14 at 11:23
This question came from our site for professional and enthusiast programmers.
add a comment |
I've been trying to enable TDE in a mirrored SQL Server 2008 R2 configuration. Enabling TDE is not so difficult, though when I enable TDE on the principal, the mirror database goes in to a suspended state.
There are a few good articles here and here as well as many others, but they all demonstrate enabling TDE on a mirrored configuration with importing the database in the mirror before enabling encryption. I've not yet found a way to simply turn on TDE in an existing mirrored system.
I've gone so far as to export the service master key and service keys from the Principal and import them in the mirror. I've tried transact-sql'ing mirroring in the hopes that what I was seeing in the GUI was erroneous due to some advanced mirroring things going on, with no success.
Is enabling TDE across an existing mirrored SQL Server setup possible?
sql-server sql-server-2008-r2 mirroring transparent-data-encryption
edited Dec 22 '14 at 10:35
Paul White♦
52.9k14281457
migrated from stackoverflow.com Dec 16 '14 at 11:23
This question came from our site for professional and enthusiast programmers.
Was there any error when restoring the SMK?
– Sean Gallardy
Dec 25 '14 at 14:25
Did you also try the 2 steps mentioned by Richard Moulton in your second link? He said: For mirroring to work for me, the following extra two 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;
– RLF
Dec 25 '14 at 15:00
add a comment |
I've been trying to enable TDE in a mirrored SQL Server 2008 R2 configuration. Enabling TDE is not so difficult, though when I enable TDE on the principal, the mirror database goes in to a suspended state.
There are a few good articles here and here as well as many others, but they all demonstrate enabling TDE on a mirrored configuration with importing the database in the mirror before enabling encryption. I've not yet found a way to simply turn on TDE in an existing mirrored system.
I've gone so far as to export the service master key and service keys from the Principal and import them in the mirror. I've tried transact-sql'ing mirroring in the hopes that what I was seeing in the GUI was erroneous due to some advanced mirroring things going on, with no success.
Is enabling TDE across an existing mirrored SQL Server setup possible?
sql-server sql-server-2008-r2 mirroring transparent-data-encryption
edited Dec 22 '14 at 10:35
Paul White♦
52.9k14281457
I've been trying to enable TDE in a mirrored SQL Server 2008 R2 configuration. Enabling TDE is not so difficult, though when I enable TDE on the principal, the mirror database goes in to a suspended state.
There are a few good articles here and here as well as many others, but they all demonstrate enabling TDE on a mirrored configuration with importing the database in the mirror before enabling encryption. I've not yet found a way to simply turn on TDE in an existing mirrored system.
I've gone so far as to export the service master key and service keys from the Principal and import them in the mirror. I've tried transact-sql'ing mirroring in the hopes that what I was seeing in the GUI was erroneous due to some advanced mirroring things going on, with no success.
Is enabling TDE across an existing mirrored SQL Server setup possible?
sql-server sql-server-2008-r2 mirroring transparent-data-encryption
sql-server sql-server-2008-r2 mirroring transparent-data-encryption
edited Dec 22 '14 at 10:35
Paul White♦
52.9k14281457
edited Dec 22 '14 at 10:35
Paul White♦
52.9k14281457
edited Dec 22 '14 at 10:35
Paul White♦
52.9k14281457
edited Dec 22 '14 at 10:35
Paul White♦
52.9k14281457
edited Dec 22 '14 at 10:35
Paul White♦
52.9k14281457
52.9k14281457
asked Dec 15 '14 at 21:03
Dan Trainor
migrated from stackoverflow.com Dec 16 '14 at 11:23
This question came from our site for professional and enthusiast programmers.
migrated from stackoverflow.com Dec 16 '14 at 11:23
This question came from our site for professional and enthusiast programmers.
Was there any error when restoring the SMK?
– Sean Gallardy
Dec 25 '14 at 14:25
Did you also try the 2 steps mentioned by Richard Moulton in your second link? He said: For mirroring to work for me, the following extra two 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;
– RLF
Dec 25 '14 at 15:00
add a comment |
Was there any error when restoring the SMK?
– Sean Gallardy
Dec 25 '14 at 14:25
Did you also try the 2 steps mentioned by Richard Moulton in your second link? He said: For mirroring to work for me, the following extra two 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;
– RLF
Dec 25 '14 at 15:00
Was there any error when restoring the SMK?
– Sean Gallardy
Dec 25 '14 at 14:25
Was there any error when restoring the SMK?
– Sean Gallardy
Dec 25 '14 at 14:25
Did you also try the 2 steps mentioned by Richard Moulton in your second link? He said: For mirroring to work for me, the following extra two 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;– RLF
Dec 25 '14 at 15:00
Did you also try the 2 steps mentioned by Richard Moulton in your second link? He said: For mirroring to work for me, the following extra two 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;– RLF
Dec 25 '14 at 15:00
add a comment |
3 Answers
3
active
oldest
votes
From the looks of this article, you would probably have to break the mirror first, setup TDE, and then setup the mirror again. Not ideal, but it would work.
answered Dec 30 '14 at 19:31
ConnorConnor
31315
add a comment |
Actually, you can. I've tried this on SQL Server 2016 SP1, but I think it works for previous versions as well. The steps are described here . Remember that the mirror database will not show that encryption is enabled (but it is) until you do the fail over.
answered Feb 23 '18 at 22:05
Florin FloreaFlorin Florea
112
add a comment |
If we understand encryption hierarchy, we can easily deduce that how to enable TDB without any interruption for a DB with DB mirroring.
Step1. Check master key on both principle and mirroring servers.
Step2. On principle server, if there is master key, we do nothing on this step, if not, create master key.
Step3. On principle server, create certificate protected by the master key.
Step4. On principle server, backup certificate, and then copy the certificate to mirroring server.
Step5. On mirroring server, if there is not master key, create it.
Step6. On mirroring server, restore certificate.
Step7. On principle server, create database encryption key for DB that will be enabled TDE.
Step8. On principle server, set encryption on.
answered 15 mins ago
SandySandy
1
New contributor
Sandy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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%2f86287%2fenabling-tde-without-breaking-existing-mirror%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
From the looks of this article, you would probably have to break the mirror first, setup TDE, and then setup the mirror again. Not ideal, but it would work.
answered Dec 30 '14 at 19:31
ConnorConnor
31315
add a comment |
From the looks of this article, you would probably have to break the mirror first, setup TDE, and then setup the mirror again. Not ideal, but it would work.
answered Dec 30 '14 at 19:31
ConnorConnor
31315
add a comment |
From the looks of this article, you would probably have to break the mirror first, setup TDE, and then setup the mirror again. Not ideal, but it would work.
answered Dec 30 '14 at 19:31
ConnorConnor
31315
From the looks of this article, you would probably have to break the mirror first, setup TDE, and then setup the mirror again. Not ideal, but it would work.
answered Dec 30 '14 at 19:31
ConnorConnor
31315
answered Dec 30 '14 at 19:31
ConnorConnor
31315
answered Dec 30 '14 at 19:31
ConnorConnor
31315
answered Dec 30 '14 at 19:31
ConnorConnor
31315
31315
add a comment |
add a comment |
Actually, you can. I've tried this on SQL Server 2016 SP1, but I think it works for previous versions as well. The steps are described here . Remember that the mirror database will not show that encryption is enabled (but it is) until you do the fail over.
answered Feb 23 '18 at 22:05
Florin FloreaFlorin Florea
112
add a comment |
Actually, you can. I've tried this on SQL Server 2016 SP1, but I think it works for previous versions as well. The steps are described here . Remember that the mirror database will not show that encryption is enabled (but it is) until you do the fail over.
answered Feb 23 '18 at 22:05
Florin FloreaFlorin Florea
112
add a comment |
Actually, you can. I've tried this on SQL Server 2016 SP1, but I think it works for previous versions as well. The steps are described here . Remember that the mirror database will not show that encryption is enabled (but it is) until you do the fail over.
answered Feb 23 '18 at 22:05
Florin FloreaFlorin Florea
112
Actually, you can. I've tried this on SQL Server 2016 SP1, but I think it works for previous versions as well. The steps are described here . Remember that the mirror database will not show that encryption is enabled (but it is) until you do the fail over.
answered Feb 23 '18 at 22:05
Florin FloreaFlorin Florea
112
answered Feb 23 '18 at 22:05
Florin FloreaFlorin Florea
112
answered Feb 23 '18 at 22:05
Florin FloreaFlorin Florea
112
answered Feb 23 '18 at 22:05
Florin FloreaFlorin Florea
112
112
add a comment |
add a comment |
If we understand encryption hierarchy, we can easily deduce that how to enable TDB without any interruption for a DB with DB mirroring.
Step1. Check master key on both principle and mirroring servers.
Step2. On principle server, if there is master key, we do nothing on this step, if not, create master key.
Step3. On principle server, create certificate protected by the master key.
Step4. On principle server, backup certificate, and then copy the certificate to mirroring server.
Step5. On mirroring server, if there is not master key, create it.
Step6. On mirroring server, restore certificate.
Step7. On principle server, create database encryption key for DB that will be enabled TDE.
Step8. On principle server, set encryption on.
answered 15 mins ago
SandySandy
1
New contributor
Sandy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
If we understand encryption hierarchy, we can easily deduce that how to enable TDB without any interruption for a DB with DB mirroring.
Step1. Check master key on both principle and mirroring servers.
Step2. On principle server, if there is master key, we do nothing on this step, if not, create master key.
Step3. On principle server, create certificate protected by the master key.
Step4. On principle server, backup certificate, and then copy the certificate to mirroring server.
Step5. On mirroring server, if there is not master key, create it.
Step6. On mirroring server, restore certificate.
Step7. On principle server, create database encryption key for DB that will be enabled TDE.
Step8. On principle server, set encryption on.
answered 15 mins ago
SandySandy
1
New contributor
Sandy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
If we understand encryption hierarchy, we can easily deduce that how to enable TDB without any interruption for a DB with DB mirroring.
Step1. Check master key on both principle and mirroring servers.
Step2. On principle server, if there is master key, we do nothing on this step, if not, create master key.
Step3. On principle server, create certificate protected by the master key.
Step4. On principle server, backup certificate, and then copy the certificate to mirroring server.
Step5. On mirroring server, if there is not master key, create it.
Step6. On mirroring server, restore certificate.
Step7. On principle server, create database encryption key for DB that will be enabled TDE.
Step8. On principle server, set encryption on.
answered 15 mins ago
SandySandy
1
New contributor
Sandy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
If we understand encryption hierarchy, we can easily deduce that how to enable TDB without any interruption for a DB with DB mirroring.
Step1. Check master key on both principle and mirroring servers.
Step2. On principle server, if there is master key, we do nothing on this step, if not, create master key.
Step3. On principle server, create certificate protected by the master key.
Step4. On principle server, backup certificate, and then copy the certificate to mirroring server.
Step5. On mirroring server, if there is not master key, create it.
Step6. On mirroring server, restore certificate.
Step7. On principle server, create database encryption key for DB that will be enabled TDE.
Step8. On principle server, set encryption on.
answered 15 mins ago
SandySandy
1
New contributor
Sandy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 15 mins ago
SandySandy
1
New contributor
Sandy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 15 mins ago
SandySandy
1
answered 15 mins ago
SandySandy
1
1
New contributor
Sandy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Sandy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Sandy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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%2f86287%2fenabling-tde-without-breaking-existing-mirror%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
Was there any error when restoring the SMK?
– Sean Gallardy
Dec 25 '14 at 14:25
Did you also try the 2 steps mentioned by Richard Moulton in your second link? He said: For mirroring to work for me, the following extra two 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;– RLF
Dec 25 '14 at 15:00