SQL71501: Error validating element while activating always encrypted

Francisco Vargas 0 Reputation points
2024-07-17T15:48:42.3233333+00:00

I was activating always encryted in my database when this error showed:

Error SQL71501: Error validating element [dbo].[VIEW_NAME]: View: [dbo].[VIEW_NAME] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[TABLE_NAME].[c], [dbo].[TABLE_NAME].[Id] or [dbo].[TABLE_NAME].[c].

There were a lot of this kind of messages, just to be clear the column I was encrypted had nothing to do with the view.

I found that all the views with problems have reference to anoter database, let see:

In database_1 i have view_1 that uses by a synonymous to access table_A on database_2.

How i can activate always encrypted in my database?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,629 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,896 Reputation points
    2024-07-17T17:09:29.8833333+00:00

    contains an unresolved reference to an object

    Cross-database access with three-part object coulifier are not supported in Always Encrypt.

  2. Erland Sommarskog 109.6K Reputation points MVP
    2024-07-17T19:08:19.5866667+00:00

    I don't know what Olaf means with three-part object references not being supported with Always Encrypted. Clearly, if you have a view that is working, and you encrypt a column that has no relation to the view, that view should keep working. If SSDT starts to freak out because of this, I would consider throwing SSDT out the window. (No, I don't use SSDT myself.)

    But you say:

    In database_1 i have view_1 that uses by a synonymous to access table_A on database_2.

    So, view_1 is like this?

    CREATE VIEW view_1 AS
   SELECT * FROM database_2.dbo.table_A

    If so, I think it is better to use synonyms instead:

    CREATE SYNONYM view_1 FOR database_2.dbo.table_A

    This is a little more lightweight. But if it stops SSDT from barfing, I don't know.

  3. Bruce (SqlWork.com) 63,666 Reputation points
    2024-07-17T19:38:51.9666667+00:00

    always encrypted means the client encrypts and decrypts. the database uses the encrypted value for compares and this limits the use of the column in join and where clauses.

    as values are compared while encrypted, a deterministic encryption must be used, and if two columns are compared they must use the same encryption and keys. you can see this causes some limitations.

    see limitations:

    https://video2.skills-academy.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16#limitations

    you can implement secure enclaves (allow the server to decrypt columns internally) and get pattern matching of encrypted columns

    .

    https://video2.skills-academy.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sql-server-ver16

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.