2

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?

9

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.

  • ok. You mentioned like not to use "ENCRYPTBYPASSPHRASE". What is the problem in using it? What are the disadvantage of this method? – IT researcher Nov 10 '14 at 12:40
  • 1
    The disadvantage is right there, in your post: your API has to pass the password to every place where the data is accessed. – Remus Rusanu Nov 10 '14 at 12:52
0

Context_info acts like a global variable. It's binary so will require some work to extract the password each time it is used. BOL has details and examples.

0

Without commenting on your encryption solution per se, storing a string or password in the database can be as trivial as creating a single-row, single-column table and properly restricting the permissions on that table. Another approach would be to create an encrypted view with a hard-coded column.

CREATE VIEW dbo.mySecret
WITH ENCRYPTION
AS
SELECT 'password' AS pwd
GO

GRANT SELECT ON dbo.mySecret TO aSelectFew;

Or you could build a primitive encrypted scalar function that uses a paramter as a primitive "password" of sorts - this would give you an additional layer of security.

Using ownership chaining, you can allow your stored procedure to retrieve the value, while still denying "regular users" permissions to view the table/view/function's contents.

Or, why not put the password in your scalar encryption function in the first place - just remember to add WITH ENCRYPTION to it.

Important:

  • Ownership chaining works for all stored procedures with the same owner as the table/view/function, not just yours.
  • Encrypted views can be decrypted if you have access to a dedicated administrator console (DAC) using generally available decryption scripts.
  • Permissions can be by-passed with a) higher/dbo permissions or b) access to a backup that can be restored on a different machine.
0

SQL Server has no global variables. If you want to make something available to all sessions, use a table and write the data there. Another option is persisting the data in a global temporary table (prefixed with two # signs). It could get tricky to detect whether the table got out of scope though.

If you want to make a value available throughout the whole life of a session, the closest thing I can think of is CONTEXT_INFO. Again, a local temporary table (with a single #) might do the trick as well.

That said, both methods will not prevent the users from seeing the password in clear text. CONTEXT_INFO is binary, so users won't see the clear text directly (it has to be cast to a char type), but it's mere "security by obscurity".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.