Compartilhar via

FAQ: How to connect to SQL Express from "downlevel clients"(Access 2003, VS 2003, VB 6, etc(basically anything that is not using .Net 2.0 or the new SQL Native Client))

  • Artigo

This is the short version, the longer version is further down, also make sure and review the SQL Express BOL and the Mini BOL.

Follow these steps:

1. Make sure express is running correctly (assumes a default install)

 Drop to a cmd prompt

 Type the following

        sqlcmd -S.\sqlexpress

 You should get a prompt like this

        1>

 Type

        Exit

 to exit sqlcmd

2. Enable Protocols

     a. Launch SQL Computer Manager

     b. Expand "Server Network Configuration"

     c. Expand Protocols for "SQLEXPRESS"

     d. Enable Np (for local and remote access)

     e. Enable TCP (for local and remote access)

3. Restart SQL Express

     a. can be done from either SQL Computer Manager, Services applet, or command line( net start mssql$sqlexpress)

4. Start SQL Browser service from Service applet or command line( net start sqlbrowser)

At this point the client should be able to connect, if the machine is rebooted then the last step (4) needs to be repeated as this service must always be running. You can also set this service to autostart via Computer Manager, Services Management Applet.

These actions need to be performed wherever the machine is that has SQL Server Express running on it. If machine A is running SQL Express and an attempt is made to connect from a local tool or applet the steps must be performed on machine A. If an attempt is made to connect from a different machine(B) then the steps still need to be performed on Machine A.

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

The long version.

This entry is designed to help explain the reasons behind the steps in the short version of this post. Lets walk through the steps and the problems and try and explain whats happening

Checking to make sure that Express is running via SQLCMD

The default install for SQL Express installs Express as a named instance(called SQLExpress), with no network listening and with Windows Authentication support only.

To connect to a named instance of SQL Server the convention is to use a servername of the format <servername>\<instancename>, its also possible to shortcut the machinename to either "." or "(local)"(these shortcuts work with all protocols, localhost will also work but only with TCP/IP in older clients,however SQL Native Access supports resolution of LocalHost for Named Pipes and Shared Memory).

No user id and password has been specified because of the default windows authentication, if the install had been changed to specify mixed mode authentication and a password had been given then this could have been used.

Enabling Protocols

Because SQL Server Express does not listen on the network by default connections are made using a local protocol, in the case of the VS 2005 SKUs this is the shared memory protocol. The interface to this has changed in SQL Server 2005 such that older clients can no longer use it. Hence for older clients to work a different local protocol or a network protocol must be used, these are not enabled by default and must be manually enabled.

SQL Browser Service

In general when a client connects remotely to a SQL Server named instance the client does not know the port that the instance is listening on(you can work around this by configuring SQL Server named instances to listen on a specific port and then specifying that port in the connection string in the format <servername>,<port number>)

So when an application attempts to connect to a named instance in the form <servername>\<instancename> the client connection needs to be directed to the correct port. In SQL Server 2000 one of the running services had a built in listener that received the named instance connection requests and redirected them appropriately.

In SQL Server 2005 this functionality has been moved to a dedicated listener service, that service is SQLBrowser.

SQLBrowser also performs another service, that of SQL Server Discovery, its common in many UIs from Microsoft and others to have the ability to browse for instances of SQL Server running locally or remotely, again in SQL Server 2000 this was handled by one of the running instances of SQL Server, in SQL Server 2005 this is handled by the SQLBrowser Service.

Thus the SQLBrowser Service must be started to be able to discover any instance on the machine, and also to connect to named instances through protocols other than shared memory. If network protocols are not enabled via the setup switch then the browser service is not set to autostart.

SQL Native Client

SQL Server native connectivity is defined as connectivity through OLE DB(ADO uses OLE DB under the covers) or ODBC means in SQL Server 2005 (dblib is not an included as a data access technology in SQL Server 2005).

In previous releases of SQL Server(SQL7 and SQL2000) an update to MDAC was preferred for client apps, this means the installation of an entire MDAC update for SQL Server connectivity. In SQL Server 2005 we no longer require an MDAC update as we have refactored out the SQL Server specific connectivity components for OLE DB and ODBC.

As such in SQL Server 2005 no MDAC update is required to connect(although MDAC 2.8 is preferred, any MDAC from Windows 2000 SP3 upwards is supported), but a new component is required. This component is referred to as SQL Native Access.

These components include support for OLE DB and ODBC accessed through a single .dll called: SQLNCLI.dll, this file and its support files are redistributable.

SQL Native Client is NOT required for managed data access via .Net APIs.

 

-Euan Garden

Product Unit Manager

SQL Server Tools

Comments

  • Anonymous
    July 22, 2004
    Excellent post, thanks Euan.

  • Anonymous
    July 24, 2004
    Euan,

    Thank you for the tips.

    On my Windows 2003 machine, SQLExpress service has the name "mssql$sqlexpress".

    If this is the general case, then the net stop command would need to be modified to:

    net start "mssql$sqlexpress"

  • Anonymous
    July 25, 2004
    Good catch thanks.

    I changed the entry.

  • Anonymous
    July 29, 2004
    Euan:

    This is exactly what I have been looking for, a detailed step by step instruction to connect an existing Access database or a new Access 2002 project to SQLX. Thank you!

    I believe that I have followed each step, but I still cannot connect. I have posted this issue this morning on the MS Newgroup blog, but thought I would ask here as well.

    Besides the instructions you provide above, is there any need to set up specific logins or users within SQLX on the server?

    If I am using windows authentication, do I need to have the same user name installed on the server as the user name on the client PC that is trying to connect?

    Sorry for these novice questions, but I have been working on this for days now without any luck.....

    Sam

  • Anonymous
    August 06, 2004
    Sam,
    What error are you getting? Have you been helped in the newsgroups?

    -Euan Garden
    Product Unit Manager
    SQL Server Tools

  • Anonymous
    August 09, 2004
    Euan:

    Thank you for responding. No, I have received no real help from the Newsgroup and this is getting very frustrating.

    I have installed SQLX as a server on a PC on our LAN - we run Netware - but I also have TCP/IP installed as a protocol.

    Attempting to connect my desktop PC to the server using just about any source, Access, Control Panel ODBC, or VB Exp gives me various error messages basically saying it cannot find the Instance on the server. I CAN connect OK using SM on my desktop PC connecting to itself running as a server.

    I do note that the server is not using the default port - 1433 and the log file indicates this is an error most likely associated with the port being already in use by another application, but TCPView shows 1433 is not in use.

    Using Windows Authentication results in an error message stating the client was not able to connect. I notice in my Event Viewer it shows the following error:

    The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.

    I have tried repeatedly to vary each attempt, but no connection.

    Any thoughts? and thanks !

  • Anonymous
    August 09, 2004
    Can you connect locally by doing sqlcmd -S.sqlexpress locally on the machine?

    -Euan

  • Anonymous
    August 10, 2004
    Yes, no problem with command prompt..

    I have been able to connect locally via an ADP file (but cannot create any tables- I understand this problem) and set up an ODBC connection, and upsize an exisitng db, but ONLY locally.

    It really seems to relate only to connecting to SQLX on anotehr PC.

    Thxxx...

  • Anonymous
    August 10, 2004
    Can you look in the log for SQl Server and see what port its listening on?

  • Anonymous
    August 11, 2004
    Euan: Yes, it appears to be port 1106. I enclose a portion of the log below [I can send more if needed]

    When I run TCPView on that machine it does not show port 1433 in use, so this confuses me.

    I just re-installed SQLX on the server [for about the 10th time], and set it up with a Domain Account instead of a local account. When trying to create an ODBC connection from my desktop PC, I keep getting the 'Login failed for user 'DCSS057Guest' error message.

    Any ideas? I really appreciate your help.

    2004-08-11 09:31:49.37 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2004-08-11 09:31:49.37 Server Detected 1 CPUs. This is an informational message; no user action is required.
    2004-08-11 09:31:49.85 Server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
    2004-08-11 09:31:50.00 Server Database mirroring has been enabled on this instance of SQL Server.
    2004-08-11 09:31:50.04 spid4s Starting up database 'master'.
    2004-08-11 09:31:50.14 spid4s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2004-08-11 09:31:50.20 spid4s SQL Trace started. Trace ID = '1'. Login Name = 'sa'.
    2004-08-11 09:31:50.20 spid4s Starting up database 'mssqlsystemresource'.
    2004-08-11 09:31:50.37 Server Warning:Encryption is not available, could not find a valid certificate to load.
    2004-08-11 09:31:50.37 spid4s Server name is 'DCSS057SQLEXPRESS'. This is an informational message only. No user action is required.
    2004-08-11 09:31:50.39 spid7s Starting up database 'model'.
    2004-08-11 09:31:50.39 Server Server is listening on [ 'any' <ipv4> 1106].
    2004-08-11 09:31:55.96 Server Dedicated admin connection support was not started because of error 0x490, status code: 0x0. This error typically indicates a socket-based error, such as a port already in use.
    2004-08-11 09:31:55.96 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2004-08-11 09:31:56.06 spid4s Starting up database 'msdb'.
    2004-08-11 09:31:56.85 spid7s Clearing tempdb database.
    2004-08-11 09:31:58.26 spid7s Starting up database 'tempdb'.
    2004-08-11 09:31:58.37 spid4s Recovery is complete. This is an informational message only. No user action is required.
    2004-08-11 09:31:58.37 spid12s The Service Broker endpoint is disabled or not configured.
    2004-08-11 09:31:58.37 spid12s Database Mirroring Transport is disabled in the endpoint configuration.
    2004-08-11 09:31:58.39 spid12s Service Broker Manager has started.
    2004-08-11 09:32:45.84 Logon Login succeeded for user 'DCSS057SQLServer'. Connection: trusted. [CLIENT: <local machine>]

  • Anonymous
    August 11, 2004
    The comment has been removed

  • Anonymous
    August 12, 2004
    Since i installed beta2 of SQLExpress and enabled SQLExpress-TCP-Connections and SQLBrowser i get this errormessage in eventlog/application every 50-60 seconds:

    "The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid."

    EventID: 3
    Source: SQLBrowser
    Type: Warning
    Category: None

  • Anonymous
    August 13, 2004
    Euan:

    I hope you got my last post. I found the problem. Thanks for your help.

  • Anonymous
    August 17, 2004
    I'm getting this same message that Flynn is getting every minute in my event log with beta 2..

    "The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid."

  • Anonymous
    August 17, 2004
    Euan:

    in comparison with Access2000 adp <-> MSDE2000 connection, Access2003<->SQLX is too slowly on my PC. I use tcp or named pipes protocols, as you described before. I have to wait for a 3-5 seconds while view is working. On 2000 for similar view it took less than 1 second.

  • Anonymous
    August 19, 2004
    Any Clues how to fix this ?

    its Related to this i tryed changeing the ip address its listening on

    Dedicated admin connection support was not started because of error 0x490, status code: 0x0. This error typically indicates a socket-based error, such as a port already in use.

    BTW this is the Latest Released Beta off the site.

  • Anonymous
    August 19, 2004
    Just to make things a bit clearer..
    This is Windows 2003 Sever running AD, IIS, Exchange.. (its my personal Development/Learning Server if i was working for someone it whuldent be so cluttered :) )

    I presume running AD is not the issue tho.

  • Anonymous
    October 26, 2004
    Code Camp II: SQL Server 2005 Express Chalk Talk Notes

  • Anonymous
    January 05, 2005
    ローカル接続の Downlevel client (2005 以前)には SQL Browser、 TCP/IP 有効化が必要(Shared Memoryは 2005 で interface が変更されている)

  • Anonymous
    February 08, 2007
    PingBack from http://bestlong.no-ip.com/blog/?p=105

  • Anonymous
    July 13, 2007
    http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx Friday, July 23, 2004 1:09 AM sqlexpress FAQ: How to connect to SQL Express from "downlevel clients"(Access 2003, VS 2003, VB 6, etc(basically anything that is not using .Net 2.0 or the new

  • Anonymous
    May 15, 2008
    PingBack from http://jordon.clearviewlink.info/access2003sqlserverconnectionstring.html

  • Anonymous
    May 15, 2008
    PingBack from http://kaleb.freeinfocontent.info/access2003sqlserverconnectionstring.html

  • Anonymous
    July 02, 2008
    PingBack from http://luka.adultstoriesdirect.com/sqlserver2005clientconfiguration.html

  • Anonymous
    November 09, 2008
    The connection string with sqlexpress shows an error saying it is an unrecogonized  escape sequence.What should be the format for connection string using tcp/ip?

  • Anonymous
    November 10, 2008
    It depends on what language you're connecting from from; also, your reference to sqlexpress looks suspect. This is the type of question you should ask in the SQL Express forum, please post a question there with more details about the full connection string you're using. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1

  • Mike