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 1 and 2 and type the answer here:
  • Post
  • Perhaps you are using C# and not using @ string prefix, then try:

    localhost\\SQLExpress

    Or you can use @ prefix on connection string, then you do not need to escape backslash:

    @"Server=localhost\SQLExpress;..."

  • I am almost getting frustrated please help me.

    I need to establish a connection to the sqlexpress on my system from my visual c#. I am programming a smart device using visual studio 2005.

    I did the following

    I enabled remote connections from the surface area config for tcp and named pipes

    I started the browser

    I could connect from MS visual studio

    The server is up and running

    but when i tried to connect from visual studio using the code

    using System;

    using System.Collections.Generic;

    using System.Text;

    using System.Data.SqlClient;

    using System.Windows.Forms;

    using System.IO;

    using System.Reflection;

    using System.Data;

    namespace mQAQI

    {

       class sqlDatabaseUtil

       {

           SqlConnection dataConnection = new SqlConnection();

           public void getConnection()

           {

               try

               {

                   String connString = "Data Source=abudawe\\sqlexpress;Initial Catalog=mQAQI;Integrated Security=True";

                   //String connString = "Data Source=xx.sdf";

                   dataConnection.ConnectionString = connString;

                   dataConnection.Open();

                   MessageBox.Show("Connected to database successfully.");

                }

                catch (FileNotFoundException fe)

                {

                    MessageBox.Show(fe.StackTrace + "Error Accessing the database.");

                    MessageBox.Show(fe.Message);

                }

                catch (SqlException sqle)

                {

                    MessageBox.Show(sqle.StackTrace + "Error Accessing the database.");

                    MessageBox.Show(sqle.Message);                

                }

                catch (IOException io)

                {

                    MessageBox.Show(io.StackTrace + "Error Accessing the database.");

                    MessageBox.Show(io.Message);

                }

              /* catch (Exception e)

               {

                   MessageBox.Show(e.StackTrace + "Error Accessing the database.");

                   Console.WriteLine(e.StackTrace);

               }*/

               finally

               {

                   dataConnection.Close();

               }

           }

       }

    }

    I get the following message

    "specified server not found: abudawe\sqlexpress"

    Please what am i doing wrong. I have battled with this issue for the past 4 days

    Thanks

  • the error is 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)

  • I repeatedly get the message "Microsoft SQL Server 2005 was unable to install on your computer." I uninstall the and reinstall SQL and FFI software and same message.

    Error signature

    EventType: sql90setup P1: unknown P2: 0x643 P3: unknown P4: 0x643 P5: unknown P6: unknown P7: msxm16.msi@6.20.1099.0

    Error Report Contents

    c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0002.cab

    What do I do?

  • I am using Sql server 2005 express with my app writen in vb6.

    I have a problem that after a day of work

    (when I come in the morning ) the app is working very slow.

    So when I am opening the sql managment tool or restarting the sql service

    everything is back to noraml.

    i was to trying to configure the database to auto close = false

    and also auto shrink is off.

    Please see i you can help...

    Thanks in advance

    Alon

  • I am having issues with the Windows XP firewall.  I have made the exceptions for sqlsrver.exe and sqlbrowser.exe as well as udp ports 1433 and 1434.  The connection works fine as long as the fire wall is turned off.

    Any ideas?

  • Hi,

    I created a linked server on sql server 2005 express edition and tried to access linked server which is sql server 2000 enterprise in my case. But, Infortunately I am not able to connect..I am getting following error...

    Server: Msg 65535, Level 16, State 1, Line 0

    SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].

    OLE DB provider "SQLNCLI" for linked server "CentralDB" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "CentralDB" returned message "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.".

    I have checked...Browser is working...remote connection is enabled and their is not problem from firewall. Still I am getting this error. Though If I create a link server vice versa I am able to do so. I mean from sql server2000 to sql server 2005 express edition.

    Let me know if you need any additional information.

    Please help.

    Thank you,

    Mithalesh Gupta

    mithalesh.gupta@gmail.com

  • Hi to All,

    Its a very generous problem, which may have many solutions. But as we have witnessed so many user saying that they have tried everything.

    What solved my problem is..

    I upgraded sql server 2000 from sp2 to SP4 and if you already have sp4 on sql server 2000 then run Instcat.sql. As per my experience I can assure you this will work for sure, if you are exhausted with all the other workarounds.

    Thanks,

    Mithalesh

    mithalesh.gupta@gmail.com

  • Hi,

    Our company just deployed Active Directory Services in an effort to improve security.  

    Before, I had no problems connecting to SQL Express.  Now, I've uninstalled SQL Express, reinstalled, etc.  Tried different things, but can't seem to establish a connection.

    Thanks for any insight.

  • Kudos on a great blog -- most helpful.  

    One issue I ran into, that I'm mentioning in case it helps others, is that when you're struggling with ODBC issues specific to MS SQL Server 2005, especially on MS Windows 2000 (yeah, it's old, I know) then make sure you have the latest MDAC components installed.  That got me beyond a hurdle that (I believe) involved the dynamic vs. static TCP port.  

    I hope this helps.

    Regards,

    Andre Gous

  • Hello,

    I am trying to make an odbc connection from a Windows XP pro Station to a Windows 2008 R2 Server with SQL 2008. When i do a command on the server "SQLCMD -L" i can see the server and the server instance of the SQL server so like:

    <SERVERNAME>

    <SERVERNAME>\SQLEXPRESS

    Within the Server i can also make perfectly all odbc connections i want.

    Coming to the client, when i do there a command "SQLCMD -L"i only get to see the servername

    <SERVERNAME>

    But bot the instance. When i still try to make a connection to thsi server with an odbc connection i get a message like "Sql server does not exist or acces is denied" SQl server error 10060

    How can i make the instance available to the client?

  • Hi, I'm not able to Insert,Select,Delete data on my sqlexpress using webservice (ASP.NET) which is hosted on IIS 6.0 windows 7 ultimate. Whenever i tried to connect it gives me error saying Login Failed: Database is read-only..

    Please help me with this...

    with regards,

    Padam Sonar

Page 7 of 7 (102 items) «34567