3

I just deployed a fresh installation of SQL Server 2012 on a Windows Server 2012 Datacenter R2 environment. The installation completed with 0 warnings and 0 failures.

During the installation I specified Integrated Windows Authentication so it did not prompt me for a local SA password. I added the domain security group that should be granted administrative rights as well.

Immediately after the installer completed, I launched SMSS and am greeted by this error when attempting to connect.

enter image description here

After clicking okay and then pressing "Connect" a second time I get this dialog.

enter image description here

The TechNet article relating to this issue says to ensure that Named Pipes is behind TCP/IP in the protocol order, which it is.

enter image description here

Any help would be greatly appreciated.

UPDATE I tried switching to SQL Authentication mode by launching in Single User Mode, enabling the 'sa' account and setting the password but the errors remain the same.

Improve this question
9
  • Open the ERRORLOG file with notepad (you'll have to search your system for ERRORLOG files with no extension) and see what state is associated with the 18456 error. You can see a mapping here. sqlblog.org/2020/07/28/troubleshooting-error-18456
    – Aaron Bertrand
    Apr 28, 2014 at 18:57
  • Possibly stupid questions but have you double checked that you created a default instance not a named one? Are you certain that the service is actually running?
    – Kenneth Fisher
    Apr 28, 2014 at 18:58
  • @Kenneth it looks from the Configuration Manager screen shot that it is a default instance. We can't tell from there whether it's actually running, but the Login failed error means it must be.
    – Aaron Bertrand
    Apr 28, 2014 at 18:59
  • @AaronBertrand Duh, I missed the Network Config entry. And your right of course. If he get's a login error it has to be up.
    – Kenneth Fisher
    Apr 28, 2014 at 19:02
  • Also, when you installed SQL Server, did you add this login to the list of the instance's administrators? This is an extra step not covered by merely selecting Windows Auth during setup (though it is on the same screen).
    – Aaron Bertrand
    Apr 28, 2014 at 19:04

1 Answer 1

Reset to default
3

Yup, that last little comment was it. Even though you can select a domain group for the Instance administrators, it will create this problem. You must add individual user accounts. Thanks for the help and pointers!

In the end I put it into single user mode and ran the following command from an elevated prompt:

sqlcmd -S localhost from the command prompt

EXEC sp_addsrvrolemember
'domain\username', 'sysadmin'; GO

exit

Restarted the SQL services normally and went right in.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.