What are the challenges you face when working across database platforms? Take the survey

Asymmetric Key Error: KeyAlgorithm should be 1R, 2R or 3R

athornburyathornbury Posts: 6 Bronze 2
edited May 3, 2016 3:48PM in SQL Compare 11
I am using SQL Compare against a database using an EKM device from Vormetric. When I try to do the comparison even with the "Ignore Certificates, symmetric keys and asymmetric keys" option enabled, i receive the error below. When i query sys.assymetric_keys, the algorithm and algorithm_desc columns are:

algorithm algorithm_desc
NA RSA_2048

The value NA in algorithm is inconsistent with the information available on BOL for sys.asymmetric_keys, but nonetheless it is there. More interesting is a comparison in sys.symmetric_key. NA is an allowable value in the key_algorithm column when an EKM is used and the algorithm_desc column would be NULL as a result. So it would appear that sys.asymmetric_keys on BOL needs an update and there is at least one additional value for the algorithm column.

That said, what can I do in SQL Compare to overcome this error? Is there anything at this point?

System.ArgumentOutOfRangeException: KeyAlgorithm should be 1R, 2R or 3R
Parameter name: keyAlgorithm
Actual value was NA.
at #8rg.#Ssg.#M23(String #qUGd)
at #8rg.#Ssg.#omh()
at #8rg.#Ssg.Populate(DBPopulationOptions #WXrc)
at RedGate.SQLCompare.Engine.Database.Register(ConnectionProperties #Ap8b, Options #5OPb, #7ELd #cxk)
at RedGate.SQLCompare.Common.CompareEngineExecutor.DoRegisterDatabaseForCompare(Database #RHc, LiveDatabaseSource #ovUb)
at RedGate.SQLCompare.Common.CompareEngineExecutor.DoRegisterDatabaseForCompare(Database #RHc, IDataSource #ovUb)
at RedGate.SQLCompare.Common.CompareEngineExecutor.#7bH()
at RedGate.Shared.Utils.ProgressTasks.ProgressTask.ExecuteSubTasks()
at RedGate.Shared.Controls.ProgressDialogEx.<>c__DisplayClass7.<DoTasks>b__4(Object q)

Thank you!!

-- Alex


  • Options
    Anu DAnu D Posts: 876 Silver 3
    Hi Alex,

    Thanks for your post and apologies for the error.

    Can you confirm the SQL Server database version of both source and Target?

    And also I would recommend you to try the very latest version of SQL Compare as it has several bug fixes.

    Link to installer
    Link to release notes

    Let me know if upgrading the version helps and also let me know the SQL Server version of both the databases.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • Options

    SQL Server 2008 R2 - 10.50.6220
    Database compatibility is SQL Server 2008 (100)

    I am comparing from the database against TFS source control. I upgraded to SQL Compare and the error still occurs. I added a note on the sys.asymmetric_keys BOL entry for 2008 R2 (I don't know if this behavior applies to later versions but I suspect it would):

    https://msdn.microsoft.com/en-us/librar ... (v=sql.105).aspx

    The error in SQL Compare is most likely that the 'NA' value in the algorithm column is not listed on BOL. I further suspect that use of hardware EKMs is not widespread and thus this situation is not common. I will be happy to demo via a WebEx, etc.

    Thank you for the update.

  • Options
    bensalabensala Posts: 7 Bronze 1
    Did you ever get any answer to this? I just started at a company that uses a 3rd party encryption and am having a similar issue. I can't even snapshot a database...
  • Options
    Mark JMark J Posts: 125 Bronze 4
    I'll check up on this - I think we've got a fix in the works which should hopefully be released soon
  • Options
    Mark JMark J Posts: 125 Bronze 4
    Ok, it looks like we've released a couple of fixes in this area (from http://documentation.red-gate.com/displ ... ease+notes):

    - - March 15th, 2016 / SC-8138: Asymmetric keys with NA algorithm (e.g. from hardware EKM) no longer cause comparison to fail.
    - - May 3rd, 2016 / SC-8524: support hardware crypto devices (e.g. nCipher) for asymmetric key encryption

    so give it another shot with the latest Frequent Updates version and let us know if you're still having problems :)
  • Options
    bensalabensala Posts: 7 Bronze 1
    Fixed! Thank you very much! I almost cried a little when I thought I wouldn't be able to use SQL compare anymore. =)

Sign In or Register to comment.