none
Using SQL Server 2005 Public Keys in VB.Net

    Question

  • I'm creating an asymmetric key in SQL Server:

                  CREATE ASYMMETRIC KEY MY_KEY
                                WITH ALGORITHM = RSA_512
                                ENCRYPTION BY PASSWORD = 'password1@'

    I'm then taking the public key...

                  SELECT public_key
                                FROM sys.asymmetric_keys
                                WHERE [name] = 'MY_KEY'

    I'm trying to import this into VB.Net using RSACryptoServiceProvider.

                  Dim RSA As New
    Security.Cryptography.RSACryptoServiceProvider(512)
                  RSA.ImportCspBlob(KeyBlobByteArray)

    I have to add the following line to get it to not give me errors.
    (Why?) But the data looks right when I look at the exponet and modulus
    of the parameters that are exported and then immediately imported.
    Something is obviously being thrown away when I do this, but I don't
    know what. It's probably the key to the whole problem.

                  RSA.ImportParameters(RSA.ExportParameters(False))

    I encrypt the message...

                  EncryptedMessageByteArray = RSA.Encrypt(MessageByteArray,
    False)

    And in SQL server I try to decrypt the message and I get NULL.

                  SET @DecryptedMessage =
                                DecryptByAsymKey (
                                              AsymKey_ID('MY_KEY') ,
                                              @EncryptedMessage ,
                                              N'password1@')

    I've noticed that the encrypted array is completely different in VB
    than when I encrypt it in SQL (obviously), but I can't figure out how
    to configure to the RSACryptoServiceProvider to match what SQL Server
    is doing under the hood.

    Any ideas?

    Thanks,
    Jerrill
    Monday, May 01, 2006 1:58 PM

Answers

  •   The key CspBlob is correct, but the public key blob we expose was originally intended for CLR strong-names, not for encrypting. If you want to use this key for encryption you will have to mark it as valid for encryption (AT_EXCHANGE). I am not an expert in CLR, and I am sure there is a clean way to do it, but in the meantime you can just switch the flag manually before the ImportCspBlob call:

     

    // Set the AT_EXCHANGE flag

    KeyBlobByteArray[5] |= 0x80;

     

      After this you should not have any problem to encrypt data using this key, but you will face a different problem. CLR RSACryptoServiceProvider will reverse the inputs/outputs, and you will have to reverse the encrypted data before being able to use it in SQL Server. I strongly recommend to read about this particular problem in http://blogs.msdn.com/shawnfa/archive/2005/12/05/500144.aspx, Shawn has an excellent article describing this behavior and how to workaround it.

     

      I hope I was able to answer your question.

     

      -Raul Garcia

       SDE/T

       US-SQL Server Engine

     

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, May 03, 2006 1:58 AM
    Moderator
  •   I didn’t explain the AT_EXCHANGE flag properly: This flag can replace the ImportParemeters call as we are marking the key blob as “valid for encryption” before we import it. For example you can try:

     

    ... 

    ' RG: Set the AT_EXCHANGE flag

    PublicKey(5) = PublicKey(5) Or &H80

     

    'import the public key blob

    RSA.ImportCspBlob(PublicKey)

     

    ' RG: You can now remove this line of code           

    'there's something missing from the key blob.. this fixes it!

    ' RSA.ImportParameters(RSA.ExportParameters(False))

     

      I really don’t know why your code is not working with larger keys. I just wrote simple VB demo (using your function) where I extract the public key from SQL Server, import it in the CLR, encrypt a simple blob and then I decrypt it again on SQL Server; it worked for me. I tried it using all three key lengths with the same result.

     

      Can you share more of your code? I would like to investigate this issue.  Thanks a lot.

     

    -Raul Garcia

     SDE/T

     SQL Server Engine

    Friday, May 05, 2006 2:27 AM
    Moderator

All replies

  •   The key CspBlob is correct, but the public key blob we expose was originally intended for CLR strong-names, not for encrypting. If you want to use this key for encryption you will have to mark it as valid for encryption (AT_EXCHANGE). I am not an expert in CLR, and I am sure there is a clean way to do it, but in the meantime you can just switch the flag manually before the ImportCspBlob call:

     

    // Set the AT_EXCHANGE flag

    KeyBlobByteArray[5] |= 0x80;

     

      After this you should not have any problem to encrypt data using this key, but you will face a different problem. CLR RSACryptoServiceProvider will reverse the inputs/outputs, and you will have to reverse the encrypted data before being able to use it in SQL Server. I strongly recommend to read about this particular problem in http://blogs.msdn.com/shawnfa/archive/2005/12/05/500144.aspx, Shawn has an excellent article describing this behavior and how to workaround it.

     

      I hope I was able to answer your question.

     

      -Raul Garcia

       SDE/T

       US-SQL Server Engine

     

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, May 03, 2006 1:58 AM
    Moderator
  • Success!

    As is turns out, I didn't even have to set the AT_EXCANGE flag. After reversing the output of the RSACryptoServiceProvider, SQL Server was able to decrypt the message using the private key.

    Thank you very much for the reply!
    Jerrill
    Thursday, May 04, 2006 2:09 PM
  • Public Shared Function AsymmetricEncrypt _
        (ByVal Data As Byte(), ByVal PublicKey As Byte()) As Byte()


        Try

            'create a new instance of RSACryptoServiceProvider
            '  to handle SQL Server's public key

            Dim RSA As New Security.Cryptography. _
                RSACryptoServiceProvider()


            'import the public key blob
            RSA.ImportCspBlob(PublicKey)

            'there's something missing from the key blob.. this fixes it!
            RSA.ImportParameters(RSA.ExportParameters(False))

            'encrypt the data
            Dim EncryptedData As Byte() = RSA.Encrypt(Data, False)

            'change the byte order to accommodate CLR quirk
            Array.Reverse(EncryptedData)

            'return the encrypted data
            Return EncryptedData

        Catch ex As Exception

           Throw New Exception("Encryption failed.", ex)

        End Try


    End Function

    OK! So, it only partially worked... Above is some sample code that works with RSA_512 formatted public keys from SQL Server. However, data encrypted with the larger public keys (RSA_1024 and RSA_2048) using the same code will not decrypt when returned to SQL Server.

    Any ideas?
    Thursday, May 04, 2006 11:39 PM
  • And again, the AT_EXCHANGE flag doesn't seem to have any effect either way.
    Thursday, May 04, 2006 11:48 PM
  •   I didn’t explain the AT_EXCHANGE flag properly: This flag can replace the ImportParemeters call as we are marking the key blob as “valid for encryption” before we import it. For example you can try:

     

    ... 

    ' RG: Set the AT_EXCHANGE flag

    PublicKey(5) = PublicKey(5) Or &H80

     

    'import the public key blob

    RSA.ImportCspBlob(PublicKey)

     

    ' RG: You can now remove this line of code           

    'there's something missing from the key blob.. this fixes it!

    ' RSA.ImportParameters(RSA.ExportParameters(False))

     

      I really don’t know why your code is not working with larger keys. I just wrote simple VB demo (using your function) where I extract the public key from SQL Server, import it in the CLR, encrypt a simple blob and then I decrypt it again on SQL Server; it worked for me. I tried it using all three key lengths with the same result.

     

      Can you share more of your code? I would like to investigate this issue.  Thanks a lot.

     

    -Raul Garcia

     SDE/T

     SQL Server Engine

    Friday, May 05, 2006 2:27 AM
    Moderator
  • Success... again! You are correct. The code does work for all the key sizes. I was truncating the public key when I imported it from the server. I fixed that problem and everything is working now.

    Thanks for the clarification on the AT_EXCHANGE flag. I've made the change to my code and have happily removed the mystery "this fixes it!" line.

    Jerrill
    Friday, May 05, 2006 2:15 PM
  • With .Net v2.0.50727
    and Sql server SP2 the applying of the AT_EXCHANGE flag seems to return into an error (sometimes can dechiffre, sometimes not (50%??).

    Just reversing the Array does the Job
    Tuesday, September 04, 2007 8:33 AM
  • Hi Jerrill,

    How do you pass the public key to the AsymmetricEncrypt function ?

    I have tried to call the function

    TextBox1.Text = AsymmetricEncrypt(ASCII.GetBytes("this is a test"),_
     ASCII.GetBytes(" the very lengthy binary public_key")).ToString

     

    BUT received "Bad Version of provider."

     

     

     


    --yousef
    Tuesday, September 28, 2010 10:02 AM