locked
Connection Protocol Issue RRS feed

  • Question

  • Hi All,

    Running Windows Server 2008 R2 with SQL Server 2008 R2.  I'm having a problem whereby I am unable to connect to the local default instance using Shared Memory or Named Pipes.  I should note the issue isn't one of access, but undesired protocol being used.  Even if I force the connection protocol in our application to Shared Memory the protocol still shows as TCP (in [sys].[dm_exec_connections]), even though we are able to connect via Shared Memory with SSMS.

    I've enabled Shared Memory, Named Pipes and TCP/IP under the Client Protocols section as well as in the Protocols for MSSQLSERVER in SQL Server Configuration Manager.  Also, the SQL Server Agent and SQL Browser services have been set for automatic startup and are running.  The server has also been restarted to reflect the protocol modifications.

    On a Windows 7 test machine we have the same settings configured in Configuration Manager and the application does connect to the local test database with Named Pipes.

    We have an application installed on the same server that the database engine is located and use Remote Desktop Services to serve this application to users through a RemoteApp.  Being that the application and database reside on the same server, whether we force the connection protocol or not, shouldn't the connection try to use Shared Memory, then Named Pipes, then TCP?  And if that is the case, what configuration settings am I missing that is allowing the first two protocols to be skipped and/or fail without error messages?

    Any help is greatly appreciated!

    Best Regards

    Brad

    Thursday, July 20, 2017 5:01 PM

All replies

  • Hi 2012S4,

     

    "shouldn't the connection try to use Shared Memory, then Named Pipes, then TCP?"

     

    It isn't, application uses SQL Native Client to connect with SQL Server, the default protocol or the protocol order for client computers can be setted manually. Please refer to the following steps:

     

    1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Client Protocols, and then click Properties.
    2. In the Enabled Protocols box, click Move Up or Move Down, to change the order in which protocols are tried, when attempting to connect to SQL Server. The top protocol in the Enabled Protocols box is the default protocol.

     

    Besides, according to your description 'Even if I force the connection protocol in our application to Shared Memory', could you please share the method forcing this?

     

    Best Regards,

    Teige

     



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Teige Gao Friday, July 21, 2017 8:08 AM
    Friday, July 21, 2017 8:07 AM
  • Hello Brad,

    You can use the "SQL Server Client Configuration Tool" = CliConfg.exe from System32/SysWOW64 Folder to disable / force protocol order on client side.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 21, 2017 8:25 AM
  • Hi Teige,

    Thanks for the response.  I had set the protocol order previously, apologies for neglecting to mention that in my original post.  As to how we force the protocol in our application, it is a legacy application written in VB6.  We use ADO to connect to the database, and (attempt to) force the protocol in the connection string like so:

    Provider=SQLOLEDB.1;Persist Security Info=False;Network Library=DBMSLPCN;Initial Catalog=<Database>;Data Source=<Server>;User ID=<User ID>;Password=<Password>;

    We've also tried with the Network Library portion removed and with lpc: in front of the server name but to no avail.

    Thoughts?

    Friday, July 21, 2017 3:12 PM
  • Hi Olaf,

    Thanks for the response.  I accessed that utility on the server in question and found that Shared Memory was not enabled, nor were Named Pipes or TCP/IP.  I enabled all three and applied the changes, but the application connection is still coming up as TCP.  Further, when I reverted the changes I got:

    "All the protocols have been disabled.  Super socket Net-Library will use the default protocols to connect to the remote server."

    Thoughts?

    Friday, July 21, 2017 3:18 PM
  • Other option to force a protocol is to use the protocol shortcut in front of server name: SM for shared memory (works only local), NP for named pipes and TCP for it

    Example: Local connection (= . as Server Name), normally done by Shared Memory, here forced to use Named Pipes:


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 21, 2017 3:36 PM
  • Hi Olaf,

    Though this approach does work for us when using SSMS, it does not when we attempt to connect via ADO.  See my reply to Teige for more info.

    Thanks!

    Friday, July 21, 2017 4:32 PM
  • So how do you specify the server name? If you use the server name in the same way from SSMS, do you get shared memory?

    Also, you should use SQLNCLI10 for the provider. Not that I think it will address this issue, but that is a more modern version than SQLOLEDB. SQLNCLI10 is part of SQL Server Native Client.

    Friday, July 21, 2017 9:14 PM
  • Hi Brad,

    Please correct me if I’m wrong:

    >>Being that the application and database reside on the same server, whether we force the connection protocol or not, shouldn't the connection try to use Shared Memory, then Named Pipes, then TCP?

    The order doesn’t sounds right, by default it should be using Shared Memory first, then TCP/IP, and will try Named Pipes if TCP/IP fails. Also, it will skip Shared Memory if you are not connecting to a local SQL Server instance.

    What doesn’t really make sense here is that you don’t even need to specify shared memory if both client application and SQL Server are on the same machine, it will use it by default. Could you please make sure you are actually connecting to the local SQL Server instance?

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 2, 2017 3:59 PM
  • Does this really matter to your application performance?   TCP/IP on the local server does not go out over the network, or even to the network adapter, and is very fast.

    David


    Microsoft Technology Center - Dallas
    My blog

    Wednesday, August 2, 2017 4:23 PM