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

Question

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.

The fact that you're getting an error from the Names Pipes Provider tells us that you're not using the TCP/IP protocol when you're trying to establish the connection. Try adding the "tcp" prefix and specifying the port number:

tcp:name.cloudapp.net,1433

I too struggled with something similar. My guess is your actual problem is connecting to a SQL Express instance running on a different machine. The steps to do this can be summarized as follows:

1. Ensure SQL Express is configured for SQL Authentication as well as Windows Authentication (the default). You do this via SQL Server Management Studio (SSMS) Server Properties/Security
2. In SSMS create a new login called "sqlUser", say, with a suitable password, "sql", say. Ensure this new login is set for SQL Authentication, not Windows Authentication. SSMS Server Security/Logins/Properties/General. Also ensure "Enforce password policy" is unchecked
3. Under Properties/Server Roles ensure this new user has the "sysadmin" role
4. In SQL Server Configuration Manager SSCM (search for SQLServerManagerxx.msc file in Windows\SysWOW64 if you can't find SSCM) under SQL Server Network Configuration/Protocols for SQLExpress make sure TCP/IP is enabled. You can disable Named Pipes if you want
5. Right-click protocol TCP/IP and on the IPAddresses tab, ensure every one of the IP addresses is set to Enabled Yes, and TCP Port 1433 (this is the default port for SQL Server)
6. In Windows Firewall (WF.msc) create two new Inbound Rules - one for SQL Server and another for SQL Browser Service. For SQL Server you need to open TCP Port 1433 (if you are using the default port for SQL Server) and very importantly for the SQL Browser Service you need to open UDP Port 1434. Name these two rules suitably in your firewall
7. Stop and restart the SQL Server Service using either SSCM or the Services.msc snap-in
8. In the Services.msc snap-in make sure SQL Browser Service Startup Type is Automatic and then start this service

At this point you should be able to connect remotely, using SQL Authentication, user "sqlUser" password "sql" to the SQL Express instance configured as above. A final tip and easy way to check this out is to create an empty text file with the .UDL extension, say "Test.UDL" on your desktop. Double-clicking to edit this file invokes the Microsoft Data Link Properties dialog with which you can quickly test your remote SQL connection