MSSQL error ”The specified network name is no longer available”

This problem has been bugging us for a long time. When you try to connect to Microsoft’s SQL Server using IP address with SQL Management studio you get this error:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (.Net SqlClient Data Provider)

When you dismiss the error it just keeps popping up time to time but otherwise everything seems to work. This is caused by the Management Studio attempting to connect to the SQL Server by the server name reported by the server. This can happen for example when your production environment is isolated from intranet and servers do not have fully qualified domain names.

However this is easy to fix, you just need to change the server name to correspond to the IP address of the server:

sp_dropserver <current name>;
sp_addserver 'a.b.c.d', local;

After this you need to restart the SQL Server’s instance and you are done. You can check that the new name set correcyly by running: