0

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

  • According to the doc, CREATE MASTER KEY requires CONTROL permissions on the database. You would need to first GRANT CONTROL ON DATABASE::YourDatabase to YourUser. However, CONTROL confers more permissions than normal users need and creating a master key is a one-time task typically performed by DBAs. This looks like an x-y problem. Add details to your question why a normal user needs to do this. – Dan Guzman Dec 28 '19 at 13:39
  • Thank you Dan for your reply.I have gone through that doc before, but when I used this"GRANT CONTROL ON DATABASE::[master] to user " I was thrown an error that I cannot grant, revoke or deny permissions to sa, dbo, sys or yourself. Now what should I do ? – Ankita Potdar Dec 28 '19 at 13:49
  • Scripts I tried are: CREATE LOGIN LoaderRC20 WITH PASSWORD = 'a123STRONGpassword!'; CREATE USER LoaderRC20 FOR LOGIN LoaderRC20; ALTER ROLE dbmanager ADD MEMBER [LoaderRC20]; ALTER ROLE loginmanager ADD MEMBER [LoaderRC20]; ALTER ROLE db_owner ADD MEMBER [LoaderRC20]; GRANT CONTROL ON DATABASE::[master] to LoaderRC20; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'; – Ankita Potdar Dec 28 '19 at 13:56
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.