Use SQL Server Management Studio to connect remotely to an SQL Server Express instance hosted on an Azure Virtual Machine

Question

Initial Attempt

I have an Azure VM with Windows Server 2012, on which I just installed SQL Server 2012 Express Database Engine component. Then, I followed the instructions here to connect remotely with SQL Server Management Studio.

1. Create a TCP endpoint for the virtual machine
2. Open TCP ports in the Windows firewall
3. Configure SQL Server to listen on the TCP protocol
4. Configure SQL Server for mixed mode authentication
5. Create SQL Server authentication logins
6. Determine the DNS name of the virtual machine
7. Connect to the Database Engine from another computer

After step seven I received the following error:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

What else do I need to configure before connecting remotely?

Troubleshooting

I have been following the troubleshooting instructions here. Each blockquote below is a step described that that link.

Confirm the instance of the SQL Server Database Engine is installed and running.

Done. We installed SQL Server Express, and it is running as a named instance called SQLEXPRESS.

If you are attempting to connect to a named instance, make sure the SQL Server Browser service is running.

Done. We followed the steps here to turn on the SQL Server Browser service.

Get the IP Address of the computer.

Done. We will use these later for testing connectivity and maybe for setting up a static port for SQLEXPRESS.

10.241.62.155

fe80::45c:8c29:e19f:f78b%15

Get the TCP port number used by SQL Server.

Done. The SQL Server Management Studio server logs showed that the server was listening on port 49169.

Enable Protocols

Done. We had already enabled TCP/IP in the configuration manager, but we restarted the SQL Server Service just in case.

Testing TCP/IP Connectivity

Done. We used tcping.exe to test connectivity (cmd ping doesn't work quickly with Azure.) We were able to connect to port 80.

• tcping.exe buddha.cloudapp.net > successful
• tcping.exe buddha.cloudapp.net 80 > successful

Testing a Local Connection

Done. We used sqlcmd.exe from the command prompt and were able to connect locally via TCP with a username and password.

• sqlcmd -S Buddha\SQLEXPRESS (success via shared memory protocol)
• sqlcmd -S tcp:Buddha\SQLEXPRESS (success via TCP)
• sqlcmd -S tcp:Buddha\SQLEXPRESS -U sa -P (success via TCP with username)
• sqlcmd -S tcp:10.241.62.155\SQLEXPRESS -U sa -P (success with internal IP)

Opening a Port in the Firewall

We opened the port on which we SQLEXPRESS listens. The server logs (above) showed that SQLEXPRESS was listening on port 49169, but this is just one of many dynamic ports, and we wanted to set up the static port 1435.

• Use WF.msc to create an inbound TCP rule for port 1435.
• Use Azure Management Portal to create a TCP endpoint for port 1435.

The troubleshooting instructions also say:

If you are connecting to a named instance or a port other than TCP port 1433, you must also open the UDP port 1434 for the SQL Server Browser service.

Since we are connecting SQLEXPRESS (a named instance), we needed to open port 1434 for UDP.

• Use WF.msc to create an inbound UCP rule for port 1434.
• Use Azure Management Portal to create a UDP endpoint for port 1434

Further research about connecting to named instances revealed dynamic port issues. The reason why we are using port 1435 (static) instead of port 49169 (one of many effective options.)

Instances of SQL Server Express, SQL Server Compact, and named instances of the Database Engine use dynamic ports. To configure these instances to use a specific port, see Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager). and here.

Done. We went to SQL Configuration Manager > SQL Server Network Configuration > Protocols for SQLEXPRESS > TCP/IP, we did the following.

Protocol Tab > Listen All > NO.

IP Addresses Tab > for each listed address

• Enabled > Yes
• TCP Dynamic Ports > Blank (delete the zero)
• TCP Port > 1435 (or your choice)

After restarting the SQLEXPRESS service, we again looked in the SQL Server Management Studio logs, and found that the Server is Listening on port 1435!!! Hooray!

Testing the Connection

Done. We opened SQL Server Management Studio on our local (non-Azure) computer and connected.

• buddha.cloudapp.net,1435 OR buddha.cloudapp.net\SQLEXPRESS
• sa
• password

Success.

Answer 1

Here are the three web pages on which we found the answer. The most difficult part was setting up static ports for SQLEXPRESS.

Provisioning a SQL Server Virtual Machine on Windows Azure. These initial instructions provided 25% of the answer.

How to Troubleshoot Connecting to the SQL Server Database Engine. Reading this carefully provided another 50% of the answer.

How to configure SQL server to listen on different ports on different IP addresses?. This enabled setting up static ports for named instances (eg SQLEXPRESS.) It took us the final 25% of the way to the answer.