Tableau and Azure SQL Server Always Encrypted

Proposed solution 1: Self-hosted Deployment Model with Tableau Server

  • Columns originally encrypted with Always Encrypted → client-side decryption by the SQL Server driver installed on Tableau Server’s VM
  • Columns originally encrypted with Always Encypted with Security Enclaves → server-side decryption on a secure enclave located in the Database Server.

Proposed Solution 2: SaaS Deployment Model with Tableau Online and Tableau Bridge (acting as an on-premises data gateway)

How to deal with SQL Server Always Encrypted in Tableau?

It’s important to understand that a database columns encrypted with Always Encrypted are very difficult to query and analyse, because of a serious limitation on the implementation of the technology, which prevents most advanced queries to work.

Enabling Always Encrypted in Tableau

When it comes to Tableau’s connectivity to SQL Server & Azure SQL DB, we must enable Always Encrypted (or Always Encrypted with Security Enclaves) feature in the sqlserver driver and (optionally) where locate the encryption keys used to originally encrypt data.

SQL Server ODBC driver Always Encrypted API summary, source

a) Tableau’s out-of-the-box SQL Server & Azure SQL DB connectors

This is the fastest way to get started. Simply create a Tableau Data Source Customization (TDC) file that contains your extra connection strings as follows (ps: not familiar with TDC files? Check this excellent post):

  • TDC sample for SQL Server using Always Encrypted and encryption keys stored on the user’s local or local machine’s trusted store (i.e. we did not need to specify the key vault location details):
  • TDC sample for Azure SQL Database with Always Encrypted with Secure Enclaves, encryption keys are also available localy and accessible by the runas user, but the Enclave Attestation Provider is running on Azure in this case. Notice that I’ve also changed the database class, vendor and driver from sqlserver to azure_sqldb:
  • Tableau Desktop for Windows: My Documents\My Tableau Repository\Datasources
  • Tableau Desktop for Mac: Not possible at the moment, as it doesn’t use Microsoft’s native sql server driver. You can still open encrypted data without a tdc, but if you really need to decrypt it, try out MS Sql Server JDBC driver and Tableau’s Generic JDBC connector (I have’t tested it yet, but should work)
  • Tableau Bridge: tdc file not needed, all extra connection strings are already saved and embedded in the data source we publish from Tableau Desktop to Tableau Online.
  • Tableau Server for Windows: ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Datasources
  • Tableau Server for Linux: /var/opt/tableau/tableau_server/data/tabsvc/vizqlserver/Datasources/
  • Additional note for TDC customizations on Tableau Server: Any connection string extras you’d like added to the TDC files need to be allow-listed on by a Tableau Server Admin. If, for example, we want to enable parameters ColumnEncryption and KeyStoreScretet, the following TSM commands are neeeded:
source

b) Generic ODBC Connector, alongside a custom DSN created using Microsoft Sql Server 17+ ODBC driver.

Since we’ll be just creating very simple tabular reports with Always Encrypted data (due to Microsoft limitations discussed above), ODBC driver is a perfectly valid solution.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store