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 2 and 2 and type the answer here:
  • Post
  • msdn博客 http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx http://blogs.msdn.com/abhinaba/archive/2006/12/21/is-object-oriented-programming-good.aspx http://blogs.msdn.com/jeffbe/archive/2006/03/17/553858.aspx http://blogs.msdn.com/somasegar/archive/2006/12/07/rc-of-msdnwiki-available.aspx..

  • PingBack from http://www.ie16.com/provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server/

  • PingBack from http://www.ie16.com/re-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server/

  • PingBack from http://www.ie16.com/reg-error-while-browsing-report-server-in-iis-manager/

  • PingBack from http://www.ie16.com/re-reg-error-while-browsing-report-server-in-iis-manager/

  • Hi I have problem in connecting sql Server in vista.

    i was develop a desktop application on visual studio. Setup contains Sqlserver and dotnet framework . Its works fine when i was deploy on Xp. but it did not work on vista and throwing exception that is given below.

    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)

    please help me out.

  • hey

    my database name is gssportal\sqlexpress

    but i'm unable to connect from my tomcat. It is giving Login failed for user .. Even i chaged my database to Mixed Mode(Windows as well as SQL Authentication)

    please help me.

  • Hey

    i configured my data base in tomcat in this way

    <ResourceParams name="MSSQL">

       <parameter>

         <name>url</name>

       <value>jdbc:sqlserver://gssportal\sqlexpress:1433;DatabaseName=webexpenses3

    </value>

       </parameter>

       <parameter>

         <name>password</name>

         <value>sql@gss</value>

       </parameter>

       <parameter>

         <name>maxActive</name>

         <value>4</value>

       </parameter>

       <parameter>

         <name>maxWait</name>

         <value>5000</value>

       </parameter>

       <parameter>

         <name>driverClassName</name>

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

       </parameter>

       <parameter>

         <name>username</name>

         <value>sa</value>

       </parameter>

       <parameter>

         <name>maxIdle</name>

         <value>2</value>

       </parameter>

     </ResourceParams>

    I'm able to login from sql server management studio

    but when i try to connect form tomcat it is giving this error

    org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFact

    ory (Login failed for user 'sa'.)

           at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSou

    rce.java:855)

           at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource

    .java:540)

           at com.gssamerica.expensereporting.ui.common.SQLManager.getConnection(SQ

    LManager.java:95)

           at com.gssamerica.expensereporting.business.dao.CacheHome.getExpenseIds(

    CacheHome.java:46)

           at com.gssamerica.expensereporting.business.listener.LookupCacheListener

    .cacheExpenseId(LookupCacheListener.java:183)

           at com.gssamerica.expensereporting.business.listener.LookupCacheListener

    .contextInitialized(LookupCacheListener.java:55)

           at org.apache.catalina.core.StandardContext.listenerStart(StandardContex

    t.java:3827)

           at org.apache.catalina.core.StandardContext.start(StandardContext.java:4

    343)

           at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase

    .java:823)

           at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:80

    7)

           at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:595)

           at org.apache.catalina.core.StandardHostDeployer.addChild(StandardHostDe

    ployer.java:903)

           at sun.reflect.GeneratedMethodAccessor56.invoke(Unknown Source)

           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces

    sorImpl.java:25)

           at java.lang.reflect.Method.invoke(Method.java:585)

           at org.apache.commons.beanutils.MethodUtils.invokeMethod(MethodUtils.jav

    a:216)

           at org.apache.commons.digester.SetNextRule.end(SetNextRule.java:256)

           at org.apache.commons.digester.Rule.end(Rule.java:276)

           at org.apache.commons.digester.Digester.endElement(Digester.java:1058)

           at org.apache.catalina.util.CatalinaDigester.endElement(CatalinaDigester

    .java:76)

           at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source

    )

           at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanEndElement(

    Unknown Source)

           at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContent

    Dispatcher.dispatch(Unknown Source)

           at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Un

    known Source)

           at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)

           at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)

           at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)

           at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)

           at org.apache.commons.digester.Digester.parse(Digester.java:1567)

           at org.apache.catalina.core.StandardHostDeployer.install(StandardHostDep

    loyer.java:488)

           at org.apache.catalina.core.StandardHost.install(StandardHost.java:863)

           at org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.j

    ava:483)

           at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:427

    )

           at org.apache.catalina.startup.HostConfig.checkContextLastModified(HostC

    onfig.java:800)

           at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1085)

           at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java

    :327)

           at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(Lifecycl

    eSupport.java:119)

           at org.apache.catalina.core.StandardHost.backgroundProcess(StandardHost.

    java:800)

           at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.p

    rocessChildren(ContainerBase.java:1619)

           at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.p

    rocessChildren(ContainerBase.java:1628)

           at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.r

    un(ContainerBase.java:1608)

           at java.lang.Thread.run(Thread.java:595)

    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for use

    r 'sa'.

           at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError

    (Unknown Source)

           at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source)

           at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)

           at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(Unknown So

    urce)

           at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source

    )

           at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(Unknown S

    ource)

           at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecu

    te(Unknown Source)

           at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)

           at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unkno

    wn Source)

           at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknow

    n Source)

           at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover

    (Unknown Source)

           at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour

    ce)

           at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

           at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(Driv

    erConnectionFactory.java:37)

           at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(Poolable

    ConnectionFactory.java:290)

           at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(Bas

    icDataSource.java:877)

           at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSou

    rce.java:851)

           ... 41 more

    Jun 20, 2008 12:43:10 PM org.hibernate.util.JDBCExceptionReporter logExceptions

    WARNING: SQL Error: 0, SQLState: null

    Jun 20, 2008 12:43:10 PM org.hibernate.util.JDBCExceptionReporter logExceptions

    SEVERE: Cannot create PoolableConnectionFactory (Login failed for user 'sa'.)

    Jun 20, 2008 12:43:10 PM com.gssamerica.expensereporting.business.dao.StatusHome

    StatusList

    SEVERE: find by example failed

    org.hibernate.exception.GenericJDBCException: Cannot open connection

           at org.hibernate.exception.SQLStateConverter.handledNonSpecificException

    (SQLStateConverter.java:103)

           at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.j

    ava:91)

           at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp

    er.java:43)

           at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp

    er.java:29)

           at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager

    .java:420)

  • Installed SQL express 2005 [ SQLEXPR_ADV ].

    Operating system vista.

    I couldn't create locally a connection under object explorer.

    The following error pops up :

    TITLE: Connect to Server

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

    Cannot connect to BINYAM-PC\SQLEXPRESS.

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

    ADDITIONAL INFORMATION:

    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) (Microsoft SQL Server, Error: -1)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

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

    BUTTONS:

    OK

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

  • Installed SQL express 2005 [ SQLEXPR_ADV ].

    Operating system vista.

    I couldn't create locally a connection under object explorer.

    The following error pops up :

    TITLE: Connect to Server

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

    Cannot connect to BINYAM-PC\SQLEXPRESS.

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

    ADDITIONAL INFORMATION:

    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) (Microsoft SQL Server, Error: -1)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

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

    BUTTONS:

    OK

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

  • Help Help

    I have been successfully running a connnection to my sql server express 2005 from several computers on our local network. However I am having problems now with two pc's after I changed the windows user account name and password. This is probably a simple fix, but where can I fix this. Do I need to accordingly setup/change a windows user account on the server? Any advice is much appreciated!

  • I should clarify that it is the username on the 2 client pc's that have been changed, which seemed to cause a problem logging on to SQL on the server.

  • <a href= http://index4.diolas.com >movie ticket graphic</a> <a href= http://index1.diolas.com >girls caught on camera</a> <a href= http://index2.diolas.com >massey ferguson 8150</a> <a href= http://index3.diolas.com >french country bedroom picture design in massachusetts</a> <a href= http://index5.diolas.com >columbine- cassie</a>

  • This is for your information. If you want to understand the dynamic allocated port on SQL Server. This KB article is pretty good. So when SQL Server allocate a port you can almost be sure it stay that port until you change it manually.

  • localhost\SQLEXPRESS

    gives Unrecognized Escape Sequence

Page 6 of 7 (102 items) «34567