Tableau and Azure SQL Server Always Encrypted
TL;DR; It is possible to use Tableau’s native connectors to decrypt data coming from SQL Server Always Encrypted (with and without Security Enclaves) with a few caveats. This post provides options and how-tos.
Thanks to GDPR and a general surge in sophisticated cyber attacks, data encryption is becoming a staple for most organisations. PII (Personally Identifiable Information), such as Social Security Numbers, Credit Card Details, Full Names, Addresses and so on are usually he focus point.
All the major cloud database vendors automatically encrypt all customer data at rest (e.g. Snowflake, GCP, AWS and Azure), however the level protection of these out of the box solutions offer are usually just enough to tick a compliance requirement. Companies who are really serious about data security will implement a complementary level of encryption, such Always Encrypted for SQL Server.
Always Encrypted is a data encryption technology that helps protect sensitive data at rest on the server, during movement between client and server, and while the data is in use, ensuring that sensitive data never appears as plaintext inside the database system. There’s a good discussion about these differences in this Microsoft Azure Blog post.
Here I present a couple of Tableau deployment models for dealing with encrypted data stored in SQL Server (and Azure SQL) with Always Encrypted and Always Encrypted with Security Enclaves and how to get started.
Hypothetical use case: A HR firm has encrypted personal employee details such as Salary and Social Security numbers and they want to create an Employee dashboard that only shows plain-text data to the actual employee. Tableau dashboard developers should only see encrypted data.
Proposed solution 1: Self-hosted Deployment Model with Tableau Server
In the above diagram, Tableau developers use Tableau Desktop to work with encrypted data coming directly from SQL Server or Azure SQL. They can connect to SQL Server or Azure SQL with the out-of-the-box connector and it will simply show encrypted data as expected (with a few limitations by Microsoft, as discussed later in this post). This is how encrypted data would appear for them in Tableau (without row-level security, i.e. all data rows are visible):
Report consumers (employees) should only see their own personal data and in plain text. To filter and present them with their individual data points, data sources were configured with at least one type of row-level security (RLS), which is completely independent from data decryption. The actual data decryption happens in one of the following two places, depending on the Always Encrypted technology used in first place to encrypt the columns:
- 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.
Either way, Tableau Server will return plain text data to end users (employees) through a secure SSL-encrypted communications channel. In other words, all data coming out of Tableau Server will be decrypted and If RLS is implemented correctly, end-users will only see their own data in plain text, nothing else. Hence why it’s paramount to implement Row-level security in this solution. I cannot stress this enough.
Here’s an how decrypted plain text data would appear in Tableau (without row-level security):
Proposed Solution 2: SaaS Deployment Model with Tableau Online and Tableau Bridge (acting as an on-premises data gateway)
In this model, Tableau Online will not be able to access SQL Server/Azure SQL directly, as we have no means of enabling the Always Encrypted feature on the Native SQL connector. There’s no support for the Generic ODBC connector in Tableau Online either.
To go around that, we can simply leverage Tableau Bridge to tunnel all queries in realtime directly between Tableau Online and the database and to take care of decryption.
Customers choosing this option need to deploy one or many Tableau Bridge client(s) on a VM that is within reach to the SQL DB. They will also need to install sqlserver 17.4+ odbc driver, so that it can take care of data decryption.
Everything else works in the same way as in Proposed Solution 1.
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.
Microsoft seems to recognize this as a serious issue, hence it’s released Always Encrypted with Security Enclaves to try to address some of these limitations. However, still to date (as of Aug 2021), none of the Always Encrypted methods supports rich enough calculations that include aggregations (e.g. SUM, AVG, COUNT) which are needed by Tableau or any BI platform users. In fact, this could be a major blocker for self-service analytics adoption, as the majority of use cases involve data aggregations. After all, Aggregated Measures is Tableau’s Hot Sauce :D
Not to worry! We can still access Always Encrypted columns in Tableau, we just have to instruct it not to aggregate data by default. This workaround should suffice for simple reports, where we want to present data in tabular format at the lowest lever of granularity (i.e. individual row level, as it’s stored in the database). After all, we’re dealing with PII data, so we shouldn’t be “playing” with it anyway.
This is what happens when we query in Tableau encrypted columns (e.g. SSN and Salary) and how the workaround comes handy:
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.
This is achieved by adding a few extra connection strings parameters, such as these:
To effectively leverage these parametes in Tableau, we have the following options:
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:
As you can see above, you can mix an match parameters to meet your requirements. In these tdc samples I’ve chosen to keep the encryption keys locally, so I’m not specifying anything related to key secrets, as it will simply use the ones found immediately in the trusted store. However a most common scenario might be to store these keys in a centralized Azure Key Vault.
Place these .tdc files in one of these folders, depending on your operating system and Tableau client:
- 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:
tsm configuration set -k native_api.ODBCConnectionStringSecurityList -v ColumnEncryption,KeyStoreSecret --force-keys
tsm pending-changes apply
BEWARE: As soon as you add a custom TDC to Tableau Server, all data source connections for that specific database class will include those customizations. The issue comes starts when you try to connect to a database table that is not Always Encrypted, you will see bad data. Again, this is a limitation of Microsoft’s implementation.
For this reason, I do thing ODBC is a very compelling option!
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.
Make sure you have the latest sql driver installed (v17.4+), you can get them from here: https://www.tableau.com/support/drivers
Here’s an example in Windows:
b.1) Open ODBC Data Source Administrator (64-bit) → New System DSN → choose SQL Server 17 driver
b.2) Select the appropriate database authentication method
b.3) Enable Column Encryption (aka Always Encrypted). Optionally enter the attestation provider, if you are using one:
b.4) Save the new DNS config and go to Windows Registry to add the additional connection string parameters as needed in this location:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<DNS-NAME>:
And that’s it!
Now just hop onto Tableau Server or Desktop, choose the appropriate connector and have fun seeing Always Encrypted data in plain text!
I’d love to hear about your use case for Always Encrypted. Please share your story below!
