As I have indicated in my farewell post after I resigned from the Azure SQL Security org, there are some features and functionality-gaps which I would have loved to see being prioritized.

And as I am still feeling very attached to this work, I wanted to write down my latest thoughts on Access Control in SQL Server and Azure SQL.

Maybe some of it will even get picked up by a successor in Access Control for SQL Server and Azure SQL.

Content:

1. Make the effect of ownership-chains in SQL optional
2. Limit or block the use of SA
3. Complete the RBAC coverage for SQL using Azure IAM RBAC
4. Continue the improvements for PoLP compliance for SQL – remove sysadmin and db_owner requirements
5. Modernize SQL Agent security
6. Implement Attribute Based Access Control for SQL based on MAC-principles
7. Better Entra ID experience to allow testing and similar

Alright, let’s get to the details:

Please pretty please… 🙂

1) Make the effect of ownership-chains in SQL optional

Background

This is about a specific behavior of the SQL permission system, which, in a nutshell, leads to that when an object is called from another module or view, permissions will not be checked if the owners of the two objects in this “chain” are the same.
While this is super helpful and makes authorization much simpler to set up, it also introduces a risk of unforeseen Data Exfiltration attacks. As I have been presenting this subject to hundreds of people over the last 2 decades, I can tell from experience that less than 5% of those working with SQL understand this in all detail and consequences.

With the introduction of external access control via Microsoft Purview Data access policies this issue becomes visible from a new angle. Even such a system, designed for centrally controlling access to SQL objects via policies and its attribute based denies can be circumvented using ownership-chaining!

Feature request

My proposed solution is to make this ownership chaining based behavior explicit so it can be disabled on a per-user and per database or even better per schema-level. Instead of just assuming that no permissions need to be checked if all owners in the chain are the same, it will require the user to have a new permission, something like INHERIT_ PERMISSIONS_VIA_CHAIN. Without that, no permissions will be inherited from the ownership-chains for that user.

The following diagram depicts the behaviors possible with that change:

From well-informed sources I know that there is a working prototype 😉

2) Limit or block the use of SA

The built-in sa is still the most attacked account in SQL Server for obvious reasons (high value target, known account name and sid).

While it will need to keep working for internal processes until a big system overhaul, it should be possible to at least block or limit any interactive use of sa to block this attack vector.

Note: This will also require to improve security for SQL Server Agent (another topic on this list to make SQL Server comply with PoLP (Principle of Least Privilege) and replication.

3) Complete the RBAC coverage for SQL using Azure IAM RBAC

Background

My biggest project was to develop a way for SQL to understand and enforce access control (both Authentication and Authorization) at scale, controlled from an external point: the policy provider.

While integrating with Azure IAM RBAC (What is Azure role-based access control) would have been the logical choice, for manpower reasons we based the architecture on Microsoft Purview. This made it possible to extend the concept to a form of Attribute Based Access Control (ABAC) as well, using the classifiers from Purview. (I will get to that in a later point again)

We managed to release a comprehensive and self-sufficient set of permissions, which cover two typical roles for SQL: Performance Monitoring & Analysis as well as Security Auditing. Those are now available under the name DevOps policies within Purview (more here: Create, list, update, and delete Microsoft Purview DevOps policies ).

However, there has been no progress since then, which makes is hard to justify for anyone to start using this policy infrastructure.

Feature request

Please craft the next complete set of roles, for example “end-users” to allow the use of the most typical database objects (tables, views, functions, stored procedures) with read and write, respectively execute-access.
I would even hope that this can be continued under the Azure IAM RBAC model, which would open this space for all customers, not requiring Microsoft Purview, which as I have heard from all sides, is a big overhead and blocker for using the RBAC roles.

4) Continue the improvements for PoLP compliance for SQL – remove sysadmin and db_owner requirements

Background

While I was working in the SQL Data security team, unfortunately there was never a dedicated budget for reworking the SQL permission model. However, I managed to justify the introduction of about 50 new granular permissions with the work on the RBAC action-system: Since all these new actions by design had to be more granular than SQL was at that time, and the code for the permission checks had to be adjusted anyway, we could justify the additions to the SQL permission with exactly the fact that we had to change the specific permission checks in the engine code anyway.
So instead of waiting for a later time for a budget for SQL permission granularity improvements, we did it in one go with very little overhead.

Here is the blog post explaining the new permissions:
New granular permissions for SQL Server 2022 and Azure SQL to improve adherence with PoLP and here some more details on the work behind:
Revamped SQL Permission system for Principle of Least Privilege and external policies – internals

Feature request

My wish is that the work on removing sysadmin/db_owner requirements continue. There is too much to do to list all the details, but I can probably safely say that finishing the work on DBCC-command permissions should be on top of this list. Finalizing the new permission requirements was one of the last tasks that I finished before I left, so it should b easy to pick that up even without a PM successor in place.

The next permission changes then should be anything around security functionalities such as creating accounts, roles and managing permissions.
For example, it should be possible to differentiate between someone who creates a database role and someone who can grant permissions to a role. And both should be possible without at the same time being able to create accounts.

5) Modernize SQL Agent security

I don’t think I will offend anyone still working on SQL Server if I say what every consultant knows: The SQL Agent job system security has been a thorn in any security concept for SQL for the last 2 decades by now. It was developed in a time where PoLP and SoD (Separation of Duties) where merely exotic terms that seemed unnecessary for SQL Server management functionalities.

To allow SQL Server management with lower permissions, we need a way to allow groups of users to create and edit certain jobs instead of being forced to have every job owned by sa because of the current limitations. The current system is just not built for real-world requirements where multiple non-admins need to change a job.

Given the age of the SQL Agent job system code (much older than most engineers who still work in the SQL org), I do think it would be easier to rebuild something modern from scratch. – But by that I mean something that can be used without forcing customers to connect to the cloud to download jobs. (just saying 🙂

6) Implement Attribute Based Access Control for SQL based on MAC-principles

Background

With SQL Server 2012 Data Classification (SQL Data Discovery and Classification ) was introduced.

This functionality allows labeling columns with free text labels which then also show up in Audit records.
While working on the Purview Integration for RBAC policies, a team working in parallel also implemented a way to use Microsoft Purview Labels as the source for the MIP labels (using the infrastructure that we implemented for RBAC) and in 2024 the Purview team published the Preview for what is now called “information protection policies”: Microsoft Purview Information Protection policy)

Those policies can use labels on columns in Azure SQL database which are stored in the Purview catalog to make Deny-only decisions for queries in SQL. In other words, it is possible to specify that when a column is labelled as “sensitive” that a policy is implemented that prohibits certain users to see the content. If such user writes a query that touches the “sensitive” column, the query will fail with a permission error.

However, this kind of policy can be circumvented by using ownership-chains in SQL. (See my number 1 wish in this list). In my eyes, this severely weakens the concept of a central access control policy and should not be the default-behavior for a proper MAC- or ABAC-system in my opinion. (MAC = Mandatory Access Control, ABAC: Attribute Based Access Control)

Secondly, customers choosing to use these information protection policies currently will see their queries failing with a permission error on each denied column like so:

Failing queries like that breaks applications unnecessarily. One should consider that this is a policy that evaluates an attribute on a column and not a permission. There is a fine distinction that can be made: permissions are a static concept. You either have it or you don’t. An attribute on the other hand is not granted/given. While some attributes may behave statically, in terms of evaluation, they can be assumed dynamic. Not because a policy or a permission is changed, but because the attribute itself can be changed (by a process that is separate from policy authoring itself).

Therefore, it is much more useful if the system simply redacts the values that are to be blocked instead of failing the whole query.

Feature request

My wish would be to create a proper ABAC system that (A) does not fail queries but instead redacts columns which are not allowed to be seen, so that the query still runs but omits data for these columns and (B) ensure that this cannot be otherwise circumvented via the effect of ownership-chaining.
By redacting the data, this system could be used without risk of breaking applications.
And why it’s important to block circumventing classification-based access policies I don’t think requires further explanation :-).

Further thoughts:

It would be good to consider fundamental principles such as the Bell-La Padula model to further tighten this security model to limit which labels which persona can set and change in which direction (which requires a hierarchical relationship between classification-labels to exist in first place).
Lastly, I would hope that at last some of this functionality will be usable in SQL without using Microsoft Purview at all, including on-premises.

7) Better Entra ID experience to allow testing and similar

Entra ID and Windows Active Directory provide much stronger security guarantees that SQL Server’s own Authentication mechanism, “SQL Authentication”. Yet, even professionals like me still use SQL Auth extensively, both for testing and in demos. Why is that?
The main reason that for testing and demos professionals like me need to be able to quickly use some dummy account. This is even true for Engineers at Microsoft. This is a real world, practical issue.

However, till today there is no solution to assist with this fundamental requirement. Any account has to be properly requested from an Active Directory/Entra ID Administrator, which can go through many hoops.

What I am wishing for is that someone come up with a way to allow non-Administrators to quickly spawn up new accounts for limited use. Of course, these accounts must have no privileges at all, and it must be easy to track who the actual “owner”/creator is, so that nobody can just use this to hide his or her identity.
Admittedly this is something the SQL data team cannot built alone. But given that this is such a common reason, I thought I would mention it here.

Conclusion

If you read until here, you must be really interested in SQL Security. Maybe you even want to work in the SQL Security team?

There are other wishes I would have for SQL security, but this post is about Access Control related functionalities, which is the space I was working in.

Thank you for reading and let me know your thoughts

Andreas