Home / server / Questions / 182239
Accepted
David Murdoch
Asked: 2010-09-18 07:02:09 +0800 CST

SQL Server 2008 - Can't Log On via local SQL Server Management Studio

  • 772

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!

windows-server-2008 sql-server-2008 windows-authentication

1 Answers

  1. Best Answer

    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.

    • 3