Intelliopz

Renew expired Client Secret – SQL Server TDE with Extensible Key Management(EKM)

 

You wanted to have your SQL server databases protected by any malicious party who steals your physical media like disk drives, backup tables or other media and restore/attach the databases and browse its contents. So, you went ahead and configured TDE(Transparent Data Encryption) on your SQL server databases. 

Adding extra layer of security, you did not want to keep database encryption key(DEK) on your master database. So, you have configured Extensible key management (EKM) for your TDE database setup. 

You can read more about Securing SQL Server with Transparent Data Encryption (TDE ) – Extensible Key Management(EKM) in this article:
https://intelliopz.com.au/securing-sql-server-with-transparent-data-encryption-tde-extensible-key-management-ekm/

 

As part of this setup, you created a “client Secret”  in application registration which comes with an expiry as below. 


You have a situation now that your client secret has expired/or going to expire. 

 



What will happen if the client secret expired?

Your TDE enabled database will go into a “Recovery Pending” state. 

 

And you will see following messages in the SQL Server Logs.

 

Message
Cannot open session for cryptographic provider ‘AzureKV_EKM’. Provider error code: 3303. (Provider Error – No explanation is available, consult EKM Provider for details)

 

Now lets find out from online documents what error number 3303 means:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-connector-maintenance-troubleshooting?view=sql-server-ver15#AppendixC


And you can see the client secret expiry message in Azure portal.

 

 

 

How we can fix:

1. First you need to create a new “client secret” from the same application registration. 

Go the app registration under “Azure Active Directory” , and then click “App registration”. Then select your application name, and click “Certificates and secrets”.


Add a new “Client Secret” and add to commit. 


Please copy new “secret value” as you will not be able to see this once you leave this page. 
Now we got new secret created.

 

2. Go to SQL server and from management studio:

Alter the credential with the new client secret key values. 
Note: Please remove hyphens from client ID when constructing the secret. 

 

USE master;
ALTER CREDENTIAL sysadmin_ekm_credentials
WITH IDENTITY = 'TDESQLKeyValult',
SECRET = 'ab6fdba1ca2cd45fdca727b021b7491cf6K-E7Q456dfcVhEsQY~hzWR6iANtdw43g';



 

3. Once completed, just bounce the SQL Server services. 

Now you can see the database has recovered successfully.


Note: If this is a Availability group(AG) SQL server environment, the about steps need to be performed on all the nodes one by one. 

 

 

Summary:

It is important to know how you can recover the database once the secret has expired. But you must renew “Client secret” before it expires to ovoid any downtime for the production systems. 
The procedure described above is the same for renewing the client secret before it expired. 

 

How do you monitor and get an alert for the client secret before expiry:
That will be covered in another blog.