Select the product you need help with

FIX: "Incorrect syntax near 'IDENTITY_VALUE'" error when you try to decrypt data that is migrated from another database in SQL Server 2008 R2, SQL Server 2012 or in SQL Server 2008

Article ID: 2780146 - View products that this article applies to.

Expand all | Collapse all

Symptoms

Consider the following scenario:

You create a symmetric key from an Extensible Key Management (EKM) provider in Microsoft SQL Server 2008, Microsoft SQL Server 2012 or Microsoft SQL Server 2008 R2.
You encrypt some data in a database by using this symmetric key.
You migrate the data to another database.
You try to create a symmetric key in the new database from the same EKM provider. However, encrypted data in the new database cannot be decrypted because the newly created symmetric key has a GUID number, and this GUID number differs from the original symmetric key that encrypted the data in the original database.
To re-create the symmetric key by using the same GUID as the original symmetric key, you use the IDENTITY_VALUE option to specify the identity phrase that was used with the original symmetric key.

In this scenario, you receive an error message that resembles the following:

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'IDENTITY_VALUE'

Back to the top | Give Feedback

The fix for this issue was first released in Cumulative Update 6. For more information about how to obtain this cumulative update package for SQL Server 2012, click the following article number to view the article in the Microsoft Knowledge Base:

2728897

(http://support.microsoft.com/kb/2728897/ )

Cumulative update package 6 for SQL Server 2012

Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2692828

(http://support.microsoft.com/kb/2692828 / )

The SQL Server 2012 builds that were released after SQL Server 2012 was released

Cumulative Update 11 for SQL Server 2008 R2 Service Pack 1

The fix for this issue was first released in Cumulative Update 11. For more information about how to obtain this cumulative update package for SQL Server 2008 R2 Service Pack 1, click the following article number to view the article in the Microsoft Knowledge Base:

2812683

(http://support.microsoft.com/kb/2812683/ )

Cumulative update package 11 for SQL Server 2008 R2 Service Pack 1

Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 Service Pack 1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2567616

(http://support.microsoft.com/kb/2567616/ )

The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released

Cumulative Update 9 for SQL Server 2008 Service Pack 3 (SP3)

The fix for this issue was first released in Cumulative Update 9. For more information about how to obtain this cumulative update package for SQL Server 2008 Service Pack 3, click the following article number to view the article in the Microsoft Knowledge Base:

2799883

(http://support.microsoft.com/kb/2799883/ )

Cumulative update package 9 for SQL Server 2008 Service Pack 3

Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 Service Pack 3 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2629969

(http://support.microsoft.com/kb/2629969/ )

The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 3 was released

Cumulative Update 4 for SQL Server 2008 R2 Service Pack 2 (SP2)

The fix for this issue was first released in Cumulative Update 4. For more information about how to obtain this cumulative update package for SQL Server 2008 R2 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:

2777358

(http://support.microsoft.com/kb/2777358/ )

Cumulative update package 4 for SQL Server 2008 R2 Service Pack 2

Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 Service Pack 2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2730301

(http://support.microsoft.com/kb/2730301/ )

The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 2 was released

Back to the top | Give Feedback