SQL Server - How to encrypt and decrypt passwords (with Salt) using CLR (C #)

Views: 9.412 views

In this article

Reading Time: 4 minutes

Hello people,
Good night!

In this post, I will demonstrate how to encrypt and decrypt passwords with CLR (C #). For this, I will use the .NET Framework's MD5CryptoServiceProvider and TripleDESCryptoServiceProvider encryption algorithms, which allow you to use a (Salt) keyword to ensure that this key will be used in data encryption and can only be decrypted using that secret keyword.

If you don't know the CLR and would like to know more about this powerful feature of SQL Server, visit this link.

View source

Utils.cs Class:

using System.Security.Cryptography;
using System.Text;

namespace Bibliotecas.Model
{
    public static class Utils
    {
        
        public static TripleDES CreateDES(string key)
        {
            MD5 md5 = new MD5CryptoServiceProvider();
            TripleDES des = new TripleDESCryptoServiceProvider();
            des.Key = md5.ComputeHash(Encoding.Unicode.GetBytes(key));
            des.IV = new byte[des.BlockSize / 8];
            return des;
        }

    }

}

using System.Security.Cryptography;

using System.Text;

namespace Bibliotecas.Model

{

public static class Utils

{

public static TripleDES CreateDES(string key)

{

MD5 md5 = new MD5CryptoServiceProvider();

TripleDES des = new TripleDESCryptoServiceProvider();

des.Key = md5.ComputeHash(Encoding.Unicode.GetBytes(key));

des.IV = new byte[des.BlockSize / 8];

return des;

}

fncCrypt_Password.cs:

using System;
using System.Data.SqlTypes;
using System.Text;
using Bibliotecas.Model;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncCriptografa_String(SqlString Ds_Senha)
    {

        if (Ds_Senha.IsNull)
            return SqlString.Null;

        if (string.IsNullOrEmpty(Ds_Senha.Value))
            return SqlString.Null;

        const string salt = "http://dirceuresende.com/";
        var des = Utils.CreateDES(salt);
        var ct = des.CreateEncryptor();
        var input = Encoding.Unicode.GetBytes(Ds_Senha.Value);
        var retorno = "!=!enC!=!" + Convert.ToBase64String(ct.TransformFinalBlock(input, 0, input.Length));

        return retorno;

    }
    
}

using System;

using System.Data.SqlTypes;

using System.Text;

using Bibliotecas.Model;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncCriptografa_String(SqlString Ds_Senha)

{

if (Ds_Senha.IsNull)

return SqlString.Null;

if (string.IsNullOrEmpty(Ds_Senha.Value))

return SqlString.Null;

const string salt = "http://dirceuresende.com/";

var des = Utils.CreateDES(salt);

var ct = des.CreateEncryptor();

var input = Encoding.Unicode.GetBytes(Ds_Senha.Value);

var retorno = "!=!enC!=!" + Convert.ToBase64String(ct.TransformFinalBlock(input, 0, input.Length));

return retorno;

}

fncDescripts_String.cs

using System;
using System.Data.SqlTypes;
using System.Text;
using Bibliotecas.Model;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncDescriptografa_String(SqlString Ds_Senha)
    {

        if (Ds_Senha.IsNull)
            return SqlString.Null;

        if (string.IsNullOrEmpty(Ds_Senha.Value))
            return SqlString.Null;

        var senhaCriptografada = Ds_Senha.Value.Replace("!=!enC!=!", string.Empty);

        var b = Convert.FromBase64String(senhaCriptografada);

        const string salt = "http://dirceuresende.com/";
        var des = Utils.CreateDES(salt);
        var ct = des.CreateDecryptor();
        var output = ct.TransformFinalBlock(b, 0, b.Length);
        var retorno = Encoding.Unicode.GetString(output);

        return retorno;

    }
    
}

using System;

using System.Data.SqlTypes;

using System.Text;

using Bibliotecas.Model;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncDescriptografa_String(SqlString Ds_Senha)

{

if (Ds_Senha.IsNull)

return SqlString.Null;

if (string.IsNullOrEmpty(Ds_Senha.Value))

return SqlString.Null;

var senhaCriptografada = Ds_Senha.Value.Replace("!=!enC!=!", string.Empty);

var b = Convert.FromBase64String(senhaCriptografada);

const string salt = "http://dirceuresende.com/";

var des = Utils.CreateDES(salt);

var ct = des.CreateDecryptor();

var output = ct.TransformFinalBlock(b, 0, b.Length);

var retorno = Encoding.Unicode.GetString(output);

return retorno;

}

Examples of use

Encrypting Data

Decrypting Data

This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms

If you compiled your CLR assembly on a server running Windows Server, you will probably encounter this error message when trying to perform both of these post functions.

Msg 6522, Level 16, State 1, Line 6
The .NET Framework error occurred during execution of user-defined routine or aggregate “fncCrypto_String”:
System.InvalidOperationException: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms.
System.InvalidOperationException:
at System.Security.Cryptography.MD5CryptoServiceProvider..ctor ()
at Libraries.Model.Utils.CreateDES (String key)
at UserDefinedFunctions.fncCriptografa_String (SqlString Ds_Password)

This is because the algorithms using (MD5CryptoServiceProvider and TripleDESCryptoServiceProvider) do not conform to the Microsoft FIPS standard.

This problem is similar to what I reported in the post. SQL Server Reporting Services - Error Saving Report: System.InvalidOperationException This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms., but I will use a different solution for this case.

So that you can use these functions on your server, I will present two solutions to solve this problem:

1 Option: Disable FIPS
To disable this setting, simply access the Windows registry, find the “HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Control \ Lsa \ fipsalgorithmpolicy” setting and change the “Enabled” key to 0.

After that, restart the SQL Server service and you will be able to use these functions normally.

2 Option: Change the SQL Server sqlservr.exe.config File
One way to resolve this issue without disabling this security policy (if enabled, your company's security analyst probably thinks it's a good option) is by changing the file sqlservr.exe.config, usually located in C: \ Program Files \ Microsoft SQL Server \ MSSQL12.YourInstanceName \ MSSQL \ Binn and add the following configuration to the end of the file, within the session :

<enforceFIPSPolicy enabled="0" />

1	<enforceFIPSPolicy enabled="0" />

Your file should look like this:

After changing the file, restart the SQL Server service. This change will need to be made in all instances where you will use this function.

Another alternative is to make this change in machine.config (Usually located at C: \ Windows \ Microsoft.NET \ Framework64 \ v4.0.30319 \ Config), but keep in mind that using this solution will disable this option for ALL .NET server applications.

Supported and unsupported encryption algorithms

See also the list below to identify which algorithms are supported and which are not by the “System.Security.Cryptography” namespace:

Hash Algorithms
- HMACSHA1
- MACTripleDES
- SHA1CryptoServiceProvider

Symmetric Algorithms (Use same key for encryption and decryption)
- DESCryptoServiceProvider
- TripleDESCryptoServiceProvider

Asymmetric Algorithms (Use a public key for encryption and a private key for decryption)
- DSACryptoServiceProvider
- RSACryptoServiceProvider

Algorithms that do not support FIPS standards (and should not be used)
- HMACMD5
- HMACRIPEMD160
- HMACSHA256
- HMACSHA384
- HMACSHA512
- MD5CryptoServiceProvider
- RC2CryptoServiceProvider
- RijndaelManaged
- RIPEMD160Managed
- SHA1Managed

That's it folks!
I hope you enjoyed the post.

A hug and see you next.

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - How to encrypt and decrypt passwords (with Salt) using CLR (C #)

Examples of use

This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms

Supported and unsupported encryption algorithms

You may also like ...

Leave a Comment Cancel reply

Examples of use

This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms

Supported and unsupported encryption algorithms

You may also like ...

SQL Server - Using STRING_SPLIT to transform strings from a row into columns

SQL Server - How to Hide Databases from Unauthorized Users

SQL Server - How to recover the source code of deleted objects (View, Stored Procedure, Function and Trigger)

Leave a Comment Cancel reply