none
ODBC connection issue to SQL 2000 SP4. [TCP/IP Sockets]Specified SQL server not found.

    Question

  • Server: Stand alone Windows 2003 R2 Enterprise x64 Edition Service Pack 2

    The server has 4 quad-core processors with 32Gb RAM

    SQL: 2 instances of SQL Server 2000 Enterprise Edition SP4 with cumulative update version: 8.00.2040

    I am trying to connect to the named instance of SQL Server with an ODBC connection. I am a local admin on the server and a sysadmin on both the default instance and the named instance. I get the error message

    Connection failed:
    SQLState: '01000'
    SQL Server Error: 11004
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
    Connection failed:
    SQLState: '08001'
    SQL Server Error: 6
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

    My users are trying to connect via SQL login and are also receiving the same error message.

    The default instance is listening on port 1433. The named instance is listening on port 1464. There is no Windows firewall installed on the server.

    The annoying thing is that if I click on the button called "Client Configuration" when using the "ODBC Data Source Administrator" dialogue wizard, and I un-tick the radio box called "Dynamically determine port", and hardcode the port 1464 into the dialogue box, everything works as it should do.

    Unfortunately this is not an acceptable solution as my 100 users don't understand ODBC connections and want a seemless journey to this new server from the old one. We are using GSS entries to trick the network into resolving the old server name to the new server name.

    I am connecting from a Windows XP laptop service pack 3 and most of my users will be connecting similarly.

    When using SQL Server Network Utility I can verify that each instance has only "TCP/IP" selected in the enabled protocols, and that when looking into the properties the port numbers are correct. 1433 for the default instance, 1464 for the named instance.

    Both instances are running under domain accounts with local administrator privileges.

    When I look at regedit entries as described in forums I can see the correct port numbers being used.

     

    Any help with this difficult matter would be greatly appreciated. I will post more relevant information as I come across it.

    Regards

    Dale

     

     

    Monday, April 11, 2011 2:30 PM

All replies