1

I have been trying all kinds of tricks - from enabling TCP/IP, adding exception to firewalls to poking bill gates vodoo doll. But nothing seems to work for me. Is it really that difficult to connect to sql server 2008 via management studio ? Maybe, robbing a bank would be easier.

While i figure out which bank to rob, can you help me to fix the problem ?

When I try to login to sql server 2008, this is the error i get- A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

The links i tried so far -

blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

and some more random links on the net.

sky scraper
  • 2,044
  • 6
  • 24
  • 26
  • Well, I am a new to SQL server and DBMS, so i don't really understand most of the things you say. Can you suggest how i do all those things which you mentioned ? – sky scraper Sep 07 '12 at 23:49
  • 1
    There are two ports that you must allow through the firewall to make it easy to connect to the database. Port 1434 is used to 'discover computers running SQLServer'. Port 1433 is used to connect to the database. Are both ports open on the database server? – James L. Sep 07 '12 at 23:51
  • @JamesL. - how do i check if the ports are open on the database server ? – sky scraper Sep 07 '12 at 23:53
  • @JamesL. - in SSMS type = database engine, name = (local), auth = sql server auth, because i chose mixed mode in setup and login = named instance name created during setup ie sqlserver, password = passing – sky scraper Sep 07 '12 at 23:55
  • 1
    Are you running the database server on the same computer that you are running SSMS? – James L. Sep 07 '12 at 23:56
  • I added 1433 to windows firewall exception rule according to a tutorial on the net. – sky scraper Sep 07 '12 at 23:57
  • @JamesL. - yes, the database is on my local computer – sky scraper Sep 07 '12 at 23:57
  • If you run `SQL Server Configuration Manager`, is `TCP/IP` enabled under *SQL Server Network Configuration | Protocols for MSSQLServer*? – James L. Sep 08 '12 at 00:01
  • 1
    Make sure the SQLServer service is running, and execute `netstat -nba` from an elevated DOS prompt. You should see `TCP 0.0.0.0:1433 0.0.0.0 LISTENING` and `MSSQLSERVER` on the next line under it. If you don't see this, SQLServer isn't listening on port 1433. – James L. Sep 08 '12 at 00:11
  • @JamesL. - TCP/IP was enabled. Named pipes was also enabled. But that did not solve the problem. – sky scraper Sep 08 '12 at 01:47
  • 1
    Run `netstat -nba` from an elevated DOS prompt. Look for `0.0.0.0:1433`. Is MSSQLServer listed under it. Or look for MSSQLServer. What does the TCP line read just above it? – James L. Sep 08 '12 at 01:50
  • @JamesL. - did netstat -nba. There is no port 1433 anywhere. How do I make it listen on port 1433 ? – sky scraper Sep 08 '12 at 01:57
  • 1
    The link you posted in your question to *sqlauthority* includes several comments/discussion after the blog post. Look at the second thread started by Vince. He explains how to quit using a dynamic port and force it to use port 1433. – James L. Sep 08 '12 at 02:04
  • @JamesL. - changed all the dynamic ports to blank and tcp to 1433. Chose login name as per andrei's answer. Now i get a new error - Login failed for XXX\YYY. Error 18456. Tried this link, but its useless - http://www.katieandemil.com/microsoft-sql-server-error-18456-login-failed-for-user – sky scraper Sep 08 '12 at 02:25
  • 1
    You are making progress. At least SQLServer is responding to you now. I always use `Windows Authentication` when I'm connecting to a SQLServer instance running on the same machine as SSMS. Have you tried this? If so, log in as `sa` and use the password you set during the installation. – James L. Sep 08 '12 at 02:27
  • @JamesL. Thanks man ! You are the king ! It works perfectly. I wonder why login does not work when user name = xxx\yyy. – sky scraper Sep 08 '12 at 02:32
  • 1
    You have to add your user as an authorized SQLServer account. There was an option to do that during the installation. You can also do it later through SSMS by adding a Windows user under the root *Security | Login* folder in the SSMS object explorer. – James L. Sep 08 '12 at 02:35
  • Now i am reinstalling SS2008. Same old problem. This time i forgot how to run cmd in elevated mode. For future reference, this is how it can be done - right click cmd icon > run as administrator. Everything is as you described above. extra link - http://www.mydigitallife.info/how-to-open-elevated-command-prompt-with-administrator-privileges-in-windows-vista/ – sky scraper Jan 01 '13 at 23:33

1 Answers1

2

When you installed SQLServer, there are several options that may affect the way SQLServer runs and how you have to connect to it. The link you included in your question that points to the sqlauthority blog has an additional thread posted by Vince. Be sure to follow those instructions so that SQLServer will be set to listen on port 1433 and not a dynamic port.

Also, when you log-in, if it tells you that the login fails (e.g., for xxx\yyy), then you probably missed the option in the install to add the current user as an authorized users. In that case, you will have to log in through SSMS using SQL Server Authentication, and provide sa as the username and the password that you set during the install.

After you log in, you can add your Windows user account to the list of authorized users by expanding the root Security folder in the SSMS object explorer, right-click Logins and select New Login. From there you can search for your user account and add a Windows authentication user. That will make it possible for you to log in using Windows Authentication so you won't have to type your password every time you launch SSMS.

James L.
  • 9,384
  • 5
  • 38
  • 77
  • Thank you very much James. I learned a lot from you. BTW, how can I learn all these basic concepts about sql server ? – sky scraper Sep 08 '12 at 15:19
  • 1
    I'm sure someone has put together a tutorial, or a blog, or something. But I think most of us learn it from the school of hard knocks. Sorry I can't point you to anything specific. – James L. Sep 10 '12 at 16:35