November 3, 2009 at 10:01 am
Rudy Panigas (11/3/2009)
Wow! I was not expecting this article to generate so much discussion. I would like to thanks everyone for your comments and helpJohn, 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
I don't think we really understood the power of the single user mode. This stuff is not on the SQL Features list :hehe:
November 3, 2009 at 10:16 am
Nice article Rudy. Thanks for sharing!
November 3, 2009 at 10:35 am
Very useful article and discussion - we have removed the built in group from some of our instances, and I did wonder how we would recover from a SNAFU. Thanks.
November 3, 2009 at 1:59 pm
Here's another one that you can try...
Stop all the SQL Services.
Change the SQL Service account to use the localsystem service
Create a batch file with the SQLCMD connection and the CREATE LOGIN statements.
Schedule the batch file within windows scheduler and have it run under the localsystem account.
November 3, 2009 at 2:27 pm
This article is based on the assumption that you still have server access (as a local admin or above?)
The content of discussion is good.
The title should change.
[Quote]Stop all the SQL Services.
Change the SQL Service account to use the localsystem service
Create a batch file with the SQLCMD connection and the CREATE LOGIN statements.
Schedule the batch file within windows scheduler and have it run under the localsystem account. [/Quote]
He is already a local admin or above. No need to use LocalSystem account.
Jason
http://dbace.us
😛
November 3, 2009 at 3:34 pm
john.vanda (11/3/2009)
This happens if you don't want your outsourced IT Dept. seeing accounting data, such as payroll. SQL Server single user mode must use a separate set of permissions that, when active, allows anyone with local admin permissions rights to the data. So, the IT Dept. could still get into the data if they switched it to single user mode? Nice.
I know, right. Very uncomfortable feeling knowing that the network team could still get in if they really wanted to.
Remember that with the exception of strong encryption with properly handled keys, someone with physical access to the system and enough knowledge can bypass the vast majority of security.
If you really need to keep the network team away from certain information, then encryption is the way to go.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
November 3, 2009 at 4:17 pm
Thanks for this ariticle. Could come in handy one day.
Just a few things to clear up from some of the comments here:
1. You just need to be a member of the windows Administration group in order to use this
and 2. You have to be a member of the windows Administration group in order to use this
You don't need to be a member of the sysadmin group in SQL Server.
November 3, 2009 at 4:36 pm
There was a video by Popular SQL Server Security MVP Brian Kelly.
Visit sqlshare and lookup video by the above author or the link below
http://www.sqlshare.com/how-to-recover-from-being-locked-out-of-a-sql-server-2005-database_578.aspx
November 3, 2009 at 11:17 pm
Thanks Rudy for sharing this wonderful article.
Like other members, I never thought about using single-user mode in this way. I hope I'll never have to use this 🙂 .
Cheers,
Fahim
November 4, 2009 at 7:32 am
jswong05 (11/3/2009)
This article is based on the assumption that you still have server access (as a local admin or above?)The content of discussion is good.
The title should change.
The assumption that you still have server access it a correct one. If you do not have local admin access (or above) then get someone who has access to either work with you or give you the proper rights.
If you need to perform the tasks mentioned in the article then you should be letting your boss, network admin and security admin know. This is not just something you do for fun on a production server.
You mentioned the title should be changed. What would you have called it? I think this title works very well. 10,000 plus hits here is very good indeed.
Rudy
Rudy
November 4, 2009 at 1:53 pm
You mentioned the title should be changed. What would you have called it? I think this title works very well. 10,000 plus hits here is very good indeed.
People look at the title expecting to find a way to break in to a SQL Server. We have 10,000 some hackers wanna to be.
How about rename title as
How to connect to a sql server 2005 when build-in administrator account was removed?
Jason
http://dbace.us
😛
November 4, 2009 at 2:22 pm
jswong05 (11/4/2009)
You mentioned the title should be changed. What would you have called it? I think this title works very well. 10,000 plus hits here is very good indeed.
People look at the title expecting to find a way to break in to a SQL Server. We have 10,000 some hackers wanna to be.
How about rename title as
How to connect to a sql server 2005 when build-in administrator account was removed?
The intention of this article is to help DBAs with a problem that is very real, not common, but does happen. It provides a solve to a problem.
If the title is misleading to "hackers" then sorry for them. This site has not suggested that I change the title, so why would I change it?
My final point on this.
A "hacker" would need physical and logical access to the server to in order to execute these steps. It's up to the Network Administrators and Security Administrators to worry about these types of access.
Rudy
November 4, 2009 at 2:29 pm
How to connect to a sql server 2005 when build-in administrator account was removed?
My point is "lost sa password" is common. "build-in administrator account was removed" is not so common (intentionally was done in your case). You are addressing "build-in administrator account was removed" and "lost sa password".
If I call my article "how to make 1 million bucks from SQL Server 2005", it will be a hit. (sorry, don't mean disrespect, just exaggerate to make a point).
Nobody forces you to change a thing.
K. Brian Kelley -- also use title locked out, but he did not name his tilte with word "completely".
Jason
http://dbace.us
😛
November 4, 2009 at 2:42 pm
It's tragic that this excellent article is being dragged down by bickering over a title. Take the article for what it is (e.g. informative), and move on. Stop trolling please.
November 4, 2009 at 2:44 pm
Please, no disrespect taken. Actually nice to hear other points of view 🙂
Now let me see if I can answer your comments.
Yes losing the sa password is common, sort of and removing the builtin\admin account is becoming more and more common. You see, by removing this account a Network admin (or someone with equal rights like domain admins) can not connect in the SQL server and view the data. They can administrate the server (OS) but not SQL server. This makes the SQL server more secure.
But I do see what you mean with regards to the title. I think I'll try to be a bit more creative with the next article's title.
Thanks for all the input.
Rudy
Rudy
Viewing 15 posts - 31 through 45 (of 97 total)
You must be logged in to reply to this topic. Login to reply