1

1) I am trying to encrypt some varchar columns which are not identity / primary using the always encrypted feature of SQL Server 2016, but getting an error code 71501,71561 for unresolved and / or ambiguous reference.

How do I resolve this ?

2) Is there a way to nominate a column as primary key for all tables at once ,which do not have a Primary Key .

Thanks

2
  • Could you please explain how are you encrypting the columns, and post your code/schema? Jul 24, 2017 at 20:35
  • I am using encrypt column dialog box with deterministic encryption the column I am trying to encrypt is EmpCode which is a primary key for that table. It is being referenced by many stored procedures and views
    – Anurag Raj
    Jul 25, 2017 at 11:57

1 Answer 1

1

Regarding your first question, which version of SSMS, are you using? If it's older than 17.0, I recommend trying the newest version (https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms).

Based on your description, I suspect you are using SSMS 16.x. The likely root cause of this problem is that the objects in your database have external dependencies (on objects in other databases) or have broken dependencies. In SSMS 16.x, the Always Encrypted wizard does a comprehensive validation of all dependencies in the entire database, including objects not dependent on that tables that you are trying to encrypt. We have fixed this problem in SSMS 17, by limiting the validation only to the tables containing the columns you are encrypting and the wizard does not error out anymore when encountering a validation error. Instead, it completes encryption and produces a warning, allowing you to investigate/address the issue later.

Regarding your second question - you need to designate a primary key column for each table.

2
  • I tried using SSMS 17.1. The errors were different this time Jul 26 2017 16:04:01 [Informational] TaskUpdates: Message:Task: 'Performing encryption operations' -- Status: 'Failed' -- Details: 'Task failed due to following error: Exception has been thrown by the target of an invocation.'. Errors: Error SQL71520: Error validating element [dbo].[Payroll_Table1]: An explicit value for the identity column in a table or a table-valued function ([dbo].[Obj1]) can be specified only when a column list is used. The same error code has come multiple times
    – Anurag Raj
    Jul 26, 2017 at 10:41
  • I am using SSMS 17.1. I Added a new column in the table , which is not being referenced by any object , still I am getting the same error. Please help Thanks
    – Anurag Raj
    Jul 27, 2017 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.