Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Is the SQL Server services running in a dedicated service account with admin privileges? If so can you use that to authenticate? –  SuperCoolMoss Oct 27 '09 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 –  rotard Oct 28 '09 at 14:37
add comment

5 Answers 5

up vote 2 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
    
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 –  rotard Oct 28 '09 at 14:45
add comment

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

share|improve this answer
    
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 –  rotard Oct 29 '09 at 19:29
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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 –  rotard Oct 28 '09 at 16:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.