SQL Server: Cannot log into SQL Server Management Studio on Server

Question

I installed SQL Server 2012 on a Windows Server 2012.

I am going back a few days later and unable to log into SQL Server Management Studio (SMS) using Windows Authentication, even if I launch SMS using the "Run as Admin". I keep receiving error 18456.

Sadly, I did not write down the mode that I set during the installation or the user name that I created, if any. I should have, but did not.

The event viewer shows a bit more detail. The entry is:

Login failed for user '\Administrator', Reason: Could not find a login matching the name provided. (CLIENT: )

Since SMS is not an option for anything at the moment, how do I restore access? I am logged in using the Administrator account.

Solution

It looks like you can do this the same way in 2012 that you do in 2008.

1. Shut down SQL Server in Services.
2. Launch SQL Server in single user mode (navigate to the directory with an elevated command prompt and type "SQLServr.Exe –m"
3. Start a new command line prompt.
4. Connect with sqlcmd. ("sqlcmd -S.", unless there's an instance name, then "sqlcmd -sServername\instance")

5. Create a new login for yourself on the command line:

   CREATE LOGIN [MYOFFICE-SERVER\Administrator] FROM WINDOWS; GO

6. Add that user to the sysadmin group:

   exec SP_ADDSRVROLEMEMBER 'MYOFFICE-SERVER\Administrator','SYSADMIN'; GO

7. The "GO" is important. It should acknowledge that "GO."

Good luck!

Comments

[Sarah Weinberger]

I had to open up 2 admin command prompts, as "SQLServer.exe -m" stays executing emitting status lines every once in a while. On the second command prompt, I entered sqlcmd -S<my server name> and that got me to a 1> prompt. I entered the two lines (CREATE... and SP_...). That got me to a 3> prompt. I then ctrl+x out of the sqlservre.xe and restarted the SQL Server service. I then went back to SMS and no go, same error. I selected the same, Windows Authentication mode, not that I have other choices. DId I do something wrong?

[Katherine Villyard]

You need to add the GO. 1> CREATE LOGIN .\Administrator FROM WINDOWS 2> GO 3> SP_ADDSRVROLEMEMBER '.\Administrator','SYSADMIN' 4> GO It should acknowledge the "GO" with a success or failure message.

[Sarah Weinberger]

2 issues. I had to break Go into a separate number, so that I could see the message, but the other issue I am not sure how to solve. Say the server name is myOffice-Server, so I connect using (less the double quotes) "sqlcmd -SmyOffice-Server". I then enter 1> CREATE... 2> GO and receive the message "Msg 102. Level 15. State 1, SErver MYOFFICE-SERVER, Line 1, Icorrect syntax near '-'. How do I indicate that the server name has a dash inside? Can I let -S use something else? The problem is that -S by itself did not work. It wants a server name.

[Katherine Villyard]

Yeah, hang on, editing. It's going to want a square bracket, my bad, sorry.

[Sarah Weinberger]

"sqlcmd -S[myOffice-Server]" did not work and neither did using a single and double quote around the server name. :-(

[Katherine Villyard]

"sqlcmd -smyOffice-Server", and "CREATE LOGIN [MYOFFICE-SERVER\Administrator] FROM WINDOWS"

[Katherine Villyard]

let us continue this discussion in chat

[Sarah Weinberger]

There is no acknowledgment for the second go, unless you count going back to 1> an acknowledgement. Also, I had to break the first line into 2 commands with no semi-colon. Then I got an acknowledgement and everything worked. I am able to go into SMS now. Yay!