October 31, 2011 at 6:03 am
Hi,
We are planning on upgrading a SQL 2000 database to SQL 2008 R2 shortly with the new database being house on a windows clustered machines with a central filestore between them.
As part of the upgrade we are going to encrypt a column on the database using keys.
Our code is (approximately):
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'password'
CREATE CERTIFICATE OurCert
WITH SUBJECT = 'Our Encryption Key';
CREATE SYMMETRIC KEY OurSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE OurCert;
This works fine, but we are unsure as to how this would work if the windows cluster switched over. We thought the keys were tied to the machine, which could create problems.
Does anyone have any experience here to help?
Many thanks,
Graham
October 31, 2011 at 6:57 am
GRussell31 (10/31/2011)
Hi,We are planning on upgrading a SQL 2000 database to SQL 2008 R2 shortly with the new database being house on a windows clustered machines with a central filestore between them.
As part of the upgrade we are going to encrypt a column on the database using keys.
Our code is (approximately):
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'password'
CREATE CERTIFICATE OurCert
WITH SUBJECT = 'Our Encryption Key';
CREATE SYMMETRIC KEY OurSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE OurCert;
This works fine, but we are unsure as to how this would work if the windows cluster switched over. We thought the keys were tied to the machine, which could create problems.
Does anyone have any experience here to help?
Many thanks,
Graham
the database master key when created will be encrypted by using the service master key, this avoids the need to open the master key first. If you "Drop encryption by service master key" you need to explicitly open the master key first before using.
The certificate encryption uses the database master key where possible unless encryption by password is specified.
The machine names shouldn't have any effect, the virtual networkname is the clustered instance identifier and is shared by the cluster nodes. The service master key is the one you need to watch. Change the account that the SQL instance services run under and your service master key changes 😉
You can find more info on all this in Books Online
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply