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?
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.
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\AccountTCP = IP Connection Shared memory = Local Connection
Your local connection would show up as a Shared memory connection.
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
Verifying the network configuration in the SQL Server Configuration Manager can help resolve issues. Possible errors:
Enabled: Yes and Active: Yes)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.
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)
