none
Unable to connect to express with TCP/IP

    Question

  •  

    I am unable to connect to express edition by specifying 'TCP/IP'  in the Network Protocol option of 'Connection Properties' in managment studio. It gives the following error.

     

     

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to MYCOMPUTER\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
    ------------------------------

     

     

    I had already enabled TCP/IP from SQL Server Configuration Manager.

    Wednesday, May 09, 2007 9:02 AM

All replies

  • Hi,

     

    The following applies to an Sql Express instance on a Small Business Server 2003.

     

    It took me many frustrating weeks to stumble upon the reason why I couldn't connect to SBS from my PC.

     

    Sql Server defaults to listening on Port 1433  ( and Browser listens on port 1434) HOWEVER, Sql Express listens on a dynamic port, which when it opens, looks for the last port opened, but can change it to another, so there is nothing concrete to work to.

     

    To overcome the problem you need to log in to the SBS as Administrator, or get the administrator to do this for you. You will also need the name of the SbServer.

     

    The only way I know to locate the port being used, is to go to the SBS server and connect to the SqlExpress instance running on that machine.

     

    Then run the following query 

      

     SELECT serverproperty('ERRORLOGFILENAME')    which will give you the location of the error log file. Open this file in notepad and look for similar details to the following line.

     

    2007-04-05  15:50:30.71 SERVER  Server is listening on [ 'any' <ipv4> 23935]       The number which will be in 23935 position will be the port being used.

     

    When you get that port number, go to the client machine, and try the following command from the command prompt.

     

    telnet  nameoftheSBSserver  23935   substituting the port number you located.

     

    If you get a blank screen, your port is opened. Press Ctrl + ] > enter, then type quit> enter to get out of telnet.

     

    Get to that point and it's a start. Then I recommend the next step if you are able.

     

    Specify a static port for accessing SqlExpress Server.

     

    On the SBS Server, launch Sql Configuration Manager.

    Click on SqlServer Network Configuration

    Right Click on "Protocols for SqlExpress"

    Right Click on the "TCP/IP" child node

    Click on Properties

    Click on IPAddresses tab

     

    Bottom section should have a greyed out heading called IPAll.

    Delete the entry for TCP Dynamic Ports and leave blank

    In TCP Port value type a number , for example, 2333

     

    Then Stop and Restart Sql Express

     

    To check that you can connect remotely use the following SQLCMD from your remote computer.

     

    SQLCMD -E -S YourServerName\SQLEXPRESS,2333   The comma between SqlExpress and 2333 lets the server know it's a port.

     

    Hope the above information helps you a little.

     

     

     

     

    Wednesday, May 09, 2007 11:02 AM