Reputation: 1307

Decrypt AES 128 on T-SQL

I have a database that is currently using AES 128. The database has about 8 million records, and what the client wants is to decode the passwords and hash them instead so the passwords cannot be decrypted. This is a web app with data stored on a remote server. I tried using a web app to do the conversion, but it keeps timing out. Since this is 8 mil, it will take a while to go through all the items, so my next idea was to get SQL do do the decryption and hashing. I could let it run for the next few days.

The problem I am having is that each column has the encrypted password with a unique salt. I can't find a function to decrypt the password using the encrypted password and salt. Is there a function? Even third party? Is there a better way to go about this?

Thanks!

Upvotes: 3

Answers (3)

Nicholas Carey

Reputation: 74385

The easiest/only way to do this in SQL Server would by to write a CLR User-Defined Function (UDF) in C#. See

for more details. If it was me, I'd add a new column to contain the new password hash and run an update statement periodically to construct the new password hash, something like this:

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

where DecryptAndHash() is your CLR UDF. Once the transform is complete, you should be free to drop the old column and roll out the update to use the new authentication logic.

Probably want to put an trigger on the table to keep the hash in sync with the encrypted password in case anybody changes their password while all this is going on.

FWIW, the code shouldn't be much more complicated than

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

        }

    }
}

Implementation of the internals of EncryptionEngine is left as an exercise for the reader.

Upvotes: 3

Can Gencer

Reputation: 8885

You can take a look at the authentication of your application, and see from the source code how it authenticates the password. There you should see that the app is encrypting the password and comparing it with the encrypted value in the database. The encryption function there should be easy to reverse. A salt is not usually used along with encryption, it is used when generating a hash to protected against lookup attacks.

I don't think SQL can do decryption on AES128, not in a straightforward manner anyway. But you can write a simple .NET app using the standard APIs that will decrypt each password, hash it with the salt and write it back to the database.

Upvotes: 2

Aviad P.

Reputation: 32680

The point with storing encrypted passwords is that they cannot be decrypted. The encryption is in fact made on some constant (+salt) using the password as the key.

So basically the goal has already been met, you cannot decrypt the "passwords" to get their clear text versions.

Upvotes: -1

Decrypt AES 128 on T-SQL

Answers (3)

Related Questions