Cannot to connect to SQL server 2008?

1
votes

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.

1
sql-server-2008
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.
Are you running the database server on the same computer that you are running SSMS? - James L.
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.
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.
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.

1 Answers

2
votes

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.