<div id="readability-page-1" class="page"><div>
<figure data-ke-mobilestyle="widthOrigin" data-origin-width="1080" data-origin-height="727"><img src="https://blog.kakaocdn.net/dn/baBm74/btsENR48kkB/LXvJfkNv8JFS1C5sC0H4bk/img.jpg" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" width="457" height="308" data-origin-width="1080" data-origin-height="727"></figure>


SQL Server 2017 이상 버전에서는 AES256 키를 사용하는 암호로 데이터를 암호화합니다.


사용법
<pre id="code_1707903454383" data-ke-language="sql" data-ke-type="codeblock"><code>EncryptByPassPhrase ( { 'passphrase' | @passphrase } 
 , { 'cleartext' | @cleartext } 
 [ , { add_authenticator | @add_authenticator } 
 , { authenticator | @authenticator } ] )</code></pre>


샘플 예제
<pre id="code_1707903365285" data-ke-language="csharp" data-ke-type="codeblock"><code>-- 암호화
DECLARE @EncryptionBytes varbinary(max) = ENCRYPTBYPASSPHRASE('symmetric_key_guid', N'대한민국 짝짝짝',1,'authenticator')
SELECT @EncryptionBytes

-- 복호화
DECLARE @DecryptBytes nvarchar(max) = DecryptByPassphrase('symmetric_key_guid', @EncryptionBytes, 1 ,'authenticator')
SELECT @DecryptBytes</code></pre>


암호화 반환값은 최대 크기가 8,000바이트인 바이트열입니다.



<h4 data-ke-size="size20"> C# 으로 복호화 하기</h4>

<a href="https://github.com/krcs/SQLServerCrypto" target="_blank" rel="noopener&nbsp;noreferrer">https://github.com/krcs/SQLServerCrypto</a>


암호화 된 값을, 복호화 할때 C# 에서 함수를 2가지 제공하며, Authenticator 필요에 따라 제공합니다.
<pre id="code_1707903609865" data-ke-language="csharp" data-ke-type="codeblock"><code>-- DecryptByPassPhraseWithoutVerification() 함수사용
var ciphertext = "0x0100000038C94F7223E0BA2F772B611857F9D45DAF781607CC77F4A856CF08CC2DB9DF14A0593259CB3A4A2BFEDB485C002CA04B6A98BEB1B47EB107";
var password = "symmetric_key_guid";
var decrypted = SQLServerCryptoMethod.DecryptByPassPhraseWithoutVerification(password, ciphertext);
Console.WriteLine(decrypted);



-- DecryptByPassPhrase() 함수사용
var passphrase = "symmetric_key_guid";
var authenticator = "authenticator";
var ciphertext = "0x02000000E920E0F4BDD60C4151FDE26351A1E6CC6C40DA16BBD1338FD66103F616D86D5A4BD8138821D0D849A320C9A5AEAEB28E50CCAAD8961888AF01D593472EEAA744";
var decrypted = SQLServerCryptoMethod.DecryptByPassPhrase(passphrase, ciphertext, 1, authenticator);
Console.WriteLine(decrypted);</code></pre>




참고사이트&nbsp;

<a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-2017" target="_blank" rel="noopener&nbsp;noreferrer">https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-2017</a>
<figure id="og_1707903522881" contenteditable="false" data-ke-type="opengraph" data-ke-align="alignCenter" data-og-type="website" data-og-title="ENCRYPTBYPASSPHRASE (Transact-SQL) - SQL Server" data-og-description="ENCRYPTBYPASSPHRASE (Transact-SQL)" data-og-host="learn.microsoft.com" data-og-source-url="https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-2017" data-og-url="https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-ver16" data-og-image="https://scrap.kakaocdn.net/dn/buXbsc/hyVjhejHvo/HVpwc00LAsrjyAErSnfx6k/img.png?width=1200&amp;height=630&amp;face=0_0_1200_630"><a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-2017" target="_blank" rel="noopener" data-source-url="https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-2017">

<div>
ENCRYPTBYPASSPHRASE (Transact-SQL) - SQL Server
ENCRYPTBYPASSPHRASE (Transact-SQL)
learn.microsoft.com
</div>
</a><img src="https://scrap.kakaocdn.net/dn/buXbsc/hyVjhejHvo/HVpwc00LAsrjyAErSnfx6k/img.png?width=1200&amp;height=630&amp;face=0_0_1200_630"></figure>
</div></div>

심재운 블로그

SQL Server 2017 이상 버전에서는 AES256 키를 사용하는 암호로 데이터를 암호화합니다.
 
 
사용법
EncryptByPassPhrase ( { 'passphrase' | @passphrase }   
    , { 'cleartext' | @cleartext }  
  [ , { add_authenticator | @add_authenticator }  
    , { authenticator | @authenticator } ] )
 
 
샘플 예제
-- 암호화
DECLARE @EncryptionBytes varbinary(max) =  ENCRYPTBYPASSPHRASE('symmetric_key_guid', N'대한민국 짝짝짝',1,'authenticator')
SELECT @EncryptionBytes
-- 복호화
DECLARE @DecryptBytes nvarchar(max) =  DecryptByPassphrase('symmetric_key_guid', @EncryptionBytes, 1 ,'authenticator')
SELECT @DecryptBytes
 
 
암호화 반환값은 최대 크기가 8,000바이트인 바이트열입니다.
 
 
 
 C# 으로 복호화 하기
 
https://github.com/krcs/SQLServerCrypto
 
 
암호화 된 값을, 복호화 할때 C# 에서 함수를 2가지 제공하며, Authenticator 필요에 따라 제공합니다.
-- DecryptByPassPhraseWithoutVerification() 함수사용
var ciphertext = "0x0100000038C94F7223E0BA2F772B611857F9D45DAF781607CC77F4A856CF08CC2DB9DF14A0593259CB3A4A2BFEDB485C002CA04B6A98BEB1B47EB107";
var password = "symmetric_key_guid";
var decrypted = SQLServerCryptoMethod.DecryptByPassPhraseWithoutVerification(password, ciphertext);
Console.WriteLine(decrypted);
-- DecryptByPassPhrase() 함수사용
var passphrase = "symmetric_key_guid";
var authenticator = "authenticator";
var ciphertext = "0x02000000E920E0F4BDD60C4151FDE26351A1E6CC6C40DA16BBD1338FD66103F616D86D5A4BD8138821D0D849A320C9A5AEAEB28E50CCAAD8961888AF01D593472EEAA744";
var decrypted = SQLServerCryptoMethod.DecryptByPassPhrase(passphrase, ciphertext, 1, authenticator);
Console.WriteLine(decrypted);
 
 
 
 
참고사이트 
 
https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-2017

SQL Server 2017 : 암복호화 EncryptByPassPhrase 와 DecryptByPassPhrase 사용하기

댓글