Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    128

    Unanswered: Decrypt sproc returning NULL to non DBO.

    I'm still having issues with this despite my attempts to resolve. I even
    have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution,
    but still the encrypted data returns nulls when I exec as a user other than
    DBO. Below is precisely what I have done. All ideas are welcomed.

    TIA, ChrisR


    --If there is no master key, create one now

    IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD =
    '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
    r$$#1946kcj$n44ncjhdlj'
    GO

    CREATE CERTIFICATE HumanResources037
    WITH SUBJECT = 'Employee Social Security Numbers';
    GO

    CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE HumanResources037;
    GO

    USE [AdventureWorks];
    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.

    alter 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*/

  2. #2
    Join Date
    Nov 2004
    Posts
    128
    I have made some changes to the scripts, but the outcome is the same. Everything needed is below, so all ideas are welcomed. Also, please make sure to use these scripts, not the last ones.


    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*/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •