I recently got a request to obfuscate data at Dev environment.  Most of the times these kind of requests come in environments where dealing with sensitive data. We usually do dev refresh from prod on monthly basis to make sure both at sync. So whenever a refresh happens we need to obfuscate sensitive data at Development environment.

Obfuscation is nothing but replacing the original data with dummy data. There are few options / native methods available in SQL Server. I preferred the below methodology.

Here I am just giving an example using a variable; we can follow the same methodology while dealing with tables.

How to Encrypt and Decrypt String in Sql Server:

/*****************************************************/

/*** @pwd - Generate a random pwd every time *********/
/*** Always operate these things from DBA database ***/
/*****************************************************/

DECLARE @pwd            NVARCHAR(100),
        @txt            VARCHAR(30),
        @EncryptedText  VARBINARY(1000);

SELECT  @txt ='ACCNO-NYCITI-01678999',
        @pwd = N'ObIhkju8-9m64Gbn';

SELECT @EncryptedText = ENCRYPTBYPASSPHRASE(@pwd,@txt)
SELECT @EncryptedText AS 'Encrypted Text'
SELECT CONVERT(VARCHAR(30),DECRYPTBYPASSPHRASE(@pwd,@EncryptedText))
AS'Original Text

OutPut:

Note: While decrypting it must be converted as varchar