How to Connect to a SQL 2005 Server When You Are Completely Locked Out

  • 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 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

    I don't think we really understood the power of the single user mode. This stuff is not on the SQL Features list :hehe:

  • Nice article Rudy. Thanks for sharing!

  • 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.

  • 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.



    Shamless self promotion - read my blog http://sirsql.net

  • 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.

  • 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/

  • 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.

  • 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

  • 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



    [font="Tahoma"]Fahim Ahmed[/font]
    [font="Times New Roman"]Knowledge is a journey, not a destiny [/font]

  • 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

  • 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?

  • 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

  • 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".

  • 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.

  • 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