Use Cryptography.RSACryptoServiceProvider assembly in SQL Server 2017 for decryption

Question

In my asp.net MVC project, i created functions to encrypt/decryt via RSA which works fine as expected. I stored public/private keys on azure vault and accessing on runtime which is also fine.

I am using those functions to encrypt the data at application level and then save to database which is also fine. Now my requirement was to compare that encrypted data with the data comping from my website, so I created CLR function and ran into database.

But when i run that clr function, it throws below error.

Msg 6522, Level 16, State 1, Line 3
A .NET Framework error occurred during execution of user-defined routine or aggregate "Decrypt": 
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.KeyContainerPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: 
   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
   at System.Security.CodeAccessPermission.Demand()
   at System.Security.Cryptography.RSACryptoServiceProvider.ImportParameters(RSAParameters parameters)
   at System.Security.Cryptography.RSA.FromXmlString(String xmlString)
   at EMCDatabase.CryptographyHelper.Decrypt(Byte[] data, Int32 keySize, String publicAndPrivateKeyXml)
   at EMCDatabase.CryptographyHelper.Decrypt(String encryptedText, String PrivateKey)
   at UserDefinedFunctions.Decrypt(String SQL, String key)
.

Can anyone please help me fix this issue?

Below is my source code

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString Decrypt(string SQL, string key)
    {
        return CryptographyHelper.Decrypt(SQL, key);
    }
}

public static class CryptographyHelper
{
    private static readonly bool _optimalAsymmetricEncryptionPadding = false;
    //These keys are of 2048byte

    //private readonly static string PrivateKey = AzureKeyVault.GetRSAPrivateKey().Result;

    public static string Decrypt(string encryptedText, string PrivateKey)
    {
        int keySize = 0;
        string publicAndPrivateKeyXml = "";
        GetKeyFromEncryptionString(PrivateKey, out keySize, out publicAndPrivateKeyXml);
        var decrypted = Decrypt(Convert.FromBase64String(encryptedText), keySize, publicAndPrivateKeyXml);

        return Encoding.UTF8.GetString(decrypted);
    }

    private static byte[] Decrypt(byte[] data, int keySize, string publicAndPrivateKeyXml)
    {
        if (data == null || data.Length == 0) throw new ArgumentException("Data are empty", "data");
        if (!IsKeySizeValid(keySize)) throw new ArgumentException("Key size is not valid", "keySize");
        if (String.IsNullOrEmpty(publicAndPrivateKeyXml)) throw new ArgumentException("Key is null or empty", "publicAndPrivateKeyXml");
        using (var provider = new RSACryptoServiceProvider(keySize))
        {
            provider.FromXmlString(publicAndPrivateKeyXml);
            return provider.Decrypt(data, _optimalAsymmetricEncryptionPadding);
        }
    }

    private static int GetMaxDataLength(int keySize)
    {
        if (_optimalAsymmetricEncryptionPadding)
        {
            return ((keySize - 384) / 8) + 7;
        }
        return ((keySize - 384) / 8) + 37;
    }

    private static bool IsKeySizeValid(int keySize)
    {
        return keySize >= 384 && keySize <= 16384 && keySize % 8 == 0;
    }

    private static void GetKeyFromEncryptionString(string rawkey, out int keySize, out string xmlKey)
    {
        keySize = 0;
        xmlKey = "";
        if (rawkey != null && rawkey.Length > 0)
        {
            byte[] keyBytes = Convert.FromBase64String(rawkey);
            var stringKey = Encoding.UTF8.GetString(keyBytes);
            if (stringKey.Contains("!"))
            {
                var splittedValues = stringKey.Split(new char[] { '!' }, 2);
                try
                {
                    keySize = int.Parse(splittedValues[0]);
                    xmlKey = splittedValues[1];
                }
                catch (Exception e) { }
            }
        }
    }
}

public enum RSAKeySize
{
    Key512 = 512,
    Key1024 = 1024,
    Key2048 = 2048,
    Key4096 = 4096
}
public class RSAKeysTypes
{
    public string PublicKey { get; set; }
    public string PrivateKey { get; set; }
}
public class RSACryptographyKeyGenerator
{
    public RSAKeysTypes GenerateKeys(RSAKeySize rsaKeySize)
    {
        int keySize = (int)rsaKeySize;
        if (keySize % 2 != 0 || keySize < 512)
            throw new Exception("Key should be multiple of two and greater than 512.");
        var rsaKeysTypes = new RSAKeysTypes();
        using (var provider = new RSACryptoServiceProvider(keySize))
        {
            var publicKey = provider.ToXmlString(false);
            var privateKey = provider.ToXmlString(true);
            var publicKeyWithSize = IncludeKeyInEncryptionString(publicKey, keySize);
            var privateKeyWithSize = IncludeKeyInEncryptionString(privateKey, keySize);
            rsaKeysTypes.PublicKey = publicKeyWithSize;
            rsaKeysTypes.PrivateKey = privateKeyWithSize;
        }
        return rsaKeysTypes;
    }
    private string IncludeKeyInEncryptionString(string publicKey, int keySize)
    {
        return Convert.ToBase64String(Encoding.UTF8.GetBytes(keySize.ToString() + "!" + publicKey));
    }
}

Update When I create assembly with EXTERNAL_ACCESS, I get below error

Altering [EMCDatabase]...
(70,1): SQL72014: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 ALTER ASSEMBLY for assembly 'EMCDatabase' failed because assembly 'EMCDatabase' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.
(70,0): SQL72045: Script execution error.  The executed script:
ALTER ASSEMBLY [EMCDatabase]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001C83895E0000000000000000E00022200B0130000012000000060000000000004E310000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000FC3000004F00000000400000B002000000000000000000000000000000000000006000000C000000C42F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000054110000002000000012000000020000000000000000000000000000200000602E72737263000000B0020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000
An error occurred while the batch was being executed.

Answer 1

A CLR Asssembly must be at least EXTERNAL_ACCESS to get KeyContainerPermission.

See

EXTERNAL_ACCESS assemblies also have the following permissions and values:

Permission  Value(s)/Description
DistributedTransactionPermission    Unrestricted: Distributed transactions are allowed.
DNSPermission   Unrestricted: Permission to request information from Domain Name Servers.
EnvironmentPermission   Unrestricted: Full access to system and user environment variables is allowed.
EventLogPermission  Administer: The following actions are allowed: creating an event source, reading existing logs, deleting event sources or logs, responding to entries, clearing an event log, listening to events, and accessing a collection of all event logs.
FileIOPermission    Unrestricted: Full access to files and folders is allowed.
KeyContainerPermission  Unrestricted: Full access to key containers is allowed.
NetworkInformationPermission    Access: Pinging is permitted.
RegistryPermission  Allows read rights to HKEY_CLASSES_ROOT, HKEY_LOCAL_MACHINE, HKEY_CURRENT_USER, HKEY_CURRENT_CONFIG, and HKEY_USERS.
SecurityPermission  Assertion: Ability to assert that all the callers of this code have the requisite permission for the operation.
ControlPrincipal: Ability to manipulate the principal object.
Execution: Permission to execute managed code.
SerializationFormatter: Ability to provide serialization services.
SmtpPermission  Access: Outbound connections to SMTP host port 25 are allowed.
SocketPermission    Connect: Outbound connections (all ports, all protocols) on a transport address are allowed.
SqlClientPermission Unrestricted: Full access to the datasource is allowed.
StorePermission Unrestricted: Full access to X.509 certificate stores is allowed.
WebPermission   Connect: Outbound connections to web resources are allowed.

CLR Integration Code Access Security

Also this

return Encoding.UTF8.GetString(decrypted);

should probably be:

return Convert.ToBase64String(decrypted);