November 3, 2009 at 8:37 am
True, but the servers would have to be restarted in single user mode. Hopefully your monitoring systems would alert you that the server has been restarted. You should then review all logs server logs and sql server logs and question your staff as to who and why this server was started in single user mode. I would be getting the security department involved too.
Rudy
Rudy
November 3, 2009 at 8:39 am
this way is not exact, because when you type sqlcmd -E you will obtain à time out for sql connexion because your account dont existe in sys.logins.
In the case when you have a login , it is not necessary to stop sql service you can access anr execute query like (create bultin\administrators from windows).
The group bultin\administrators allows to system administratot to connect in sysadmin, the best practise is to change the role for this group to "public".
When you install sql server, sql server add news groups like sysadmin login, you can add yout account in this group in order to connect you on sql server.
I repeat, this article is not applied in sql server
November 3, 2009 at 8:41 am
this way is not exact, because when you type sqlcmd -E you will obtain à time out for sql connexion because your account dont existe in sys.logins.
In the case when you have a login , it is not necessary to stop sql service you can access anr execute query like (create bultin\administrators from windows).
The group bultin\administrators allows to system administratot to connect in sysadmin, the best practise is to change the role for this group to "public".
When you install sql server, sql server add news groups like sysadmin login, you can add yout account in this group in order to connect you on sql server.
I repeat, this article is not applied in sql server
November 3, 2009 at 8:41 am
The main question (I think) is unanswered:
If you have removed BUILTIN\Administrators and all other administrative access to the SQL instance, how can you log into the SQL instance with administrative access?
Rudy - are you suggesting that by starting the instance in single user mode and using SQLCMD -E that the access can be bypassed? If so, that is news to me.
November 3, 2009 at 8:42 am
this way is not exact, because when you type sqlcmd -E you will obtain à time out for sql connexion because your account dont existe in sys.logins.
In the case when you have a login , it is not necessary to stop sql service you can access anr execute query like (create bultin\administrators from windows).
The group bultin\administrators allows to system administratot to connect in sysadmin, the best practise is to change the role for this group to "public".
When you install sql server, sql server add news groups like sysadmin login, you can add yout account in this group in order to connect you on sql server.
I repeat, this article is not applied in sql server
November 3, 2009 at 8:46 am
ALUN WILLIAMS (11/3/2009)
The main question (I think) is unanswered:If you have removed BUILTIN\Administrators and all other administrative access to the SQL instance, how can you log into the SQL instance with administrative access?
Rudy - are you suggesting that by starting the instance in single user mode and using SQLCMD -E that the access can be bypassed? If so, that is news to me.
It was answered. As I quoted from MSDN on page 1 of this thread...
Under certain circumstances, you may have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.
Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.
November 3, 2009 at 9:02 am
Rudy, I think several of the previous posters have never found themselves truly "locked out" of a SQL Server instance and thus, do not grasp the relevance of your article. I have found myself in this position TWICE; once when I was just getting my feet wet with SQL Server security administration (my own fault) and again later when a consultant changed the "SA" password on our development box without my knowledge. In each of those cases, the method in your article would have been invaluable. Thank you for sharing, and I hope that I never have to use it in practice! 🙂
November 3, 2009 at 9:25 am
sqlservr.exe -m
When you shut down the cmd window used to open sqlserver in single user mode, does the sql server automatically switch to a multiuser mode or do we have to do anything else ?
--Thanks
November 3, 2009 at 9:31 am
sprasad-897633 (11/3/2009)
sqlservr.exe -mWhen you shut down the cmd window used to open sqlserver in single user mode, does the sql server automatically switch to a multiuser mode or do we have to do anything else ?
--Thanks
Once you've created a windows or sql login for authentication, you'd need to restart the sql server service without the -m switch to return to a normal mode (i.e., multi-user, restricted-user, etc...)
November 3, 2009 at 9:36 am
Hi John
Thanks - was sceptical about this but just tried it and sure enough.
You learn something new every day I guess...
Rgds
November 3, 2009 at 9:41 am
ALUN WILLIAMS (11/3/2009)
Hi JohnThanks - was sceptical about this but just tried it and sure enough.
You learn something new every day I guess...
Rgds
Alun,
It blew me away too. Seems like a security "feature" I'd prefer be disabled by default and configured through sp_configure like most other sensitive security features (i.e. xp_cmdshell and DAC).
November 3, 2009 at 9:54 am
Is this the case as well for SQL 2008?
November 3, 2009 at 9:55 am
Rudy:
Very nice. Hope I never have to use this. Never would have thought of this. Very cool!!
Steve B.:-D
November 3, 2009 at 9:58 am
Wow! I was not expecting this article to generate so much discussion. I would like to thanks everyone for your comments and help
John, big thanks to you for helping me out here.
jbowers, glad to see that my article is being taken as it was meant to taken.
If you ever find yourself locked out of a SQL server regardless of how or why, I hope you remember this article. It will prove its weight in gold.
🙂
Rudy
Rudy
November 3, 2009 at 9:59 am
Glen Moffitt-317424, this does work with SQL 2008.
Rudy
Viewing 15 posts - 16 through 30 (of 97 total)
You must be logged in to reply to this topic. Login to reply