RC2 Crypt SQL Server and C#

Question

He was investigating how to implement an encryption algorithm in my application. He had seen the RC2.

On the one hand, I managed to encrypt and decrypt from c #.

On the other hand, I also managed to encrypt and decrypt from SQL Server.

But what encrypted from SQL server, I can not decipher it from C # and also when you encrypted from C # I can not decipher from SQL Server.

Of course the encryption key and initial vector are the same.

I guess what keeps the SQL server as data encryption is not simply the binary encryption but adds something more.

Edit: C# code:

public static byte[] Cifrar(string dato, string clave, string vectorInicial)
    {
        byte[] plainText = Encoding.ASCII.GetBytes(dato);
        byte[] keys = Encoding.ASCII.GetBytes(clave);
        byte[] vecI = Encoding.ASCII.GetBytes(vectorInicial);
        MemoryStream memdata = new MemoryStream();


        RC2CryptoServiceProvider rc2 = new RC2CryptoServiceProvider();
        ICryptoTransform transform;
        rc2.Mode = CipherMode.CBC;
        transform = rc2.CreateEncryptor(keys, vecI);

        CryptoStream encStream = new CryptoStream(memdata,transform,CryptoStreamMode.Write);


        encStream.Write(plainText, 0, plainText.Length);
        encStream.Close();

        return memdata.ToArray();
    }

    public static string Descrifrar(byte[] dato,string clave, string vectorInicial)
    {
        byte[] keys = Encoding.ASCII.GetBytes(clave);
        byte[] vecI = Encoding.ASCII.GetBytes(vectorInicial);
         MemoryStream memdata = new MemoryStream();

        RC2CryptoServiceProvider rc2 = new RC2CryptoServiceProvider();
        ICryptoTransform transform;
        rc2.Mode = CipherMode.CBC;
        transform = rc2.CreateDecryptor(keys, vecI);

        CryptoStream encStream = new CryptoStream(memdata,transform,CryptoStreamMode.Write);


        encStream.Write(dato, 0, dato.Length);
        encStream.Close();

        return Encoding.ASCII.GetString(memdata.ToArray());

    }

For encrypt i Use:

CifradoRC2.Cifrar("Perico", "sdsdfdsf323", "huihuihuih123");

T-SQL Code:

CREATE SYMMETRIC KEY NuevaClaveDeCifrado WITH IDENTITY_VALUE = 'huihuihuih123',ALGORITHM = RC2,KEY_SOURCE = 'sdsdfdsf323' ENCRYPTION BY Password = 'dsjdkflJ435907NnmM#sX003';

CREATE PROCEDURE OpenSqlKeys
AS
BEGIN    
     SET NOCOUNT ON;
     BEGIN TRY
             OPEN SYMMETRIC KEY NuevaClaveDeCifrado DECRYPTION BY PASSWORD ='dsjdkflJ435907NnmM#sX003';
     END TRY
     BEGIN CATCH
             -- Handle non-existant key here
     END CATCH
END
GO

CREATE FUNCTION Encrypt(@ValueToEncrypt varchar(max))
RETURNS varbinary(256)
AS
BEGIN    
-- Declare the return variable here    
DECLARE @Result varbinary(256)  
SET @Result = EncryptByKey(Key_GUID('NuevaClaveDeCifrado'), @ValueToEncrypt)     
-- Return the result of the function    
RETURN @Result
END
GO

CREATE FUNCTION Decrypt(@ValueToDecrypt varbinary(256))
RETURNS varchar(max)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(max)
    SET @Result = DecryptByKey(@ValueToDecrypt)
    -- Return the result of the function
    RETURN @Result
END

The binary result of encrypt "Perico" T-SQL: 0x009C6828879054489426381EFD2E3FD501000000266B3AF2ED7029C1F567AF8119C5D458FCDE1E984B2262A0

C#:

0x9FC66B77905837F5

Thanks!

Are you expecting both encryptions to generate the same result? — JeffO, Mar 13 '12 at 12:40
I need the same result to decipher from C # the SQL server and SQL Server from the C # — JoseMancebo, Mar 13 '12 at 12:46

Jaques · Answer 1 · 2012-03-14T13:04:03.480

I think the reason why the SQL and the RC2CryptoServiceProvider outputs are different is because you specify a password for the key that you create in SQL. That is why there is a lenght difference between your 2 results. I suggest that you use a CLR dll and you the C# code to do what you want.

Here is an example. I've used your code to create the functions

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text;
using System.Security.Cryptography;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBinary Cifrar(string dato, string clave, string vectorInicial)
    {
        byte[] plainText = Encoding.ASCII.GetBytes(dato);
        byte[] keys = Encoding.ASCII.GetBytes(clave);
        byte[] vecI = Encoding.ASCII.GetBytes(vectorInicial);
        MemoryStream memdata = new MemoryStream();


        RC2CryptoServiceProvider rc2 = new RC2CryptoServiceProvider();
        ICryptoTransform transform;
        rc2.Mode = CipherMode.CBC;
        transform = rc2.CreateEncryptor(keys, vecI);

        CryptoStream encStream = new CryptoStream(memdata, transform, CryptoStreamMode.Write);


        encStream.Write(plainText, 0, plainText.Length);
        encStream.Close();
        return new SqlBinary(memdata.ToArray());
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Descrifrar(byte[] dato, string clave, string vectorInicial)
    {
        byte[] keys = Encoding.ASCII.GetBytes(clave);
        byte[] vecI = Encoding.ASCII.GetBytes(vectorInicial);
        MemoryStream memdata = new MemoryStream();

        RC2CryptoServiceProvider rc2 = new RC2CryptoServiceProvider();
        ICryptoTransform transform;
        rc2.Mode = CipherMode.CBC;
        transform = rc2.CreateDecryptor(keys, vecI);

        CryptoStream encStream = new CryptoStream(memdata, transform, CryptoStreamMode.Write);


        encStream.Write(dato, 0, dato.Length);
        encStream.Close();

        return new SqlString(Encoding.ASCII.GetString(memdata.ToArray()));
    }
};

You need to create a SQL CLR Database Project. It will connect you to a database. Follow the steps and make sure you read the warnings. Don't go and stop the production server now. Add a new item, and select User-Defined Function. Paste the code in there. Compile it to make sure everything is fine, then Right click on the project, and select Deploy. You can test it then and see how it works, and if it is doing what you want.

I've tested on my machine, and it seems to do the job.

RC2 Crypt SQL Server and C#

1 Answers1