TechnicalArchitectureWorx

The (Unofficial) ITWorx Technical Architecture Blog

MS SQL 2005 remote connection problem.

Posted by archworx on October 16, 2006

I am sure this is a problem a lot of people face each day.

Description:

When you try to connect to SQL 2005 remotely, you get an error message in you application that says:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)”

Probable Cause:

The most probable cause of this problem is that your SQL Server is not configured for remote connections.

Solution:

The solution to this problem is to apply the configuration settings outlined in this MSDN article.

N.B. when you configure the SQL 2005 Surface Area, make sure you choose “using both TCP/IP and Named Pipes”.
SQL 2005 remote connection configuration

12 Responses to “MS SQL 2005 remote connection problem.”

  1. Moheb said

    It is to strange , but it seems they r trying to close a lot of things for security purpoase “May be “, which makes things doesn’t work as it should be
    Moheb

  2. Steve said

    Hi I have the same problem and have set the SQL Server Surface area Configuration as discussed above and still have the problem. I am using VS 2005 and SQL Server 2005. Any other suggestions would be really welcome.
    regards Steve

  3. archworx said

    Dear Steve,
    Did you try the article linked above?
    Please keep me posted if you are still having trouble.

  4. Ibraheem Hamdi said

    Maybe it’s late to participate, but choosing both pipes & TCP/IP is important to connect to the server from a Java application, even though it’s local!

    Another thing to be configured, concerning Java also, is the mixed mode authentication ..

    Ibraheem (Rookie)

  5. Vene said

    Yes and DIDN’T WORK WITH ME, PLEASE READ…
    Recently I uploaded the free version of SQL 2005 EXPRESS EDITION, previous of that I was working with SQL 2000 with no problem at all, to migrate my database from SQL 2000 to SQL 2005 I used the restore procedure and was successfully updated.
    I can interact with my new SQL 2005 database using SERVER MANAGEMENT STUDIO EXPRESS, the problem is remotely I can’t open it!!
    So, I did some research and knew that SQL 2005 by default is not remotely enabled and the solution is:

    1.Enable the TCP/IP protocol using the Surface Area Configuration Utility
    2.Make sure the TCP/IP protocol is enabled in the SQL Server Configuration Utility
    3.start SQL Server browser
    4.Exceptions are added in Firewall
    5.Restart the computer

    Well, I did this probably 10 times but the error still is coming, here is the error
    ……A connection was successfully established with the server, but then an error occurred during the pre-login handshake. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 – An invalid argument was supplied.)….
    This is the Web.config connection string I’m using (NET 2.0)

    Can someone help in this one, please….
    Vene

  6. Vene said

    Sorry I forgot to write the connection string I use

  7. Vene said

    Well I did not forget to write the connection string but Iguess the emaildo not like
    connectionStrings

    add name=”myconectionmame” connectionString=”Data Source=(local);Initial Catalog=nydatabasename;User ID=sa;PWD=mypassword” providerName=”System.Data.SqlClient”

    connectionStrings

  8. Well I’m gonna have to look up a couple more things but this was a good strting point.

  9. glee videobb…

    […]MS SQL 2005 remote connection problem. « TechnicalArchitectureWorx[…]…

  10. Fashion Stud Earrings…

    […]MS SQL 2005 remote connection problem. « TechnicalArchitectureWorx[…]…

  11. Moshi Monsters…

    […]MS SQL 2005 remote connection problem. « TechnicalArchitectureWorx[…]…

  12. catering equipment South Africa…

    […]MS SQL 2005 remote connection problem. « TechnicalArchitectureWorx[…]…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: