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 5 and 6 and type the answer here:
  • Post
  • Meenal

        Please refer the following blog to check out what is potential cause of the 'Login Failed'.

    http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

    Good Luck!

    Ming.

  • Meenal

        Please refer the following blog to check out what is potential cause of the 'Login Failed'.

    http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

    Good Luck!

    Ming.

  • Ming,

    I have a Vista customer with an odd problem. If his router is on he does not get a connection to the SQL database or gets one so slow that it is unusable. If he turns off the router it works right. If he turns on the router after making a sucessful connection it works right. I am presuming that this a connection timeout problem and we will try a longer setting there. My question to you is what SQL 2005 is doing that conflicts with the router? He is running the application and the SQL Express database on a single machine and it is the only machine connected to the router. In trying to fix this we have added sqlservr, sqlbrowser and port 1433 to the firewall exceptions and set scope to his subnet only. The router manufacturer say the router should not be doing anything to calls limited to the subnet. TIA for any ideas you have on this problem.

  • Hi,

    I have this issue with the connectivity (timeout expired) in my .asp code.

    Microsoft OLE DB Provider for SQL Server error '80004005' Timeout expired

    /SQLDB/resultpage.asp, line 49

    The connection string is:

    set conn = Server.CreateObject("ADODB.Connection")

    conn.open "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DBName;UID=sa;PWD=password"

    Environment:

    1. MS Windows Server 2003, running SQL Server 2005 SP1, DHCP

    2. MS Windows 2000 Server SP4, hosting IIS, connection type = TCP-IP

    Tried few links, but it does not seem to work.

    Anyone can help?

  • is it possible to interactively query SQLBROWSER (via command line interface) to retrieve the list of sql servers broadcasting their existence?  i am not a win32 programmer and do not have a win32 development environment.  my only programming experience is with platform neutral perl.  any help would be appreciated.  net start indicates that i have the sqlbrowser service running.  control panels - admin tools - services - also shows that it is running.  but i dont know if a command line interface is available...  or might someone have a small stand alone executable that i can use for this purpose?

  • vista stand alone computer.  sqlexpress default installation (shared memory enabled).  having trouble connecting.  error is '[dbnetlib open] sql server does not exist or access denied'.

    this error seems to be a bit contradictory to me.  information ive read about sqlexpress states that 'dbnetlib' is only to be used for tcp\ip connectivity to sqlexpress server.  not for shared memory.

    if sqlexpress is installed locally, and client is also local, and sqlexpress is configured to use shared memory for connectivity - why would dbnetlib be utilized at all?  is my connect string not ideally configured to use shared memory?

    my connect string presently looks like;

    Provider=SQLOLEDB.1;

    Integrated Security=SSPI;

    Initial Catalog=MYDB;

    Use Encryption for Data=False

    i dont know how to step by step troubleshoot a shared memory connection.

  • I had the following error when connecting ODBC to sqlserver on a different machine "does not exist or access is denied".

    After reading loads of posts and trying this, that and the other for weekades I came up with what worked for me (Phew!), it might work for you.

    In "Local Users and Groups" I added myself (power user) to the "SQLServer2005MSSQLServerADHelperUser$INSTANCE",

    "SQLServer2005MSSQLUser$SERVER$INSTANCE" and

    "SQLServer2005SQLBrowserUser$SERVER" groups.

    BINGO

    IS

    HIS NAME-O

    Obviously obvious from the error message isn't it just.....

    HTHUMF

  • I think I got a solution...

    the error is in the connection string and in the configuration... if you'll use the IP instead of (loca), localhost, <machinename>\SQLEXPRESS, it will work. So use 127.0.0.1... the server will love it... and one more thing... at Protocols (SQL Server Configuration) at TCP/IP, set at IPAll - TCP Dymanic ports 1433 and one more... of course... set trusted connection to true... (you should allow remote connections too)..

    So, the connection string is :

    "Provider=SQLNCLI;Server=127.0.0.1;Database=database;Trusted_Connection=yes;"

    Hope this helps someone..

    P.S. : from my point of view.. I think this solution is a stupid one as long as local = localhost = 127.0.0.1... I guess for the SQLExpress.... local = localhost != 127.0.0.1

  • I do not get the error "cannot find odbc" when I run my ASP.NET website with debugging (using the service on a random port #).

    But when I try the site using http://localhost/websitename/index.aspx  it says it cannot find the ODBC connection.

    Must be something to do with my IIS config... can't find anything that pertains to this however..

  • Hi I have problem in connecting sql express sp2 in vista.

    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 have done:enabling tcp/ip and named pipeline for sql server.

    please help me out.

  • Hi There,

    I have been going around in circles with this problem.  Can you please throw some light on why we keep getting this error.

    I am using the following connection string.

    ===============================================

    Source=.\SQLEXPRESS;AttachDbFilename=C:\PROGRAM FILES\ATHLETIC GATEWAY AS\OLYMPIATOPPENS TRENINGSDAGBOK V2.0\DATA\OLTDDB.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True

    ===============================================

    And i keep getting this error almost all the time.

    ===============================================

    Message: Cannot open user default database. Login failed.

    Login failed for user 'PLAIN06\adminuser'.

    ===============================================

    One reason for this is that, we have two applications accessing the DB.  One is a [WEB] based application [Running on Cassini Web server].  The other one is a [WINDOWS] based application.

    I verified my code to check for any connections that might have been left open after a open connection call.  But all open connection call is properly followed by a close connection call.  I hope i am right in understand this that the connections are closed once opened using a close connection component method call.  Or does it take time for a closure of the same like in case of any connection pooling.

    Specific to my case, i have to run the [Windows] based application before running the [WEB] application.  The Windows application does a synchronization process first.

    Again for resynchronization i have to close the [WEB] application and also close the Cassini web server process physically to once again allow my [Windows] based application to connect to the db.

    I agree that considering the nature of SQL Server User Instance i will be able to have a single user connect to the database at any given point in time.  However having said this is there a way to verify if there is any open connection to this database from my .net code.

    If so please let me know of the same.

    Thanks & Regards

    Sougandh Pavithran

  • <a href= http://index1.rutyqe.com >tallulah river campground</a>

  • Stdimmen dadrin übersein, dasss untear Bersücksichtigung dser Art der SQaL Sserver-Instanaz i Usaer in daer Lasge, einsen einzigen Benutzer die Verbindsung zur Datsenbank zu einem beliebigen Punkt in der Zeit. Allerdings haben gesagt, diese gibt es auch einen Weg um zu prüfen, ob es eine offene Verbindung zu dieser Datenbank aus meiner. Net-Code

  • Ddieses Versagefn verurssacht wersden köannen, durch die Tatsache, dass im Rahmen der Standard-SQL-Server-Einstellungen ist es nicht möglich, Remote-Verbindungen. (Provider: Named Pipes Provider, error: 40 - konnte nicht geöffnet werden eine Verbindung zu SQL-Server)

    Icah habde getdan: Sfo ködnnen dide TCdP / IdP und den Namend für sdie SQL Servers-Piapeline

  • Sqdlexdpress lodkal instdalliert idst, undd der Kundde isdt audch vord Odrt, udnd sqlexpresss so konfidguriert ist, verwenden desn gemeinsamen Speicher für die Konnektivität - warum sollte dbnetlib auf allen genutzt werden? String ist meine Verbindung nicht optimal konfiguriert, um mit gemeinsam genutzten

Page 5 of 7 (102 items) «34567