locked
Encrypt in SQL Decrypt in .NET - How I made it RRS feed

  • Question

  • Hey. I want to encrypt data on SQL-Server and decrypt it on a .NET application.The best way in my Situation was to use Certificates.

    In my Test DB i had to create a Master-Key:

    CREATE MASTER KEY ENCRPTION 
    BY PASSWORD = 'TestEncryption1212'

    Then I created a certificate on SQL-Server

    CREATE CERTIFICATE TestCertificate1212
    WITH SUBJECT = 'TestEncryption1212'

    Next I exported it to the File-System:

    BACKUP CERTIFICATE TestCertificate1212
    TO FILE = 'C:\temp\TestCertificate1212.cer'
    WITH PRIVATE KEY (
    	FILE = 'C:\temp\TestPrivateKey1212.pvk',
    	ENCRYPTION BY PASSWORD = 'TestEncryption1212'
    	)


    If I add a

    DECRYPT BY PASSWORD = 'TestEncryption1212'

    I get an error which says that the Private Key is encrypted by the master password.

    I encrypt a Collumn in my Test DB

    ALTER TABLE dbo.Encrypt
    ADD EncryptedCol varbinary (256)
    
    UPDATE dbo.Encrypt
    SET EncryptedCol = 
    	ENCRYPTBYCERT(
    		CERT_ID('TestCertificate1212'),
    		ClearTextCol
    	)


    so far, so good.

    Next Step is to generate a PFX file which combines the certificate with the privatekey file

    I change the directory in cmd to the folder where the pvk2pfx.exe is and execute it:

    >cd "Program Files\Microsoft SDK
    s\Windows\v6.1\Bin"
    >pvk2pfx.exe -pvk C:\temp\TestPrivateKey1212.pvk -spc C:\temp\TestCertificate1212.cer -pfx C:\temp\TestPFX.pfx

    In my .NET application (C#-ConsoleApplication)  I get the Data via an SqlConnection ..  I pass the data on to a Function which I called

    public string DecryptDocIDWithFileCert(string pfxFilePath, byte[] EncryptedDocID)

    This function should open the Certificate and decrypt the Data and return the clear Text back to the Application:

    public string DecryptDocIDWithFileCert(string pfxFilePath, byte[] EncryptedDocID)
    {
    	string DecryptedDocID = "";
    	X509Certificate2 cert = new X509Certificate2(pfxFilePath, "TestEncryption1212");
    	if (cert == null)
    	{
    		throw new Exception("Certificate " + pfxFilePath + " Does not exist");
    	}
    	if (cert.HasPrivateKey)
    	{
    		RSACryptoServiceProvider RsaCSP = (RSACryptoServiceProvider)cert.PrivateKey;
    		byte[] ret = RsaCSP.Decrypt(EncryptedDocID, true);
    		if (ret == null)
    		{
    			throw new Exception("Decryption with RSA failed");
    		}
    		DecryptedDocID = System.Text.Encoding.UTF8.GetString(ret);
    	}
    	else
    	{
    		throw new Exception("Certificate " + pfxFilePath + " has no Private Key; ");
    	}
    
    	return DecryptedDocID;
    }


    When it comes to decrytion i get the Error:

    System.Security.Cryptography.CryptographicException: The specified network password is not correct.
    
       at System.Security.Cryptography.CryptographicException.ThrowCryptogaphicException(Int32 hr)
       at System.Security.Cryptography.X509Certificates.X509Utils._LoadCertFromFile(String fileName, IntPtr password, UInt32 dwFlags, Boolean persistKeySet, SafeCertContextHandle& pCertCtx)
       at System.Security.Cryptography.X509Certificates.X509Certificate.LoadCertificateFromFile(String fileName, Object password, X509KeyStorageFlags keyStorageFlags)
       at System.Security.Cryptography.X509Certificates.X509Certificate2..ctor(String fileName, String password)
       at TestDecryption.MyDecryptor.DecryptDocIDWithFileCert(String pfxFilePath, Byte[] EncryptedDocID) in C:\Users\developmentUser\Documents\Visual Studio 2008\Projects\TestDecryption\TestDecryption\MyDecryptor.cs:line 57
       at TestDecryption.Program.Main(String[] args) in C:\Users\developmentUser\Documents\Visual Studio 2008\Projects\TestDecryption\TestDecryption\Program.cs:line 37

    I am tired of searching for the error. Is there anybody out there who sees the error and can tell me what I'm doing wrong. I think it the problem is that the Private Key is encrypted with the Master Key and I can't decrypt it. I think all what i would need is an sql statement where I can set the password for the privat key without the Master Key ? Or am I on a completly wrong way?

    Please help me!


    UPDATE

    If I make my Certificate outside SQL_Server with the command:

    C:\temp\createCert>makecert -sv PrivateKey.pvk -n "cn=TestCertificate" TestCerti
    ficate.cer -b 01/01/2013 -e 01/01/2014 -sky Exchange -pe

    Run all my SQL Scripts again:

    ALTER TABLE dbo.Encrypt
    DROP COLUMN EncryptedCol
    
    DROP CERTIFICATE TestCertificate1212
    DROP MASTER KEY
    
    CREATE CERTIFICATE TestCertificate
    	FROM FILE = 'C:\TEMP\createCert\TestCertificate.cer'
    	WITH PRIVATE KEY (
    		FILE = 'C:\TEMP\createCert\PrivateKey.pvk',
    		ENCRYPTION BY PASSWORD = 'TestEncryption123'
    --		DECRYPTION BY PASSWORD = 'TestEncryption123'
    	)
    
    ALTER TABLE dbo.Encrypt
    ADD EncryptedCol varbinary (256)
    
    UPDATE dbo.Encrypt
    SET EncryptedCol = ENCRYPTBYCERT(CERT_ID('TestCertificate'),SecondCol)

    and then try to decrypt it i get serveral errors:

    eighter I use

    byte [] ret = RsaCSP.Decrypt(EncryptedDochID, true);

    and get:

    System.Security.Cryptography.CryptographicException: Error occurred while decoding OAEP padding.
       at System.Security.Cryptography.Utils._DecryptPKWin2KEnh(SafeKeyHandle hPubKey, Byte[] key, Boolean fOAEP, Int32& hr)
       at System.Security.Cryptography.RSACryptoServiceProvider.Decrypt(Byte[] rgb,Boolean fOAEP)
       at TestDecryption.MyDecryptor.DecryptDocIDWithFileCert(String pfxFilePath, Byte[] EncryptedDocID) in C:\Users\developmentUser\Documents\Visual Studio 2008\Projects\TestDecryption\TestDecryption\MyDecryptor.cs:line 71
       at TestDecryption.Program.Main(String[] args) in C:\Users\developmentUser\Documents\Visual Studio 2008\Projects\TestDecryption\TestDecryption\Program.cs:line 37
       

    Or I use:

    byte [] ret = RsaCSP.Decrypt(EncryptedDochID, false);

    and get:

    System.Security.Cryptography.CryptographicException: Bad Data.
       at System.Security.Cryptography.CryptographicException.ThrowCryptogaphicException(Int32 hr)
       at System.Security.Cryptography.Utils._DecryptKey(SafeKeyHandle hPubKey, Byte[] key, Int32 dwFlags)
       at System.Security.Cryptography.RSACryptoServiceProvider.Decrypt(Byte[] rgb, Boolean fOAEP)
       at TestDecryption.MyDecryptor.DecryptDocIDWithFileCert(String pfxFilePath, Byte[] EncryptedDocID) in C:\Users\developmentUser\Documents\Visual Studio 2008\Projects\TestDecryption\TestDecryption\MyDecryptor.cs:line 71
       at TestDecryption.Program.Main(String[] args) in C:\Users\developmentUser\Documents\Visual Studio 2008\Projects\TestDecryption\TestDecryption\Program.cs:line 37
       
    or
    
    System.Security.Cryptography.CryptographicException: Not enough storage is available to process this command.
       at System.Security.Cryptography.CryptographicException.ThrowCryptogaphicException(Int32 hr)
       at System.Security.Cryptography.Utils._DecryptKey(SafeKeyHandle hPubKey, Byte[] key, Int32 dwFlags)
       at System.Security.Cryptography.RSACryptoServiceProvider.Decrypt(Byte[] rgb, Boolean fOAEP)
       at TestDecryption.MyDecryptor.DecryptDocIDWithFileCert(String pfxFilePath, Byte[] EncryptedDocID) in C:\Users\developmentUser\Documents\Visual Studio 2008\Projects\TestDecryption\TestDecryption\MyDecryptor.cs:line 71
       at TestDecryption.Program.Main(String[] args) in C:\Users\developmentUser\Documents\Visual Studio 2008\Projects\TestDecryption\TestDecryption\Program.cs:line 37

    depending on the flag i set by makeCert (-pe)

    my new Question is:

    Is SQL-Server using RSA to encrypt Data with ENCRYPTBYCERT()????????????? Or am I trying the infeasable?

    Yess, it seems to use RSA look at the answer

    UPDATE:
    How can I decrypt the Data using RsaCryptoServiceProvider and a self made certificate?



    • Edited by superschaf Wednesday, July 31, 2013 3:18 PM changed Title
    Wednesday, July 24, 2013 2:55 PM

Answers

  • Answer:
    Created Certificate outside SQL:

      
      Change Directory to cd "C:\Program Files\MicrosoftSDKs\Windows\v6.1\Bin"    copy makecert.exe and pvk2pfx.exe to temp\createCert\   
     C:\temp\createCert>makecert -sv PrivateKey.pvk -n "cn=TestCertificate" TestCertificate.cer -b 01/01/2013 -e 01/01/2014 -sky Exchange


    created pfx file

       
     C:\temp\createCert>pvk2pfx -pvk PrivateKey.pvk -spc TestCertificate.cer -po TestEncryption123


    > * Yes, export the private key
    > * Include all certificates in the certification path if possible
    > * Export all extendend properties

    Set password: TestEncryption123

    choose Filename for pfx file...

    ran this on SQL Server:

      
    USE master 
    GO
     
    CREATE DATABASE TestEncryptionDecryption
    GO
     
    USE TestEncryptionDecryption
     
    CREATE TABLE Encrypt 
    (
    	intCol int,
    	clearTextCol varchar(128)
    )
    GO
     
    INSERT INTO Encrypt (intCol, clearTextCol)
    VALUES
    	('1', 'Links'),
    	('2', 'Zwo'),
    	('3', 'Drei'),
    	('4', 'Vier'),
    	('5', 'Fünf'),
    	('6', '5ech5')
    GO
     
     
    CREATE CERTIFICATE TestCertificate
    	FROM FILE = 'C:\temp\createCert\TestCertificate.cer'
    	WITH PRIVATE KEY (
    		FILE = 'C:\temp\createCert\PrivateKey.pvk',
    		ENCRYPTION BY PASSWORD = 'TestEncryption123'
    	)
    GO
     
    SELECT * FROM sys.certificates
     
     
    ALTER TABLE Encrypt
    ADD 
    	encryptedCol varbinary (128)
    GO
     
    UPDATE Encrypt
    SET
    	encryptedCol = ENCRYPTBYCERT
    	(
    		CERT_ID('TestCertificate'),
    		clearTextCol
    	)
    GO
     
     
    DECLARE @Passwd nvarchar(128) = 'TestEncryption123'
    SELECT intCol,
    	clearTextCol,
    	encryptedCol,
    	CAST
    	(
    		DECRYPTBYCERT
    		(
    			CERT_ID('TestCertificate'),
    			encryptedCol,
    			@Passwd
    		) AS varchar(128)
    	) AS decryptedCol
    FROM dbo.Encrypt
    
    GO




    made my Testprogram:

      
    using System;
    using System.Data.SqlClient;
     
    namespace Testbeispiel_Encryption
    {
    	class Program
    	{
    		static void Main(string[] args)
    		{
    			//create a new instance of MyDecryptor
    			MyDecryptor Decryptor = new MyDecryptor();
    			//not recommended to store password as a cleartext (but for development purposes ;) 
    			string password = "TestEncryption123";
    			System.Security.SecureString securePassword = new System.Security.SecureString();
    			//create Secure Password
    			foreach (char keyChar in password.ToCharArray())
    				securePassword.AppendChar(keyChar);
    			//Insert Connection String (local because I am working on the Server)
    			string constr = "DATA SOURCE=(local);INITIAL CATALOG=TestEncryptionDecryption;INTEGRATED SECURITY=SSPI;";
    			using (SqlConnection con = new SqlConnection(constr))
    			{
    				//Open the connection to the initial catalog
    				con.Open();
    				//create SELECT statement
    				string cmdstr1 = "SELECT intCol, clearTextCol, encryptedCol FROM dbo.Encrypt";
     
    				using (SqlCommand cmd = new SqlCommand(cmdstr1, con))
    				{
    					SqlDataReader reader = cmd.ExecuteReader();
    					//now we read data
    					while (reader.Read())
    					{
    						try
    						{
    							Console.Write(reader[0] + "\t" + reader[1] + "\t" + reader[2] + "\t" );
    							Console.WriteLine(Decryptor.DecryptDocIDWithFileCert(@"C:\temp\createCert\TestCertificate.pfx", securePassword, (byte[])reader[2]));
    						}
    						catch (Exception e)
    						{
    							Console.WriteLine(e.ToString());
    							break;
    						}
    					}
    					reader.Close();
    				}
    				con.Close();
    			}
    			Console.ReadKey();
    		}
    	}
    }
    



    and my Testdecryptor:

     
    using System;
    using System.Security.Cryptography;
    using System.Security.Cryptography.X509Certificates;
    namespace Testbeispiel_Encryption
    {
    	public class MyDecryptor
    	{
    		public MyDecryptor()
    		{
    			//TODO Constructor
    		}
    		~MyDecryptor()
    		{
    			//TODO Destructor
    		}
     
    		///<summary>
    		///Opens certificate and private Key, Decrypts Data and returns the decrypted DocID
    		///</summary>
    		///<param name="pfxFilePath">The Filepath to the Certificate (.pfx) File!</param>
    		///<param name="password">The password for accessing the pfx file</param>
    		///<param name="EncryptedDocID">the Value of the encrypted Collumn, Encrypted by the Certifiate of the pfx File</param>
    		///<returns>string DecryptedDocID - the decrypted Value of the DocID</returns>
    		public string DecryptDocIDWithFileCert(string pfxFilePath, System.Security.SecureString password, byte[] EncryptedDocID)
    		{
    			//Reverse the encrypted DocID because SQL reverses the byte array on encryption
    			//and so does RSACryptoServiceProvider. 
    			Array.Reverse(EncryptedDocID);
    			string DecryptedDocID = "";
     
    			//load certificate from filesystem and open it with provided password
    			X509Certificate2 cert = new X509Certificate2(pfxFilePath, password);
    			if (cert == null)
    			{
    				throw new Exception("Certificate " + pfxFilePath + " Does not exist");
    			}
     
    			//Check if certificate has a private key, otherwhise we are unable to decrypt any message
    			//encrypted with the certs public key so we throw an exception
    			if (cert.HasPrivateKey)
    			{
    				RSACryptoServiceProvider RsaCSP = (RSACryptoServiceProvider)cert.PrivateKey;
    				//actualy decrypt message with RSACryptoServiceProvider and set OAEP Padding to false
    				//Don't know the reason, otherwhise it didn't work correctly 
    				byte[] ret = RsaCSP.Decrypt(EncryptedDocID, false);
    				if (ret == null)
    				{
    					throw new Exception("Decryption with RSA failed");
    				}
    				//Encode DocID to UTF7 String. UTF8 didn't recognise umlauts
    				DecryptedDocID = System.Text.Encoding.UTF7.GetString(ret);
    			}
    			else
    			{
    				throw new Exception("Certificate " + pfxFilePath + " has no Private Key; ");
    			}
    			return DecryptedDocID;
    		}
    	}
    }
    


    AND FINALY IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Clear Database
    DROP CERTIFICATE TestCertificate
    GO
     
    DROP TABLE Encrypt
    GO
     
    USE master
    GO
     
    DROP DATABASE TestEncryptionDecryption
    GO
    
     



     
    • Marked as answer by superschaf Wednesday, July 31, 2013 3:16 PM
    Wednesday, July 31, 2013 3:16 PM

All replies

  • Yess, it seems that it is encrypted by RSA. Michael Coles was making something very simular to mine and used RsaCryptoServiceProvider to Encrypt the Data in his .NET application. But he uses a stored procedure to decrypt the data. I don't want to decrypt the data on the server. I need to decrypt it also with RsaCryptoServiceProvider..

    So the new Title should be: How can I get rid of my error?

    http://sqlblog.com/blogs/michael_coles/archive/2010/01/29/encrypt-it-in-net-decrypt-it-on-sql-server.aspx

    Friday, July 26, 2013 8:44 AM
  • Answer:
    Created Certificate outside SQL:

      
      Change Directory to cd "C:\Program Files\MicrosoftSDKs\Windows\v6.1\Bin"    copy makecert.exe and pvk2pfx.exe to temp\createCert\   
     C:\temp\createCert>makecert -sv PrivateKey.pvk -n "cn=TestCertificate" TestCertificate.cer -b 01/01/2013 -e 01/01/2014 -sky Exchange


    created pfx file

       
     C:\temp\createCert>pvk2pfx -pvk PrivateKey.pvk -spc TestCertificate.cer -po TestEncryption123


    > * Yes, export the private key
    > * Include all certificates in the certification path if possible
    > * Export all extendend properties

    Set password: TestEncryption123

    choose Filename for pfx file...

    ran this on SQL Server:

      
    USE master 
    GO
     
    CREATE DATABASE TestEncryptionDecryption
    GO
     
    USE TestEncryptionDecryption
     
    CREATE TABLE Encrypt 
    (
    	intCol int,
    	clearTextCol varchar(128)
    )
    GO
     
    INSERT INTO Encrypt (intCol, clearTextCol)
    VALUES
    	('1', 'Links'),
    	('2', 'Zwo'),
    	('3', 'Drei'),
    	('4', 'Vier'),
    	('5', 'Fünf'),
    	('6', '5ech5')
    GO
     
     
    CREATE CERTIFICATE TestCertificate
    	FROM FILE = 'C:\temp\createCert\TestCertificate.cer'
    	WITH PRIVATE KEY (
    		FILE = 'C:\temp\createCert\PrivateKey.pvk',
    		ENCRYPTION BY PASSWORD = 'TestEncryption123'
    	)
    GO
     
    SELECT * FROM sys.certificates
     
     
    ALTER TABLE Encrypt
    ADD 
    	encryptedCol varbinary (128)
    GO
     
    UPDATE Encrypt
    SET
    	encryptedCol = ENCRYPTBYCERT
    	(
    		CERT_ID('TestCertificate'),
    		clearTextCol
    	)
    GO
     
     
    DECLARE @Passwd nvarchar(128) = 'TestEncryption123'
    SELECT intCol,
    	clearTextCol,
    	encryptedCol,
    	CAST
    	(
    		DECRYPTBYCERT
    		(
    			CERT_ID('TestCertificate'),
    			encryptedCol,
    			@Passwd
    		) AS varchar(128)
    	) AS decryptedCol
    FROM dbo.Encrypt
    
    GO




    made my Testprogram:

      
    using System;
    using System.Data.SqlClient;
     
    namespace Testbeispiel_Encryption
    {
    	class Program
    	{
    		static void Main(string[] args)
    		{
    			//create a new instance of MyDecryptor
    			MyDecryptor Decryptor = new MyDecryptor();
    			//not recommended to store password as a cleartext (but for development purposes ;) 
    			string password = "TestEncryption123";
    			System.Security.SecureString securePassword = new System.Security.SecureString();
    			//create Secure Password
    			foreach (char keyChar in password.ToCharArray())
    				securePassword.AppendChar(keyChar);
    			//Insert Connection String (local because I am working on the Server)
    			string constr = "DATA SOURCE=(local);INITIAL CATALOG=TestEncryptionDecryption;INTEGRATED SECURITY=SSPI;";
    			using (SqlConnection con = new SqlConnection(constr))
    			{
    				//Open the connection to the initial catalog
    				con.Open();
    				//create SELECT statement
    				string cmdstr1 = "SELECT intCol, clearTextCol, encryptedCol FROM dbo.Encrypt";
     
    				using (SqlCommand cmd = new SqlCommand(cmdstr1, con))
    				{
    					SqlDataReader reader = cmd.ExecuteReader();
    					//now we read data
    					while (reader.Read())
    					{
    						try
    						{
    							Console.Write(reader[0] + "\t" + reader[1] + "\t" + reader[2] + "\t" );
    							Console.WriteLine(Decryptor.DecryptDocIDWithFileCert(@"C:\temp\createCert\TestCertificate.pfx", securePassword, (byte[])reader[2]));
    						}
    						catch (Exception e)
    						{
    							Console.WriteLine(e.ToString());
    							break;
    						}
    					}
    					reader.Close();
    				}
    				con.Close();
    			}
    			Console.ReadKey();
    		}
    	}
    }
    



    and my Testdecryptor:

     
    using System;
    using System.Security.Cryptography;
    using System.Security.Cryptography.X509Certificates;
    namespace Testbeispiel_Encryption
    {
    	public class MyDecryptor
    	{
    		public MyDecryptor()
    		{
    			//TODO Constructor
    		}
    		~MyDecryptor()
    		{
    			//TODO Destructor
    		}
     
    		///<summary>
    		///Opens certificate and private Key, Decrypts Data and returns the decrypted DocID
    		///</summary>
    		///<param name="pfxFilePath">The Filepath to the Certificate (.pfx) File!</param>
    		///<param name="password">The password for accessing the pfx file</param>
    		///<param name="EncryptedDocID">the Value of the encrypted Collumn, Encrypted by the Certifiate of the pfx File</param>
    		///<returns>string DecryptedDocID - the decrypted Value of the DocID</returns>
    		public string DecryptDocIDWithFileCert(string pfxFilePath, System.Security.SecureString password, byte[] EncryptedDocID)
    		{
    			//Reverse the encrypted DocID because SQL reverses the byte array on encryption
    			//and so does RSACryptoServiceProvider. 
    			Array.Reverse(EncryptedDocID);
    			string DecryptedDocID = "";
     
    			//load certificate from filesystem and open it with provided password
    			X509Certificate2 cert = new X509Certificate2(pfxFilePath, password);
    			if (cert == null)
    			{
    				throw new Exception("Certificate " + pfxFilePath + " Does not exist");
    			}
     
    			//Check if certificate has a private key, otherwhise we are unable to decrypt any message
    			//encrypted with the certs public key so we throw an exception
    			if (cert.HasPrivateKey)
    			{
    				RSACryptoServiceProvider RsaCSP = (RSACryptoServiceProvider)cert.PrivateKey;
    				//actualy decrypt message with RSACryptoServiceProvider and set OAEP Padding to false
    				//Don't know the reason, otherwhise it didn't work correctly 
    				byte[] ret = RsaCSP.Decrypt(EncryptedDocID, false);
    				if (ret == null)
    				{
    					throw new Exception("Decryption with RSA failed");
    				}
    				//Encode DocID to UTF7 String. UTF8 didn't recognise umlauts
    				DecryptedDocID = System.Text.Encoding.UTF7.GetString(ret);
    			}
    			else
    			{
    				throw new Exception("Certificate " + pfxFilePath + " has no Private Key; ");
    			}
    			return DecryptedDocID;
    		}
    	}
    }
    


    AND FINALY IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Clear Database
    DROP CERTIFICATE TestCertificate
    GO
     
    DROP TABLE Encrypt
    GO
     
    USE master
    GO
     
    DROP DATABASE TestEncryptionDecryption
    GO
    
     



     
    • Marked as answer by superschaf Wednesday, July 31, 2013 3:16 PM
    Wednesday, July 31, 2013 3:16 PM
  • Dear friends, 

    Do you have any sample showing how to make de oposit operation?

    I need to Encrypt in .NET and Decrypt in SQL 

    Best Regards

    Robson C Machado

    Wednesday, June 7, 2017 9:49 PM