In this episode, we're going to look into how we can read that Always Encrypted data (On Prem, with the Key secured by Azure Key Vault), using Power BI and PowerBI Desktop.
This is part 2 in the riveting SQL Always Encrypted mini series...
Part 1 (here) we covered the basics of Always Encrypted with SSMS, and some of its gotchas.
Our encrypted table definition remains as below.
USE AETesting;
CREATE TABLE dbo.EncryptedTable(
Id INT IDENTITY(1,1) CONSTRAINT PK_EncryptedTable PRIMARY KEY CLUSTERED,
LastName nvarchar(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AECEK, ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
FirstName nvarchar(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AECEK, ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
);This is where things start to get a little more interesting compared to Pt1, as now we have a different application in the mix for reading the data. So how can that application retrieve the key needed to successfully decrypt?
Power BI Desktop
Lets assume we were developing a new report, and start with PowerBI Desktop.
As normal, we start with Get Data , then SQL Server.
And then enter the details...
Note, at this point you don't see any other options....
...and the preview?
Hmmmm, encrypted data....
The Standard SQL Server Connector for Power BI does not support Always Encrypted. There are no configuration options you can make to the connection string in this case.
The Standard SQL Server Connector for Power BI does not support Always Encrypted.
Well, that's it then, we might as well pack up and go to the pub now, right?
...usually i'd say yes, but in this case NO, put your coat back on , we just need to do things differently.
What we have to do is use a different connector, that's all. So, for this, we will use ODBC instead.
This does mean there are several other steps that we will need to complete first though. We will need add a 64bit Datasource on our local client that holds the connection details AND also has the details to access Key Vault.
So, with this in mind, lets step back slightly.
We have a Key Vault - KeyVaultAE, and a Key in there AEKey
We need to be able to access that Key Vault from elsewhere.
Azure App Registration
In order to do this, we need to create a App Registration in Azure
This then gives us something in Azure to secure access to Key Vault Against. So lets set one up now...
Just click + New Registration and give it a name. i've used PBIOPDGApp here. Click Register
Open the App Registration, and make a note of the Application (client) ID value. This is effectively it's UserID.
Next, expand the Manage blade and select Certificates and Secrets.
Select the Client Secrets tab and +New Client Secret
Enter a description and select your chosen Expiry period.
At this point, the Client Secrets List will show you the Value of the secret THIS ONE TIME ONLY. Make a note of the Value now.
Here, I created PBIOPDGApp-secret2 just to show what it looks like. When you navigate away from this screen, the Value will be permanently masked like PBIOPDGApp-secret below.
Key Vault Access
We now have an object in Azure we can use, that needs access to the resources in Azure, namely KeyVault.
So, open KeyVault, and then Access control (IAM) - Add Role Assignment.
In the list of Roles, select Key Vault Crypto User
Members - select User, group or service principal, and search for the App Registration name you created earlier. Select this.
Review and assign, to grant the access.
Now we have everything in Azure to allow access to the Key used for our Always Encrypted data.
ODBC Data Sources
Ok, back to our client on Prem.
For this to work, we need to use a newer version of ODBC that supports and allows us to configure options for Encryption.
Install ODBC V18. (V17 should work, but latest and greatest here). You can get this from: Download ODBC Driver for SQL Server - ODBC Driver for SQL Server | Microsoft Learn
Create a 64 bit datasource. (Search for it)
System DSN, as we want this for the entire computer, not just one user.
Create the DSN as normal, adding in the SQL target, and credentials etc to access the database. The key point *groan* here is we now have a Column Encryption tick box... select that and then KeyStore Configuration
Now choose KeyVaultClientSecret for the Authentication, and enter in the CLIENT ID for the Azure App Registration that you created earlier. Mine was PBIOPDGApp above.
Note, we haven't had to enter the Secret value for the Azure App Registration yet.
The Registry Secrets!
This is where things get a little clunky in my opinion.
Open Registry Editor on your client.
Navigate to \HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\{Your Datasource Name}
Enter in the Value for the secret we created earlier against the Azure App Registration.
Yes, I feel a bit dirty having to do that too.
We now have a datasource pointing to our database, that also has the correct encryption details accessible in order to open keys and decrypt data. Cool.
Back to Power BI Desktop.
Lets add our data source again, with ODBC this time as mentioned above.
Choose our new DSN... You can add the full detail in the connection string here instead to avoid the registry...i'll do that in a follow up post.
Preview.... and hurrah! We're decrypting data!
That can now be loaded as normal.
What about Power BI up in that thar cloud? This is local, with a local DSN on the developer client PC. Power BI, no desktop, no registry etc.
Power BI.
In order to make use of ODBC and the additional connection properties we need, then we need to introduce an On Premises Data Gateway (OPDW). Granted, for accessing on prem data like we want to do here, then we would need one regardless.
On Premises Data Gateway
This is your bridge to the cloud, and you install this on a VM in your on premises environment. If you have a hybrid environment, then the chances are that you may have one or more configured already.
You can download OPDG from:
Installation is simple, basically...
Choose a location to install,
an email address to use with the gateway - this needs access within your Azure tenant, you then sign in.
Register a new gateway
Enter the name of the gateway, and then a password that would be your recovery key in case you need to restore the gateway elsewhere.
Set the Region for the gateway here, ensure this is correct.
Full details can be found at: Install an on-premises data gateway | Microsoft Learn
This is now on a VM in your environment, which CAN have additional drivers installed and registry entries amended. So now, this server (or multiple servers in the case of a OPDG cluster) requires the configuration performed as with PowerBI Desktop above. Namely:
Installation of ODBC V18
Creation of DSN to point at the SQL Datasource
Amendment of registry key to include the secret value
Check that the gateway is registered correctly in your PowerBI tenant using the Settings > Manage connections and gateways as normal.
Publish your Power BI Desktop Report as normal.
You may recieve a message around a disconnected dataset, clicking on goto dataset will redirect you to the below.
Jump over to Power BI and Settings Cog, Power BI Settings
Now Semantic Models, and select the model(s) for your published PBI Report, then expand Gateway and Cloud connections.
You will be presented with:
Click the Action arrow to expand, and then Add to gateway
Now we create the connection in our OPDG, supplying a name, and the Authentication method to use to access SQL Server. You'll notice that the dsn is selected and this matches up to the name of the ODBC datasource on our OPDG VM.
When created, return to the Power BI Settings - Semantic Models, and choose the new ODBG gateway connection to map to.
Now, open your published PowerBI Report. Yay!
Lets test it by adding a row in our source. SSMS, with logged into Azure with someone who has Key access.
DECLARE @LastName nvarchar(50) = 'Wench';
DECLARE @FirstName nvarchar(50) = 'Sarah';
INSERT INTO dbo.EncryptedTable (LastName, FirstName)
VALUES (@LastName, @FirstName)
DECLARE @LastName nvarchar(50) = 'Haveyou?';
DECLARE @FirstName nvarchar(50) = 'NoFaith';
INSERT INTO dbo.EncryptedTable (LastName, FirstName)
VALUES (@LastName, @FirstName);Refresh our semantic model in the PBI workspace and... refresh our report... *drumroll *
We now have our super secret data accessible through Power BI.
The same basic approach would apply for SQL cloud offerings also, the driver used for connectivity needs to support Encryption, so the PowerBI connection needs to route through an OPDG (even though its not OnPrem as such).
Phew, we got there, that was a bit of a grind. Part of me would like this to be a lot easier, and the standard SQL connectors updated to allow Always Encryted support out of the box...given how long AE has been around now, I can't see that happening. But, at least you have a method to use PowerBI to read your encrypted data now.
So we've created Always Encrypted data in Part 1 with SSMS, and learnt how to read it using Power BI in Part 2. In Part 3 i'll demonstrate how to write Always Encrypted data using SSIS.
Hope that you have enjoyed reading.
Rodbc
Yorumlar