I want passphrase hidden from SQL Profiler trace

Question

Hi - I have a sql 2008 database and some of the account information has been encrypted by the function "encryptbypassphrase"

I have a stored procedure to decrypt the vales once the passphase parameter is supplied by the user.

the user is apply to supply this passphrase though a asp.net application which I am building. My issue is that the profiler can easily trace the parameters passed to the sql server and this reveals the passphrase .

is there any way i can stop profiler to read the passphrase. Back in the sql 2000 days when a password was passed profiler would have hidden that textdata... is there something similar in sql2008?

 

 

Answer 1

If your motivation is to protect data from anyone in the sysadmin role, using the EncryptByPassphrase() feature will require that you generate your DML statements in the application and send the batch to SQL Server. In this example, your application may store the passphrases outside of SQL Server in a configuration file or directly in the application code. Of course, the sysadmin should not have access to the source code or file location containing the passphrases. When the statement is sent to SQL Server from the application, SQL Server Profiler will not reveal text related to encryption functions. When your application code passes the DecryptByPassphrase() function coupled with the proper passphrase, Profiler will replace the text of the event with "The text has been replaced with this comment for security reasons." Navigating toward an approach to embed DML SQL statements within the application code carries a number of concerns. http://programming4.us/security/1074.aspx

---

Manish

Answer 2

Will it help to encrypt the stored procedure?

Answer 3

See if ENCRYPTBYKEY function :-  http://msdn.microsoft.com/en-us/library/ms174361.aspx can be used in this scenario. But it may need some changes in the coding.

---

Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog