As a security best practice you run SQL Server under a domain account and the agent runs under another domain account. These domain accounts are not in the Domain Users group and certainly not in the Domain Admins group. Well, that’s how it should be and luckily most of the times it is. In most companies it’s also not uncommon to see a separation of duties between roles. The most skilled Windows professionals have Administrator privileges on the Operating System but cannot access installed SQL Server installations. And there is no direct need for SQL DBA’s to access the Operating System. In short, Windows Admin equals Administrator and DBA equals SYSADMIN.
Recently I have come accross a lot of installation that use LOCAL SYSTEM as the service account for SQL Server. This is not a best practice and I wanted to take this moment to show why you should not do this. And I will it works two ways as well. If you are an OS Admin you don’t want it and if you are a DBA you don’t want it as well.
First off, why should an OS Admin care if SQL is running as LOCAL SYSTEM. That’s easy, xp_cmdshell runs in the context of the service account, regardless the user who is executing the command. With LOCAL SYSTEM an SQL User can gain all sorts of permissions on the operating system you don’t want it to have. All a hacker has to do is try to elevate his permissions in SQL and since a simple webportal requires SYSADMIN permissions these days this should be no problem. Of course that’s not true but a lot of people still don’t understand what least privileged means. Running as LOCAL SYSTEM is bad from an OS perspective because you are running with the highest credentials.
But for DBA’s it’s a problem as well and this comes down to the same part as the OS Admins. If you are running SQL as LOCAL SYSTEM and thus granting LOCAL SYSTEM SYSADMIN privileges you are implicit granting all services and processes running under the LOCAL SYSTEM context full control over your SQL installation. Do you wonder what that is? Try this simple powershell oneliner to get all running services that run as LOCAL SYSTEM:
gwmi win32_service | where {$_.StartName -eq “localSystem” -and $_.Started -eq $true} | ft
So, that’s around 45 on my Windows 2008 R2 system. This means 45 processes I don’t control have access to my installation. So, now you might reconsider running SQL as LOCAL SYSTEM. Perhaps this little demonstration will make DBA’s understand the danger.
I have a plain SQL Instance, nothing fancy, just running as LOCAL SYSTEM. The only users on the system are all the builtin users present after the installation.
All an attacker, or co-worker who want access to the data, has to do is get a handle to a LOCAL SYSTEM process, create a SYSADMIN user or elevate privileges and he is off. This isn’t as hard as you think. Windows has made it real easy for you, provided you have the proper OS privileges, to start a process with LOCAL SYSTEM credentials. One way is called Windows Scheduler. The Windows Scheduler runs as LOCAL SYSTEM. All there is to it, is to schedule a task that runs a SQL Query and schedule it for one time in the future nearby, like one minute away. Get some coffee and when you return the scheduler has kicked the process and you are now officially SYSADMIN. The next screenshot shows an example of a scheduled task adding a login and granting it SYSADMIN privileges. The task is set to run as NT AUTHORITY\SYSTEM and scheduled for 19:25.
At the given time the task will run and if you query sys.server_principals you will see a user is added at the time the task ran.
And this is just one example. Almost all hacking scenarios involve getting control over high privileged system processes. True, only admins on the box can do this, but they can be your biggest threat. I used to work in a highly secured environment where we did not have to worry about people from the outside getting in, this was just not possible. The biggest threat becomes the people on the inside. Administrators who want the job done quickly and elevate their permissions, users who try to gain more access than required. Sometimes without meaning to, but sometimes with a cause. It’s not an uncommon scenario these days people on the inside are getting the information outside or use this information to their advantage. So, should you care about securing yout environment and tighten security on the inside as well. I think you should. At least be aware of dangers and risks involved. If they don’t apply to your organization that’s fine, if they do; follow the best practices and use a domain account. And remember, this domain account does not have to be a Domain User as well. This is done by default, but not necessary to run SQL Server. And remember, it’s no use to use a Domain account and then add LOCAL SYSTEM as user granting it SYSADMIN because some other service needs this.