none
EncryptByPassPhrase and SQL Server Profiler

    Pregunta

  • Hello,

    My client wants to encrypt data using sql server 2005 encryption mechanism. But one of the core requirements is that the DBA should not be able to decrypt my data on the SQL Server.

    I have opted for the EncryptByPassPhrase option. But here is my problem:

    I call the encryptByPassPhrase function within my stored procedures and I pass in the pass phrase as a parameter in Stored Proc call from the client intranet application.

    The problem is that one can clearly see the passphrase in the Profiler when I call my stored procedures.

    I did a bunch of research and found out that if I added a commented out "sp_password" command in my stored procedure, then the stored procedure call gets masked from the profiler. This seem to have been true with SQL Server 2000 but is not true for SQL Server 2005.

    Does anyone know of a method to mark a stored procedure -- or a parameter to a stored procedure -- as masked so that either the call or the parameter value are masked ?

    Thank You,

    Tarin

    jueves, 17 de agosto de 2006 6:18

Respuestas

  •    An alternative could be using a symmetric key protected by a password, as long as your application generates the CREATE SYMMETRIC KEY and OPEN SYMMETRIC KEY statements directly instead of calling them inside a SP (otherwise the password will still be passed as a parameter, and will be in clear in the profiler).

     

    -- Profiler will display

    -- --*CREATE SYMMETRIC KEY-----------------------------------------------------------------------

    CREATE SYMMETRIC KEY key01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD = '&0m3 p4xw0Rd'

    go

     

    CREATE TABLE t_demo( data varbinary(100) )

    go

     

    -- Profiler:

    -- --*OPEN SYMMETRIC KEY-------------------------------------------

    OPEN SYMMETRIC KEY key01 DECRYPTION BY PASSWORD = '&0m3 p4xw0Rd'

    go

     

    -- The password is not needed naymore,

    -- The key will remain open in this session until CLOSE SYMMETRIC KEY is called

    INSERT INTO t_demo VALUES( EncryptByKey( key_guid('key01'), 'My Secret' ))

    go

     

    SELECT convert(varchar(100), DecryptByKey( data )) as 'plaintext' FROM t_demo

    go

     

    CLOSE SYMMETRIC KEY key01

    Go

     

      The masking of the profiler by adding “sp_password” was actually a bug in SQL Server 2000. I have opened a suggestion to add a mechanism to mark modules as sensitive in order to mask them from the profiler.

     

      Thanks a lot for your feedback,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

     

    jueves, 17 de agosto de 2006 17:25
    Moderador

Todas las respuestas

  •    An alternative could be using a symmetric key protected by a password, as long as your application generates the CREATE SYMMETRIC KEY and OPEN SYMMETRIC KEY statements directly instead of calling them inside a SP (otherwise the password will still be passed as a parameter, and will be in clear in the profiler).

     

    -- Profiler will display

    -- --*CREATE SYMMETRIC KEY-----------------------------------------------------------------------

    CREATE SYMMETRIC KEY key01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD = '&0m3 p4xw0Rd'

    go

     

    CREATE TABLE t_demo( data varbinary(100) )

    go

     

    -- Profiler:

    -- --*OPEN SYMMETRIC KEY-------------------------------------------

    OPEN SYMMETRIC KEY key01 DECRYPTION BY PASSWORD = '&0m3 p4xw0Rd'

    go

     

    -- The password is not needed naymore,

    -- The key will remain open in this session until CLOSE SYMMETRIC KEY is called

    INSERT INTO t_demo VALUES( EncryptByKey( key_guid('key01'), 'My Secret' ))

    go

     

    SELECT convert(varchar(100), DecryptByKey( data )) as 'plaintext' FROM t_demo

    go

     

    CLOSE SYMMETRIC KEY key01

    Go

     

      The masking of the profiler by adding “sp_password” was actually a bug in SQL Server 2000. I have opened a suggestion to add a mechanism to mark modules as sensitive in order to mask them from the profiler.

     

      Thanks a lot for your feedback,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

     

    jueves, 17 de agosto de 2006 17:25
    Moderador
  • Am trying to implement Encryption in our application in order to meet the HIPAA compliance using SQL Server 2008. One of the regulation states that the key encrypting the data cannot reside on the database. That leaves me with the only option of using EncryptByPassPhrase. I have few questions regarding this:

    1. In one of the blogs i read EncryptByPassPhrase internally creates a symmetric key using TripleDES Algorithm to encrypt the data. (http://www.pluralsight-training.net/community/blogs/dan/archive/2006/04/09/21375.aspx?CommentPosted=true#commentmessage) Does SQL Server cache this generated key or keep it in memory beyond the current transaction boundary ?

    2. FIPS Compliance says that Triple DES (2 Key) will no longer be compliant from Dec 2010 onwards. Would you have any idea what Algorithm Microsoft plans to use for EncryptByPassPhrase behind the scenes going forward ?

    3. Do i have any alternative option other than EncryptByPassPhrase where the key wont be stored on the server ?

    4. I plan to use the context_info to send the password to the stored procedure. Will that be visible during profiling ? 

    jueves, 22 de julio de 2010 17:01
  • Will it help to encrypt the stored procedure?
    lunes, 05 de marzo de 2012 22:16