Sql-server – Saving a variable globally in SQL server it self and use it when required

sql serversql-server-2008-r2stored-procedurest-sql

We are using ENCRYPTBYPASSPHRASE to encrypt some columns of some database. To decrypt the values every time we need to pass passphrase with DECRYPTBYPASSPHRASE.

So I planned to create a stored procedure as shown below

CREATE function [dbo].[SQLEncrypt](@str varchar(max)) returns varchar(max) 
as 
 begin
 declare @test nvarchar(max)
 set @test=''


    set @test =   convert(varchar(MAX),DECRYPTBYPASSPHRASE ('password',@str))



 return @test
END

GO

I can use this stored procedure as shown below to decrypt a column

----usgae---

select master.dbo.SQLEncrypt(columnname) from tablename--
-----------------

Now I am planning to store this password somewhere globally in SQL server(Don't know if it is possible or not) and then use it in stored procedure. IS it possible to do by anyway? Also can I hide the password from users?

Best Answer

Don't.

While is possible to use context_info, that is a really really really bad choice for a password. All users with VIEW SERVER STATE will see it in sys.dm_exec_sessions. SQL Profiler will not know what you're doing when you set it and will not obfuscate it in produced events. And more similar badness.

Instead use proper encryption hierarchy. Do not encrypt the data by passphrase, is never the right choice. Instead encrypt it with a symmetric key. Then open the key in the session using the password, see OPEN SYMMETRIC KEY. The key will become available in your session and the data can be automatically decrypted. This is the recommended way, don't try to outsmart it.

Related Question