THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Coles: Sergeant SQL

SQL Server development, news and information from the front lines

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

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;

GO

 

-- Switch to the new test database

USE Test;

GO

 

-- Create database master key

CREATE MASTER KEY

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

GO

 

-- Create a test certificate

CREATE CERTIFICATE TestCert

   WITH SUBJECT = 'Test Certificate',

   EXPIRY_DATE = '20151231';

GO

 

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

-- it's installed correctly

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

GO

 

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

 

USE Test;

GO

 

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

-- directory exists

BACKUP CERTIFICATE TestCert

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

GO

 

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)

AS

BEGIN

      SELECT CAST

            (

                  DECRYPTBYCERT

                  (

                        CERT_ID('TestCert'),

                        @EncryptedPassword

                  ) AS nvarchar(100)

            ) AS DecryptedPassword;

END;

GO

 

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.

 

 

Comments

 

merrillaldrich said:

I know this doesn't directly relate to the original poster's question, but for the more general case: if you have encrypted data in some .NET application, and you want to store a decrypted version of that data in a SQL Server table, would it be simpler/safer to decrypt the data on the client side and then transmit it to SQL server using SQL server's simple/built in encrypted connection?

January 29, 2010 1:20 PM
 

Mike C said:

It would definitely be easiest to secure your communications with SSL/TLS between clients and SQL Server, and you secure a lot more info (you secure all communications).

You incur a lot of overhead for encrypting all client/server communications; probably a lot more than you want to if all you really want to secure are short strings occasionally passed from client to server.  In this instance the OP just wanted to secure the password for the CREATE LOGIN statements his client code is generating.

But for your more specific question, I would suggest not even decrypting the data to store in the SQL Server table. If your client app already knows how to encrypt/decrypt its own data, just send it and store it in client-encrypted format in the database.

January 29, 2010 8:19 PM
 

Richard said:

There's a simpler way to reverse an array in .NET without a loop - the static Array.Reverse method:

byte[] Bytes = ...;

Array.Reverse(Bytes);

return Bytes;

Also, would it not be possible to create a SQLCLR function to decrypt the data within SQL?

February 8, 2010 8:29 AM
 

Mike C said:

Hi Richard,

I was looking for a Reverse method while writing this, and I guess I overlooked Array.Reverse.

You could create a SQLCLR function to decrypt the data within SQL, but there's really no advantage.  You would need to either: (a) open up a context connection to use the T-SQL functionality, or (b) load the certificate private key from somewhere into SQLCLR to make it work.  

Option (a) adds unnecessary overhead since you're using the certificate that SQL Server is managing already, the one that's directly accessible from T-SQL.  You're just adding a layer of overhead since you need to open up a context connection from within CLR.

Option (b) means you are now planning to manage your encryption certificate yourself instead of letting SQL Server do it.  Do you want to store the private key on the local hard drive?  That's really a big advantage of SQL Server encryption--SQL Server manages your encryption keys and certificates for you.  Key management is one of the hardest tasks in encryption, and not one I would want to take on myself if it can be avoided.

February 21, 2010 5:51 PM
New Comments to this post are disabled

This Blog

Syndication

News

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement