Creating Function to use C# Class Library Method

Question

Creating Function to use C# Class Library Method

chris_barnhart

Ten Centuries

Points: 1095
More actions
October 15, 2019 at 9:52 pm

#3689121

Hello All,
We recently acquired an organization who wrote some C# libraries to encrypt/decrypt their data at the Application level, then store the decrypted value(s) into a MySQL database. My task is now to get said Data onto our Reporting SQL Server and store as decrypted.
What I's like to do is create a SQL CLR function that calls the DECRYPT method from the Class Library and return the decrypted value. So, when I query the MySQL database using an open query I set up from SQL Server, I can just do something like "select fnDecrypt(key, column) from table" and get back the decrypted value for said column. The DECRYPT function takes two parameters, the Encryption Key and the Value you want to decrypt...havent decided if I want to store the key and retrieve or embed statically in function for now until I get more familiar. Using VS2017, I've created a SQL Server Database Project and added the C# CLR User Defined Function and got all References and Assemblies from the Class libraries resolved, but now I'm kind of stuck as to how to utilize the method.
Unfortunately, I'm not a developer nor do I have any C# experience at all up to this point...the furthest I've gotten over the past couple days is creating a little Console application that uses the decrypt method by passing in the values hard-coded so I could know the encrypted value could indeed be decrypted by said Library Method and Key provided.
Does anyone have any templates, example or pointers on how to accomplish this? Everything I've come across so far is how to publish the CLR or little simple code to return Hello Word from the function or something. Any shoves in the righth direction or links to previous answers would be appreciated. The image is from the little console I did just to make sure the method worked on one of the encrypted values stored in the DB.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

chris_barnhart Ten Centuries Points: 1095 More actions · Answer 1

Well, I just found this article which seems to make it really easy based on what I got so far. Looks like I can just modify my console app a little bit and then call it with the function. I figured you;d have to create more classes and calls, etc.. like the blank template in the CLR UDF .cs starts out with, which I'm still a bit lost in doing so---when to Public Partial Class, Static Void, etc... and when to use what functions in combination with the methods and variables---that's really the part I'm still lost on with the myriad of options and as a newbie, not understanding when to invoke what and where. I wish I had time to learn the language from start to finish, but you know how time-sensitive projects go---just get it done quickly.

https://www.sqlservercentral.com/articles/how-to-compile-deploy-and-consume-a-sql-server-clr-assembly

Steve Collins SSCrazy Points: 2069 More actions · Answer 2

chris_barnhart wrote:

..., then store the decrypted value(s) into a MySQL database. My task is now to get said Data onto our Reporting SQL Server and store as decrypted.

The MySQL is not encrypted? Could you not export/import to SQL Server?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

chris_barnhart Ten Centuries Points: 1095 More actions · Answer 3

The data is stored on a MySQL database as encrypted. The encryption is not done by MySQL, but instead an application using the C# libraries encrypts the data before it stores onto the MySQL database.

I am copying all the data onto a SQL Server Reporting Instance, but there is no reason to leave it encrypted for this purpose, so I am importing from MySQL to SQL Server and trying to decrypt it along the way. i've tried using SQL Server native decryption, but haven't had much luck since I wasnt putting the correct value as the encrypted value, as the encrypted data is also hashed and salted. I wasn't taking into consideration the Convert.To/FromBase64 calls that are used with the decryption method, so I wasnt converting to or from Base64 when i was performing the substring, Take or TakeLast functions to get just the unhashed and unsalted encrypted value. Since the libraries also perform hash checking and easily remove the hash and salt, that's when I decided it would be better just to use the C# library instead of forcing SQL Server to try and do it. Didnt seem fruitful to try reinventing a wheel that was at our disposal. Its just the C# stuff I'm not familiar with---I can read the code just fine, but developing is a different story---always easier to read code than to generate from scratch and know what to use, when and how.

Hope this addresses your question.

Thanks,

Chris

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 4

Hi Chris. If you haven't even gotten to the deployment part yet, that is even more fun (yes, that is sarcasm). You will want to look at the following two posts of mine:

SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1

SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

Solution 1 is more ideal, but Solution 2 is a bit easier so might be the way to go. Both include working example code so that could help with your other questions.

Take care,

Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

chris_barnhart Ten Centuries Points: 1095 More actions · Answer 5

Solomon Rutzky wrote:

Hi Chris. If you haven't even gotten to the deployment part yet, that is even more fun (yes, that is sarcasm). You will want to look at the following two posts of mine:
SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2
Solution 1 is more ideal, but Solution 2 is a bit easier so might be the way to go. Both include working example code so that could help with your other questions.
Take care,
Solomon..

Thanks Solomon, I'll take a more in depth look later tonight or in the morning...I stayed up all night to step through both libraries and understand what they were doing before creating my little console utility, so probably need a fresh start. Never looked at any C# up until a day or so ago 🙂 We are currently on SQL 2016 SP2 Standard, but I do understand the importance of making everything ready for the next upgrade, so this is good stuff to know and follow.

Steve Collins SSCrazy Points: 2069 More actions · Answer 6

chris_barnhart wrote:

I wasn't taking into consideration the Convert.To/FromBase64 calls that are used with the decryption method, so I wasnt converting to or from Base64 when i was performing the substring, Take or TakeLast functions to get just the unhashed and unsalted encrypted value.

The code below (copy, paste, run) demonstrates:

Create 2 guids (a salt and a string) and concatenate them together (into @string_to_convert)
Convert @string_to_convert into binary base64 converted text (into @binary_base64)
Converts from binary base64 back into varchar(max) and substrings to get original salt and string

Maybe this addresses the missing string conversions in Sql

declare
  @saltchar(36)=cast(newid() as char(36)),
  @stringchar(36)=cast(newid() as char(36));
select @salt salt, @string string;
declare
  @string_to_convertvarchar(max)=concat(@salt, @string),
  @binary_base64varchar(max),
  @unconverted_stringvarchar(max);

/* convert from varchar to binary base 64 (varchar) */
select @binary_base64=(select cast(@string_to_convert as varbinary(max)) for xml path(''), binary base64);
select @binary_base64 converted_string;

/* convert from binary base 64 (varchar) to varchar */
select @unconverted_string=(select cast(cast(@binary_base64 as xml ).value('.','varbinary(max)') as varchar(max)));
select left(@unconverted_string, 36) salt, right(@unconverted_string, 36) string;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

chris_barnhart Ten Centuries Points: 1095 More actions · Answer 7

Thanks SC, but it was more like the type of transformations below...and excitedly enough after working through a few issues, I was able to create the SQL CLR UDF and it actually works and decrypts the data when passing the field from the query into it.

I appreciate everyone's feedback and help!

// Strip HMAC off!

var cypherText = stringToDecrypt.Substring(64, stringToDecrypt.Length - 64);

var encodedCypherText = Convert.FromBase64String(cypherText);

//Peel the Salt off the front of the String so we can Decrypt

var stringToDecodeEncoded = Convert.FromBase64String(Convert.ToBase64String(encodedCypherText));

var stringToDecodeFormed = TakeLastAaron(stringToDecodeEncoded, stringToDecodeEncoded.Length - 16).ToArray();

// Decrypt the string and then return it!

return Encoding.UTF8.GetString(cipher.TransformFinalBlock(stringToDecodeFormed, 0, stringToDecodeFormed.Length));