I was having a conversation with some friends the other day and Jen McCown (blog|twitter) asked about SQL Server security references and “What’s something that’s really difficult in SQL Server Security.” As happens sometimes I started thinking about this in the back of my head and I realized something. The two absolute hardest things that people run into with security (at least in my ever so humble opinion) are
- Who: or Who am I when I try to access a resource?
- Where: or Where am I when I try to access a resource? And Where is that resource?
Now the two are very heavily related but let’s start with Where?
In other words: Which instance am I on? Which database? What machine and what directory am I running that Powershell script from? I mean how often have you run that script in master instead of the correct user database? Or you’re running a Powershell script that has files in the same directory but for some reason you can’t find them? Just to make it a bit worse, let’s bring in the cloud. For example, is my instance a managed instance? Is it on-premises? Are the resources I need in the same subscription?
Now let’s talk about Who?
Let’s start with one of the easiest to follow, but most commonly made mistakes in security (again, from my point of view). The name of something is not that thing. I’m Ken.Fisher in AAD (Azure Active Directory), Ken.Fisher in AD (Active Directory), Ken.Fisher is my user name in the database named Ken.Fisher and it’s also my name in the database Ken.Fisqher. That’s a lot of Ken’s! And every single one of them is different. Did you know there is a billionaire out there named Ken Fisher? I’m not him either (although I wouldn’t mind access to his bank accounts for a day or two).
Next is the much more difficult aspect of Who. I’m running an app. What account is that app using to connect to the network? To the database? etc. It might be using my network account (at least initially), it also could be using an application Id. Heck, it could be using one account to access network/file system resources and an entirely different (or even several) account(s) to access one or more databases. This can be a real problem at times. In fact I put together a post about using an extended event session to catch errors just so I could figure out which account was getting the dread You don’t have access to this resource error.
How are they related?
It comes down to the idea that you need to know where the resource you want is and what account you’ll be using when you get there. If I need to access three different tables, each in three different databases, I’ll be using three different users (simplifying things a bit here, don’t @ me!) to access those tables.
To tie this all together let me give you a simple example: I need to access the table sys.tables in the database ken.fisqher on ManagedInstanceA. I log into my network as DomainKen.Fisher. Then I log into the Azure Active Directory account Ken.Fisher and use that account to access the appropriate server principal (login) on ManagedInstanceA. That login then needs access to the database principal (user) Ken.Fisher in the database ken.fisqher. And last but not least my user needs access to sys.tables.
Your life will be infinitely easier when working through a security problem if you remember the path you need to take from Where you are to Where the resource is and then Who you are going to be at each step. With that information you’ve got a pretty good idea which security account needs access to which resource.