4

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?

Improve this question
2
  • Is the SQL Server services running in a dedicated service account with admin privileges? If so can you use that to authenticate? Commented Oct 27, 2009 at 19:53
  • I tried that, or close enough, by setting it to run under my admin account. It did not work. It seemed like a good idea though Commented Oct 28, 2009 at 14:37

5 Answers 5

Reset to default
2

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

Improve this answer
1

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.

Improve this answer
1
  • I have re-attached databases and restored backups, so that shouldn't be a problem -- so long as the data is good, which it should be since the users say it is working correctly Commented Oct 28, 2009 at 14:45
1

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

Improve this answer
1
  • Yes, like I said towards the end, I did try that. But no luck. Thanks for the advice though. It looks like a re-install for me Commented Oct 29, 2009 at 19:29
0

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.

Improve this answer
0

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.

Improve this answer
1
  • The server itself won't be reformatted. There are too many other things running on it for that to work, unfortunately. I'd like to reformat it Commented Oct 28, 2009 at 16:53

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.