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?

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
gillonba
  • 343
  • 1
  • 4
  • 16
  • 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 – gillonba Oct 28 '09 at 14:37

5 Answers5

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

gillonba
  • 343
  • 1
  • 4
  • 16
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.

Moose
  • 1,641
  • 1
  • 9
  • 7
  • 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 – gillonba Oct 28 '09 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

  • 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 – gillonba Oct 29 '09 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.

Sam
  • 13
  • 1
  • 5
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.

  • 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 – gillonba Oct 28 '09 at 16:53