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 7 and type the answer here:
  • Post
  • Hi, Gary

       From my understanding, you can try following to confirm:

    1)

    Without installing VS 2005 (Team Suite) + SQL Server 2005,

       http://localhost/SQLServerExpressTest/Default.aspx worked?

    With installing VS 2005 (Team Suite) + SQL Server 2005,

    http://localhost/SQLServerExpressTest/Default.aspx did not work?

    Did you make any IIS configuration change before installing VS 2005(team suite) + SQL Server 2005?

    2) Can you also try using osql /S.\SQLExpress /E from your client box to see whether connection to SQL Express breaked after intalling VS 2005 + SQL Server 2005? From the error, that seems to you established a connection, but later on some client operation failed. But this way can make sure from client app that not through IIS, do same data operation and if worked fine, that might nail down to something in your IIS deployment issue.

    3) Also, you can open SQL Server profile to see when the error occured, which client operation happened?

    I also post your question to SQL Express Forum(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=760610&SiteID=1&mode=1) and our SQL techinical disscussion alias trying to find out mor solution for your problem, I will post answer once I get feedback.

    Good Luck!
    Ming.

  • With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote...
  • With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote

  • all you need is:

    CONN_STRING = "Server=.\\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI";

    You must allow Remote Connections too:

    Start->Programs->Sql Server 2005->Configuration Tools->SQL Server Surface Area Configuration. Click "Surface Area Configuration for Services and Connections". Choose "Database Engine->Remote Connections", select "Local and Remote Connections", Apply, OK

  • Can we change the name of SQl server Express, at the time of installation, from default machine name to any other name. IS this Possible, please help it is very urgent.

  • By default, SQL Express was installed as a named instance, and the fixed instance name is "sqlexpress", hence when you make connection, you need specify" Data Source = <machinename>\sqlexpress" in your connection string.

  • Actually im trying to say, whether it is Possible to change <machinename> at time of installing Sql express. As apps made in sql server2K5 express, when they are distributed to clients we have to change the machine name in conn string. Is there any solution fr this.

  • I'm having a problem connecting with a Java application but I CAN connect using my .Net application - the user name and password are the same for both.  

    The error I get is: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "CORNERS" requested by the login. The login failed. An interesing note - I get the same message if the database is not running.

    SQL Server Express 2005 is installed in mixed mode.  

    Here is my connection string in the .Net appplication: <add key="connectString" value="Server=(local);UID=sa;PWD=myPasswd;Database=CORNERS" />.  

    These are my values in my Java app web.xml -

    <init-param>

           <param-name>DBDriver</param-name>

           <param-value>com.microsoft.sqlserver.jdbc.SQLServerDriver</param-value>

    </init-param>

    <init-param>

           <param-name>DBURL</param-name>

           <param-value>jdbc:sqlserver://localhost\sqlexpress:1055;databaseName=CORNERS</param-value>

    </init-param>

    <init-param>

           <param-name>DBUser</param-name>

           <param-value>sa</param-value>

    </init-param>

    <init-param>

           <param-name>DBPwd</param-name>

           <param-value>myPasswd</param-value>

    </init-param>.

    And yes, the port is 1055 - I checked to find it.

    I am using Microsoft SQL Server 2005 JDBC Driver 1.0 (sqljdbc_1.0.809.102).

    Does anyone have any idea what is wrong so that the login fails in the Java application but works in the .Net application?

  • Hi adisciullo ,

    I'm afraid that our team is not very familiar with the JDBC driver.  I suggest that you post your question on the SQL Server Data Access forum as members of our JDBC team normally monitor it for JDBC-related questions.

    Il-Sung.

  • Can you get a trace? Also, can you try our 1.1 driver? 1.1 driver has better tracing.

  • Hi, I am having connection problem with SQL Server Express 2005. The problem occurs everyday after 6pm (after working hour). The error messenge is:

    Microsoft SQL Server Login

    --------------------------

    Connection failed

    SQLState 01000

    SQL Server Error: 10060

    [microsoft][ODBC SQL Server Driver]{TCP/IP Sockets]Connection Open (connect)

    Connection failed

    SQL State: 08001

    SQL Server Error: 17

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

    All computers which connect to the server will get this error messenge. I have been working on this for few days but without any luck. I would be grateful if someone could help. Thank you.

    Note: The computers are working fine before 6pm.

  • The error message indicates the client cannot open a socket to the sql server due to timeout.  10060 is socket error code for timeout.  To verify this you could use telnet and attempt to open a connection to port 1433 from some client machine ->

    telnet 123.123.123.123 1433

    where 123.123.123.123 is IP of SQL Server.

    I would check all network hardware between clients and server, it would be a firewall or router or application level firewall blocking traffic most likely.

  • Hello there,

    I have a connection problem with sql server express but i believe you can help me with this.

    First, I tried your Remote connection string above:

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

    and it works but somehow when i use my own database since your using the master, it ask for Login. Heres the error:

    Microsoft SQL Native Client error '80004005'

    Cannot open database <mydatabase> requested by the login. The login failed.

  • Hi, Junifer

       You need to grant dbaccess to your database for your login credential.

       1) Assume you were using NT login, windows authentication, you can use ManagementStudio, connect to sqlexpress, go to security, add login, choose the account, and choose default database = mydatabase.

       2) If you were using SQL login, you can go to security, found the account, click properties of it, then make sure it has access to your own database.

    good luck!

    Ming.

  • Hey Ming,

    Thank you for the reply, but before i receive your reply, i have it working already simply by openning the Management Studio Express, right click on mydatabase then properties. I click on Permission and under users or roles i added two objects guest and public then grant them few permission under Explicit Permission.

    But i'm just wondering about security. Is my method safe/advisable?

    I will also try your suggestion and i know it will work.

Page 2 of 7 (102 items) 12345»