Home / server / Questions / 78450
Accepted
gillonba
Asked: 2009-10-27 12:02:19 +0800 CST

locked out of my own SQL server

  • 772

I have a server at a branch location that is running SQL 2005 and I can't seem to log in. The users are able to use the application that depends on the database just fine, but I cannot access the SQL server with the admin account. We only had one account configured for admin access and no SA account. Regardless of whether I try to log on through SQL management studio or "osql -E" through the command line I get this entry in the logs:

2009-10-26 15:55:39.14 Logon Error: 18456, Severity: 14, State: 11. 2009-10-26 15:55:39.14 Logon Login failed for user ''. [CLIENT: ]

It doesn't seem to make any difference whether I run the server in multi- or single-user mode. All the advice I have been able to find online so far has requied that I either log on with another user account (that's the problem...) or log in under single-user mode (tried it). Is there another way to gain access? Maybe a way to set an SA password so I can log on that way? Do I just have to reinstall SQL?

sql-server

5 Answers

  1. Best Answer

    A reinstall worked. I have my server back. This time the SA account is being left on. I suspect that part of the problem was that the server is a domain controller. Supposedly that complicates authentication. But I am back online, I just have to restore the DB and reconfigure the logins. Thanks to everyone to posted

    • 2

  2. if you have the server configured for mixed or windows integrated mode, you can check to make sure that your account is in the local administrators group on the machine.

    If that doesn't work, if you do not have any encrypted fields, you can stop the server service and copy the databases before you reinstall. Once you reinstall, you should be able to re-attach the databases and correct the security in them.

    This is not exactly for the faint of heart though. Good luck.

    • 1

  3. This may solve your problem: anyone with Windows administrative access can stop SQL Server, start it in single-user mode, and modify SQL logins at will.

    http://msdn.microsoft.com/en-us/library/dd207004.aspx

    • 1

  4. As far as i know, a reinstall is all you can do at this point. I would google and google well for a way to get that data backed up.

    • 0

  5. I'd also look for a way to copy the database files off and then attach them to an SQL instance on another machine to make sure the data is usable before you blow away the first machine.

    • 0