0

Just installed SQL Server 2012 Enterprise on my work machine, and I'm unable to connect to my local instance (MSSQLSERVER). I get the "Login failed..." message.

I enabled the SQL Server Browser service, and have tried many different combinations of logins, using both Windows Authentication and SQL Server Authentication, and nothing seems to be working.

Per some previous suggestions, I've tried disabling all services except "SQL Server (MSSQLSERVER)" and then restarting "SQL Server (MSSQLSERVER)", and that didn't work. I've tried running SSMS as Administrator, and that didn't work. I've tried "Repairing" my SQL Server install using the install CD, and that didn't work either.

Any suggestions?

slm
  • 7,615
  • 16
  • 56
  • 76
smoothgrips
  • 115
  • 1
  • 4
  • Have you looked at what protocols are enabled; TCP/IP, Named Pipes, Shared Memory? – joeqwerty May 07 '13 at 15:01
  • How can I verify which protocols are enabled/disabled? EDIT: In Configuration Manager, Named Pipes were disabled. I enabled it and restarted the SQL Server instance and am still unable to connect – smoothgrips May 07 '13 at 15:25

2 Answers2

2

Gary, the part of SQL Server setup you need to look at is as follows. Make sure you record what users and groups are in the Specify SQL Server administrators section.

enter image description here

Greenstone Walker
  • 779
  • 1
  • 5
  • 16
0

If you can't get in with Windows authentication most likely you didn't provision any Windows accounts during setup

It used to be that the builtin\administrators group was automatically provisioned with the sysadmin role but that changed in SQL 2008 and you now need to manually provision Windows accounts / groups either during setup or post-installation.

It's likely that you'll need to log in using the builtin sa account and set up your authentication as sa. If you've tried logging in as sa and are still getting an error then it's probably a transport issue. If this is the case can you post the detailed error message you get?

EDIT
Based on your comments, check your server and client transport settings to make sure the protocols are enabled.

In SQL Server Configuration Manager:

  1. Expand SQL Server Network Configuration and click Protocols for MSSQLSERVER
  2. Check that Shared Memory is enabled
  3. Expand SQL Native Client 11.0 Configuration and click Client Protocols
  4. Check that Shared Memory is enabled

You will need to restart the SQL Server service if you need to change the SQL Server Network Configuration settings.

squillman
  • 37,883
  • 12
  • 92
  • 146
  • When I try to connect using my computer name as the server name, using SQL Server Authentication, with "sa" as the login (no password), I get "Login failed for user 'sa'". When I _first_ tried using login = "sa" with my machine's password I use for logging in, I got an error, something along the lines of "Able to connect but experienced a transport error". Now when I try the same thing I get "Login failed for user 'sa'". It seems that transport error was a one time thing. EDIT: closed SSMS, reopened, tried logging in with "sa" and local password, got that error message again: – smoothgrips May 07 '13 at 15:09
  • The sa password won't be blank, and it won't be the same as the password you login to your machine with. The sa account is a SQL Server specific account with its own password. You would have set this password during installation. That's the password you need to use. – squillman May 07 '13 at 15:12
  • "A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233) --> No process is on the other end of the pipe" – smoothgrips May 07 '13 at 15:13
  • I never set any sa password during the installation process – smoothgrips May 07 '13 at 15:14
  • "No process is on the other end of the pipe" - is the SQL Server service started? Also, are you saying that you left the sa password blank during setup? – squillman May 07 '13 at 15:23
  • Yes, all processes are started, include SQL Server Browser – smoothgrips May 07 '13 at 15:24
  • See my edit to check the protocol settings for Shared Memory – squillman May 07 '13 at 15:30
  • In Configuration Manager, I've verified that all protocols (Shared Memory, Named Pipes, and TCP/IP) are enabled for both SQL Server Network Configuration and SQL Native Client 11.0 Configuration. After restarting the service, I am still getting the "Login failed..." message. I did find the SQL Server instance, opened Properties, and have tried playing around with the Log On tab, changing it from Built-in account (all three options), and also "This account:" and have tried setting that, all with no luck. – smoothgrips May 07 '13 at 15:39
  • Ok that's good, hmmm. So just to confirm, in SSMS you are entering your machine name in the Server Name box, SQL Authentication in the Authentication drop-down, sa in the User name box and a blank password, right? You can also try entering `(local)` in the Server name box. – squillman May 07 '13 at 15:44
  • When I enter my machine name in the Server Name box, SQL Authentication in the Authentication drop-down, sa in the User name bok and a blank password, I get the "No process is on the other end of the pipe" message. When I try "(local)" in the Server name box, I get the same error message – smoothgrips May 07 '13 at 16:08
  • Ah, ok. So the issue is that you did not install with mixed mode authentication. Sorry, I was thinking backwards in my original answer (I edited my incorrect statement out of my answer). Your server is running in Windows Authentication only mode so the sa account is not enabled. You'll need to log into your machine with the same account that you installed SQL Server with to connect. If that doesn't work then your best bet is to reinstall and make sure you provision Windows accounts / groups during install. – squillman May 07 '13 at 16:25
  • Okay, yeah, I was leaning towards a complete uninstall and a fresh install. I will take the new install very slowly, making sure to read everything carefully. Thank you for your help, I appreciate it. – smoothgrips May 07 '13 at 16:31