Encryption user error

David Chase 681 Reputation points
2020-12-21T15:17:53.97+00:00

I was trying one last test of Always Encrypted and this time I got the error "The reverse engineering operation cannot continue because you do not have View Definition permission on the database."
This is my 2nd attempted test as the first one worked perfectly. I could not finf View Definition in the user security options. Any ideas?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
0 comments
Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2020-12-24T10:34:47.353+00:00

    Explain what? What a service account is? You log on to the machine which runs SQL Server and IIS as DOMAIN\Admin or whatever. You run the Always Encrypted wizard, and the certificate ends up in the certificate store of DOMAIN\Admin.

    However, the web server does typically not run as DOMAIN\Admin, but some other account. I have feeling that IIS typically as as Local Service, but I know very little about IIS, and you may be better off asking in a forum for IIS (if that is the web server you are using) for details. I googled around, and I found https://stackoverflow.com/questions/38462367/where-does-one-place-the-always-encrypted-certificate-on-an-iis-7-5-web-server. There are two solutions suggested. The first one may be the simpler one, but I don't like it from a security standpoint, so I would suggest that you try the second.

    I don't know if you have made your initial tests with IIS Express, but this is different. IIS Express runs in your user space, so in this case, it will find your personal certificate store.

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Robbie Varn 346 Reputation points
    2020-12-21T16:07:20.333+00:00

    The View Definition permission is in the database properties under the explicit tab near the bottom.

  2. Erland Sommarskog 100.8K Reputation points MVP
    2020-12-21T22:24:51.793+00:00

    If you did not have VIEW DEFINITION, there may be more permissions you are missing. I don't recall on the top of my head what permission you need here, but for that kind of operation, you would typically have to be in the db_ddladmin or db_owner role. And since you apparently were able to grant VIEW DEFINITION, you appear to have access to an account with sufficient permissions, so why not use that?

    0 comments
  3. David Chase 681 Reputation points
    2020-12-21T22:54:07.583+00:00

    Just strange to me that a test last week on the same database worked fine. I will try the additional permissions.

  4. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2020-12-22T03:14:40.363+00:00

    Hi @David Chase ,

    > The reverse engineering operation cannot continue because you do not have View Definition permission on the database.

    There are four permissions for Always Encrypted:

    • ALTER ANY COLUMN MASTER KEY (Required to create and delete a column master key.)
    • ALTER ANY COLUMN ENCRYPTION KEY (Required to create and delete a column encryption key.)
    • VIEW ANY COLUMN MASTER KEY DEFINITION (Required to access and read the metadata of the column master keys to manage keys or query encrypted columns.)
    • VIEW ANY COLUMN ENCRYPTION KEY DEFINITION (Required to access and read the metadata of the column encryption key to manage keys or query encrypted columns.)

    Refer to MS document Always Encrypted.

    Suggest you selecting the db_owner role for the user to resolve your issue.

    Best regards,
    Cathy

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.