locked
Why Is Remote Connection So Difficult? RRS feed

  • Question

  • Why is remote connections using SQL Server Express 2008 so difficult? 

    Before, you point me to one of hundreds of messages or KB articles, please understand that I do have "remote connection configuration" working on some machines in my network.  However, it is on other machines on the same network, that have almost identical configurations where the connections are denied with the infamous:

    error 26 - Error Locating Server/Instance Specified
    error 40 -  (similar)

    The point of this post is to initiate a discussion.   Not provide another reference to "Remote connections disabled by default, Enable TCP/IP.Start Instance......
    Those actions can "sometimes" fix the issue and "sometimes not".

    The vast number of queries about the task of remotely connecting should be at least an "critical sign"  that something needs to be fixed.  Express editions are to easily available to a wide audience and provide a migration path to more sophisticated/involved solutions.   SQL Server Express 2008 does not meet this criteria.

    The whole problem with Remote Access to SQL Server Express needs to be fixed.

    Any thoughts?

    Trey

    Sunday, November 22, 2009 4:37 PM

All replies

  • Trey,

    Good point.

    The truth is that we are SQL DBA-s and developers and not network experts. Normally a network expert should deal with connectivity issues. However, frequently, we have to deal with it.

    I have seen 3 areas of difficulty:

    1. Can you ping the physical server? Firewall?
    2. Can you SQLping it? SQL Server running and listening.
    3. Login/Password Authentication issues: Windows or SQL


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Sunday, November 22, 2009 11:08 PM
  • Hi,

    Though it is a good point for discussion , what i would really say these troubles are not only dedicated to
    SQL server / express product. It is the Infrastructure environment in which it lives ( Firewall configuration ,
    trusted connection between ADs etc).  More the complications and complaince set on your network , more tougher
    is to connect to SQL from a remote client.
     
    Adding to KALMAN's suggestions , i would check the port in which the SQL listens and try to telnet that port from remote.


    Thanks, Leks
    Monday, November 23, 2009 12:02 AM
    Answerer
  • Or may be you are not writing the connecting string properly or entering the wrong servername while connecting.

    Given the Error 26 -
    1. First thing make sure Servername is correct, no typos.
    2. If you are connecting to named instance make sure you seperating instance name and server name by bakcward slash \ and not by forward slash /.  It should be servername\instancename.  Also Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string
    3. Make sure server machine is reachable, and you can ping it.
    4. If it is a named instance make sure SQLBrowser is running and an excpetion is added in the firewall for Browser exe.
    5. Also make sure you have added the UDP port on which Server is running in firewall exeception. 

    And above mentioned things also applied to Full versions of SQL Server also, so I don't really think it is a SQL Server Express 2008 issue.  
    Mangal Pardeshi 
    SQL With Mangal
    Monday, November 23, 2009 6:17 AM
  • Leks and Kalman,

    I question the decision by Microsoft to have the default installation "not support" remote connection.  How common would be the scenario be for SQL Express not to be accessed by another computer on the network?  I can only imagine it to be used in such an isolated, stand-alone configuration by a overwhelming minority of users.

    For example, not only do I need a "networkable" version, but my network consists, of 2 XP machines, 1 Vista, 1 Win 7.  All with their own network pecularities.  

    SQL Server Express is not striving for less complexity.  As mentioned previously, I can't think of another Microsoft product that has generated the number of KB's, blogs, Support/Forum messages, sorrounding an issue like SQL Server has and remote access/network support. 

    Would anyone care to propose another product or situation that is as "user punishing" as SQL Server Express configuration. 

    My suggestion, out-of-the-box, it should default to Windows Authorization, TCP enabled at least.  

    Any other suggestions or recommendations? Or do we just suffer through this poor design and complexity? 

    Trey
     
    Monday, November 23, 2009 6:32 AM
  • Mangal,

    You re-inforce my point. 

    All those steps are convoluted and non-intuitive.  Take Step 5 for example.  Isn't it a bit odd to have to have experience with UDP ports and Firewall exceptions.   IMHO , that is asking too much.  (Besides, I have Firewalls turned off).


    And the error provided as an example "Error 26" , shows up using SQL Studion Management Express it is not only when building connection strings for a custom application.

    All of your steps, could be included as parts of a default installation is my point.

    The overall installation/use of SQL Server (Express) is too complicated and Microsoft needs to ask themselves if they really think this solution with hundreds of blogs, KB's etc. required to accomplich simple and common tasks provides the best customer experience.  ?

    Perhaps, I'm missing something. 

    Trey

    Monday, November 23, 2009 5:56 PM
  •   Take Step 5 for example.  Isn't it a bit odd to have to have experience with UDP ports and Firewall exceptions.   IMHO , that is asking too much.  (Besides, I have Firewalls turned off).

    Trey


    Trey,

    This is my point exactly: connectivity is the domain of a network experts. Frequently, network experts are not available, so SQL DBA-s, SQL developers and SQL casual users must deal with heavy-duty networking issues.


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Monday, November 23, 2009 10:07 PM
  • Kalman,

    Yes, we agree, but what can/should Microsoft do to minimize the issues?  

    As far as I can see, what they do is produce another series of blogs with multiple steps, that make you explore arcane corners of the operating system.

    The default installation of SQL Server Express should be set-up and default to support remote access/connections.  That is the standard configuration users expect.  It is backward and in-efficient to have it the way it currently is. 

    What would it take for MS to see the light?


    Trey 
    Monday, November 23, 2009 11:17 PM
  • Trey,

    How about if you submit a suggestion to the SQL Server Team?

    Link: https://connect.microsoft.com/SQLServer?wa=wsignin1.0
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Monday, November 23, 2009 11:21 PM
  • hi Trey,
    just my $0.02 worth..
    it's "so "difficult" as all this deals with "security".. and it's a big concern as every "producer/ISV" should provide a software that "leaves" the installing machine as protected as possible.. the overall surface area should be protected, as it is by default from XP sp2 on.. this means that, from that OS service pack, the underlying platform is "closed" to external attacker (or this is the intention, even if this is not a completely achievable position :( ).. the integrated firewall closes external connections.. the SQL team just added the setup possibility to allow/disallow network protocols activation.. you do it "on demand" and manually.. it's disabled by default.. again, to limit the surface area...
    all this comes becouse SQLExpress is intended as a "personal" database engine as well, meaning to support local connectivity only.. and this does not require network protocols as local connections are by default established over SharedMemory.. more.. the concern about the "personal" use of the Express edition is reinforced by the User Instance feature.. and all this is to protect unaware users that are not required to even know they have SQL Server installed on their machine, so they are not required to dig into security issues and the like..
    this was a "big" problem in old MSDE (SQLExpress predecessor in SQL Server 2000 time) time frame.. lot of MSDE instances have been installed without tha actual knowledge of the end users.. and viruses like Slammer had a good time becouse those unaware users did not even know they had to patch their "systems"..
    from that time on, the Redmond guys decided to "close all doors" by default.. at SQL level, at OS level, and so on..
    but I do understand your requirements, where you "plan" to provided a "shared" instance for a local network... but, again and IMVHO, it's a catch 22 scenario..
    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Monday, November 23, 2009 11:40 PM
  • Kalman,

    I'm afraid using Microsoft connect would be a bit of a black hole.   In fact it isn't necessary.   For example, I just took a look at the first page screen of this forum.   Here are the titles of questions that have been asked.

    Full-text Index Hang
    Cannot connect from client to SQL Server 2005 Express
    SQL Express connectivity issue on Vista Home Premium
    Sql Server Management Studio 2008 (MSSQL 2008) problem.
    Instal SQL Server 2008 EXpress Advanced
    SQL server won't allow remote connections with TCP/IP
    Connecting to SQL Server 2005 Express with VB6
    SQL Server [&Express] 2008 fails to install correctly
    Alter check constraint using alter table in SQL2008
    SQL Server Management Studio Express on Windows 7
    Cannot start sql express 2005 on windows 7
    Why Is Remote Connection So Difficult?
    Sql Server Express 2008 Installation error 1608
    Can't connect to database created in SQL Express 2008
    Must Reboot Computer to Run .NET Application
    KB970892 Sql server will not insall


    Just looking at the titles, it looks like 12 out of 16 issues deal with installation or connectivty.  That says to me something is wrong with the product and it is "user punishing".

    I'm not bashing Microsoft, I'm trying to raise awareness with a product team.  And more importantly hopefully, that new user will see this thread and know that they are not alone.   A large number of users experience the same frustrations and issues.

    Trey

    Tuesday, November 24, 2009 4:17 PM
  • Trey,

    I am a DBA and I have to deal with these issues constantly and successfully.

    I have sympathy for non-DBA-s dealing with these issues. Lots of sympathy.

    Even though I am a DBA, the computer can be "brutal" to me also on occasions.

    Here is a joke:

    A woman calls Microsoft support: I can't print from Excel. It worked yesterday.

    MS support: Is your printer on and connected to the computer?

    Customer: Darn..my cat jumped on the printer cable and unlodged it.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Monday, November 30, 2009 2:34 PM