none
Why need to specify default port in connection string when sql server browser service is not running

    Question

  • Hi,

    I'm using ADO to connect to SQL Server 2005/2008 Express edition remotely. I enabled TCP/IP protocol in SQL Server settings, configured SQL Server to use fixed listening TCP port 1433, and essentially can successfully make remote connections.

    Note that SQL Server browser service is not running.

    I use the following connection string:
    Provider=SQLOLEDB;Data Source=sqlsrv\SQLEXPRESS;Initial Catalog=Test;Integrated Security=SSPI;

    TCP port 1433 is a default port for SQL Server, isn't it? But the strange thing is that I can't connect if I don't specify port number in connection string.
    If I just add ",1433" in connection string after server name, it connects without a problem.
    If I start SQL Server browser service, I can connect without specifying a port number.

    Is this a correct behaviour? Why should I specify DEFAULT port in connection string?
    I found mentions of the same issue on Internet but haven't found any explanations why it is so.
    Maybe someone knows any solutions how to avoid this requirement of default port?

    I think it is not very convenient. It is like requiring to specify port 80 in browser after every web site name.

    I tried in both SQL Server 2005 Express and SQL Server 2008 Express. The issue exists in both of them.
    Wednesday, June 17, 2009 6:31 PM

Answers

All replies

  • There are two ways to connect to SQL Server, using the Serername,Portnumber syntax or the ServerName\InstanceName syntax. The latter relies on the SQL Server Browser service. Thats simply it. The rule is, if there is an instance specified, fo to the UDP port to ask the browser for the real port.

    -Jens
    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, June 17, 2009 8:34 PM
    Moderator
  • So, this means if browser service is not running, I must always specify port number for Express edition (since it has instance name).

    I wonder why it doesn't try to use default port. I need to run browser service just to make it connect to default port?

    If I connect to SQL Server Enterprise edition, I don't need to specify default port in connection string. It does not have instance name, but anyway it is smart enough to connect to default instance.
    Wednesday, June 17, 2009 9:05 PM
  • hi,
    Jens already reported the "big picture" about "how it works".. but if the instance is listening on the default port (not standard setting for SQLExpress instances) you should be able to connect to it without specifying the 1433 port.. and I can confirm it having both SQLExpress 2005 and 2008 "default" instances (listening on TCP/IP 1433 and SQLBrowser disabled) running and working as expected.. if not provided, the network stack will default to 1433 port, eventually failing if no service is listening there on..
    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Wednesday, June 17, 2009 11:17 PM
    Moderator
  • You mean it should work without specifying the port in connection string even for named instances? So, what could I do wrong?

    > if the instance is listening on the default port

    Yes, it listens on 1433. I said above if I just add ",1433" to connection string, it connects successfully. Otherwise it fails.
    Wednesday, June 17, 2009 11:37 PM
  • hi,
    you probably do not have a "default" instance, just a named instance listening on the default SQL Server port.. again, Jens already briefly explained the issue..
    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Thursday, June 18, 2009 1:15 AM
    Moderator
  • I don't have a "default" instance for sure. I have a named instance (which was installed with default installer settings): SQLExpress.
    So, must always specify port? I need short definite answer, yes or no?
    Thursday, June 18, 2009 1:35 AM

  • "However, if the SQL Server Browser service is not running, the following connections do not work:
    • Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe)."


    http://msdn.microsoft.com/en-us/library/ms181087.aspx

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    • Marked as answer by Evgeny369 Thursday, June 18, 2009 10:57 AM
    Thursday, June 18, 2009 7:42 AM
    Moderator
  • Thanks, that was helpful.
    Thursday, June 18, 2009 10:57 AM