Addressing SQL Server and TDE with AKV errors


I recently wrote an Azure Data Studio Notebook on how to setup TDE for SQL Server 2019 Standard Edition (yes, SQL Server 2019 Standard Edition has TDE) using Azure Key Vault. I ran into a few issues that I had to debug, which I am outlining below. Make sure that you are following the pre-requisites when you are setting TDE with Azure Key Vault.

The first one was a 404 error. When I looked the application event log, I saw the following error:

Operation: getKeyByName
Key Name: ContosoRSAKey0
Message: [error:112, info:404, state:0] The server responded 404, because the key name was not found. Please make sure the key name exists in your vault.

The simple reason for the above error is that I was using an incorrect key name or the key didn’t exist in my Azure Key Vault. So the remediation is to check if the key exists in your Azure Key Vault. If not, then create the key.

Another error I ran into was a 401 error. The following information was included with the event:

Operation: acquireToken
Key Name:
Message: [error:108, info:401, state:0] Server responded 401 for the request. Make sure the client Id and secret are correct, and the credential string is a concatenation of AAD client Id and secret without hyphens.

The CREATE CREDENTIAL command has the following syntax:

CREATE CREDENTIAL Azure_EKM_TDE_cred WITH IDENTITY = 'SQLStandardKeyVault', -- for global Azure -- WITH IDENTITY = 'ContosoDevKeyVault.vault.usgovcloudapi.net', -- for Azure Government -- WITH IDENTITY = 'ContosoDevKeyVault.vault.azure.cn', -- for Azure China 21Vianet -- WITH IDENTITY = 'ContosoDevKeyVault.vault.microsoftazure.de', -- for Azure Germany SECRET = '<combination of AAD Client ID without hyphens and AAD Client Secret>' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov

The IDENTITY here is the name of your Azure key vault.
The SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together. You will need to create a “New Client Secret” for your Azure AD app registration. See steps here.

Your AAD Client ID will be a GUID and so will your Client Secret will be a random alphanumeric string. If you don’t have the client secret, then create new one and use that.

Upcoming sessions at Microsoft Ignite and PASS Summit


The next week of November is going to be an action packed week for me with two back to back conferences: Microsoft Ignite 2019, Orlando, Florida and PASS Summit 2019, Seattle, Washington.

Below are the sessions that I will be delivering at Microsoft Ignite.

Mission critical performance with SQL Server 2019 – In this session, my colleague, Kevin Farlee, and I will be talking about the various performance and scale improvements that SQL Server 2019 will be delivering at a great price performance that lets you run SQL Server 2019 with the best TCO for your Tier-1 workloads.

Azure SQL Database Edge – Overview – In this session, my colleague, Sourabh Agarwal, and I will talk about the new innovations we are bringing to the edge for ARM64 and x64 with Azure SQL Database Edge. We will also talk about some of the scenarios where Azure SQL Database Edge helped make our customers successful in their IoT applications.

Azure Arc: Bring Azure Data Services to On-Premises, Multi-Cloud and Edge – In this session, James Rowland Jones and I will walk you through the Azure Arc announcements and show you deployments of our data services on Azure Arc.

If you are going to Ignite and are interested in Data, then we hope to see you at our sessions.

At PASS Summit, I will be deliver another session on Azure SQL Database Edge which will talk more about how you can “Develop once, deploy anywhere” with our edge database offering.

Looking forward to see the #SQLFamily at PASS Summit and Microsoft Ignite.

SQL PASS Summit 2017


It is that time of the year when I get to meet the SQL Family. It is always wonderful to put a face to that Twitter handle that I exchanged #sqlchats with or connected with on LinkedIn. SQL PASS Summit is probably one of the largest gathering of data professionals under a single roof.

This year, I will be presenting a session on “Building One Million Predictions Per Second Using SQL-R”.

Date: Nov 3rd, 2017
Time: 11am
Room: Tahoma 5 (TCC Level 3)
Abstract:
Using the power of OLTP and data transformation in SQL 2016 and advanced analytics in Microsoft R Server, various industries that really push the boundary of processing higher number of transaction per second (tps) for different use cases. In this talk, we will walk through the use case of predicting loan charge off (loan default) rate, architecture configuration that enable this use case, and rich visual dashboard that allow customer to do what-if analysis. Attend this session to find out how SQL + R allows you to build an “intelligent data warehouse”.

There will be a number of sessions delivered at PASS from the Tiger team this year and you will find a lot of the folks at the SQL Server Clinic.

If you have a technical question, a troubleshooting challenge, give product feedback, or want to find out about best practices running your SQL Server? Then the SQL Clinic is the place you want to go to. SQL Clinic is the hub of technical experts from SQL Product Group (including Tiger team), SQL CAT, SQL Customer Support Services (CSS), SQL Premier Field Engineering (PFE) and others.

SQL PASS Summit gives me an unique opportunity to meet the #SQLFamily during an annual event, gather feedback from customers and get to see some old friends from across the globe!

Hope to see you at the event!