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.

Hacking Social Security Numbers

Mike C — Mon, 13 Jul 2009 00:09:00 GMT

According to this paper from the Proceedings of the National Academy of the Sciences (PNAS), social security numbers (SSNs) are pretty easy for hackers, identity thieves, and other miscreants to predict based on publicly available data. I found this interesting partly because I just recently (a few months ago) wrote a chapter for a book discussing security for SSNs.

Here's the deal - all SSNs have a very regular structure that looks like this: xxx-yy-zzzz. With 9 numeric digits there are 1 billion possible combinations that can be assigned. And of course we have the same information that identity thieves have - the rules for SSN assignment are posted for the public at the Social Security Administration website.

Here are some of the key rules that determine how SSNs are assigned, summarized from the SSA website:

xxx is a 3-digit Area Number, and is assigned based on the ZIP Code from which the request to assign the SSN originates.
yy is a 2-digit Group Number, which is assigned in a predictable (nonconsecutive) order. The order of assignment of Group Numbers is also documented on the SSA website as well. It's always a number between "01" and "99".
zzzz is a 4-digit Serial Number, which is a number between "0001" and "9999".
There are a few stray SSNs that have been taken out of circulation for various reasons (used in marketing campaigns, etc.)
And of course no SSN is ever reassigned.

According to the rules a bad guy can narrow down the scope of his search substantially just by eliminating all SSNs that begin with 8xx, 9xx, 666, and 000. That eliminates a couple 100 million+. No SSNs have been assigned with a Group Number above 772, eliminating tens of millions in the 773 - 799 range. No SSNs have, or will be, assigned with Group Numbers of 00 or Serial Numbers of 0000, eliminating millions more. In addition the Group Numbers that have been assigned are available from the SSA website high group list, knocking hundreds of millions more possible SSNs off the list.

This is just the beginning -- it gets better:

If you know where a person applied for their SSN (in many cases this will be where they were born, or close to it) you can use the SSN Allocations list to narrow down the search substantially. In some cases this won't work though, since some parents don't apply for an SSN for their child immediately at birth.

All this is to show how an identity thief can use the location and approximate date of birth to accurately guess the first 5 digits of the SSN. The PNAS authors were able to correctly guess the first 5 digits of SSNs with a single try for 44% of their test records.

At the other end of the spectrum, identity thieves can use the SSA's Death Master File (DMF) to narrow down the last 4 digits (the Serial Number). The PNAS authors used the DMF to figure out statistical distributions of SSN Serial Numbers to dramatically narrow down the last 4 digits. They correctly guessed the complete SSNs for 8.5% of the test records with less than 1,000 attempts each; making the SSN for 8.5% of those tested less secure than a 4-digit ATM card PIN (in fact the authors compared it to an insecure 3-digit financial PIN).

The authors' testing showed that overall full SSNs can be guessed with an accuracy of between 0.08% to 10% with less than 1,000 attempts each. In rural areas they guessed complete SSNs at the rate of >60% for rural areas on the very first attempt.

To put some hard numbers to it, the authors estimated (based on various fairly reasonable assumptions), that an identity thief targeting a specific location (like a given state) could guess SSNs and obtain credit card accounts at the rate of about 47 per minute.

Makes you wonder how secure your SSN is, really.

Search results matching tags 'Security' and 'algorithms'

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

Hacking Social Security Numbers