October 27, 2014 at 3:56 am
About a month ago I installed SQL Server 2014 on a test box. This morning I tried to use it for the first time and have been unable to connect. I am sure that I used the option to add my domain account (a local admin on the test box) as an admin user during set-up, but am unable to connect using Windows authentication. I enabled mixed authentication during installation but I can't remember the sa password - true to best practices, I didn't write this down!
Any ideas how I can recover from this situation without reinstalling the SQL server?
Tony
October 27, 2014 at 4:00 am
Is the service running?
What error do you get?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2014 at 4:09 am
Yes, the service is running. I restarted it to be sure, but it makes no difference. It is running under the
Local Network Service account.
The error message I get is as follows:
TITLE: Connect to Server
------------------------------
Cannot connect to TBDESKTOP\SQL2014.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'MicrosoftAccount\XXXXXXXXXXX@hotmail.com'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
The box is running 64 bit Windows 8.1 Pro, and I am logged on with my Windows Live account (anonymised in the error message above).
Tony
October 27, 2014 at 4:27 am
It doesn't look like you're logged in as a domain user:
Login failed for user 'MicrosoftAccount\XXXXXXXXXXX@hotmail.com'. (Microsoft SQL Server, Error: 18456)
Either log in as a domain user or run Management Studio as a domain user.
-- Gianluca Sartori
October 27, 2014 at 4:32 am
I am using the only account I ever use on that test box, and it's the one I used when I installed the software. This box is not connected to a domain, only to my home workgroup. Sorry, my initial post a bit misleading in that respect.
Tony
October 27, 2014 at 4:34 am
spaghettidba (10/27/2014)
It doesn't look like you're logged in as a domain user:Login failed for user 'MicrosoftAccount\XXXXXXXXXXX@hotmail.com'. (Microsoft SQL Server, Error: 18456)
Either log in as a domain user or run Management Studio as a domain user.
Not sure how Windows 8 works in this regard, but isn't the LiveID coupled to a domain account?
Anyway, follow this article on how to gain access to SQL Server when you are locked out:
Connect to SQL Server When System Administrators Are Locked Out
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 27, 2014 at 4:34 am
Never tried SQL Server with the Windows Live logins, I configured my win 8 box to use a local machine login instead.
Can you find the latest errorlog file and look at the exact text of the login error from there?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2014 at 4:45 am
GilaMonster (10/27/2014)
Never tried SQL Server with the Windows Live logins, I configured my win 8 box to use a local machine login instead.
I did it on my previous laptop without too much issues.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 27, 2014 at 5:04 am
Thanks Koen,
I followed your link and used the steps there...
1. Restart SQL Server in single user mode
2. Start SSMS as administrator
3. Connect to SQL Server using object explorer
It worked. I was able to connect using my Windows Live account, which hadn't been added as a user during installation (I was sure I'd done that.....). I was then able to add my Windows Live account as a sysadmin user, and also to reset the sa password to something a little bit more memorable.
Thanks for all the help
Tony
October 27, 2014 at 5:06 am
Tony Bater (10/27/2014)
Thanks Koen,I followed your link and used the steps there...
1. Restart SQL Server in single user mode
2. Start SSMS as administrator
3. Connect to SQL Server using object explorer
It worked. I was able to connect using my Windows Live account, which hadn't been added as a user during installation (I was sure I'd done that.....). I was then able to add my Windows Live account as a sysadmin user, and also to reset the sa password to something a little bit more memorable.
Thanks for all the help
OK, glad you got it solved 🙂
And thanks for posting back.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply