locked
SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified RRS feed

  • Question

  • Hello all,
    i (should) well know http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
    and how SQL Server Browser works.
    The strange thing that i can't explain is that with SQL Server Browser service down on both nodes of my MSCS, every attempt of connection using other than
    sqlcmd (without specifying tcp port) goes wrong (that is the expecting behaviour), but the same behaviour is expected for sqlcmd too but it don't seems to be.

    In other words this works fine:
    sqlcmd -S sqlclusterfla02\INSTANCE2

    This works fine:
    sqlcmd -S sqlclusterfla02\INSTANCE2,1662


    This works fine:
    sqlwb.exe -S sqlclusterfla02\INSTANCE2 -E,1662

    And this doesn't work at all and returns SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified:
    sqlwb.exe -S sqlclusterfla02\INSTANCE2 -E


    Obviously the latter works fine whenever i startup SQL Server Browser on sqlclusterfla02 node.

    Thanks

    Thursday, October 16, 2008 12:13 PM

Answers

  • No, this works for any type of connections, and the cache is stored in a LastConnect hive for the corresponding client under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client.

     

    My guess is that sqlwb uses exactly the same registry as sqlcmd, but to confirm, try the steps in your example and check how the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client changed.

     

    There should be several client hives there - SNI9.0 and/or SNI10.0, SuperSocketNetLib. Check which one gets a new entry in the LastConnect hive after the successful connection in step 3.

     

    Let me know if you still have any questions.

     

    Thanks

    Stoyko

    Friday, October 17, 2008 4:14 PM

All replies

  • You may have a cached connection.

     

    Every successful connection will add an entry in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\LastConnect (resp. SNI10.0 if using SQL Server 2008) that will contain the port number.

     

    Check this hive and see if it has entries for sqlclusterfla02\INSTANCE2. If it does, connection will succeed even with SQL Browser down. Try deleting these entries and shutting down Browser. You shouldn't be able to connect afterwards.

     

    You can also use SQL Configuration Manager to check if there are aliases defined. Such aliases may help make a successful connection with SQL Browser down.

     

    Hope this helps - let us know if you have other questions!

     

    Stoyko

    Thursday, October 16, 2008 4:47 PM
  •  Stoyko Kostov - MSFT wrote:

    You may have a cached connection.

     

    Every successful connection will add an entry in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\LastConnect (resp. SNI10.0 if using SQL Server 2008) that will contain the port number.




    Thanks a lot that was exaclty the cause of that strange behaviour. But confirm me that this mechanism works only for sqlcmd connections?
     
    At this point maybe there's one another regedit cached entry that permit this other kind of magic:

    0) sqlbrowser.exe down on sqlclusterfla02 node.
    1) sqlwb.exe -S sqlclusterfla02\INSTANCE2 -E don't works
    2) sqlbrowser.exe up on sqlclusterfla02 node.
    3) sqlwb.exe -S sqlclusterfla02\INSTANCE2 -E works
    4) sqlbrowser.exe down on sqlclusterfla02 node.
    5) sqlwb.exe -S sqlclusterfla02\INSTANCE2 -E works !!!!   <<<= = = =

    Thanks again
    Friday, October 17, 2008 9:19 AM
  • No, this works for any type of connections, and the cache is stored in a LastConnect hive for the corresponding client under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client.

     

    My guess is that sqlwb uses exactly the same registry as sqlcmd, but to confirm, try the steps in your example and check how the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client changed.

     

    There should be several client hives there - SNI9.0 and/or SNI10.0, SuperSocketNetLib. Check which one gets a new entry in the LastConnect hive after the successful connection in step 3.

     

    Let me know if you still have any questions.

     

    Thanks

    Stoyko

    Friday, October 17, 2008 4:14 PM
  •  Stoyko Kostov - MSFT wrote:


    There should be several client hives there - SNI9.0 and/or SNI10.0, SuperSocketNetLib. Check which one gets a new entry in the LastConnect hive after the successful connection in step 3.




    Hi Stoyko,

    Confirm to you that here happen exaclty what i told you.
    By one side the cached connections (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client regedit entries) seem to work only for sqlcmd connections. Try it!. Stated sqlbrowser.exe is down, sqlwb.exe doesn't connect whille sqlcmd.exe does.
    By the other side after step 3) no entries found in the registry to explain this kind of magic: sqlwb.exe is able to connect despite sqlbrowser.exe has brought down.

    Hope to have been clear.
    Best regards
    Flavio
    Monday, October 20, 2008 7:38 AM
  • http://sqlerrormessages.blogspot.com/2009/12/specified-sql-server-not-found-or-error.html
    Saturday, December 19, 2009 10:02 AM