Securely alert to SQL starting in Single User Mode

Question

Securely alert to SQL starting in Single User Mode

thebody

Valued Member

Points: 66
More actions
May 24, 2022 at 10:05 pm

#4038644

So, I have a server that needs to be uber locked down, and I'd like to take the extra precaution that prevents a local administrator on the machine being able to start it in single user mode and log in as an admin. This is just one of a bunch of precautions, but it's one I'm having trouble with.
I can fire a stored procedure when the server starts that checks parameters and sends an email if it detects the single user flag. This works fine if I run it manually, but start up stored procs don't fire in single user mode so doesn't work. I can't fire it from SQL Agent because that won't be started, and I want to avoid just querying the service state from powershell and scheduler or something because a local admin could just disable that.
Do I have tunnel vision here? Is it even possible to create an alert that detects this scenario?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

This could probably be done with a DDL trigger.

So obviously the local admin has at least ALTER permissions, right? A user who can set it to single user could disable or drop the trigger, if that is a concern.

thebody Valued Member Points: 66 More actions · Answer 2

The local admin would have no permissions in SQL at all, until they restart in single user mode, at which point they become a sysadmin. There's no way I am aware of to turn off that built in SQL Functionality. Start in single user - all local admins become SQL Sysadmins. This is what I am trying to protect against(or at least alert on immediately).

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

I'm not a Windows kinda guy but couldn't you make it so the server is in a different (private) domain that none of the local admins can't even get into? Kind of like building an on-prem mini-cloud?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael L John One Orange Chip Points: 27056 More actions · Answer 4

Set up a SQL job that executes on start up. Create a stored procedure that is executed by this job, and sends an email.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

thebody Valued Member Points: 66 More actions · Answer 5

Thanks Michael - that approach works fine in normal situation, but doesn't actually protect against this particular usecase as a local windows administrator can simple disable the startup of SQL agent to prevent that type of logging/response.

I'm trying to push for something similar to what Jeff talks about - actually having an independent Azure DB set up for this function. The argument I'm having is a bit circular though. The database contains data sensitive within the organisation so they don't want it outside the existing security perimeter, but they want to protect it from everyone including the people who control all of the security mechanisms that protect it.

*sigh*

Michael L John One Orange Chip Points: 27056 More actions · Answer 6

thebody wrote:

Thanks Michael - that approach works fine in normal situation, but doesn't actually protect against this particular usecase as a local windows administrator can simple disable the startup of SQL agent to prevent that type of logging/response.
I'm trying to push for something similar to what Jeff talks about - actually having an independent Azure DB set up for this function. The argument I'm having is a bit circular though. The database contains data sensitive within the organisation so they don't want it outside the existing security perimeter, but they want to protect it from everyone including the people who control all of the security mechanisms that protect it.
*sigh*

You could leverage multiple "tests" for this purpose. Executing the SQL job is one. Creating something like a PowerShell script that reads the registry, looks at the startup parameters, and sends a notification that runs on a schedule from a remote machine may help.

Have you considered something like restricting local admins? When a user needs to access the system, they request this and a process runs that grants the access?

As an example, we have a process in place where a user is granted the ability to request access to systems. If they request access to production, a process runs that adds them to the appropriate AD or Azure AD group. A process runs every 20 minutes, and it removes the access that has expired.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

thebody Valued Member Points: 66 More actions · Answer 7

Thanks Michael,

I like that concept. We do have a similar workflow available that I can plug into and alert on changes outside the whitelisted existing admins. Tying down that admin group is absolutely part of the existing protections(and the lack of least privilege here is absolutely part of the bigger discussion).

I think I am going to have to lower expectations from "No-one, including the people who design and monitor the checks, can access this system without us knowing immediately." to "We have secured this system to the best of our ability given the current policies and constraints. Here is a list of possible risks that need to be accepted by business if we cannot modify those constraints."

Ain't security fun?

Rob