It is not necessary to create and use a key persisted in the database for encryption. If you are willing to manage the key yourself—and take responsibility for keeping the secret secret—use the EncryptByPassPhrase and DecryptByPassPhrase functions. These functions require only a pass phrase as the first parameter and either clear text or ciphertext as the second, and they do not let you select the algorithm used.
It means you just need to have the phrase, functions and data (encrypted or decrypted); it does not matter in which SQL Server instance or database you are.
Make sure that you use a strong password. SQL Server does not do any kind of password checking, even if you are using the password validation features available for SQL logins.
Note The encryption algorithm used by EncryptByPassPhrase is undocumented.
Example:
I created two functions one to encrypt data and second for decryption. Further from security point of view you can encrypt these functions just to avoid seeing their body text.
USE TEST
-- Data Encryption Functions
Create Function DBO.EncryptBPData(@Data varchar(Max)) Returns varbinary(Max)
With Encryption
As
Begin
Declare @v1 varbinary(Max)
Set @v1 = EncryptByPassphrase('#$MyTestEncryption$#',@Data)
Return @v1
End
-- Data Decryption Functions
Create Function DBO.DecryptBPData(@Data varbinary(Max)) Returns varchar(Max)
With Encryption
As
Begin
Declare @v1 varchar(Max)
Set @v1 = Convert(varchar(Max),DecryptByPassphrase('#$MyTestEncryption$#',@Data))
Return @v1
End
-- Usage of above functions
DECLARE @VAR1 VARCHAR(100)
DECLARE @VAR2 VARBINARY(100)
SET @VAR2 = DBO.EncryptBPData('This is a test phrase to encrypt.')
SELECT @VAR2
SET @VAR1 = DBO.DecryptBPData(@VAR2)
SELECT @VAR1
