0

There was an ip change on the sql server and after that I cannot connect from other machines ssms but can connect locally .

The firewall is disabled on the server and moreover its listening on port 1433 and all the network protocols are open named pipes, shared memory and tcpip.

TCP/IP properties IPAII - TCP port set to 1433 and TCP Dynamic Ports are blank

Am i missing something?

closed as unclear what you're asking by hot2use, Erik Darling, Max Vernon, Colin 't Hart, Marco Sep 3 '18 at 13:10

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • I tried IP,1433 also by the default instance name - – SQL_NoExpert Aug 30 '18 at 13:30
  • 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: 0 - The remote computer refused the network connection.) (Microsoft SQL Server, Error: 1225) – SQL_NoExpert Aug 30 '18 at 13:30
  • I tied to telnet which failed as well ! – SQL_NoExpert Aug 30 '18 at 13:31
  • It listens to 'All' – SQL_NoExpert Aug 30 '18 at 13:40
  • Yes i have changed it to the new Ip – SQL_NoExpert Aug 30 '18 at 13:56
2

Possible Solutions

  1. Did you restart the SQL Server instance after the IP address was changed? If not, then the SQL Server was possibly still listening on the old IP address.

  2. When connecting locally you normally utilise the Shared Memory connection, that is why you were able to connect locally, but not from remote (see 1.). The Shared Memory connection does not require the IP address. You can verify this by running the following query after connecting locally:

    select 
        sdec.net_transport, 
        sdec.session_id, 
        sdes.login_name 
    from sys.dm_exec_connections as sdec 
    join sys.dm_exec_sessions as sdes 
        on sdec.session_id = sdes.session_id
    

    Possible output:

    net_transport session_id  login_name
    -------------------------------------------------
    TCP           51          NT SERVICE\ReportServer  
    Shared memory 52          Domain\Account
    Shared memory 55          NT SERVICE\SQLSERVERAGENT
    Shared memory 54          NT SERVICE\SQLSERVERAGENT
    Shared memory 53          NT SERVICE\SQLSERVERAGENT
    Session       55          NT SERVICE\SQLSERVERAGENT
    Session       55          NT SERVICE\SQLSERVERAGENT
    Shared memory 56          NT SERVICE\ReportServer
    Shared memory 57          Domain\Account
    
    TCP = IP Connection
    Shared memory = Local Connection
    

    Your local connection would show up as a Shared memory connection.

  3. Did you flush the DNS cache on your other server/computer before you tried connecting? Sometimes cleaning the cache will resolve IP <> Hostname resolutions issues:

     ipconfig /flushdns
    
  4. Verifying the network configuration in the SQL Server Configuration Manager can help resolve issues. Possible errors:

    • IP Address does not exist (See 1.: Reboot Instance)
    • IP Address is not configured to listen (Set Enabled: Yes and Active: Yes)
  5. After rebooting the SQL Server instance check the SQL Server Errorlog file to verify that it is indeed listening on the TCP stack:

    2018-08-30 16:15:47.12 spid15s     Server is listening on [ 'any' <ipv6> 1433].
    2018-08-30 16:15:47.13 spid15s     Server is listening on [ 'any' <ipv4> 1433].
    2018-08-30 16:15:47.13 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2018-08-30 16:15:47.13 spid10s     The resource database build version is 12.00.5589. This is an informational message only. No user action is required.
    2018-08-30 16:15:47.13 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2018-08-30 16:15:47.14 Server      Server is listening on [ ::1 <ipv6> 1434].
    2018-08-30 16:15:47.15 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2018-08-30 16:15:47.15 Server      Dedicated admin connection support was established for listening locally on port 1434.
    2018-08-30 16:15:47.15 SQL Server is now ready for client connections. This is an informational message; no user action is required.
    

    This instance was previously not listening on the TCP stack:

    2018-08-24 08:36:09.06 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2018-08-24 08:36:09.07 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2018-08-24 08:36:09.08 Server      Server is listening on [ ::1 <ipv6> 1434].
    2018-08-24 08:36:09.08 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2018-08-24 08:36:09.08 Server      Dedicated admin connection support was established for listening locally on port 1434.
    2018-08-24 08:36:09.09 spid15s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
    
  6. It could be a firewall rule that hasn't been adapted to the new IP address of the server. In that case you would have to have your company's network department look at the rules on the firewall and fix the rule for your server or if you only having issues with your Windows Firewall, check the rules there.
    (Added from comments)

Not the answer you're looking for? Browse other questions tagged or ask your own question.