Post reply

Encrypting Advice

  • September 21, 2011 at 10:45 am

    #257766

    Hi,

    I'm requesting advice

    I'm testing sql server encrypting features, and some doubts I have now

    1.) Is possible to use a Stored Procedure to create a Symmetric Key ? so, I could later use this key to update or include records in a table to cipher one column.

    Scenario that I'm dreaming:

    By user request, access to some sensitive data must be protected, user must manage his own keys.

    User have access to this data via reporting services, nobody even dba should have access to this data.

    I'm thinking in a stored procedure that request the user password, create a symmetric key and this key should be used when query this data.

    Is this possible ? Thanks for your ideas....an if possible sample code...

    Thanks for your advice

  • September 21, 2011 at 10:59 am

    #1384678

    Yes, but it requires one of the following:

    - All users have the ability to CREATE SYMMETRIC KEY in the database.

    - You create a stored procedure with an EXECUTE AS clause that executes as a user with the ability to create symmetric keys (and then does an ALTER AUTHORIZATION on the key to give it to the user)

    - A third option is to use application based encryption. If your DBAs are also members of the local administrators group on the SQL Server, this is the only way to prevent the DBAs from ever getting to the data. Otherwise they could use a debugger at any point to get the password or decrypted data.

    K. Brian Kelley
    @kbriankelley

  • September 21, 2011 at 12:44 pm

    #1384741

    Hi,

    Good Advice to have in mind...Thanks.. in this moment I'm looking the sql server side options, which I think is better for us by now.

    I'm writing the following code, when I execute this procedure, PASSWORD ENCRYPTION is taking @Ciphertext, literally, and not the variable passed to it. If I ommit both ticks (') then I have an 'Incorrect Synxtax near @Ciphertext'

    Today, I'm blind and there is something that I could not fix for myself.

    What I'm doing wrong ?

    Thanks Again

    CREATE PROCEDURE [dbo].[Testsymmkey] @Ciphertext char(20)

    AS

    BEGIN

    SET NOCOUNT ON;

    CREATE SYMMETRIC KEY User2SymKeyPwd

    WITH ALGORITHM = TRIPLE_DES

    ENCRYPTION BY PASSWORD = '@Ciphertext'

    END

  • September 21, 2011 at 12:56 pm

    #1384746

    the constructor allows only literal strings, not a variable, so

    ENCRYPTION BY PASSWORD =@var fails,

    ENCRYPTION BY PASSWORD ='static string' is good

    so i think you'll have to switch to dynamic SQL;

    logically, wouldn't your example fail on the second call, since the key User2SymKeyPwd would already exist?

    i'm just guessing, this is untested, but somthing like this might be better;

    i didn't consider the EXECUTE AS issues either.

    CREATE PROCEDURE [dbo].[Testsymmkey] @username varchar(100), @Ciphertext char(20)

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @cmd varchar(2000)

    IF NOT EXISTS(SELECT name from sys.symmetric_keys WHERE name = @username + 'SymKeyPwd')

    BEGIN

    SET @cmd = 'CREATE SYMMETRIC KEY ' + @username + 'SymKeyPwd

    WITH ALGORITHM = TRIPLE_DES

    ENCRYPTION BY PASSWORD = ''' + @Ciphertext + ''' '

    END --IF

    END --PROC

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 21, 2011 at 1:48 pm

    #1384775

    Lowell,

    Thanks a lot... :w00t:

    Thanks again

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply