July 6, 2010 at 4:48 pm
Hi,
I have encrypted a few column on my server and when i run the decrypt script the data gets displayed as intended. When i perform a backup and perform a restore and run the same decrypt stmnt i get the following error okn my local machine. Could some one please tell me what am i missing.
Msg 15581, Level 16, State 3, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.
--Create DB Master Key (SymmetricKey)
use Subscriber
go
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '@bxw+Z@M3$mHD6*$'
GO
--Create EncryptionCertificate
USE Subscriber
GO
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'MyEncryption'
GO
--The symmetric key can be encrypted by using various options such as certificate, password, symmetric key, and asymmetric key.
--A number of different algorithms can be employed for encrypting key.
--The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.
USE Subscriber
GO
CREATE SYMMETRIC KEY MySymmetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate
GO
-- Encrypt Data using Key and Certificate
--Add Columns which will hold the encrypted data in binary
USE Subscriber
GO
ALTER TABLE Table1
ADD EncryptPassword VARBINARY(256),EncryptLoginId VARBINARY(256),EncryptNumber VARBINARY(256)
GO
--Update binary column with encrypted data created by certificate and key
USE Subscriber
GO
OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION
BY CERTIFICATE MyCertificate
UPDATE Table1
SET EncryptPassword = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'),Password),
EncryptLoginId = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'),LoginId),
EncryptNumber = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'),Number)
GO
USE Database
GO
ALTER TABLE Table1
DROP COLUMN PASSWORD,loginid,number
GO
GO
USE Database
GO
SP_RENAME 'Table1.ENCRYPTPASSWORD','Password','COLUMN'
go
SP_RENAME 'Table1.ENCRYPTLOGINID','LoginId','COLUMN'
go
SP_RENAME 'Table1.ENCRYPTNumber','Number','COLUMN'
GO
--Decrypt the data of the SecondCol
USE Database
GO
OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION
BY CERTIFICATE MyCertificate
SELECT subscriberaccountid,FinancialInstitutionId,CONVERT(nvarchar(64),DECRYPTBYKEY(LoginId)) AS LoginId,CONVERT(nvarchar(64),DECRYPTBYKEY(Password)) AS 'PassWord',CONVERT(nvarchar(30),DECRYPTBYKEY(Number)) AS Number
FROM Table1
GO
July 7, 2010 at 8:13 am
backup and restore the service master key also.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply