Azure Database Security Series (Part 2)- Basic Database Protection

Continuing the journey into a more secure setup for personal information in Azure we will discuss some of the simpler things to do when designing your solutions this part of the journey will look at some basic configurations you can perform to limit access to your data containing personal information. This part of the series will focus on preventing unauthorized access of your data sources.

Limit access to the resources

The consequences of giving people too much access may cause, deliberate or accidental, breaches to your protection of personal data. You have several scenarios that may come to play like:

  • the disgruntled employee scenario: An employee who thinks they have been treated unfairly may do harm or steal data for the purpose of “bringing you down” with them.
  • whistleblowers: If it is simple to access data it is easier to export the information and use as whistleblower information.
  • identity traders: Identity theft is a real nuisance as of now (to put it mildly). With lists of SSN, and addresses you can often order credit cards for the owners, fake identity cards etc. Selling of such information could be very profitable.
  • accidental deletion of data or Azure resources. If you have permission to perform “bad things”, there is a chance that you could do it. If you can’t access then you don’t have that possibility.

Avoid Subscription based rights

One common issue is that too many people have too much rights for the resources in Azure. I believe that your Azure subscription is a valuable asset and should be treated as such. In many cases you will find the Azure subscription littered with many users, roles with owner rights on the entire subscription. This means that they will inherit the rights down to resources that they may not be entitled to use. While giving access to the entire subscription will save you time you will also put yourself at risk and its all because of laziness and lack of plan.

Don’t add owners on subscription level

So what should you do instead? Try to follow the principle of least sufficient rights. First of all you could

  1. create multiple subscriptions (one per solution): But this would probably not be enough anyway since in a team you likely have different access rights for different resources even in a team so giving all owner rights in a solution-based Azure Subscription policy would likely give the participants far too much rights. So even if you do you would probably want to combine this with the next approach.
  2. Enable Role Based Access Control (RBAC) on a fine-grained detail: Assign rights on resource groups and individual resources.
    1. Use resource groups and grant access to them for a team with identical access
    2. Assign rights on individual resources when appropriate. A SQL Security manager does not need access to your web apps.
    3. Use the entire set of roles when assigning rights to groups and people. Again don’t give your SQL Security Responsible owner rights to the database. Give a more restrictive access. You can find a complete list of roles here.
    4. Avoid the owner role as far as possible, at least lower it to contributor and reader (these roles may not give rights to others which is an important security measure).
    5. Use Azure AD Groups instead of assigning individuals directly to resources/resource groups.

Use the correct roles according to least needed privilege principle

Review your effective permissions at regular intervals

Make it a recurring task to review the effective user rights and remove people who should not have access to the resources anymore from the roles in question. I have seen, many times, that people who have quit their jobs still have rights (even owner) years after they left their assignments.

Prevent accidental removal of resources

While we in the previous section described how to assign the least needed privilege to the team working with your Azure resources you can further prevent accidental deletes of critical resources by locking them. Only owners and User-Access-Administrators may add or remove locks. You can read more about how the different locks affects your resource here.

Lock Resource

Encryption at rest (Transparent data encryption)

Encryption-at-rest is a feature to make sure that the data is encrypted where it is stored. If someone gains access to the storage where the database (files) are physically stored – that person cannot read the content. This applies to Backups and log files as well. The services utilise Azure Key Vault Service to encrypt the data.

Generally this should always be enabled and is for most consumers needed for legislative compliance. Encryption-at-rest is a feature that is nowadays set by default when you create a new SQL Database in Azure. However, this has not always been the case and you could benefit from reviewing if you have an older database (likely upgraded from a previous version of the SQL database PaaS service).

It is also important to understand that, by default, Microsoft will handle the vault keys and rotate them regularly, this basically means that this is a part of the SQL Database service. You may however decide to use custom key vault keys instead, i.e. keys that you have created yourself. Both these models have their respective advantages and disadvantages that have to be considered. Basically it is simpler to out-source the maintenance of this to Microsoft but if you need total control over the keys for some reason you may be inclined to use custom keys instead. But then you are responsible for managing the life-cycle of the keys (for example key rotation) yourself and if you don’t do it you likely have a less secure database than the one managed by Microsoft even if you use your own keys. I would recommend reading the following link for a more detailed description of the different options at this url.

You enable/disable Encryption-at-rest with Transparent-data-encryption on each database with a single click (assuming you allow the SQL Database service to manage your encryption keys).


Don’t use the SQL Database Admin account in your application

Again, the server admin account is a valuable resource that should not fall into the wrong hands. You should utilize a least needed privilege policy also here.

You should create a separate database user for your application and also for your DevOps team to limit the amount of rights they get. Some applications only need SELECT rights (like db_reader) some need both db_reader and writer while others need a custom role. Either way they don’t need to run as a user with  full rights to add, remove databases.

You can very easily create a new database user. You can create the Login first in the master database or you could create a user directly in the database as a contained user. The benefit of a contained user is that it will be restored completely on a database restore while the logins in the master database have to be recreated and resynced with the user in the alternative approach.

–Contained User
CREATE USER MyContainedAppUser WITH PASSWORD = ‘sdkasjdasjd!!”2’;
ALTER ROLE db_datareader ADD MEMBER MyContainedAppUser;

–login + user
CREATE LOGIN MyAppUserInMDBLogin WITH PASSWORD = ‘#ysadybh224!’
–in local db
–in own db
ALTER ROLE db_datareader ADD MEMBER MyAppUserInMDBUser;


Next step:

In the next session I will show how you can encrypt columns that contains sensitive information and only grant the data to be read through your application and therefore disabling people with database access.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s