Client(s) can't connect to SQL Server 2008 R2 server

Discussion in 'Networking & Security' started by exchange keys, Jul 27, 2011.

  1. exchange keys

    exchange keys Limp Gawd

    Messages:
    339
    Joined:
    Feb 18, 2009
    I'm in the process of installing an application server that requires SQL Server 2008 R2 (x64) on a separate device. However, I'm noticing this error message whenever I try to install the application through its proprietary configuration tool:

    Code:
    ERRO: CheckDBCreation failed to create database.  Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The server was not found or was not accessible.  Verify that the instance name is correct and that SQL Server is configured to allow remote connections (provider: TCP Provider, error: - = No such host is known).
    ERRO: CheckDBCreation failed to drop database
    ERRO: The provided SQL user may not have permissions to create and drop databases.
    ERRO: ConfigureServer: The Configuration did not validate
    ERRO: Task 'ValidateConfiguration' Failed (Exit Code=1)
    
    This is what I have done so far:

    On Server:
    - Installed Client Tools
    - Set it as Mixed-Mode Authentication (Windows + SQL)
    - Created inbound rule on Windows Firewall to allow TCP 1433 (SQL Connection) and UDP 1434 (SQL Browser Service) from a small IP scope (only have four "test" servers in my network) under Domain, Public, and Private
    - Created SQL accounts in Server Management Studio and verified that they are able to connect to SQL
    - Checked DB instance in Server Management Studio to allow remote connections to the server
    - Checked in Server Configuration Manager > Native Client 10.0 Configuration (32-bit) > Client Protocols, and verified that TCP/IP was enabled (default port of TCP 1433)
    - Checked in Server Configuration Manager > Server Network Configuration > Protocols of instance, and verified that TCP/IP was enabled (not using Named Pipes...not even sure what that is...)
    - Restarted the SQL services (SQL Browser Service in particular)
    - Used "telnet localhost 1433" on telnet client and failed to connect
    - Used "telnet 127.0.0.1 1434" on telnet client and failed to connect

    On client:
    - Used "telnet sql-computer-name 1433" on telnet client and failed to connect
    - Used "telnet ip-address 1433" on telnet client and failed to connect
    - Used "telnet ip-address 445" on telnet client and that worked
    - Used "telnet sql-computer-name 80" on telnet client and that worked

    So, I think it is the way I'm setting up my SQL connections. I've never set up SQL Server 2008 R2 before, so I'm rather clueless. Perhaps someone shed some light on this since I feel frustrated now.
     
  2. squishy

    squishy [H]ard|Gawd

    Messages:
    1,207
    Joined:
    May 25, 2006
    You were on the right track, but having the server listen on localhost/127.0.0.1 may or may not be what you want. Regardless, you need to tell SQL to bind to the ethernet interface since you're trying to connect to it over the network.

    Do a netstat -a on the server and see if you have 0.0.0.0:1433 or the ethernet interface:1433. You could also do telnet serverip 1433.

    If it's not listening on the desired interface, you need to look up how tell SQL to bind to that interface (I don't know, I'm not familiar with it either).
     
  3. squishy

    squishy [H]ard|Gawd

    Messages:
    1,207
    Joined:
    May 25, 2006