0

I am trying to deploy an Always Encrypted app to Amazon AWS. There is a 2016 RDS database and a Windows Server 2016 hosting the ASP.NET 4.6 app.

On the Windows Server, when the application is run under Network Service or the Application Pool Identity, we get what I'm pretty sure is this exact problem.

Our Error:

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

  • We tried the solution in that post and it didn't work
  • We tried the solution in this post and couldn't get the powershell script to return enything except runtime exceptions. I am not very familiar with powershell so maybe I don't understand the script.
  • If we import the certificate to the Andministrator account, and run the IIS app pool with the Administrator account then everything works fine (not a connectivity issue or anything)
  • We tried importing the certificate to the local computer "Personal" certificates and giving the application account full control of the keys
  • We tried importing the certificate to the local computer Trusted Root Certification Authorities (as suggested in a s/o post somewhere).
  • We tried running certmgr with "runas" for both the DefaultAppPool and Network Service account, but when prompted for the password did not know what that would be (blank password did not work). I even looked up the DefaultAppPool password as suggested here and according to that output the password is blank.

So far nothing (apart from running the app as Administrator) as worked and we're at a loss what to try next. We've looked high and low for some sort of "Microsoft Official Deployment Guide" but all that is out there is tutorials on how to get Always Encrypted working in a local dev environment, nothing about server deployment.

FirstDivision
  • 1,340
  • 3
  • 17
  • 37

1 Answers1

4

When you originally created the Always Encrypted Keys for the SQL database did you generate them in the Current User Certificate Store or the Local Machine Certificate Store? It turns out its really important to place them under Local Machine. This was the issue for me when I tried to deploy my web app to our web server. No matter where I installed the certificate on the server, SQL was still looking for it under Current User which IIS cannot see and Manage Private Key does not appear for this store so you can't assign IIS user accounts to it (at least I couldn't).

Once I'd temporarily reset my table fields to plain text, deleted the master and encryption keys and regenerated them under Local Machine on my PC, and exported the certificate, it installed into the Local Machine/Personal store just fine and all I had to do was give IIS_IUSRS permissions using "Manage Private Key" to clear the remaining "Keyset does not exist" error. Hope this saves someone else a day of googling.

allgoogledout
  • 56
  • 2
  • We ended up forgoing Always Encrypted and used the AWS Encryption at Rest feature instead because it still satisfied our requirements. I remember moving certs back and forth between the different stores though and thinking that was somehow related, so I'm going to say this probably was the answer I was looking for. – FirstDivision Mar 16 '18 at 20:20