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.

Please Vote: Windowing Enhancements in SQL Server

Mike C — Fri, 20 Nov 2009 03:45:00 GMT

Back in January 2007, SQL guru Itzik Ben-Gan posted a series of MS Connect enhancement requests concerning windowing function enhancements. Those who have used the ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() functions on SQL 2005 and 2008, you already know how useful they are. They simplify code and can improve performance considerably over the alternatives, which usually include self-joins, temp tables and/or cursors in various combinations.

Well, the windowing functionality that you've seen in SQL 2005 and 2008 is just the tip of the iceberg. The ISO SQL standard actually defines several additional options for these functions that SQL Server doesn't yet support. These additional options allow you to do some pretty amazing calculations. The ROWS and RANGE window subclauses that the standard defines allows you to perform "sliding window" calculations; the ORDER BY clause for aggregate functions which simplifies complex running sum (and other) calculations.

As a SQL developer or DBA, these enhancements will simplify your life. But don't take my word for it - read Itzik's white paper at http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc. Then let Microsoft know you want these enhancements in SQL Server by voting on Itzik's enhancement requests at the following links:

Progressive Ordered Calculations: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254397

FIRST_VALUE, LAST_VALUE functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254395

DISTINCT clause for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254393

ROWS and RANGE window subclauses: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254392

Vector expressions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254391

TOP OVER: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390

LAG and LEAD functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254388

ORDER BY for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254387

Read Itzik's article, vote on the Connect items, and spread the word!

NJSQL Saturday Event (10/24)

Mike C — Tue, 06 Oct 2009 19:37:00 GMT

The NJSQL user group is hosting a one-day Saturday SQL Server event at the Microsoft Iselin offices. On October 24th Jacob Sebastian, Robert Pearl and I will be presenting on the following SQL Server topics:

* T-SQL Defensive coding and exception handling best practices
* Database forensics in SQL Server ("Who did it and ran?")
* SQL Server 2008 spatial data ("Where in the World?")

This one-day event is free, but registration is required. More details, including registration link at: http://njsql.org/Default.aspx

Search results matching tags 'sql' and 'database'

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

Please Vote: Windowing Enhancements in SQL Server

NJSQL Saturday Event (10/24)