question

Arunthangaraj avatar image
Arunthangaraj asked

Encrypt varchar data to Varchar in sql server 2014

Hi, Need to Encrypt data of type nvarchar in SQL server 2014. The encrypted output should also be in nvarchar or varchar.(Note: Encrypted output should not be varbinay) Example : Original Text : 123Hello123 Encrypted output : 848074hcfugeg8498 Do we have any encryption algorithm in SQL server 2014, if no please suggest any custom scripts to do that. Thanks
sql server 2014encryption
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What is the purpose of the encryption? All of the SQL's built in encryption functions will result in a VARBINARY format. However, if it can be a one way hash, you might want to look at HASHBYTES. If that won't work for you, you'd probably have to roll your own solution.
1 Like 1 ·

1 Answer

· Write an Answer
srutzky avatar image
srutzky answered

Below is a lightly edited copy of an answer that I posted on StackOverlow since link-only answers are generally discouraged:

You should look into using one of the several paired `ENCRYPTBY` / `DECRYPTBY` built-in functions:

For situations that do not require decryption (i.e. a one-way transformation that cannot be transformed back into the original value), such as how passwords are typically handled, you can use the HASHBYTES function, which can do the following algorithms:

SQL Server 2005 and newer can do:

  • MD2
  • MD4
  • MD5
  • SHA
  • SHA1

SQL Server 2012 (and newer) added these:

  • SHA2_256
  • SHA2_512

All of the functions noted above (both `HASHBYTES` and `ENCRYPTBY_____` ) return a `VARBINARY`. If you need those hex digits in string format, the CONVERT function, starting in SQL Server 2008, added "style" numbers 1 and 2 to handle this:

;WITH cte AS
(
  SELECT HASHBYTES('SHA2_512', N'test') AS [HashedValue]
)
SELECT cte.HashedValue, -- VARBINARY
       CONVERT(VARCHAR(200), cte.HashedValue) AS [DefaultOrStyle0], -- converted
       CONVERT(VARCHAR(200), cte.HashedValue, 1) AS [Style1], -- leading 0x
       CONVERT(VARCHAR(200), cte.HashedValue, 2) AS [Style2] -- no leading 0x
FROM   cte;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.