Updating the IP Address for SQL Server in an Azure VM

We have been running builds from an Azure VM for over a year now, and over the last few months we have had some issues where the IP address will change on the VM, which impacts the ability to connect to the local SQLExpress database to run database scripts.

The error encountered looks like this:

Could not open a connection to SQL Server [2]. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

The reason for the error is that Azure has changed the IP address for the VM, and it is explicitly set in SQL Server Configuration Manager.

The quick fix is shown in the graphic below; here are the steps:

1. Open command prompt, run ipconfig.  The address you are looking for is under the "Tunnel adapter", the IPv6 address
2. Open SQL Server Configuration Manager, browse under SQL Server Network Configuration to the instance you are debugging, open the "TCP/IP" properties dialog by double-clicking on it in the right pane
3. Under IP1 on the "IP Addresses" tab, compare the IP address to the one in the command prompt window - in my case it is different when this error occurs
4. Just copy and paste the IP address out of cmd and into the text box in the SQL Server Configuration Manager properties dialog, save and restart SQL server, you should be in business.