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 4 and 7 and type the answer here:
  • Post
  • Hi, good thread. I've got SQLExpress running on a n XP server and a client. I can connect to my DB instance no problem from the server by name(not localhost)using SQL Server Authentication. I get an error 10060 on the client using the same strings in Studio Express under Connect To Server on the client. I've tried a multitude of recommendations. If I try to telnet to the port that the ERRLOG says the service is running on from the server, it connects. If I try it from the client, it says 'Could not open connection to the host'. I'm not sure what to tweak to get this to go. Thanks.

  • ahh....found it. check out the help screen under the TCP/IP properties screen under Protocols for SQLEXPRESS. short end is that if you are using a firewall you must use a static port. 1433 is recommended. This and all the rest of the above stuff. You also have to open the port in the windows firewall on both machines.

  • On vista when i start application as an administrator it create/delete data but when i run app in standard user mode it does not perform any operation with sql express...

  • I've got a Windows 2003 Server, where we just installed SQL Server Express 2005 and IIS on the same machine.

    Our application (classic ASP) errors with "Provider cannot be found. It may not be properly installed. " when trying to connect to the database with an anonymous connection (IUSR account).

    Oddly, it connects successfully if we turn off anonymous users.

    Any ideas?

  • Hi, Joel

       What if you turn on "anonymous" in IIS again, and try to use osql.exe to connec to your express, see whether the same error displayed. What if you turn off anoymous connection, use osql.exe, what happens? What is your connection string?

       The error you saw should not be related to anoymous users configuration but might there is exception.

    Thanks!

    Ming.

  • Hi Ming,

    Here's the connection string that I was trying to use:

    var connectionString = "Provider=SQLNCLI;Server=WEB01\\SQLEXPRESS;Database=<databasename>;UID=<username>;PWD=<pwd>;";

    This one was failing for IUSR connections, though it seems to work okay on our other development machines.

    This connection string seems to work okay:

    var connectionString = "DRIVER={SQL Native Client};Server=WEB01\\SQLEXPRESS;Database=<databasename>;UID=<username>;PWD=<pwd>;";

    What's technically the difference between these connection strings?  Any idea of why one would work for IUSR and the other would not?

  • Hi, JOel

       The first one is using OLEDB, the latter is using ODBC driver. So, from the error message, it was probably caused by your OLEDB provider was not correctly installed or you specify the wrong one.

    1) Which client provider were you using, namely, when you create your client application, did you configure any provider? what it is?

    2) what if you modify the first one by replacing "Provider =SQLOLEDB" whether it works?

    LMK if you have further question.

    Thanks!

    Ming.

  • How can I access SQL Server from the internet ?

    I have a router with a public IP

    I have SQL Server 2005 Express installed on a LAN connected machine (192.168.1.64)

    I want to access this database from the internet. How can I do ?

    Firewall is already opened for the 1433 TCP Port

    Thanks

  • Hi, RJ

       You can use ASP or write ASP.NET application. Following info and example are good start:

    http://support.microsoft.com/kb/169377

    http://samples.gotdotnet.com/quickstart/aspplus/doc/applications.aspx

    Good Luck!

    Ming.

  • Hi Ming,

    I also wish to connect to the SQLExpress through the Internet connection. However, not using ASP.Net but an .Net Windows Application.

    I have a Static IP on the server and I supposed I can write connection string as normal remote connection over the LAN?

    I attempted but failed to connect. My Server not running IIS, does it matter?

    (We disregard firewall issue)

    Thanks.

    Adrian

  • hi there, im using vb 2005 and im tring to acess a data base on a server via pocket pc application.

    i got an error on the connection string !

    Failure to open SQL Server with given connect string. [ connect string = Provider=SQLOLEDB.1;Server=duros-mobile,1433\SQLEXPRESS;Initial Catalog=praia;Integrated Security=SSPI; ]

    can  you help me?

    thanks!

  • I am trying to connect to a SQL Server DB from a pocket PC. I use the following connection string on the Pocket PC.

    "Server=192.168.1.25,1433;Initial Catalog=exilog;User ID=nybc;Password=nybc"

    I can connect to the server using the IP, port and credentials from SQL Server Management Studio Express. But when I run the application in the emulator, I get a "SQL Server does not exist or Access is Denied" SQL Exception.

    This has been killing me for the past 5 days. Can someone please help me?

    Cheers,

    Sampath

  • Hi, John

       1433 is the reserved port for sql default instance, your express was installed as a named instance, and by default it is using dynamic tcp port unless you specified.

       The solution could be

    [ connect string = Provider=SQLOLEDB.1;Server=duros-mobile\SQLEXPRESS;Initial Catalog=praia;Integrated Security=SSPI; ]

    and make sure sqlbrowser service is started.

    Good Luck!

    Ming.

  • Hi, Sampath

       The error looks like you were using MDAC, and the message is too general to identify your particular problem. Hence, I suggest, you modified your connection string to [Driver={SQL Native Client};Server=192.168.1.25\sqlexpress;Initial Catalog=exilog;User ID=nybc;Password=nybc"

    I assume you were connecting to sql express instance which is a named instance, and by default it is not using port 1433 which reserved by default instance, but a dynamic port. Or you can take a look at server errorlog to make sure your sql express was listening on tcp and find the port number, then replace 1433 in your connection string w/ the true number. Also, "Driver={SQL Native Client}" requires you must install SQL 2005 Native Client which is part of the 2K5 installation, this provider will populate more detail error info to help you figure out the root cause of connection failure.

    Good Luck!

    Ming.

  • This error was most frequently hitted by our customers, and in this post, give a brief summary of troubleshooting

Page 3 of 7 (102 items) 12345»