title: AES encrypt in c# decrypt in T-SQL
body: <p>I have written the following code to decrypt some sensitive data, in most of the cases i need to query data using T-SQL where i can't decrypt the the data that is encrypted by this code. so my question is this how can i write a function in T-SQL that work the same way as like it work in C#, I will consume that in Stored procedures.</p>
  
  <p>thanks in Advance</p>
  
  <p>Encryption Function:</p>
  
  <pre><code>public static string Encrypt(string text)
      {
          if (string.IsNullOrEmpty(EncryptionKey))
              return string.Empty;
          if (string.IsNullOrEmpty(text))
              return string.Empty;
          var clearBytes = Encoding.Unicode.GetBytes(text);
          using (var encryptor = Aes.Create())
          {
              var pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[]
              {
                  0x49,
                  0x76,
                  0x61,
                  0x6e,
                  0x20,
                  0x4d,
                  0x65,
                  0x64,
                  0x76,
                  0x65,
                  0x64,
                  0x65,
                  0x76
              });
              encryptor.Key = pdb.GetBytes(32);
              encryptor.IV = pdb.GetBytes(16);
              using (var ms = new MemoryStream())
              {
                  using (var cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
                  {
                      cs.Write(clearBytes, 0, clearBytes.Length);
                  }
                  text = Convert.ToBase64String(ms.ToArray());
              }
          }
          return text;
      }
  </code></pre>
  
  <p>Decryption Function:</p>
  
  <pre><code>public static string Decrypt(string text)
      {
          if (string.IsNullOrEmpty(EncryptionKey))
              return string.Empty;
          if (string.IsNullOrEmpty(text))
              return string.Empty;
          var cipherBytes = Convert.FromBase64String(text);
          using (var encryptor = Aes.Create())
          {
              var pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[]
              {
                  0x49,
                  0x76,
                  0x61,
                  0x6e,
                  0x20,
                  0x4d,
                  0x65,
                  0x64,
                  0x76,
                  0x65,
                  0x64,
                  0x65,
                  0x76
              });
              encryptor.Key = pdb.GetBytes(32);
              encryptor.IV = pdb.GetBytes(16);
              using (var ms = new MemoryStream())
              {
                  using (var cs = new CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write))
                  {
                      cs.Write(cipherBytes, 0, cipherBytes.Length);
                  }
                  text = Encoding.Unicode.GetString(ms.ToArray());
              }
          }
          return text;
      }
  </code></pre>