Ryfujk

how to write formula in word in latex

Professor being mistaken for a grad student

How to use deus ex machina safely?

Instead of Universal Basic Income, why not Universal Basic NEEDS?

What approach do we need to follow for projects without a test environment?

Employee lack of ownership

Why one should not leave fingerprints on bulbs and plugs?

Why is the President allowed to veto a cancellation of emergency powers?

Why doesn't the EU now just force the UK to choose between referendum and no-deal?

Why do passenger jet manufacturers design their planes with stall prevention systems?

Did Ender ever learn that he killed Stilson and/or Bonzo?

In a future war, an old lady is trying to raise a boy but one of the weapons has made everyone deaf

If I can solve Sudoku can I solve Travelling Salesman Problem(TSP)? If yes, how?

Are ETF trackers fundamentally better than individual stocks?

How to explain that I do not want to visit a country due to personal safety concern?

Gravity magic - How does it work?

What's the meaning of “spike” in the context of “adrenaline spike”?

Is it possible to upcast ritual spells?

My Graph Theory Students

Look at your watch and tell me what time is it. vs Look at your watch and tell me what time it is

How do anti-virus programs start at Windows boot?

Why doesn't using two cd commands in bash script execute the second command?

Are there other languages, besides English, where the indefinite (or definite) article varies based on sound?

What did Alexander Pope mean by "Expletives their feeble Aid do join"?

Do I need to back up the SQL Server Encryption Password and Symmetric Key?

SQL- Encryption DatabaseWhat does CONTROL mean in the context of the CertificateWhat happenes to Data when Certificated expires in SQL Server 2005Backup security: Why same keys to backup and restore?SQL Server encryption/decryptionSQL Server Column Level Encryption - Rotating KeysSQL server EncryptionSQL Server encryption - rotate keys for PCI complianceDoes MS SQL 2005 encryption protect data in the case of physical server access?Sharing keys and certificates between SQL Server 2014 server for encrypted backup and restorationHow can encrypting the key increase security level in MariaDB data at rest encryption?

3

I'm trying to setup SQL Server to use Cell-Level Encryption on some sensitive data. It seems straightforward per this example on the MSDN:

1. Create the Master Key with a strong password.


2. Create a Certificate.


3. Create a Symmetric Key with the Certificate.


4. Encrypt data as needed by using the EncryptByKey function like this: EncryptByKey(Key_GUID('SensitiveData_Key_01'), MySensitiveDataColumn)
   


5. Decrypt data as needed by using the DecryptByKey function like this: CONVERT(varchar, DecryptByKeyAutoCert(cert_ID('MyCertName'), NULL, MySensitiveDataColumn))
   

So given the above usage, let's say my server dies. I need to reinstall SQL Server on a completely new Windows machine (or VM), and restore my database from backup. If I restore the database backup will the encryption/decryption continue to work correctly?

If not, what data do I need to save/backup in order for me to be able to recover my data in the case of a catastrophic failure?

Judging from the diagrams on this other MSDN article about the Encryption Hierarchy, my guess is that I need to backup some or all of the following:

1. Password for the Master Key


2. Master Key?


3. Certificate?


4. Symmetrical Key?

sql-server sql-server-2008 certificate encryption keys

share|improve this question

asked Feb 2 '12 at 16:30

John BubriskiJohn Bubriski

13119

bumped to the homepage by Community♦ 13 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 | 

3

I'm trying to setup SQL Server to use Cell-Level Encryption on some sensitive data. It seems straightforward per this example on the MSDN:

1. Create the Master Key with a strong password.


2. Create a Certificate.


3. Create a Symmetric Key with the Certificate.


4. Encrypt data as needed by using the EncryptByKey function like this: EncryptByKey(Key_GUID('SensitiveData_Key_01'), MySensitiveDataColumn)
   


5. Decrypt data as needed by using the DecryptByKey function like this: CONVERT(varchar, DecryptByKeyAutoCert(cert_ID('MyCertName'), NULL, MySensitiveDataColumn))
   

So given the above usage, let's say my server dies. I need to reinstall SQL Server on a completely new Windows machine (or VM), and restore my database from backup. If I restore the database backup will the encryption/decryption continue to work correctly?

If not, what data do I need to save/backup in order for me to be able to recover my data in the case of a catastrophic failure?

Judging from the diagrams on this other MSDN article about the Encryption Hierarchy, my guess is that I need to backup some or all of the following:

1. Password for the Master Key


2. Master Key?


3. Certificate?


4. Symmetrical Key?

sql-server sql-server-2008 certificate encryption keys

share|improve this question

asked Feb 2 '12 at 16:30

John BubriskiJohn Bubriski

13119

bumped to the homepage by Community♦ 13 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 | 

I'm trying to setup SQL Server to use Cell-Level Encryption on some sensitive data. It seems straightforward per this example on the MSDN:

1. Create the Master Key with a strong password.


2. Create a Certificate.


3. Create a Symmetric Key with the Certificate.


4. Encrypt data as needed by using the EncryptByKey function like this: EncryptByKey(Key_GUID('SensitiveData_Key_01'), MySensitiveDataColumn)
   


5. Decrypt data as needed by using the DecryptByKey function like this: CONVERT(varchar, DecryptByKeyAutoCert(cert_ID('MyCertName'), NULL, MySensitiveDataColumn))
   

So given the above usage, let's say my server dies. I need to reinstall SQL Server on a completely new Windows machine (or VM), and restore my database from backup. If I restore the database backup will the encryption/decryption continue to work correctly?

If not, what data do I need to save/backup in order for me to be able to recover my data in the case of a catastrophic failure?

Judging from the diagrams on this other MSDN article about the Encryption Hierarchy, my guess is that I need to backup some or all of the following:

1. Password for the Master Key


2. Master Key?


3. Certificate?


4. Symmetrical Key?

sql-server sql-server-2008 certificate encryption keys

share|improve this question

asked Feb 2 '12 at 16:30

John BubriskiJohn Bubriski

13119

share|improve this question

asked Feb 2 '12 at 16:30

John BubriskiJohn Bubriski

13119

share|improve this question

asked Feb 2 '12 at 16:30

John BubriskiJohn Bubriski

13119

asked Feb 2 '12 at 16:30

John BubriskiJohn Bubriski

13119

asked Feb 2 '12 at 16:30

John BubriskiJohn Bubriski

13119

2 Answers
2

active

oldest

votes

0

After doing some more research I found this article on "Cloning" Symmetric Keys.

You can instruct the CREATE SYMMETRIC KEY function to generate the Symmetric Key using a KEY_SOURCE and IDENTITY_VALUE. You can regenerate the same key later on by passing the same values for the KEY_SOURCE and IDENTITY_VALUE.

Here is a quick example:

CREATE CERTIFICATE CreditCards

   WITH SUBJECT = 'Customer Credit Card Numbers';

GO



CREATE SYMMETRIC KEY CreditCards_Key_01

WITH KEY_SOURCE = 'A pass phrase from which to derive the key.',

    IDENTITY_VALUE = 'An identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key',

    ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE CreditCards;

GO



OPEN SYMMETRIC KEY CreditCards_Key_01

   DECRYPTION BY CERTIFICATE CreditCards



UPDATE MyTable

SET EncryptedCreditCardNumber = EncryptByKey(Key_GUID('CreditCards_Key_01'), CreditCardNumber);

GO

To quote Michael Coles from his article

"For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default."

Not sure why they didn't do it that way, or at least highlight that in the example they have out there on the MSDN!

share|improve this answer

answered Feb 2 '12 at 20:06

John BubriskiJohn Bubriski

13119

add a comment | 

0

You should simulate your proposed disaster and confirm you can recover on a test machine. Whatever you need should become apparent pretty quickly during the restore process.

share|improve this answer

answered Feb 15 '12 at 19:58

mike42mike42

717

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "2"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});


}
});

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f356237%2fdo-i-need-to-back-up-the-sql-server-encryption-password-and-symmetric-key%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

After doing some more research I found this article on "Cloning" Symmetric Keys.

You can instruct the CREATE SYMMETRIC KEY function to generate the Symmetric Key using a KEY_SOURCE and IDENTITY_VALUE. You can regenerate the same key later on by passing the same values for the KEY_SOURCE and IDENTITY_VALUE.

Here is a quick example:

CREATE CERTIFICATE CreditCards

   WITH SUBJECT = 'Customer Credit Card Numbers';

GO



CREATE SYMMETRIC KEY CreditCards_Key_01

WITH KEY_SOURCE = 'A pass phrase from which to derive the key.',

    IDENTITY_VALUE = 'An identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key',

    ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE CreditCards;

GO



OPEN SYMMETRIC KEY CreditCards_Key_01

   DECRYPTION BY CERTIFICATE CreditCards



UPDATE MyTable

SET EncryptedCreditCardNumber = EncryptByKey(Key_GUID('CreditCards_Key_01'), CreditCardNumber);

GO

To quote Michael Coles from his article

"For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default."

Not sure why they didn't do it that way, or at least highlight that in the example they have out there on the MSDN!

share|improve this answer

answered Feb 2 '12 at 20:06

John BubriskiJohn Bubriski

13119

add a comment | 

0

After doing some more research I found this article on "Cloning" Symmetric Keys.

You can instruct the CREATE SYMMETRIC KEY function to generate the Symmetric Key using a KEY_SOURCE and IDENTITY_VALUE. You can regenerate the same key later on by passing the same values for the KEY_SOURCE and IDENTITY_VALUE.

Here is a quick example:

CREATE CERTIFICATE CreditCards

   WITH SUBJECT = 'Customer Credit Card Numbers';

GO



CREATE SYMMETRIC KEY CreditCards_Key_01

WITH KEY_SOURCE = 'A pass phrase from which to derive the key.',

    IDENTITY_VALUE = 'An identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key',

    ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE CreditCards;

GO



OPEN SYMMETRIC KEY CreditCards_Key_01

   DECRYPTION BY CERTIFICATE CreditCards



UPDATE MyTable

SET EncryptedCreditCardNumber = EncryptByKey(Key_GUID('CreditCards_Key_01'), CreditCardNumber);

GO

To quote Michael Coles from his article

"For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default."

Not sure why they didn't do it that way, or at least highlight that in the example they have out there on the MSDN!

share|improve this answer

answered Feb 2 '12 at 20:06

John BubriskiJohn Bubriski

13119

add a comment | 

After doing some more research I found this article on "Cloning" Symmetric Keys.

You can instruct the CREATE SYMMETRIC KEY function to generate the Symmetric Key using a KEY_SOURCE and IDENTITY_VALUE. You can regenerate the same key later on by passing the same values for the KEY_SOURCE and IDENTITY_VALUE.

Here is a quick example:

CREATE CERTIFICATE CreditCards

   WITH SUBJECT = 'Customer Credit Card Numbers';

GO



CREATE SYMMETRIC KEY CreditCards_Key_01

WITH KEY_SOURCE = 'A pass phrase from which to derive the key.',

    IDENTITY_VALUE = 'An identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key',

    ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE CreditCards;

GO



OPEN SYMMETRIC KEY CreditCards_Key_01

   DECRYPTION BY CERTIFICATE CreditCards



UPDATE MyTable

SET EncryptedCreditCardNumber = EncryptByKey(Key_GUID('CreditCards_Key_01'), CreditCardNumber);

GO

To quote Michael Coles from his article

"For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default."

Not sure why they didn't do it that way, or at least highlight that in the example they have out there on the MSDN!

share|improve this answer

answered Feb 2 '12 at 20:06

John BubriskiJohn Bubriski

13119

CREATE CERTIFICATE CreditCards

   WITH SUBJECT = 'Customer Credit Card Numbers';

GO



CREATE SYMMETRIC KEY CreditCards_Key_01

WITH KEY_SOURCE = 'A pass phrase from which to derive the key.',

    IDENTITY_VALUE = 'An identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key',

    ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE CreditCards;

GO



OPEN SYMMETRIC KEY CreditCards_Key_01

   DECRYPTION BY CERTIFICATE CreditCards



UPDATE MyTable

SET EncryptedCreditCardNumber = EncryptByKey(Key_GUID('CreditCards_Key_01'), CreditCardNumber);

GO

share|improve this answer

answered Feb 2 '12 at 20:06

John BubriskiJohn Bubriski

13119

answered Feb 2 '12 at 20:06

John BubriskiJohn Bubriski

13119

answered Feb 2 '12 at 20:06

John BubriskiJohn Bubriski

13119

0

You should simulate your proposed disaster and confirm you can recover on a test machine. Whatever you need should become apparent pretty quickly during the restore process.

share|improve this answer

answered Feb 15 '12 at 19:58

mike42mike42

717

add a comment | 

0

You should simulate your proposed disaster and confirm you can recover on a test machine. Whatever you need should become apparent pretty quickly during the restore process.

share|improve this answer

answered Feb 15 '12 at 19:58

mike42mike42

717

add a comment | 

You should simulate your proposed disaster and confirm you can recover on a test machine. Whatever you need should become apparent pretty quickly during the restore process.

share|improve this answer

answered Feb 15 '12 at 19:58

mike42mike42

717

share|improve this answer

answered Feb 15 '12 at 19:58

mike42mike42

717

answered Feb 15 '12 at 19:58

mike42mike42

717

answered Feb 15 '12 at 19:58

mike42mike42

717