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

FYI: OS is SQL Server 2008 (NOT R2).

Yesterday, when I was trying to get Visual Studio 2010 on a remote Win XP machine to be able to debug ASP.NET over the network, I installed Active Directory (and DNS) on my server.

Well, I gave up that.

So, I un-installed Active Directory and now I can't log on to my local SQL Server instance, MSSQLSERVER2008 (aka (local) or SERVERNAME) via Windows Authentication. I get this error message:

Cannot connect to (local). Additional information: Login failed for user 'SERVERNAME\Administrator'. (Microsoft SQL Server, Error: 18456)


Some possibly relevant info:

  • I can connect with SQL Server Authentication (but I can only administer a single database with each log in this way...and there are like 20 of em).

  • I can also still connect via asp.net user SQL Server Authentication (IIS is running on the same machine).

  • I can also still connect remotely via Visual Studio's "Server Explorer" using SQL Server Authentication.

  • Also, My Administrator Account's C:/users directory is now Administrator.SERVERNAME

  • Start > run > services.msc > SQL Server (MSQSERVER2008) properties > "Log On" = "Local System account" and "Allow service to interact with desktop" is checked.

  • Start > All Programs > MS Sql Server 2008 > Configuration Tools > SQL Server Configuration Manager > SQL Server Properties > "Log on as:" = Built-in account: (Local System) ("Network Service" doesn't work {Error: The parameter is incorrect, [0x80070057]})

  • I've tried making a new administrator account and logging on that way but it still doesn't work (same error as with the other account).


My thought is that SQL Server is only allowing connections from users from the domain I had set up temporarily with Active Directory. Maybe when I un-installed active Directory it couldn't revert SQL server back correctly? But I have no idea how to fix this or if that is what happened.

Any tips or suggestions are welcome...I've tried just about everything I can think of (which isn't much)...so now I'm looking to the professionals. :-)

Thanks!

share|improve this question

1 Answer 1

up vote 3 down vote accepted

You will need to log onto the SQL server with the SA account (SQL authentication) and remove the SERVERNAME\Administrator account and re-add it back. When you promoted the machine to a DC, and then demoted it, your Administrator account SID would have been reset. So the SID the SQL server has to identify the administrator is not valid anymore.

share|improve this answer
1  
+1 That's almost verbatim what I was typing up. sa or another login in the sysadmin role. –  squillman Sep 17 '10 at 15:19
    
Thanks! I'll have to Google most of what you said first; so many acronyms! lol. I'll give it a shot and let you know how it goes. –  David Murdoch Sep 17 '10 at 15:47
    
You guys are a geniuses. Thanks. All fixed now! –  David Murdoch Sep 17 '10 at 15:59
    
BTW, what I ended up doing was starting SQL Server in single-user mode then logging in with my Windows Account (I forgot the password for sa, I guess) then followed your instructions. –  David Murdoch Sep 17 '10 at 16:04

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.