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 translations Article translations
Article ID: 2780146 - View products that this article applies to.
Expand all | Collapse all

On This Page

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'

Resolution

Cumulative update information

Cumulative update 6 for SQL Server 2012

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 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 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 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 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 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 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 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 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 2 was released

Workaround

To work around this issue, decrypt the data before you migrate it to the second database. After you migrate the data to the other database, use a new key to encrypt the data.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Properties

Article ID: 2780146 - Last Review: February 18, 2013 - Revision: 3.0
Applies to
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2012 Developer
  • SQL Server 2012 Enterprise Core
  • Microsoft SQL Server 2012 Enterprise
Keywords: 
kbqfe kbfix kbsurveynew kbexpertiseadvanced KB2780146

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com