我有一个当前正在使用AES 128的数据库.该数据库有大约800万条记录,而客户端希望解密密码并对其进行哈希处理,因此密码无法解密.这是一个Web应用程序,数据存储在远程服务器上.我尝试使用Web应用程序进行转换,但是它总是超时.由于这是800万,因此需要花一些时间来浏览所有项目,所以我的下一个想法是让SQL执行解密和哈希处理.我可以让它继续运行几天.

我遇到的问题是,每一列都有带有唯一盐的加密密码.我找不到使用加密的密码和盐解密密码的功能.有功能吗？甚至第三方？有没有更好的方法来解决这个问题？

谢谢！

解决方法:

在SQL Server中执行此操作的最简单/唯一方法是在C#中编写CLR用户定义函数(UDF).看到

> SQL Server 2005: Creating Your First C# CLR UDF in 10 Easy Steps (One of Which Includes Partying)
> SQLCLR – Create CLR User-Defined Function ( UDF ) – Check Constraint on EmailAddress Column Using RegEx
> MSDN: CLR User-Defined Functions (ADO.NET)

更多细节.如果是我,我将添加一个新列以包含新的密码哈希,并定期运行一条update语句以构造新的密码哈希,如下所示：

update top 10000 dbo.users
set hashedPassword = DecryptAndHash( encryptedPassword )
where hashedPassword is null

其中DecryptAndHash()是您的CLR UDF.转换完成后,您应该可以随意删除旧列并推出更新以使用新的身份验证逻辑.

可能想在表上放置一个触发器,以使哈希与加密密码保持同步,以防万一有人在更改密码的同时进行操作.

FWIW,代码不应该比它复杂得多

using System;
using Microsoft.SqlServer.Server;

namespace Sandbox
{
    public static class EncryptionFunctions
    {

        /// <summary>
        /// Encrypts a string
        /// </summary>
        /// <param name="plainText"></param>
        /// <returns>varbinary</returns>
        [SqlFunction]
        public static byte[] Encrypt( string plainText )
        {
            byte[] cipherText ;
            using ( EncryptionEngine cipher = EncryptionEngine.GetInstance() )
            {
                cipherText = cipher.Encrypt( plainText ) ;
            }
            return cipherText ;
        }

        /// <summary>
        /// Decrypts a previously encrypted varbinary
        /// </summary>
        /// <param name="cipherText"></param>
        /// <returns>string</returns>
        [SqlFunction]
        public static string Decrypt( byte[] cipherText )
        {
            string plainText ;
            using ( EncryptionEngine cipher = EncryptionEngine.GetInstance() )
            {
                plainText = cipher.Decrypt( cipherText ) ;
            }
            return plainText ;
        }

        /// <summary>
        /// Compute the secure hash of a [plaintext] string
        /// </summary>
        /// <param name="plainText"></param>
        /// <returns> varbinary </returns>
        [SqlFunction]
        public static byte[] SecureHash( string plainText )
        {
            byte[] hash ;
            using ( EncryptionEngine cipher = EncryptionEngine.GetInstance() )
            {
                hash = cipher.ComputeSecureHash( plainText ) ;
            }
            return hash ;
        }

        /// <summary>
        /// Convenience wrapper method to take a previously encrypted string, decrypt it and compute its secure hash
        /// </summary>
        /// <param name="cipherText"></param>
        /// <returns>varbinary</returns>
        [SqlFunction]
        public static byte[] DecryptAndHash( byte[] cipherText )
        {
            byte[] hash ;
            using ( EncryptionEngine cipher = EncryptionEngine.GetInstance() )
            {
                hash = cipher.ComputeSecureHash( cipher.Decrypt( cipherText ) ) ;
            }
            return hash ;
        }

        /// <summary>
        /// The core encrypt/decrypt/hash engine
        /// </summary>
        private class EncryptionEngine : IDisposable
        {
            /// <summary>
            /// get an instance of this class
            /// </summary>
            /// <returns></returns>
            public static EncryptionEngine GetInstance()
            {
                return new EncryptionEngine() ;
            }

            #region IDisposable Members

            /// <summary>
            /// Dispose of any unmanaged resources
            /// </summary>
            public void Dispose()
            {
                throw new NotImplementedException();
            }

            #endregion

            /// <summary>
            /// Encrypt a plaintext string
            /// </summary>
            /// <param name="plainText"></param>
            /// <returns></returns>
            internal byte[] Encrypt( string plainText )
            {
                throw new NotImplementedException();
            }

            /// <summary>
            /// Decrypt an encrypted string
            /// </summary>
            /// <param name="cipherText"></param>
            /// <returns></returns>
            internal string Decrypt( byte[] cipherText )
            {
                throw new NotImplementedException();
            }

            /// <summary>
            /// Compute the secure hash of a string
            /// </summary>
            /// <param name="plainText"></param>
            /// <returns></returns>
            internal byte[] ComputeSecureHash( string plainText )
            {
                throw new NotImplementedException();
            }

        }

    }
}

EncryptionEngine内部的实现留给读者练习.