Encrypt it in .NET/Decrypt it on SQL Server?

Mike C — Fri, 29 Jan 2010 16:52:00 GMT

A common question on the newsgroups is "how do you encrypt data in a .NET [or other] client application and then decrypt it on SQL Server [or vice versa]?" I actually ran down my list of answers to someone who asked this in the newsgroups a few weeks ago. I won’t get into the details, but the answers all pretty much say the same thing -- theoretically you could make it work (with a lot of assumptions on your part), but it won’t be easy -- and probably not worth the investment of time and energy, to be honest. Now it’s time to change my answer.

You see, when this question is brought up the people who ask usually make a specific point to ask about symmetric encryption (AES, Triple DES, etc.). You can’t easily make the “encrypt on client/decrypt on server” scenario work with symmetric encryption because SQL Server doesn’t let you import or export symmetric keys.

Asymmetric encryption is an entirely different beast. Someone asked about sending a password to SQL Server securely (not in plain text) for FIPS compliance here.

Since passwords are usually pretty short I told the poster asymmetric encryption might solve his problem. Then I decided to prove it. The code below (both T-SQL and .NET) demonstrates. All of the steps should be performed in order. The .NET code at the end needs to be put into a C# Windows Forms or Console project of your own (.NET 2.0 or higher only).

1) T-SQL: Create a test database, database master key, and certificate on SQL Server

-- Create a test database

CREATE DATABASE Test;

-- Switch to the new test database

USE Test;

-- Create database master key

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'P@$$w0rd';

-- Create a test certificate

CREATE CERTIFICATE TestCert

WITH SUBJECT = 'Test Certificate',

EXPIRY_DATE = '20151231';

-- This statement just tests the new certificate to make sure

-- it's installed correctly

SELECT ENCRYPTBYCERT(CERT_ID(N'TestCert'), 'abcdef')

2) T-SQL: Backup the certificate (public key only) to a .cer file in the file system

USE Test;

-- Backup the certificate to a .CER file; assumes c:\Temp

-- directory exists

BACKUP CERTIFICATE TestCert

TO FILE = 'c:\Temp\TestCert.cer';

3) T-SQL: Create a stored procedure that uses the certificate to decrypt a binary string passed into it

-- This procedure uses the SQL certificate to decrypt the

-- encrypted password

CREATE PROCEDURE dbo.DecryptPasswordWithSqlCert

@EncryptedPassword binary(128)

BEGIN

SELECT CAST

(

DECRYPTBYCERT

(

CERT_ID('TestCert'),

@EncryptedPassword

) AS nvarchar(100)

) AS DecryptedPassword;

END;

4) .NET: Create an X509Certificate2 object and use the public key to encrypt a string password; Call the stored procedure with the encrypted password and use the SQL Server certificate to decrypt it

// Load the certificate from the file system and create an RSACryptoServiceProvider

// from the certificate Public Key to encrypt data

private RSACryptoServiceProvider GetCryptoProvider

(

string CertificateFilename

)

{

X509Certificate2 cert = new X509Certificate2(CertificateFilename);

RSACryptoServiceProvider r = (RSACryptoServiceProvider)cert.PublicKey.Key;

return r;

}

// Encrypts string password (Unicode) with the RSACryptoServiceProvider

private byte[] EncryptPasswordWithFileCert

(

RSACryptoServiceProvider Rsa,

string Password

)

{

// Results of RSA encryption are limited to 128 bytes

byte[] Bytes = Rsa.Encrypt(Encoding.Unicode.GetBytes(Password), false);

byte[] Result = new byte[128];

// Need to reverse the order of the encrypted bytes for SQL Server encryption

for (int i = 127; i >= 0; i--)

{

Result[127 - i] = Bytes[i];

}

return Result;

}

// Connects to server/database and executes stored procedure

// The stored procedure decrypts the encrypted password you pass in

private string DecryptPasswordWithSqlCert

(

string ConnectionString,

byte[] EncryptedPassword

)

{

string DecryptedPassword = "";

using (SqlConnection Con = new SqlConnection(ConnectionString))

{

Con.Open();

using (SqlCommand Cmd = new SqlCommand("dbo.DecryptPasswordWithSqlCert", Con))

{

Cmd.CommandType = CommandType.StoredProcedure;

// Pass in the encrypted password

Cmd.Parameters.Add("@EncryptedPassword", SqlDbType.Binary, 128).Value = EncryptedPassword;

// Return the decrypted password as a string

DecryptedPassword = (string)Cmd.ExecuteScalar();

}

return DecryptedPassword;

}

// This is my connection string

private string SqlConnString = "DATA SOURCE=(local);INITIAL CATALOG=Test;INTEGRATED SECURITY=SSPI;";

private void QuickTest

{

// Create RSACryptoServiceProvider from .cer file

RSACryptoServiceProvider Rsa = GetCryptoProvider("C:\\Temp\\TestCert.cer");

// Encrypt the password with the file certificate public key

byte[] EncryptedPassword = EncryptPasswordWithFileCert(Rsa, "Test*Password123");

// Decrypt the password on the server

string DecryptedPassword = DecryptPasswordWithSqlCert(SqlConnString, EncryptedPassword);

// Output the decrypted password

Console.WriteLine(DecryptedPassword);

}

A couple of items worth noting about this code:

* SQL Server (and .NET) asymmetric encryption function have a strict limit of 128 bytes that can be returned by the encrypted result. The encryption functions add 11 bytes of padding, so you’re automatically down to 117 bytes of plain text that can be encrypted or 58 Unicode characters. You can work around these limitations by encrypting your data in chunks, but I wouldn’t advise it -- asymmetric encryption is expensive in terms of time and resources.

* For some reason SQL Server needs the .NET asymmetric encryption results reversed, byte-for-byte. Not sure of the exact reason for this, but it’s simple enough to handle (as I did in the code) with a for loop on the .NET side.

* The BACKUP CERTIFICATE statement in the sample code only exports the certificate Public Key, which is used for encryption. You can also export the Private Key (for decryption) if you wish, but there’s no need in this scenario. You’ll need to look up the syntax of the BACKUP CERTIFICATE statement in BOL if you need to export your certificate’s Private Key.

* The .NET X509Certificate2 class is used in the code sample, and it is only supported on .NET 2.0 and higher. The older .NET X509Certificate class won’t do the job because it is lacking some features that this code sample requires.

Search results matching tags 'sql', 'c#', 'encryption', 'security', and 'database'

Encrypt it in .NET/Decrypt it on SQL Server?