SQL Server - Encrypting Passwords with Symmetric Encryption Functions ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE

Views: 9.206 views

In this article

Reading Time: 8 minutes

Hey guys!!
In this article, I would like to share with you a very interesting solution to protect and encrypt passwords in SQL Server that have the possibility of recovering the original password (as long as you know the salt used), which are the functions ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE, available since SQL Server 2008.

Do you like to study about password and encryption security? See other articles on this subject:

Working with cryptographic functions (MD4, MD5, SHA1, SHA2_256, and SHA2_512) using the SQL Server HASHBYTES function
SQL Server - How to encrypt and decrypt passwords (with Salt) using CLR (C #)
SQL Server - How to identify fragile passwords, empty or equal to username
How to create a random password generator written in PHP, C # (CSharp) or Transact-SQL (T-SQL)
Working with the Base64 Encryption Algorithm in SQL Server

A Password Security Summary

When it comes to password security, we need to keep in mind that EVERY algorithm that allows retrieving the original string is not the safest option possible, as these methods are often easier to crack than using HASH-based algorithms. To improve your understanding, I will create a summary of the 5 basic types of password security we can find, in order of least secure to most secure.

1) None

The most mode unsafe possible, where the password is stored without any encryption and anyone who has access to the bank / file can view the original password.

2) Conversion

Method that adds a degree of security to the password when converting the original characters to other characters according to the algorithm used and this technique has the following characteristics:

One of the best known algorithms that use this technique is the Base64
The length of the converted string is different from the size of the original string.
A password when converted will always return the same result.
Algorithm conversion patterns are easy to identify visually, which makes it easy to identify which algorithm is used

Example:

3) Custom Conversion

A method that adds another degree of security to the previous method by applying custom scrambles on top of known conversion algorithms, such as creating a looping that iterates N times by applying the Base64 conversion multiple times and applying a function like REVERSE to each iteration.

This technique is not the most secure in the world, but it certainly makes it difficult for someone trying to crack the security of your password.

Some features of this method:

Uses conversion algorithms as well as string functions
The length of the converted string is different from the size of the original string and the original conversion algorithm.
A password when converted will always return the same result.
Algorithm conversion patterns are not easy to identify visually (and can confuse who is trying to break the code)

Example:

4) Symmetric encryption algorithms

Symmetric key algorithms are encryption algorithms that use the same cryptographic key for plaintext encryption and ciphertext decryption. The key, in practice, represents a secret shared between two or more parties that can be used to maintain a private information link. This requirement that both parties have access to the same secret key is one of the main disadvantages of symmetric key encryption compared to public key encryption (also known as asymmetric key encryption), as they use two keys (public and private). ).

Some features of this method:

Popular Algorithms: AES, Twofish, Serpent, Blowfish, CAST5, RC4, 3DES (based on DES), IDEA
The length of the encrypted string is different from the size of the original string (but it is always the same length according to the algorithm used).
The password can only be recovered if you know the private key (Salt)
A password when encrypted will return different results with each execution.
Difficult to identify which algorithm using hashing only

Example:

5) HASH Algorithms

A password encrypted with some hash function is virtually impossible to retrieve the original value and so we can say that they are one way. In this type of algorithm, it is not possible to recover the original password and the validation of its identity is made by comparisons between the encrypted hash and a new encrypted hash, generated in real time, from the password being tested.

Although extremely safe, there is a remote possibility of 2 different passwords producing the same hash (called collision). The greater the complexity and security of the algorithm used, the less likely this situation is to occur and also the longer it takes to encrypt the message and the size of the hash generated. Remember that this method is also subject to brute force attacks (as all previous ones).

Some features of this method:

Best known algorithms: MD4, MD5, SHA-1, SHA-2 (SHA-224, SHA-256, SHA-384, SHA-512, SHA-512/224, SHA-512/256), SHA-3 (Keccak ), HMAC
The length of the encrypted string is different from the size of the original string (but it is always the same length according to the algorithm used).
The password NO can be retrieved
A password when encrypted will always return the same hash
Difficult to identify which algorithm using hashing only

Example:

Using the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE Functions

Now that I've briefly summarized password security, let's talk about how to apply it to SQL Server:

Method 1 is for demonstration purposes only. You should NEVER store a password or sensitive data without an encryption / hash
Method 2 (Conversion) You Can Learn More In The Article Working with the Base64 Encryption Algorithm in SQL Server
Method 3 (Custom Conversion) I've already shown in the summary itself an example of usage (it's a creative extension of method 2)
Method 5 (HASH Algorithms) I already demonstrated in the article Working with cryptographic functions (MD4, MD5, SHA1, SHA2_256, and SHA2_512) using the SQL Server HASHBYTES function

So I was just missing demonstrating Method 4 (Symmetric Encryption Algorithms) here on the blog. And that is why we are analyzing the functions ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE, which allow you to encrypt and decrypt the data using a private key (salt) and the TRIPLE DES algorithm (3DES).

In addition to these functions, you can also use the ENCRYPTBYASYMKEY, ENCRYPTBYCERT e ENCRYPTBYKEY, but these functions are based on certificates and symmetric keys that need to be installed on the server for their use.

The use of these functions is very simple and practical:

DECLARE
    @ChavePrivada NVARCHAR(128) = 'dirceuresende.com',
    @Mensagem NVARCHAR(MAX) = 'Dirceu Resende',
    @Retorno VARBINARY(8000)

SET @Retorno = ENCRYPTBYPASSPHRASE(@ChavePrivada, @Mensagem)
SELECT CONVERT(NVARCHAR, DECRYPTBYPASSPHRASE(@ChavePrivada, @Retorno))

SELECT @Retorno

DECLARE

@ChavePrivada NVARCHAR(128) = 'dirceuresende.com',

@Mensagem NVARCHAR(MAX) = 'Dirceu Resende',

@Retorno VARBINARY(8000)

SET @Retorno = ENCRYPTBYPASSPHRASE(@ChavePrivada, @Mensagem)

SELECT CONVERT(NVARCHAR, DECRYPTBYPASSPHRASE(@ChavePrivada, @Retorno))

SELECT @Retorno

Result:

Just pay attention to NVARCHAR. Key, Message, and return format must be the same type: Either everything is VARCHAR or NVARCHAR. You cannot mix, or you will not be able to use these functions:

SQL 2017+ and Backward Compatibility

Available since SQL Server 2008, the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE functions are great simple, easy-to-implement solutions for improving the security of password and sensitive information storage, and they internally use the SHA1 algorithm to generate password hash and 3DES-128. for encryption, which is already good protection. From 2017 onwards, Microsoft changed the code for these functions for added security, and now they use SHA256 for password hashing and AES-256 for encryption.

Although it is a good thing from a password security point of view, it has created a problem for those who have older versions of SQL Server and interact with the newer versions, which I will now demonstrate.

In SQL 2008, I will generate a hash for the password “Dirceu Resende”:

With the hash generated “0x01000000940908D92A0826928234D7DD6E3FA27AF5D818FC7654AE39AC090D30DFE5C43C ”I will try to recover the original message on a SQL Server 2017:

Well, all right! Wonderful! Apparently, the SQL Server 2017 function is able to “read” the new and old formats. Now let's do the reverse: I will generate a hash in SQL Server 2017:

Wow, what a big hash .. I will use the hash “0x02000000F955D4A111500CD5F9287228B3FC927FB7640202F2EC85F6BBA2074CD2434F39FE98CFFAD46718119ABC20AFAE058E2B” para recuperar a senha original do SQL Server 2008:

Geez.. Returned NULL! And now?? Well, it doesn't have backwards compatibility. And SQL Server 2008 is no longer supported by Microsoft and probably won't have an update to include it. Do you have a solution??

But of course YES! And the responsible for that is our powerful SQLCLR! Using a function written in C #, we can provide a new function in SQL Server 2008 that can recover the password from both new and old versions:

The hash generated in SQL Server 2008 itself is also compatible with this function:

SELECT @@VERSION

DECLARE
    @ChavePrivada VARCHAR(128) = 'dirceuresende.com',
    @Mensagem VARCHAR(MAX) = 'Teste da SQLCLR',
    @Retorno VARBINARY(8000)

SET @Retorno = ENCRYPTBYPASSPHRASE(@ChavePrivada, @Mensagem)

SELECT CONVERT(VARCHAR, DECRYPTBYPASSPHRASE(@ChavePrivada, @Retorno))
SELECT CLR.dbo.fncDecryptByPassphrase(@ChavePrivada, '0x' + CONVERT(VARCHAR(MAX), @Retorno, 2))

SELECT @@VERSION

DECLARE

@ChavePrivada VARCHAR(128) = 'dirceuresende.com',

@Mensagem VARCHAR(MAX) = 'Teste da SQLCLR',

@Retorno VARBINARY(8000)

SET @Retorno = ENCRYPTBYPASSPHRASE(@ChavePrivada, @Mensagem)

SELECT CONVERT(VARCHAR, DECRYPTBYPASSPHRASE(@ChavePrivada, @Retorno))

SELECT CLR.dbo.fncDecryptByPassphrase(@ChavePrivada, '0x' + CONVERT(VARCHAR(MAX), @Retorno, 2))

Result:

Want to create this SQLCLR in your environment? Notice that you will have to create the assembly as UNSAFE (Unrestricted)due to the use of the library System.Security.Cryptography.Aesbecause the HPA (host protection attribute) “MayLeakOnAbort" not allowed in Safe or External Access mode.

Given the warning, let's go to assembly and function creation fncDecryptByPassphrase:

-- Troque pelo database onde você irá criar o assembly e a função. 
-- Por motivos de segurança, recomendo criar um DB só para os assemblies CLR
USE [CLR]
GO

-- Parâmetro necessário para publicar o assembly como UNSAFE (Unrestricted) - Cuidado com a parte de segurança!
-- https://dirceuresende.com/blog/sql-server-entendendo-os-riscos-da-propriedade-trustworthy-habilitada-em-um-database/
ALTER DATABASE [CLR] SET TRUSTWORTHY ON
GO

-- Apaga os objetos, se já existirem
IF (OBJECT_ID('dbo.fncDecryptByPassphrase') IS NOT NULL) DROP FUNCTION [dbo].[fncDecryptByPassphrase]
GO

IF (EXISTS(SELECT COUNT(*) FROM sys.assemblies WHERE [name] = 'Descriptografa_String_Salt_V2')) DROP ASSEMBLY [Descriptografa_String_Salt_V2]
GO

-- Cria o assembly SQLCLR no database
CREATE ASSEMBLY [Descriptografa_String_Salt_V2]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300C70B045E0000000000000000E00022200B013000000E00000006000000000000BE2D00000020000000400000000000100020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000006C2D00004F00000000400000F802000000000000000000000000000000000000006000000C000000342C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C40D000000200000000E000000020000000000000000000000000000200000602E72737263000000F8020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000A02D000000000000480000000200050088220000AC0900000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133004008B0100000100001100280600000A026F0700000A0A03186F0800000A100103161E6F0900000A280200000616280A00000A0B07172E041F202B021F100C0717FE01130B110B2C3600280B00000A1304280C00000A13051105031E1F106F0900000A28020000066F0D00000A00031F186F0800000A28020000060D002B520718FE01130C110C2C3600280E00000A1304280F00000A13051105031E1F206F0900000A28020000066F0D00000A00031F286F0800000A28020000060D002B12007201000070281000000A130D38C10000001105186F1100000A001105176F1200000A0011040616068E696F1300000A26110511046F1400000A08280100002B280200002B6F1700000A0011056F1800000A0916098E696F1900000A1306110616281A00000A13071107200DF0ADBAFE0116FE01130E110E2C0F00722F000070281000000A130D2B4A11061E280300002B280200002B1308110816280400002B15FE0116FE01130911092D0E281D00000A11086F1E00000A2B0C280600000A11086F1E00000A130A110A281000000A130D2B00110D2A00133003005E0000000200001173040000060A06027D010000040016067B010000046F1F00000A282000000A7E03000004252D17267E02000004FE0608000006732100000A258003000004280500002B06FE0605000006732300000A280600002B280200002B0B2B00072A2202282500000A002A56027B0100000403186F0900000A1F10282600000A2A2E730700000680020000042A1E03185D16FE012A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C0000004C030000237E0000B80300000404000023537472696E677300000000BC07000050000000235553000C0800001000000023475549440000001C0800009001000023426C6F62000000000000000200000157150208090A000000FA0133001600000100000019000000040000000300000008000000050000002600000006000000020000000200000001000000030000000200000006000000000064020100000000000600B2012C030600D2012C030600890115030F004C03000006008E0386020A009D01DD020A0014025B030600A002E70306008D02E70306006E012C030E009300860206000B02B5030600210286020600D002860206003900E7030600B700E70306009A00E7030E002803E70306000501E70306001101E7030E003301C40206005600E1000600AE02E7030600D90386020600AD03860200000000AE0000000000010001000100100079030000150001000100030110002400000015000100040003211000DD0000001500020006000600C1032E013600AA003101160001003501502000000000960050013D010100E821000000009100C503440103005222000000008618F802060004005222000000008618F802060004005B220000000083003E004A0104007122000000009118FE024F0105005222000000008618F802060005007D220000000083000A005301050000000100A803000002003B0200000100C10300000100C30300000100C3030900F80201001100F80206001900F8020A003100F80206005100F802060061001C012900610070032E0069002802340069002802390071006D003F00790067014600810067014B004900C1005000890067015600910067015B0039009C0360004900FF0166004900FC006C00410050027200410032027B00A9002E018000A900D70395004900DF03500049000503A200B9005002720071006400A700A900BF028000C100F701AE006100A100290061001E02B80069004502C500A9002801C9000C00F802DA00A9003E01E0001400F802DA00A900950302012900F8020600C900F0011F012000230089012E000B0058012E00130061012E001B00800163002B00890183002B0089011000BE00D300FB00048000000000000000000000000000000000750000000200000000000000000000002501D400000000000200000000000000000000002501C80000000000030005000000000000000000250144010000000003000200040002002B0091002D00910037009100390091004500F70049001A010000003C3E395F5F315F30003C537472696E67546F4279746541727261793E625F5F315F30003C3E635F5F446973706C6179436C617373315F300053484131003C537472696E67546F4279746541727261793E625F5F310049456E756D657261626C65603100546F55496E74333200546F496E7433320044657363726970746F67726166615F537472696E675F53616C745F56320046756E63603200534841323536006765745F55544638003C3E39003C4D6F64756C653E00547269706C65444553007365745F49560053797374656D2E44617461006D73636F726C6962003C3E630053797374656D2E436F6C6C656374696F6E732E47656E65726963007365745F4D6F64650050616464696E674D6F6465004369706865724D6F6465006765745F556E69636F64650052616E67650054616B6500456E756D657261626C650057686572650053797374656D2E436F726500666E63446563727970744279506173737068726173650043726561746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500546F4279746500496E6465784F66007365745F50616464696E6700456E636F64696E670053716C537472696E6700476574537472696E6700537562737472696E67006765745F486173680073656E686148617368006765745F4C656E677468005472616E73666F726D46696E616C426C6F636B0044657363726970746F67726166615F537472696E675F53616C745F56322E646C6C0053797374656D0053796D6D6574726963416C676F726974686D0048617368416C676F726974686D004943727970746F5472616E73666F726D00536B69700053797374656D2E4C696E7100426974436F6E766572746572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F7200437265617465446563727970746F720053797374656D2E446961676E6F7374696373004165730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730047657442797465730055736572446566696E656446756E6374696F6E73004F626A6563740053656C656374006F705F496D706C696369740073616C7400436F6E766572740053797374656D2E546578740068657800537472696E67546F42797465417272617900546F4172726179007365745F4B65790053797374656D2E53656375726974792E43727970746F67726170687900002D55006E0073007500700070006F007200740065006400200065006E006300720079007000740069006F006E00001D440065006300720079007000740020006600610069006C0065006400000000007A1ABE6F63B3BA4CBE71B26D996C8C640004200101080320000105200101111118070F1D0508081D05122112251D05091D05020E0202111D0204000012310520011D050E0420010E080520020E0808060002081D0508040000123D0400001241052001011D0504000012450400001249050001111D0E05200101114D0520010111510820031D051D0508080420001D0510100102151259011E00151259011E0008030A01050C1001011D1E00151259011E00042000125D060002091D050809100102081D1E001E000520010E1D05060702120C1D0503200008090002151259010808080615122D020802052002011C1816100102151259011E00151259011E0015122D021E0002030A01080615122D02080517100202151259011E01151259011E0015122D021E001E01040A020805050002050E0808B77A5C561934E08902060E03061210070615122D020802060002111D0E0E0500011D050E04200105080300000104200102080801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000000C70B045E00000000020000001C010000502C0000500E000052534453E8D32A00E971784FBF3457CE348ED81201000000433A5C55736572735C6469726365755C736F757263655C7265706F735C44657363726970746F67726166615F537472696E675F53616C745F56325C44657363726970746F67726166615F537472696E675F53616C745F56325C6F626A5C44656275675C44657363726970746F67726166615F537472696E675F53616C745F56322E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942D00000000000000000000AE2D0000002000000000000000000000000000000000000000000000A02D0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000009C02000000000000000000009C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004FC010000010053007400720069006E006700460069006C00650049006E0066006F000000D801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000064002200010049006E007400650072006E0061006C004E0061006D0065000000440065007300630072006900700074006F00670072006100660061005F0053007400720069006E0067005F00530061006C0074005F00560032002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000006C00220001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000440065007300630072006900700074006F00670072006100660061005F0053007400720069006E0067005F00530061006C0074005F00560032002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C03D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
	WITH PERMISSION_SET = UNSAFE
GO

-- Cria a função
CREATE FUNCTION [dbo].[fncDecryptByPassphrase] (@salt NVARCHAR (MAX), @senhaHash NVARCHAR (MAX))
RETURNS NVARCHAR (MAX)
AS
EXTERNAL NAME [Descriptografa_String_Salt_V2].[UserDefinedFunctions].[fncDecryptByPassphrase]

-- Troque pelo database onde você irá criar o assembly e a função.

-- Por motivos de segurança, recomendo criar um DB só para os assemblies CLR

USE [CLR]

-- Parâmetro necessário para publicar o assembly como UNSAFE (Unrestricted) - Cuidado com a parte de segurança!

-- https://dirceuresende.com/blog/sql-server-entendendo-os-riscos-da-propriedade-trustworthy-habilitada-em-um-database/

ALTER DATABASE [CLR] SET TRUSTWORTHY ON

-- Apaga os objetos, se já existirem

IF (OBJECT_ID('dbo.fncDecryptByPassphrase') IS NOT NULL) DROP FUNCTION [dbo].[fncDecryptByPassphrase]

IF (EXISTS(SELECT COUNT(*) FROM sys.assemblies WHERE [name] = 'Descriptografa_String_Salt_V2')) DROP ASSEMBLY [Descriptografa_String_Salt_V2]

-- Cria o assembly SQLCLR no database

CREATE ASSEMBLY [Descriptografa_String_Salt_V2]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300C70B045E0000000000000000E00022200B013000000E00000006000000000000BE2D00000020000000400000000000100020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000006C2D00004F00000000400000F802000000000000000000000000000000000000006000000C000000342C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C40D000000200000000E000000020000000000000000000000000000200000602E72737263000000F8020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000A02D000000000000480000000200050088220000AC0900000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133004008B0100000100001100280600000A026F0700000A0A03186F0800000A100103161E6F0900000A280200000616280A00000A0B07172E041F202B021F100C0717FE01130B110B2C3600280B00000A1304280C00000A13051105031E1F106F0900000A28020000066F0D00000A00031F186F0800000A28020000060D002B520718FE01130C110C2C3600280E00000A1304280F00000A13051105031E1F206F0900000A28020000066F0D00000A00031F286F0800000A28020000060D002B12007201000070281000000A130D38C10000001105186F1100000A001105176F1200000A0011040616068E696F1300000A26110511046F1400000A08280100002B280200002B6F1700000A0011056F1800000A0916098E696F1900000A1306110616281A00000A13071107200DF0ADBAFE0116FE01130E110E2C0F00722F000070281000000A130D2B4A11061E280300002B280200002B1308110816280400002B15FE0116FE01130911092D0E281D00000A11086F1E00000A2B0C280600000A11086F1E00000A130A110A281000000A130D2B00110D2A00133003005E0000000200001173040000060A06027D010000040016067B010000046F1F00000A282000000A7E03000004252D17267E02000004FE0608000006732100000A258003000004280500002B06FE0605000006732300000A280600002B280200002B0B2B00072A2202282500000A002A56027B0100000403186F0900000A1F10282600000A2A2E730700000680020000042A1E03185D16FE012A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C0000004C030000237E0000B80300000404000023537472696E677300000000BC07000050000000235553000C0800001000000023475549440000001C0800009001000023426C6F62000000000000000200000157150208090A000000FA0133001600000100000019000000040000000300000008000000050000002600000006000000020000000200000001000000030000000200000006000000000064020100000000000600B2012C030600D2012C030600890115030F004C03000006008E0386020A009D01DD020A0014025B030600A002E70306008D02E70306006E012C030E009300860206000B02B5030600210286020600D002860206003900E7030600B700E70306009A00E7030E002803E70306000501E70306001101E7030E003301C40206005600E1000600AE02E7030600D90386020600AD03860200000000AE0000000000010001000100100079030000150001000100030110002400000015000100040003211000DD0000001500020006000600C1032E013600AA003101160001003501502000000000960050013D010100E821000000009100C503440103005222000000008618F802060004005222000000008618F802060004005B220000000083003E004A0104007122000000009118FE024F0105005222000000008618F802060005007D220000000083000A005301050000000100A803000002003B0200000100C10300000100C30300000100C3030900F80201001100F80206001900F8020A003100F80206005100F802060061001C012900610070032E0069002802340069002802390071006D003F00790067014600810067014B004900C1005000890067015600910067015B0039009C0360004900FF0166004900FC006C00410050027200410032027B00A9002E018000A900D70395004900DF03500049000503A200B9005002720071006400A700A900BF028000C100F701AE006100A100290061001E02B80069004502C500A9002801C9000C00F802DA00A9003E01E0001400F802DA00A900950302012900F8020600C900F0011F012000230089012E000B0058012E00130061012E001B00800163002B00890183002B0089011000BE00D300FB00048000000000000000000000000000000000750000000200000000000000000000002501D400000000000200000000000000000000002501C80000000000030005000000000000000000250144010000000003000200040002002B0091002D00910037009100390091004500F70049001A010000003C3E395F5F315F30003C537472696E67546F4279746541727261793E625F5F315F30003C3E635F5F446973706C6179436C617373315F300053484131003C537472696E67546F4279746541727261793E625F5F310049456E756D657261626C65603100546F55496E74333200546F496E7433320044657363726970746F67726166615F537472696E675F53616C745F56320046756E63603200534841323536006765745F55544638003C3E39003C4D6F64756C653E00547269706C65444553007365745F49560053797374656D2E44617461006D73636F726C6962003C3E630053797374656D2E436F6C6C656374696F6E732E47656E65726963007365745F4D6F64650050616464696E674D6F6465004369706865724D6F6465006765745F556E69636F64650052616E67650054616B6500456E756D657261626C650057686572650053797374656D2E436F726500666E63446563727970744279506173737068726173650043726561746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500546F4279746500496E6465784F66007365745F50616464696E6700456E636F64696E670053716C537472696E6700476574537472696E6700537562737472696E67006765745F486173680073656E686148617368006765745F4C656E677468005472616E73666F726D46696E616C426C6F636B0044657363726970746F67726166615F537472696E675F53616C745F56322E646C6C0053797374656D0053796D6D6574726963416C676F726974686D0048617368416C676F726974686D004943727970746F5472616E73666F726D00536B69700053797374656D2E4C696E7100426974436F6E766572746572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F7200437265617465446563727970746F720053797374656D2E446961676E6F7374696373004165730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730047657442797465730055736572446566696E656446756E6374696F6E73004F626A6563740053656C656374006F705F496D706C696369740073616C7400436F6E766572740053797374656D2E546578740068657800537472696E67546F42797465417272617900546F4172726179007365745F4B65790053797374656D2E53656375726974792E43727970746F67726170687900002D55006E0073007500700070006F007200740065006400200065006E006300720079007000740069006F006E00001D440065006300720079007000740020006600610069006C0065006400000000007A1ABE6F63B3BA4CBE71B26D996C8C640004200101080320000105200101111118070F1D0508081D05122112251D05091D05020E0202111D0204000012310520011D050E0420010E080520020E0808060002081D0508040000123D0400001241052001011D0504000012450400001249050001111D0E05200101114D0520010111510820031D051D0508080420001D0510100102151259011E00151259011E0008030A01050C1001011D1E00151259011E00042000125D060002091D050809100102081D1E001E000520010E1D05060702120C1D0503200008090002151259010808080615122D020802052002011C1816100102151259011E00151259011E0015122D021E0002030A01080615122D02080517100202151259011E01151259011E0015122D021E001E01040A020805050002050E0808B77A5C561934E08902060E03061210070615122D020802060002111D0E0E0500011D050E04200105080300000104200102080801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000000C70B045E00000000020000001C010000502C0000500E000052534453E8D32A00E971784FBF3457CE348ED81201000000433A5C55736572735C6469726365755C736F757263655C7265706F735C44657363726970746F67726166615F537472696E675F53616C745F56325C44657363726970746F67726166615F537472696E675F53616C745F56325C6F626A5C44656275675C44657363726970746F67726166615F537472696E675F53616C745F56322E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942D00000000000000000000AE2D0000002000000000000000000000000000000000000000000000A02D0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000009C02000000000000000000009C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004FC010000010053007400720069006E006700460069006C00650049006E0066006F000000D801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000064002200010049006E007400650072006E0061006C004E0061006D0065000000440065007300630072006900700074006F00670072006100660061005F0053007400720069006E0067005F00530061006C0074005F00560032002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000006C00220001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000440065007300630072006900700074006F00670072006100660061005F0053007400720069006E0067005F00530061006C0074005F00560032002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C03D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = UNSAFE

-- Cria a função

CREATE FUNCTION [dbo].[fncDecryptByPassphrase] (@salt NVARCHAR (MAX), @senhaHash NVARCHAR (MAX))

RETURNS NVARCHAR (MAX)

EXTERNAL NAME [Descriptografa_String_Salt_V2].[UserDefinedFunctions].[fncDecryptByPassphrase]

Function source code if you want to compile and deploy the project yourself:

using System;
using System.Data.SqlTypes;
using System.Linq;
using System.Security.Cryptography;
using System.Text;

// Incluir os assemblies mscorlib, System.Core, System.Data e System.Data.Linq nas referências do projeto

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncDecryptByPassphrase(string salt, string senhaHash)
    {

        // Encode password as UTF16-LE
        byte[] passwordBytes = Encoding.Unicode.GetBytes(salt);

        // Remove leading "0x"
        senhaHash = senhaHash.Substring(2);

        int version = BitConverter.ToInt32(StringToByteArray(senhaHash.Substring(0, 8)), 0);
        int keySize = version == 1 ? 16 : 32;

        byte[] encrypted;
        HashAlgorithm hashAlgo;
        SymmetricAlgorithm cryptoAlgo;
        if (version == 1)
        {
            hashAlgo = SHA1.Create();
            cryptoAlgo = TripleDES.Create();
            cryptoAlgo.IV = StringToByteArray(senhaHash.Substring(8, 16));
            encrypted = StringToByteArray(senhaHash.Substring(24));
        }
        else if (version == 2)
        {
            hashAlgo = SHA256.Create();
            cryptoAlgo = Aes.Create();
            cryptoAlgo.IV = StringToByteArray(senhaHash.Substring(8, 32));
            encrypted = StringToByteArray(senhaHash.Substring(40));
        }
        else
        {
            return "Unsupported encryption";
        }

        cryptoAlgo.Padding = PaddingMode.PKCS7;
        cryptoAlgo.Mode = CipherMode.CBC;

        hashAlgo.TransformFinalBlock(passwordBytes, 0, passwordBytes.Length);
        cryptoAlgo.Key = hashAlgo.Hash.Take(keySize).ToArray();

        byte[] decrypted = cryptoAlgo.CreateDecryptor().TransformFinalBlock(encrypted, 0, encrypted.Length);
        uint magic = BitConverter.ToUInt32(decrypted, 0);
        if (magic != 0xbaadf00d)
        {
            return "Decrypt failed";
        }

        byte[] decryptedData = decrypted.Skip(8).ToArray();
        bool isUtf16 = (Array.IndexOf(decryptedData, (byte) 0) != -1);
        string decryptText = (isUtf16 ? Encoding.Unicode.GetString(decryptedData) : Encoding.UTF8.GetString(decryptedData));

        return decryptText;
    }

    // Method taken from https://stackoverflow.com/questions/321370/how-can-i-convert-a-hex-string-to-a-byte-array?answertab=votes#tab-top
    private static byte[] StringToByteArray(string hex)
    {
        return Enumerable.Range(0, hex.Length)
                         .Where(x => x % 2 == 0)
                         .Select(x => Convert.ToByte(hex.Substring(x, 2), 16))
                         .ToArray();
    }

}

using System;

using System.Data.SqlTypes;

using System.Linq;

using System.Security.Cryptography;

using System.Text;

// Incluir os assemblies mscorlib, System.Core, System.Data e System.Data.Linq nas referências do projeto

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncDecryptByPassphrase(string salt, string senhaHash)

{

// Encode password as UTF16-LE

byte[] passwordBytes = Encoding.Unicode.GetBytes(salt);

// Remove leading "0x"

senhaHash = senhaHash.Substring(2);

int version = BitConverter.ToInt32(StringToByteArray(senhaHash.Substring(0, 8)), 0);

int keySize = version == 1 ? 16 : 32;

byte[] encrypted;

HashAlgorithm hashAlgo;

SymmetricAlgorithm cryptoAlgo;

if (version == 1)

{

hashAlgo = SHA1.Create();

cryptoAlgo = TripleDES.Create();

cryptoAlgo.IV = StringToByteArray(senhaHash.Substring(8, 16));

encrypted = StringToByteArray(senhaHash.Substring(24));

}

else if (version == 2)

{

hashAlgo = SHA256.Create();

cryptoAlgo = Aes.Create();

cryptoAlgo.IV = StringToByteArray(senhaHash.Substring(8, 32));

encrypted = StringToByteArray(senhaHash.Substring(40));

}

else

{

return "Unsupported encryption";

}

cryptoAlgo.Padding = PaddingMode.PKCS7;

cryptoAlgo.Mode = CipherMode.CBC;

hashAlgo.TransformFinalBlock(passwordBytes, 0, passwordBytes.Length);

cryptoAlgo.Key = hashAlgo.Hash.Take(keySize).ToArray();

byte[] decrypted = cryptoAlgo.CreateDecryptor().TransformFinalBlock(encrypted, 0, encrypted.Length);

uint magic = BitConverter.ToUInt32(decrypted, 0);

if (magic != 0xbaadf00d)

{

return "Decrypt failed";

}

byte[] decryptedData = decrypted.Skip(8).ToArray();

bool isUtf16 = (Array.IndexOf(decryptedData, (byte) 0) != -1);

string decryptText = (isUtf16 ? Encoding.Unicode.GetString(decryptedData) : Encoding.UTF8.GetString(decryptedData));

return decryptText;

}

// Method taken from https://stackoverflow.com/questions/321370/how-can-i-convert-a-hex-string-to-a-byte-array?answertab=votes#tab-top

private static byte[] StringToByteArray(string hex)

{

return Enumerable.Range(0, hex.Length)

.Where(x => x % 2 == 0)

.Select(x => Convert.ToByte(hex.Substring(x, 2), 16))

.ToArray();

}

Well guys, I hope you enjoyed this very technical and complicated post .. lol
A big hug and see you in the next article!

References:

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - Encrypting passwords with the symmetric encryption functions ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE

A Password Security Summary

1) None

2) Conversion

3) Custom Conversion

4) Symmetric encryption algorithms

5) HASH Algorithms

Using the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE Functions

SQL 2017+ and Backward Compatibility

You may also like ...

Leave a Comment Cancel reply

A Password Security Summary

1) None

2) Conversion

3) Custom Conversion

4) Symmetric encryption algorithms

5) HASH Algorithms

Using the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE Functions

SQL 2017+ and Backward Compatibility

You may also like ...

SQL Server - How to unlock a login without having to change/reset the password

SQL Server - The danger of using JOIN between columns of different data types

SQL Server - How to identify and replace “strange things” (hidden characters) in strings and tables

Leave a Comment Cancel reply