Sql-server – How to create a master key on the master database?What are the roles required to GRANT to the login and user of the master db for the same

azure-sql-data-warehouseazure-sql-databasemaster-system-databasesql servert-sql

I am trying to create a master key on a master database of my azure sql datawarehouse. But my user does not have required permissions to do so. What are the roles need to be assigned/granted to the user or login of the azure sql datawarehouse?
I was able to grant control access using GUI of SSMS to the user of sql server instance on-prem. And also I was able to create a master key with the help of that role on master database of that sql server.But there's no direct provision to provide such access to the user on azure sql warehouse using GUI of SSMS.
Is there any script to give permissions to user of master db so that I am able to create a master key on it?If yes, then what are those scripts and their sequence?I have really tried hard to find answers to these questions. Please help me to find the answers

Best Answer

I don't believe it's possible, or useful to CREATE MASTER KEY in the master database in Azure SQL Database/Azure Synapse Analytics (formerly SQL Data Warehouse). And the error you're seeing is a bit misleading, as you get that error whenever you attempt an operation in master that is allowed in normal SQL Server, but prohibited in Azure SQL Database.

The Database Master Key (DMK) in the master database is used to protect Certificates and Asymmetric Keys in master, but you can't create either of those in the master database in Azure SQL Database. And in normal SQL Server the DMK in master protects the Database Encryption Keys (DEK) for Transparent Database Encryption (TDE) of user databases.

The encryption hierarchy for TDE in Azure SQL Database works differently:

Transparent data encryption encrypts the storage of an entire database by using a symmetric key called the database encryption key. This database encryption key is protected by the transparent data encryption protector. The protector is either a service-managed certificate (service-managed transparent data encryption) or an asymmetric key stored in Azure Key Vault (Bring Your Own Key). You set the transparent data encryption protector at the server level for Azure SQL Database and Data Warehouse, and instance level for Azure SQL Managed Instance.

Related Question