MS SQL Server - Encrypt sproc still returns NULL's to non DBO's.

Author

Encrypt sproc still returns NULL's to non DBO's.

ChrisR

2006-12-04, 7:12 pm

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 & #91;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 EncryptedNationalIDN
umber 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 EncryptedNationalIDN
umber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDN
umber = EncryptByKey(Key_GUI
D('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, EncryptedNationalIDN
umber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(Encrypt
edNationalIDNumber))

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=& #91;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].& #91;getDecryptedIDNu
mber] 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*/

John Bell

2006-12-06, 5:16 am

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, EncryptedNationalIDN
umber AS [Encrypted ID Number],
CONVERT(nvarchar, DecryptByKey(Encrypt
edNationalIDNumber))
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.../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 & #91;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 EncryptedNationalIDN
umber 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 EncryptedNationalIDN
umber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDN
umber = EncryptByKey(Key_GUI
D('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, EncryptedNationalIDN
umber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(Encrypt
edNationalIDNumber))

> 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=& #91;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].& #91;getDecryptedIDNu
mber] 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*/
>
>
>

ChrisR

2006-12-06, 7:12 pm

Good enough, thanks.

"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE- 2C930BF1FF04@microso
ft.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, EncryptedNationalIDN
umber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(Encrypt
edNationalIDNumber))
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.../13/512829.aspx will dop what
you[color=darkred]
> but signs the procedure instead.
>
>
> John
>
> "ChrisR" wrote:
>
Whenever[color=darkr
ed]
the[color=darkred]
'dbo'"[color=darkred]
CHECK_POLICY=OFF[col
or=darkred]
login*/[color=darkred]

Sponsored Links