Visual Studio Code SQL Server connection to encrypted database with Azure Key Vault (Always Encrypted)

Question

I'm trying to get a connection from VSCode on macOS to a SQL Server database that uses always encrypted mechanism to protect some of the columns. The master key is stored in an Azure Key Vault.

Using the always encrypted guide provided by Microsoft is was able to connect successfully to the database.

The same is true for a simple connection using VSCode on my mac without turning on the encryption/decryption. I used the mssql-extension plugin and providing the necessary information within the settings I was able to query the data

Settings

"mssql.connections": [
    {
        "server": "XXXXXXXX.database.windows.net",
        "database": "AlwaysEncrypted",
        "authenticationType": "SqlLogin",
        "user": "XXXXX",
        "password": "",
        "emptyPasswordInput": false,
        "savePassword": true,
        "profileName": "AlwaysEncrypted"
    }
]

Query

SELECT * FROM EmployeeDetails

Result

[
  {
    "EmployeeDetailsId": "1",
    "EmployeeNo": "FE00000001",
    "FirstName": "0x013EC8AB61767E1C3D934AB061BCA658B6948637812450C8245DCE4C447F59FD1D6252069A36A67E3477E1C5FB24D860E72FBCC65F98C92B92AB873CE55349672A",
    "MiddleName": "0x015354526EC17EB1151AE918514E565507EDCB5691B4215C45798CA86EB11C47EECA579242926EDFE9F6543006177CBFC03E0F95CD0D8CAE6C941AE173AAF2B925",
    "LastName": "0x0170B3FD2B0416E0607312FB2A67B0F42798EC1871FEAB90AB81235ADACDE1C4F5614099FA3B61E59FEB2D6AD599CB3A9FD031FE56F327F0C80F4BA963EE7E155A",
    "DateOfBirth": "1985-08-12 00:00:00.000"
  }
]

Following the two guides

I did try to create another connection using the mssql-extension and providing a ODBC Connection String but ultimately failed to get decrypted data when querying (the connection was established just fine). The result was the same as posted above

Settings with Connection String

"mssql.connections": [
    {
        "server": "XXXXXXXX.database.windows.net",
        "database": "AlwaysEncrypted",
        "authenticationType": "SqlLogin",
        "user": "XXXXX",
        "password": "",
        "emptyPasswordInput": false,
        "savePassword": true,
        "profileName": "AlwaysEncrypted_WithKeyVault",
        "connectionString": "SERVER=XXXXXX.database.windows.net;Trusted_Connection=Yes;DATABASE=AlwaysEncrypted;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultPassword;KeyStorePrincipalId=USER.NAME@DOMAIN.com;KeyStoreSecret=PASSWORD"
    }
]

Can anyone help me to figure out how to setup the connections right, so that the encryption/decryption will he done transparently when using VSCode?

score 1 · Answer 1 · answered Oct 09 '22 at 23:38

Bit of a stale question, but for anyone who also ends up finding this:

I managed to get a successfully connection on VS Code SQL Server by having the following settings in my settings.json mssql.connections array:

   {
      "server": "XXXX.serverhost.domain",
      "database": "XXXX",
      "authenticationType": "SqlLogin",
      "user": "XXXX",
      "password": "",
      "savePassword": true,
      "profileName": "XXXX",

      // specifically the settings below were the important ones
      "encrypt": true,
      "trustServerCertificate": true,
      "persistSecurityInfo": true
    }

Visual Studio Code SQL Server connection to encrypted database with Azure Key Vault (Always Encrypted)

1 Answers1