none
Feeling Like an idiot

    Question

  • Did a fresh install of SQL Server 2012 on a Server 2012 box dedicated to the database.  Trying to connect to the box remotely, can't.  Tested using microsoft data link, opened up the firewall ports on the server and the client, and under the Server properties I've checked "Allow remote connections to this server".  On the client when I use the microsoft data link I can see the server but it still won't form a connection.  What have I forgotten.
    Friday, July 05, 2013 4:10 PM

All replies

  • Did you restart the SQL Server service after enabling remote connections?  Check the SQL Server error log for the related "listening on" messages.

    I suggest you verify port connectivity using TELNET or PowerShell.  Examples below using the default 1433 port:

    TELET example:

    TELNET YourServerName 1433
    

    PowerShell example:

    1433 | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, July 05, 2013 4:19 PM
    Moderator
  • 1) Check the SQL Server Error Log to ensure that SQL is listening on a TCP/IP port.  The message will look like:

    2013-07-05 08:47:43.38 spid13s     Server is listening on [ 'any' <ipv6> 1433].
    2013-07-05 08:47:43.38 spid13s     Server is listening on [ 'any' <ipv4> 1433].
    2013-07-05 08:47:43.38 spid13s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2013-07-05 08:47:43.38 spid13s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2013-07-05 08:47:43.39 Server      Server is listening on [ ::1 <ipv6> 1434].
    2013-07-05 08:47:43.39 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].

    This one indicates that the server is listening on all IP addresses on port 1433.  Then ensure you can connect locally.

    2) Then from a remote box ensure that you have name resolution to the target server using nslookup

    c:\> nslookup MyServer

    3) Then from a remote box ensure that you can connect to the listening port.  I like to use the Telnet Client, which is a installable Windows feature.  Then telnet to port 1433.

    c:\> telnet MyServer 1433

    If console window goes blank, you successfully connected (SQL Server doesn't actually use the telnet protocol so you can't get any farther than connecting).

    If all that works, you should be able to connect with Sqlcmd:

    c:\> sqlcmd -S tcp:MyServer

    David


    David http://blogs.msdn.com/b/dbrowne/



    Friday, July 05, 2013 4:20 PM
  • Tried the powerShell and it said that the machine is actively refusing the connection.   Using SQL Sever Configuration Manager, under SQL Server Network Configuration, Protocols for SQL Server, TCP/IP I set the TCP Port to 1433 and removed the 0 from TCP Dynamic Ports.  Restarted SQL Server under SQL Server Services. 

    Rebooted the server and now it listening under a different port.

    Friday, July 05, 2013 4:49 PM