Troubleshoot Connectivity Issue with SQL Server Express 2005

Troubleshoot Connectivity Issue with SQL Server Express 2005

Recently, we answered a lot of customer questions about how to make a successful connection to SQL Express 2005 through MSDN forum and our blogs. Here, I collect basic info about it and a brief guide of making local and remote connection to SQL Server Express 2005.

Part I - Quick overview with SQL Server 2005 Express Edition:

http://msdn.microsoft.com/vstudio/express/sql/

http://msdn.microsoft.com/sql/express/

http://www.microsoft.com/sql/editions/express/default.mspx

 

Part II - SqlExpress Weblog and Forum:

http://blogs.msdn.com/sqlexpress/default.aspx

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1

 

Part III - SQL Express Connectivity

From Protocols point of view, I would like to give a brief guide about how to make a successful connection against Express Server from your client application.

First: Make sure the instance is running.

By default, SQL Server Express Edition is installed as a *Named Instance*, namely, it is not default instance called MSSQLSERVER, instead, by running "net start" or open services control manager, you will see a service named "MSSQL$SQLEXPRESS" running after installation. So, the instance name is "SQLExpress".

 

Secondly: Check Server ERRORLOG

Two ways:

1) By open the properties of the service, you will see the binary location, such as "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe", so normally, if you do not change configuration for the log file location, it should be located ...\MSSQL.X\MSSQL\Log\. When you open the log file, you should see some keywords such as " Express Edition " and " Server name is '<machinename>\SQLEXPRESS' " and " Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ]", etc.

2) Go to SQL Server Configuration Manager, open " SQL Server 2005 Services", choose "properties" for SQLExpress, in the advanced tab, there is a Filed called "Startup Parameter", you will find configured server log file location.

If your server was not started successfully by any reason, it is very helpful to collect info from server logs; also, you can get clear picture of protocols that server is listening on, for eg, if TCP was enabled, you should be able to see which port server is listening on, and if Np was enabled, you can make connection throgh the pipe name.

 

Thirdly: Make sure SQL Browser is enabld and running.

To enable browser, First, Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it; Secondly,You still need to make sure SqlBrowser is active. Go to SSCM, click properties of sqlbrowser service -> Advanced-> Active “Yes” or “No”, if sqlbrowser is running but is not active, the service would not serve your client with correct pipe name and Tcp port info on which your connection depends.

 

Fouthly: Configure Express if you want to mak remote connection.

By default, Named Pipe and TCP/IP Protocols were disabled after installation of SQL Express, hence, if you want to make named pipe and tcp connection, you need to follow the below instructions: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

 

Finally: Make sure correct connection string in your client application.

There are bunch of connection properties that you can specify for a SQL Connection through different providers, here, I just point to the "Server" field that point to which instance you want to connect.

Remember whenever you make connection to Express, it is a named instance, namely, you need to specify the instance name in the connection string.

 

Best practice, especially the server part in conection string.

Local Connection:

"Provider=SQLNCLI;Server=<MachineName>\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI ";

<MachineName> could be ".", "(local)","localhost", "<localhostname>".

Remote Connection:

"Provider=SQLNCLI;Server=<MachineName>\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI ";

<MachineName> could be "<remotehostname>", "<remoteIPAddress>","<FQDNofremotemachine>".

If you do not want to put intance name in the connection string, you can avoid that by specifying alias or "<machinename>,port", but we do not recommend those, since those are not convienient for you druing troubleshooting.

 

Summary:

If you encounter any questions about connectivity issue with SQL Express, please bring any exception that you saw for the above steps, and we will help you to solve the problem.

 

MING LU

SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights


 

 

Leave a Comment
  • Please add 8 and 1 and type the answer here:
  • Post
  • Thanks for this I shall post and link to here.  I was asked a general question which this should answer
  • I have installed Express 2005 on a Win2k computer. I am able to connect to the server with no problem from this machine. When I try to this Server from an XP computer on the same LAN, I get the following error:
    "Run-time error '3707': Provider cannot be found. It may not be properly installed."

    Do you know what could be wrong?

    I am attempting to connect through a connectionstring in a VB6 application. I am using the same connectionstring on both computers.

    the connectionstring is as follows:
    strConn = "DRIVER={SQL Native Client};SERVER=Reagan-AOPEN\sqlexpress;" & _
                   "DATABASE=Northwind;UID=Reagan;PWD=Reagan2006;"
  • SQL Express 2005 installs a new native driver called SQL Native Client, which is what you are using in the connection string.  On the WinXP machine you have on the network, from the error message, it appears that it does not have SQL Native Client installed.  You can either install the new driver from
    http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
    or you can change the "DRIVER" value to "DRIVER={SQL Server}" which will use MDAC.

    HTH
  • after reinstalling SQL server express edition, I changed the server name. Now I am getting alot of errors. For example, The connection provider in ASP.NET configuration is not able to connect to the database. should I change the information in machine.config? I am really confused by the new design of vs2005
  • You should not need to change anything in machine.config.  Renaming the computer after installing SQL can cause problems, I have seen this in the past with older versions of SQL.  I would suggest un-installing and re-installing SQL Server Express Edition again now that the computer is renamed.
  • I'm trying to connect using ASP 3.0 using the native client provider, I get the following error: " Response object, ASP 0104 (0x80070057)
    Operation not Allowed"

    any ideas?
  • Hi,

     If you search the errorstring, you can find helpful website about the problem, it most likely in your client APP.

    http://www.codingforums.com/showthread.php?t=54066

    Thanks!
    Ming.
  • Hi Ming,
    I am trying to connect to SQL Server 2005 Database remotely using ODBC connection Object (my requirement). the owner of the database is xx and currently i am accessing with xx login only. the permissions of the database has 'guest' added to with grant for 'Connect' enabled. but while executing the code the error i got is "Login failed for user xx. Database yy cannot be opened. login failed for xx." kindly help me out.

  • Hi, Kavitha

       Seems like you made successful connection, just need grant login for xx in your server database. Check sql booksonline might help to what is the process.

       Also, this forum can quickly resolve your issue. Please post your question there.

       http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

    Good Luck!
    Ming.
       

  • Asaspal. Memrano tu es besta. Amigo.
  • 最近在烦恼,��么就不能远程访问SQL2005的呢!怎么搞也搞不明白! 参考了下面的两个Blog,终于清晰了。 http://blogs.msdn.com/sql_protocols/ar...
  • I have SQL Express SP1, with just Shared Memory protocol enabled.  I installed using advanced settings and changing it to install as the Default Instance not the named instance.

    I am using MDAC OLEDB, connection string like "Provider=SQLOLEDB..."

    I can connect using "(local)", but I cannot connect as <machinename>.   When I try the latter I just get an error of "Timeout expired"

    On your blog earlier you said you need to use <machinename> if Shared Memory is the only enabled protocol.

    Just a little confused about this, and would appreciate a little info.

    Thanks!
  • Hi, Eric

       The error indicated that your connection was not established within the specified timeout or default timeout. So the workaround here is to enlarge connection timeout, since you are using OLEDB provider, you can directly add "Connect Timeout=120" in your connection string. The reason behind might due to when you use <machinename>, during first connection using it, MDAC would resolve it to ipaddress, so the time to make connection including the time to DNS resolution. If you try the connection for the second time, you would not came across "timeout expired" since there was cache on your machine.

    Good Luck!
    Ming.
  • Hi Eric,

    The server was working then suddenly stopped.  Any ideas?  All commands run below were on the local machine through a TS connection, if that makes any difference.

    From ERRORLOG:

    2006-09-11 21:07:03.59 spid4s      Server name is 'COMPUTER_NAME\SQLEXPRESS'. This is an informational message only. No user action is required.
    2006-09-11 21:07:03.62 spid4s      Starting up database 'msdb'.
    2006-09-11 21:07:03.84 Server      The certificate was successfully loaded for encryption.
    2006-09-11 21:07:03.95 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
    2006-09-11 21:07:03.95 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].


    C:\Documents and Settings\Administrator>osql -E -S 127.0.0.1\SQLEXPRESS

    [SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
    [SQL Native Client]Login timeout expired
    [SQL Native Client]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.

    C:\Documents and Settings\Administrator>osql -E -S COMPUTER_NAME\SQLEXPRESS
    [SQL Native Client]Shared Memory Provider: No process is on the other end of the pipe.
    [SQL Native Client]Client unable to establish connection

    C:\Documents and Settings\Administrator>osql /S\\.\pipe\MSSQL$SQLEXPRESS\sql\query /E
    [SQL Native Client]Named Pipes Provider: No process is on the other end of the pipe.
    [SQL Native Client]Client unable to establish connection

    C:\Documents and Settings\Administrator>osql /S\\.\pipe\SQLLocal\SQLEXPRESS  /E
    [SQL Native Client]Named Pipes Provider: No process is on the other end of the pipe.
    [SQL Native Client]Client unable to establish connection
  • Hi Ming,

    Thank you very much for this post - I was beginning to lose hope that someone from Microsoft cared that so many of us are battling with this problem!

    I am trying to configure my machine to allow me to connect to a local database file - rather than via SQL Server.

    Here is my connection string, which works fine on my client's server and worked fine on my development machine under VWD Express / SQL Server Express before I installed Visual Studio 2005 Team Suite (Eval) + SQL Server 2005.

    I am running XP Professional 2002 SP2, Visual Studio 2005 Team Suite (Evaluation), Visual Web Developer Express and SQL Server Express.

    The short story is that I am able to run my ASP.NET applications quite happily under the Default Development Server (//localhost:2661/SQLServerExpressTest/Default.aspx for example), but //localhost/SQLServerExpressTest/Default.aspx generates the following error:

    Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

    I would really like to be able to demonstrate my applications to clients without having to run VS to run the Development Server - I am bootstrapping my development business and want to join the Microsoft Empower program as soon as I can afford to, so that I can use VS 2005 Professional in anger.

    I also need to be able to get a code-generator I'm beta testing to be able to read the data from SQL Express database files - and to run database file based DotNetNuke websites.

    I have done the following in various attempts totalling over 60 hours in the last 4 months or so:
    1.  Checked that the SQL Server instance is running.
    2.  Checked that the SQL Browser is enabled and running.
    3.  Ensured that the folder is configured as an application in IIS.
    4.  Checked that Network Service - and Everyone, for that matter - is configured with full rights on the App-Data folder and files.
    5.  Checked the TCP/IP and Named Pipes are both enabled.
    6.  Deleted the (User specific) SQL Server Express Directory and rebooted.
    7.  Tried the SQL Express and teh SQL Server versions of the SQL Express executable.

    I've listed the various threads I've looked at and tried to implement to get things going under my signature.

    My connection string is:

    <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

    To reiterate, from an outcome point of view, I would love to be able to point IE to //localhost/foldername and have my ASP.NET data-based applications (including DotNetNuke insallations) work from local database files, without having to run an instance of VS 2005 or VWD Express for each one.

    Your post is a great starting point, for me, because it lists what to check to make sure that it all works rather than proposing one solution to a specific problem someone is experiencing.

    I would be extremely grateful for any help you could offer me.

    My best guess is that installing VS 2005 (Team Suite) + SQL Server 2005 messed things up - and some of the threads I've seen seem to be related to this combination.  I uninstalled SQL Server 2005, but it didn't make a big difference.

    Thanks you very much, Ming.

    Regards

    Gary Bartlett
    gb at prodsol dot co dot nz

    Here is a list of threads I've looked at and tried in various combinations over the last few months:

    http://forums.microsoft.com/msdn/showpost.aspx?postid=98346&siteid=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=441961&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=496500&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=521158&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=481696&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1

    http://www.sqljunkies.com/WebLog/ktegels/archive/2005/11/15/17401.aspx

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=455225&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124596&SiteID=1
Page 1 of 7 (102 items) 12345»