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 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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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!

share|improve this answer
    
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? –  Sarah Weinberger Jul 4 '13 at 2:52
    
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. –  Katherine Villyard Jul 4 '13 at 3:07
    
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. –  Sarah Weinberger Jul 4 '13 at 3:21
    
Yeah, hang on, editing. It's going to want a square bracket, my bad, sorry. –  Katherine Villyard Jul 4 '13 at 3:24
    
"sqlcmd -S[myOffice-Server]" did not work and neither did using a single and double quote around the server name. :-( –  Sarah Weinberger Jul 4 '13 at 3:27

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.