Asymmetric Key Error: KeyAlgorithm should be 1R, 2R or 3R
athornbury
Posts: 6 Bronze 2
I am using SQL Compare 11.1.0.1 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
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
Comments
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.
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
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 11.3.0.7 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.
Alex
- 11.5.2.509 - March 15th, 2016 / SC-8138: Asymmetric keys with NA algorithm (e.g. from hardware EKM) no longer cause comparison to fail.
- 11.5.8.1398 - 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
-Ben