Encryption user error

Question

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?

Answer 1

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.

Answer 2

For this context consider "your personal account" to be the same as that administrator account that you are using.

I read https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-keys-using-ssms?view=sql-server-ver15, and I see that there is a choice between "Current User" and "Local computer". I have not checked exactly what that leads to, but I assume that if you selected "Current User", this will translate to the process running the client program, that is the web server.

I also read: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/overview-of-key-management-for-always-encrypted?view=sql-server-ver15, and I noticed this:

This means that you should never run tools used to generate the keys on the computer hosting your database

The fact that you have web server and database on the same machine, diminishes the value of Always Encrypted. If that machine is compromised, the encryption keys and the database are all there. But it is a little better if they keys are in the certificate of the web-server account, if only a little.

A particular consideration is the fact that this is a hosted service. If your desire is to protect the data from being accessible for the staff at the hosting service, you need to change things around and move the web server elsewhere.

Yes, security is hard.

Answer 3

This is for a special non-profit agency that does not have the money for a 2nd server for the web. They have a stand alone Windows 2016 server with special security at this hosting site due to HIPPA requirements. They serve children and adults with brain issues using a special diet (ketogenic). My role is to get their database encrypted (just a few tables) on the database used for their asp.net web application. I just don't know where to turn as this should be a simple process.

I am working on a test copy of their database and encryption is the final thing we need. In the links that you provided there is reference to Security>Always Encrypted Keys folder under your database. I cannot find that.
After getting your response today I restored the test database and deleted the certificated in the MMC certificate store to start fresh. I logged into sql as administrator, successfully went through column encryption and then tried to open the website and got the "Failed to encrypt" error.