0

Might be early days for this - Does anyone have a PHP example of connecting to an MS SQL 2016 database, and retrieving an "Always Encrypted" column, de-crypted in plain text? I can retrieve the binary encrypted columns without problem (PDO or ODBC), but not the plain text version. Inserting/Updating an "Always Encrypted" column would be handy too.

Thanks

R Taylor
  • 29
  • 3

1 Answers1

0

If you are using ODBC driver this connection string 

SQLWCHAR *connString = L"Driver={ODBC Driver 13 for SQL Server};Server={myServer};Trusted_Connection=yes;ColumnEncryption=Enabled;";

should retrieve plaintext data for you, assuming that have access to the key. Also, this article should be useful for you.

For inserting/updating data in always encrypted columns, you can use SSMS, .Net application, or ODBC application

If you have additional questions please post them in the comments sections and I will try my best to help.

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
  • Thanks! For the "access to the key" part I had to set up and use a local server user (as opposed to an SQL user) – R Taylor Jul 31 '17 at 10:46
  • I do not quite understand what you are trying to convey using this sentence "For the "access to the key" part I had to set up and use a local server user (as opposed to an SQL user) " Could you please elaborate? – Nikhil Vithlani - Microsoft Jul 31 '17 at 18:34
  • I tried to access my database, in PHP, using an SQL user and password. I couldn't seem to get this SQL user to be allowed access to the key. When I used a local server user (e.g. myserver\User1) instead, it worked fine. – R Taylor Aug 11 '17 at 08:35
  • Could you please elaborate where is your column master key certificate is stored? Also, the following answer might help you, I have explained briefly how the server-client setup should look like for always encrypted https://stackoverflow.com/questions/42157845/always-encrypted-behavior-in-sql-server-2016/42235099#42235099 – Nikhil Vithlani - Microsoft Aug 11 '17 at 17:43