If someone would try out my script below I'd really appreciate it. Whenever
 I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
 fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
 in the execution. All ideas are welcomed.
 TIA, ChrisR
 USE [AdventureWorks];
 GO
 IF NOT EXISTS
 (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
 CREATE MASTER KEY ENCRYPTION BY
 PASSWORD = 'vato'
 GO
 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
 CREATE CERTIFICATE HumanResources037
 WITH SUBJECT = 'Employee Social Security Numbers';
 GO
 CREATE SYMMETRIC KEY SSN_Key_01
 WITH ALGORITHM = DES
 ENCRYPTION BY CERTIFICATE HumanResources037;
 GO
 -- Create a column in which to store the encrypted data
 ALTER TABLE HumanResources.Employee
 ADD EncryptedNationalIDNumber varbinary(128);
 GO
 -- Open the symmetric key with which to encrypt the data
 OPEN SYMMETRIC KEY SSN_Key_01
 DECRYPTION BY CERTIFICATE HumanResources037;
 -- Encrypt the value in column NationalIDNumber with symmetric
 -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
 UPDATE HumanResources.Employee
 SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
 NationalIDNumber);
 GO
 -- Verify the encryption.
 -- First, open the symmetric key with which to decrypt the data
 OPEN SYMMETRIC KEY SSN_Key_01
 DECRYPTION BY CERTIFICATE HumanResources037;
 GO
 -- Now list the original ID, the encrypted ID, and the
 -- decrypted ciphertext. If the decryption worked, the original
 -- and the decrypted ID will match.
 create procedure getDecryptedIDNumber
 with exec as owner
 as
 SELECT NationalIDNumber, EncryptedNationalIDNumber
 AS "Encrypted ID Number",
 CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
 AS "Decrypted ID Number"
 FROM HumanResources.Employee;
 GO
 /*works for me, shows the decrypted data*/
 exec getDecryptedIDNumber
 USE [master]
 GO
 CREATE LOGIN [test] WITH PASSWORD=N'test',
 DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
 GO
 USE [AdventureWorks]
 GO
 CREATE USER [test] FOR LOGIN [test]
 GO
 use [AdventureWorks]
 GO
 GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
 GO
 GRANT IMPERSONATE ON USER:: dbo TO test;
 GO
 /*Now, open up a "file/new/DB Engine Query" and login with the test login*/
 exec as user = 'dbo'
 exec getDecryptedIDNumber
 /*This returns NULL values where it should show the decrypted data*/Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted
ID
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what
you
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
> > If someone would try out my script below I'd really appreciate it.
Whenever
> > I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite
the
> > fact that I use "with exec as owner" in the sproc and "exec as user ='dbo'"
> > in the execution. All ideas are welcomed.
> >
> > TIA, ChrisR
> >
> >
> > USE [AdventureWorks];
> > GO
> >
> > IF NOT EXISTS
> > (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> > CREATE MASTER KEY ENCRYPTION BY
> > PASSWORD => > 'vato'
> > GO
> >
> > OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> >
> > CREATE CERTIFICATE HumanResources037
> > WITH SUBJECT = 'Employee Social Security Numbers';
> > GO
> >
> > CREATE SYMMETRIC KEY SSN_Key_01
> > WITH ALGORITHM = DES
> > ENCRYPTION BY CERTIFICATE HumanResources037;
> > GO
> >
> > -- Create a column in which to store the encrypted data
> > ALTER TABLE HumanResources.Employee
> > ADD EncryptedNationalIDNumber varbinary(128);
> > GO
> >
> > -- Open the symmetric key with which to encrypt the data
> > OPEN SYMMETRIC KEY SSN_Key_01
> > DECRYPTION BY CERTIFICATE HumanResources037;
> >
> > -- Encrypt the value in column NationalIDNumber with symmetric
> > -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> > UPDATE HumanResources.Employee
> > SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> > NationalIDNumber);
> > GO
> >
> > -- Verify the encryption.
> > -- First, open the symmetric key with which to decrypt the data
> > OPEN SYMMETRIC KEY SSN_Key_01
> > DECRYPTION BY CERTIFICATE HumanResources037;
> > GO
> >
> > -- Now list the original ID, the encrypted ID, and the
> > -- decrypted ciphertext. If the decryption worked, the original
> > -- and the decrypted ID will match.
> >
> > create procedure getDecryptedIDNumber
> > with exec as owner
> > as
> > SELECT NationalIDNumber, EncryptedNationalIDNumber
> > AS "Encrypted ID Number",
> > CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> > AS "Decrypted ID Number"
> > FROM HumanResources.Employee;
> > GO
> >
> > /*works for me, shows the decrypted data*/
> >
> > exec getDecryptedIDNumber
> >
> > USE [master]
> > GO
> >
> > CREATE LOGIN [test] WITH PASSWORD=N'test',
> > DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
> > GO
> >
> > USE [AdventureWorks]
> > GO
> >
> > CREATE USER [test] FOR LOGIN [test]
> > GO
> >
> > use [AdventureWorks]
> > GO
> >
> > GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> > GO
> >
> > GRANT IMPERSONATE ON USER:: dbo TO test;
> > GO
> >
> > /*Now, open up a "file/new/DB Engine Query" and login with the test
login*/
> > exec as user = 'dbo'
> > exec getDecryptedIDNumber
> >
> > /*This returns NULL values where it should show the decrypted data*/
> >
> >
> >
Subscribe to:
Post Comments (Atom)
 
No comments:
Post a Comment