Microsoft sql server ошибка 1225

asked Aug 30, 2018 at 13:00

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 SERVICEReportServer  
   Shared memory 52          DomainAccount
   Shared memory 55          NT SERVICESQLSERVERAGENT
   Shared memory 54          NT SERVICESQLSERVERAGENT
   Shared memory 53          NT SERVICESQLSERVERAGENT
   Session       55          NT SERVICESQLSERVERAGENT
   Session       55          NT SERVICESQLSERVERAGENT
   Shared memory 56          NT SERVICEReportServer
   Shared memory 57          DomainAccount
   
   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 [ \.pipeSQLLocalMSSQLSERVER ].
   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 [ \.pipesqlquery ].
   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 [ \.pipeSQLLocalMSSQLSERVER ].
   2018-08-24 08:36:09.07 spid15s     Server local connection provider is ready to accept connection on [ \.pipesqlquery ].
   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)}

answered Aug 30, 2018 at 14:19