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 6 and 5 and type the answer here:
  • Post
  • I am confused. I have 2 windows 2000 sp4 machines that I want to connect to a SQL Express database. Initially I had problems with both machines then I updated MDAC on them and now one of them connects and the other gets the following error:

    Connection Failed:

    SQLState: '01000'

    SQL Server Error: 10061

    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).

    Connection failed:

    SQLState: '08001'

    SQL Server Error: 17

    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

    I know that everything is configured ok on the SQL Express machine as one of the win2000 machines can connect.

    The only difference I can spot between the 2 machines is the version of the SQL ODBC driver. The one that works is 2000.85.1022.00 and the one that does not work is 2000.85.1064.00. I have no idea why they would have different drivers as the same version on MDAC was installed on both machines.

    Any help is much appreciated.

  • ConnectionOpen (Connect()) means you cannot open a socket to the remote SQL Server.  I don't think this is due to the MDAC driver version.  Most likely the tcp-ip port is blocked by Windows firewall and the "good" machine is actually connecting over named pipes and not sockets.  So if you go through the steps of adding the SQL Express instance to firewall exclusion list everything should work.  See this article for details ->

    http://msdn2.microsoft.com/en-us/library/ms175043.aspx

  • both machines are using TCP/IP

  • Then they should both work the same in theory.  Could be issues such as IPsec blocking sockets.  IPSec typically blocks machines that are outside of a domain from connecting to machines that are inside a domain.

    Simple way to verify is go to the SQL Express machine and find the ERRORLOG file for the SQL Express instance.  You can locate the ERRORLOG file by connecting to the SQL Express instance and running the following SQL statement:

    select serverproperty('ERRORLOGFILENAME')

    This will tell you the location of the error log file for the SQL Express instance.  Open this file in notepad and search for the following line:

    2007-04-02 15:50:30.71 Server      Server is listening on [ 'any' <ipv4> 5555].

    Once you know the port, then go to each client and try the following from a command prompt:

      telnet myServer 5555

    If you see this go to a blank screen, then the port is open.  Press Ctrl + ] to break out of telnet.

  • "http://flight.farexpert.net

    http://www.pricexpert.net

    http://www.my-tripz.com

    http://www.my-travelz.com"

  • i hava .net application with database in Access

    but when i run it it asks for sql server connection why

  • Dear Ming,

    Thanks for all the helpful comments - nevertheless I run into the following strange situation: I am writing a C# application in VS2005 which connects to a SQLEXPRESS database on a different server. I am using the following connection string: "Data Source=myServer\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True". When running it from the IDE (either Debug or Release mode), the connection works perfectly - however, when starting the application directly (double-clicking myApp.exe), the connection fails with "error: 26 - Error Locating Server/Instance Specified".

    Do you have any idea?

    Best regards, Adrian

  • 最近遇到一个问题,就是同样的连接字符串,在PC上和在PocketPC上使用SqlClient连接SqlServer的数据库的时候,PC可以连上而PPC连不上.且PPC在连接的实例名为默认的实例名的时候...

  • Hi Ming,

    I have a problem with a connection to database engine.  Evert time when I connect it, it always gives me this 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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

    For the server name, I put in <my servername>\<my instance>. I checked sqlbrowser and it's running. I changed the remote connection in database engine to local and remote conncection, but it still doesn't work.  

    Please help me out.  Give me a solution to this problem. Thanks so much for your help.

  • Hi, Carl

      1) What is the error message in server ERRORLOG? You can also see it from system application event log. If there is problem that indicate sql server terminate client connection, that means your client request has problem, and it is out of connectivity scope, you need to check your client application.

      2) Are you connecting Express? If so, please doublec check the instance name is "sqlexpress" in your connection string.If not, make sure you specifiy the correct instance name.

      3) Open SQL Server Configuration Manager, click client protocols, check whether TCP/NP protocols are allowed for remote connection?

      4) Look at your server ERRORLOG, see whether your sql instance is listening on the NP and TCP port.

      5) On your client machine,do "new view \\<remoteserver>" and "telnet <remoteserver> <tcpportthatserver listing on>" See whether that works?

    Good Luck!

    Ming.

  • I am a fresher of SQLServer and just started with the Personal Web Site Starter Kit. The following error was showed in the explore:

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

    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.

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

    And here is the log of SSEUtil.exe

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

    C:\>SSEUtil.exe -l

    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 have confirmed both of the SQL Server(SQLEXPRESS) and the SQL Server Browser running well. Also, I can even creat a new project (whether a web site or a windows app) and connect to my testDB with the SQLDataSource component by "./sqlexpress" connection string.

    What's the different of these two type of DB?

    How can I get through the MS's example?

  • I have visual basic .net express and sql express.  I have created my db's so I know that I can use this instance.

    I have enabled named pipes, remote connections, tcp/ip for both client protocols & protocols for sqlserverexpress.

    I have done everything that is listed on this forum to try and resolve my connectivity issues.

    I cannot connect from my webapp locally to my instance.  I get the generic error: "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 see no errors in the error log.  I have made sure my connection string says computername\sqlexpress.

    i can put anything in it and I still get the same error.

    I am going out of my mind.  Please help.

  • Hi, Neha

       Can you check out following two blogs to resolve your problem.

      http://blogs.msdn.com/sql_protocols/archive/2007/05/16/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error-xxx.aspx

    http://blogs.msdn.com/sql_protocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx

    Good Luck!

    Ming.

  • Hi all

    I am having two applications which are using same database.I have upgraded databse from sql server 2000 to 2005 on xp.one application is in vb6.0 which is connecting with the database but the application in asp is giving error as

    "Microsoft SQL Native Client (0x80004005)

    Cannot open database "HVSP" requested by the login. The login failed.

    /NewVspSql/logon.asp, line 41"

    My connection string for vb6.0 application is as follows

    "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;User ID=HVSP;Password=;Initial Catalog=HVSP;Data Source=INFO15\SQLEXPRESS"

  • 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.

Page 4 of 7 (102 items) «23456»