SQL Server - Criptografando senhas com as funções de criptografia simétrica ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE

Post Views 11,067 views

Neste artigo

Reading time 8 minutes

Fala galera!!
Nesse artigo, eu gostaria de compartilhar com vocês uma solução bem interessante para proteger e criptografar senhas no SQL Server e que possuem possibilidade de recuperação da senha original (desde que você saiba o salt utilizado), que são as funções ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE, disponíveis desde o SQL Server 2008.

Você gosta de estudar sobre segurança de senhas e criptografias? Veja outros artigos sobre esse assunto:

Um resumo sobre segurança de senhas

Quando se fala em segurança de senhas, precisamos ter em mente que TODO algoritmo que permite a recuperação da string original não é a opção mais segura possível, uma vez que esses métodos geralmente são mais fáceis de serem quebrados que utilizando algoritmos baseados em HASH. Para melhorar o seu entendimento, vou criar um resumo dos 5 tipos básicos de segurança de senha que podemos encontrar, em ordem de menos seguro para o mais seguro.

1) Nenhuma

O modo mais inseguro possível, onde a senha é armazenada sem qualquer criptografia e qualquer pessoa que tenha acesso ao banco/arquivo pode visualizar a senha original.

2) Conversão

Método que adiciona um grau de segurança na senha ao converter os caracteres originais por outros caracteres de acordo com o algoritmo utilizado e essa técnica tem as seguintes características:

Um dos algoritmos mais conhecidos que utilizam essa técnica é o Base64
O tamanho da string convertida é diferente do tamanho da string original
Uma senha ao ser convertida, sempre irá retornar o mesmo resultado
Padrões de conversão dos algoritmos são fáceis de serem identificados visualmente, o que facilita identificar qual o algoritmo utilizado

Exemplo:

3) Conversão customizada

Método que adiciona mais um grau de segurança no método anterior ao aplicar embaralhamentos personalizados em cima de algoritmos já conhecidos de conversão, como por exemplo, criar um looping que itere N vezes aplicando a conversão do Base64 várias vezes e aplicando uma função como REVERSE em cada iteração.

Essa técnica não é a mais segura do mundo, mas com certeza, já dificulta um pouco o trabalho de alguém que tente quebrar a segurança de uma senha sua.

Algumas características desse método:

Utiliza os algoritmos de conversão e também funções de string
O tamanho da string convertida é diferente do tamanho da string original e do algoritmo de conversão original
Uma senha ao ser convertida, sempre irá retornar o mesmo resultado
Padrões de conversão dos algoritmos não são fáceis de serem identificados visualmente (e podem confundir quem está tentando quebrar o código)

Exemplo:

4) Algoritmos de criptografia simétrica

Algoritmos de chave simétrica são algoritmos para criptografia que usam a mesma chave criptográfica para encriptação de texto puro e decriptação de texto cifrado. A chave, na prática, representa um segredo compartilhado entre duas ou mais partes que pode ser usado para manter uma ligação de informação privada. Este requisito de que ambas as partes possuam acesso à mesma chave secreta é uma das principais desvantagens da criptografia de chave simétrica, em comparação com a criptografia de chave pública (também conhecida como criptografia de chave assimétrica), pois utilizam duas chaves (pública e privada).

Algumas características desse método:

Algoritmos mais conhecidos: AES, Twofish, Serpent, Blowfish, CAST5, RC4, 3DES (baseado no DES), IDEA
O tamanho da string criptografada é diferente do tamanho da string original (mas é sempre o mesmo tamanho, de acordo com o algoritmo utilizado)
A senha só pode ser recuperada caso você saiba a chave privada (Salt)
Uma senha ao ser criptografada, irá retornar resultados diferentes a cada execução
Difícil identificar qual o algoritmo utilizando analisando apenas o hash

Exemplo:

5) Algoritmos de HASH

Uma senha criptografada com alguma função hash é praticamente impossível de conseguir recuperar o valor original e por isso, podemos dizer que são unidirecionais. Nesse tipo de algoritmo, não é possível recuperar a senha original e a validação da sua identidade se faz por comparações entre o hash criptografado e um novo hash criptografado, gerado em tempo real, a partir da senha que está sendo testada.

Embora seja extremamente seguro, existe a remota possibilidade de 2 senhas diferentes produzirem o mesmo hash (chamado de colisão). Quanto maior a complexidade e segurança do algoritmo utilizado, menor a probabilidade dessa situação ocorrer e também maior é o tempo necessário para criptografar a mensagem e o tamanho do hash gerado. Vale lembrar que esse método também é passível de ataques de força bruta (assim como todos os anteriores).

Algumas características desse método:

Algoritmos mais conhecidos: MD4, MD5, SHA-1, SHA-2 (SHA-224, SHA-256, SHA-384, SHA-512, SHA-512/224, SHA-512/256), SHA-3 (Keccak), HMAC
O tamanho da string criptografada é diferente do tamanho da string original (mas é sempre o mesmo tamanho, de acordo com o algoritmo utilizado)
A senha NÃO pode ser recuperada
Uma senha ao ser criptografada, irá retornar sempre o mesmo hash
Difícil identificar qual o algoritmo utilizando analisando apenas o hash

Exemplo:

Utilizando as funções ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE

Agora que fiz um breve resumo sobre segurança de senhas, vamos falar sobre como aplicar isso no SQL Server:

O método 1 é só demonstração. Você JAMAIS deve armazenar uma senha ou dado sensível sem uma criptografia/hash
O método 2 (Conversão) você pode aprender mais no artigo Trabalhando com o algoritmo de criptografia Base64 no SQL Server
O método 3 (Conversão customizada) eu já demonstrei no próprio resumo um exemplo de uso (é uma extensão criativa do método 2)
O método 5 (Algoritmos de HASH) eu já demonstrei no artigo Trabalhando com funções criptográficas (MD4, MD5, SHA1, SHA2_256 e SHA2_512) utilizando a função HASHBYTES do SQL Server

Então só estava faltando demonstrar o método 4 (Algoritmos de criptografia simétrica) aqui no blog. E é por isso que estamos analisando as funções ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE, que permitem criptografar e descriptografar os dados utilizando uma chave privada (salt) e o algoritmo TRIPLE DES (3DES).

Além dessas funções, você também pode utilizar as ENCRYPTBYASYMKEY, ENCRYPTBYCERT e ENCRYPTBYKEY, mas essas funções são baseadas em certificados e chaves simétricas que precisam ser instalados no servidor para sua utilização.

O uso dessas funções é bem simples e prático:

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:

Tenha apenas atenção quanto ao NVARCHAR. Chave, Mensagem e o formato de retorno tem que ser do mesmo tipo: ou tudo é VARCHAR ou NVARCHAR. Não pode misturar, senão não vai conseguir utilizar essas funções:

SQL 2017+ e Retrocompatibilidade

Disponível desde o SQL Server 2008, as funções ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE são ótimas soluções simples e fáceis de implementar para melhorar a segurança do armazenamento de senhas e informações sensíveis e elas utilizam internamente, o algoritmo SHA1 para gerar o hash da senha e o 3DES-128 para a criptografia, o que já é uma boa proteção. Na versão 2017 em diante, a Microsoft alterou o código dessas funções para aumentar a segurança, e agora elas utilizam o SHA256 para o hash da senha e o AES-256 para criptografia.

Embora seja um ponto positivo do ponto de vista de segurança das senhas, isso acabou gerando um problema para quem tem as versões antigas do SQL Server e interage com as versões mais novas, que eu vou demonstrar agora.

No SQL 2008, vou gerar um hash para a senha “Dirceu Resende”:

Com o hash gerado “0x01000000940908D92A0826928234D7DD6E3FA27AF5D818FC7654AE39AC090D30DFE5C43C” vou tentar recuperar a mensagem original num SQL Server 2017:

Bom, tudo certo! Maravilha! Pelo visto, a função do SQL Server 2017 consegue “ler” o formato novo e o antigo. Agora vamos fazer o inverso: Vou gerar uma hash no SQL Server 2017:

Nossa, que hash grande.. Vou utilizar a hash “0x02000000F955D4A111500CD5F9287228B3FC927FB7640202F2EC85F6BBA2074CD2434F39FE98CFFAD46718119ABC20AFAE058E2B” para recuperar a senha original do SQL Server 2008:

Vixi.. Retornou NULL! E agora?? Bom, não tem retrocompatibilidade.. E o SQL Server 2008 já não é mais suportado pela Microsoft e provavelmente, não terá um update para incluir isso. Tem solução??

Mas é claro que SIM! E o responsável por isso é o nosso poderoso SQLCLR! Utilizando uma função escrita em C#, podemos disponibilizar uma nova função no SQL Server 2008 que consiga recuperar a senha tanto das versões novas quanto das antigas:

A Hash gerada no próprio SQL Server 2008 também é compatível com essa função:

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:

Quer criar esse SQLCLR no seu ambiente? Já aviso que terá que criar o assembly como UNSAFE (Unrestricted), devido ao uso da biblioteca System.Security.Cryptography.Aes, pois o HPA (atributo de proteção de host) “MayLeakOnAbort” não são permitidos no modo Safe ou External Access.

Dado o aviso, vamos à criação do assembly e da função 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]

Código-fonte da função caso você mesmo queira compilar e fazer o deploy do projeto:

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();

}

Bom pessoal, espero que vocês tenham gostado desse post bem técnico e complicado.. rs
Um grande abraço e até o próximo artigo!

Referências:

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Criptografando senhas com as funções de criptografia simétrica ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE

Um resumo sobre segurança de senhas

1) Nenhuma

2) Conversão

3) Conversão customizada

4) Algoritmos de criptografia simétrica

5) Algoritmos de HASH

Utilizando as funções ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE

SQL 2017+ e Retrocompatibilidade

You may also like...

Deixe uma respostaCancelar resposta

Um resumo sobre segurança de senhas

1) Nenhuma

2) Conversão

3) Conversão customizada

4) Algoritmos de criptografia simétrica

5) Algoritmos de HASH

Utilizando as funções ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE

SQL 2017+ e Retrocompatibilidade

You may also like...

SQL Server – Como identificar os parâmetros de inicialização da instância utilizando T-SQL

Como validar inscrição estadual usando função T-SQL no SQL Server

SQL Server – Como exportar e importar arquivos com dados tabulares (Ex: CSV) utilizando o CLR (C#)

Deixe uma respostaCancelar resposta