Click on the banner to learn about and purchase my database training on Azure

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

Views: 9.412 views
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:



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.
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.

SQL Server Reporting Services 2016 - Disable FIPS

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 :

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
- 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)
- MD5CryptoServiceProvider
- RC2CryptoServiceProvider
- RijndaelManaged
- RIPEMD160Managed
- SHA1Managed

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

A hug and see you next.