We help IT Professionals succeed at work.

disable single user mode

1,222 Views
Last Modified: 2012-02-29
in sql 2008 or any sql for that matter, if you are a domain admin user, you can quickly stop and start the sql service in single mode and add a user into that sql instance as sysadmin. how do i stop this?
Comment
Watch Question

James MurrellQA Product Specialist
CERTIFIED EXPERT

Commented:
this may help. http://remidian.com/mssql/remove-sql-server-database-from-single-user-mode.html it sorted us out with this problem

Author

Commented:
yeah but in that article the user already added the user. how do i stop them before it all happen. and not to mention that microsoft clearly stated that they will remove this sp_dboption in the next release of sql.http://msdn.microsoft.com/en-us/library/ms187310.aspx
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I think all you need to do is to remove the BUILTIN\Administrators group or to remove some privileges from it. See here (though that writes sql 2000/2005, it applies to sql 2008 also):
http://www.mssqltips.com/sqlservertip/1017/security-issues-with-the-sql-server-builtin-administrators-group/

Author

Commented:
the builtin\administrator is not in  as i think sql2008 automatically does that for you. what i'm talking about here is someone, who is Domain admin, stop the sql service, start it up as single user mode, add a login in sqlcmd with sysadmin rights, then restart the service normal.

How do i stop people from doing that in sql?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I see...

well, I fear you cannot stop a domain admin from doing general things like:
* stop some Service (which might happen to be the sql server service)
* modify some registry entries
* start some .exe from command prompt

if you have such people doing such things, they should be given other tasks and removed from domain admins, being granted the limited permissions only that they need to do their tasks...

Author

Commented:
ok so my question is this. can i do a T-SQL BACKUP query without having sysadmin role?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Give them backupoperator role.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
To start SQL in single-user mode (or any mode at all), they must have a login and password to connect with.  In SQL2008, just being a domain admin (DA) does not give them rights to SQL.

Change the password of the login they are using.  For example, change the 'sa' password (just in case).

Checking the SQL logs might tell you what login they used to login in with to start SQL, depending on the logging options chosen.

Author

Commented:
if you are a domain admin you will be able to rdp to the sql box, and then go into sql configuration and change the service to start sql single mode. i tried. then they can just launch sqlcommand. the weird thing is sql just automatically give you permission to have access to everything when you are in sqlcommand under single user mode.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012

Commented:
You could create a Logon Trigger and if in single-user mode prevent them from logging on.  Let me know if you want me to elaborate.

Author

Commented:
yes please.
CERTIFIED EXPERT
Top Expert 2012

Commented:
start the sql service in single mode and add a user into that sql instance as sysadmin. how do i stop this?
When you say add a User, I am assuming you mean add a Login.

See if this works for you.  Please test it out carefully, as I have not tested it out and you could end up locking yourself and everyone else out totally.
CREATE TRIGGER trg_Logon ON ALL SERVER 

FOR LOGON

AS

IF ORIGINAL_LOGIN() = 'ThePeskyDomainAdminUserGoesHere'    -- Optional to limit to the one person
    If SERVERPROPERTY('IsSingleUser') = 1
	ROLLBACK

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Well, this trigger will not be a 100% safeguard...
because a person trying to do this will see the user/login not being added, could think about such a trigger, find it, disable/drop it, and still do whatever he/she wants.

again, from what I know: a Domain Admin can, in the end, do anything.
so, you should have nobody working full-time with a domain admin account, but with "limited" accounts granted the permissions to do what they need.
this might require some domain admin permissions for certain tasks (using run-as)
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
If one can't log in to SQL, how does one see a trigger?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
as domain admin, explained above, you can stop and start the sql server service into single user mode without going "into" sql server.

you can also add yourself on the sql box to the needed windows accounts to still login as an account that would bypass the trigger.

Sure, with this trigger, you can hold off 99.99% of the "hacker domain admins"...
still, the only real way to do is to remove the domain admin privilege from 99% of the "admins", actually
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
you can also add yourself on the sql box to the needed windows accounts


Isn't that only if you allow local Windows accounts access to your SQL Server, just based on being a local account?



start the sql server service into single user mode without going "into" sql server.

I didn't think starting in single-user mode disabled the startup proc(s).

If you used the startup proc as I suggested, and immediately shutdown SQL, how would anyone get into SQL to remove the startup proc?

Even if they could discern from the SQL log what happened, could they prevent it?
CERTIFIED EXPERT
Top Expert 2012

Commented:
Again, I don't know the full ramifications of this, I agree with you that the admin should not be an admin, however, I believe that the trigger should do it.  I cannot see how "you can also add yourself on the sql box to the needed windows accounts to still login as an account that would bypass the trigger" if the TRIGGER is written correctly.  But you are right, I have never had to use this before and I am sure there may be something I am overlooking.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Why a trigger instead of a startup proc?  Why even let SQL start at all if it's access you want to prevent?

The trigger is still needed as a second line of defense, of course, but I would use a startup proc too, and hopefully as the primary line of defense.
CERTIFIED EXPERT
Top Expert 2012

Commented:
Why a trigger instead of a startup proc?  Why even let SQL start at all if it's access you want to prevent?
My comments were actually directed at angelIII, but you make a good point and I don't know the answer.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.