Connecting SQL Server Virtual Machine to Web Role in same Azure Virtual Network

Question

I'm attempting to set up a Virtual Network in Windows Azure and use it to avoid opening a public endpoint on my (CUSTOM) SQL Server Virtual Machine. However, I continuously get a network related error, stating that the SQL Server wouldn't talk back in time, when trying to access my web application via my cloud service's URL.

I've looked all over the net for tutorials that show how to connect to one's own Custom-created VM instead of one of Windows Azure's preconfigured Virtual Machines, and found little of use. All the suggestions I've found I've tried.

I am working in Windows 7, using Visual Studio 2010 with the Windows Azure SDK installed, SP1.
Here are some details of what I have attempted to do to no avail.

I have:

• created the Virtual Network with
  
  • its own Affinity Group
  • a single Subnet
• added the Virtual Machine to it
  • making sure to put it in the same affinity group as the one I created for the VNet
• installed SQL Server
• configured SQL Server as per this tutorial
• Added my databases and a login that I have verified can access the database
• Both:
  1. Converted an existing Asp.NET Website to a Web App and added a Azure Deployment Package thing see here for the tutorial I followed
     • I used r-click->Publish to Azure/Publish for this one, configured to use an existing Cloud Service I had already deployed in the VNet with the SQL VM, and made sure it was in the same Subnet as the VM.
     • it is also worth noting that this application did connect to a similar VM that was deployed outside the Virtual Network (still in Azure) by opening a public endpoint on port 1433 and using the Public IP address to connect to it.
  2. Used the converted Web App's code in a brand new Azure Cloud Service project configured as per this tutorial (the first one I mentioned)
     • I attempted both publishing by:
       • r-click->Publish to Azure/Publish
       • r-click->Package and uploading it on the Azure Portal
     • in both cases both to
       • an existing Cloud Service in the VNet (and Subnet)
       • and a brand new Cloud Service created in the VNet (and Subnet) and upload package during creation or immediately publish to service as soon as started.

• Double checked that all Cloud Services and Virtual Machines I've gone through were in the VNet, and in the same Subnet.

  • My Cloud service is usually at internal IP 10.4.2.5, and the VM at 10.4.2.4. My connection string is the same as the first tutorial I mentioned only with the proper authentication and my VM's internal IP specified. Connection string follows:

    <add name="SQLServerinWAConnection"
    connectionString="Data Source=tcp:SQLVMInternalIPAddress;Initial Catalog=MyTableName;User ID=loginName;Password=thepassword;Encrypt=true;Trusted_Connection=false;TrustServerCertificate=true"
    providerName="System.Data.SqlClient" />
    

    • I also tried specifying Trusted_Connection=true

No matter what I try, I cannot get this application to connect to the SQL Server instance on that VM. I have even added a public endpoint to the VM at port 1433 and tried using its public IP and private IP, to no avail. That was my fallback, so now I'm at a serious loss.

Some implementation details that may or may not have any bearing:

• The SQL Server instance is named, not default, so instead of just 'SQLServerVM' in the object explorer in SQL Server Management Studio, it has 'SQLServerVM\SQLServerDB'.
• I have the port 1433 opened on the firewall on the VM for any IP range and any user

I will add any additional details (in case you don't want to read the whole tutorials to figure out what I've done) upon request.

There isn't by any chance a checklist available to state the things which need to be done for a web role or website to be able to connect to a virtual machine in its virtual network? That would greatly simplify troubleshooting.

Any suggestions would be greatly appreciated. I would very much like to have this working by the end of the day.

Answer 1

In my case, since our client installed SQL Server on the VM, using a named database instance, the service which hosted the instance I needed to connect to didn't have its TCP port set properly. So my detail that the SQL Server instance was named was indeed important.

If you just cannot figure out why your Web Role (Cloud Service) just isn't connecting to your Virtual Machine in the same Virtual Network, In addition to checking all of the things above in the question, check the following setting:

1. Log into the Virtual Machine (RDP)
2. Open the SQL Server Configuration Manager
3. Expand "SQL Server Network Configuration" in the left panel.
4. Click on "Protocols for {SQL Instance name here}" in the left panel.
5. Right-Click on "TCP/IP" in the right panel, go to "Properties..."
6. Double check that "Enabled" is set to "Yes".
7. Switch to the "IP Addresses" tab.
8. At this point, you should see that the "TCP Port" should be 1433 for at least the domain IP (in my case 10.4.2.4 in the "IP2" section), if not "IPALL" or some others.
   • Note that the "TCP Port" settings on all the "IP{X}" sections may have different values.

IF you don't see this SQL Server instance listening on 1433 (or some other port you are trying to configure):

1. Go to "IPALL" and change the "TCP Port" to 1433 (or whatever port you like, 1433 is the default that things will send to).
   • This will allow that port to be listened on for addresses coming to this server from anywhere.
   • Note that there is probably a cleaner way to do this, but this worked quite well for us.

This allowed me to access the SQL Server instance from all the Cloud Services in that VNet, using only the Internal IP Address of the VM, without a public endpoint opened for the port I configured (1433).
Just in case, here is the working connection string:

<add name="ApplicationServices"
    connectionString="Data Source=tcp:{VM Internal IP}\{InstanceName},{port};Initial Catalog={Table};User ID={username};Password={passwd};Encrypt=true;Trusted_Connection=false;TrustServerCertificate=true" providerName="System.Data.SqlClient"/>

Make sure you replace:

• {VM Internal IP} with your internal IP address
• {InstanceName} with your SQL Server Instance's name, or leave it and the preceding \ out entirely if you have a default instance.
• {port} should either be 1433 or whatever port you set open in your VM for that Sql Server instance.
• {Table} with the Database table you want to use by default
• {username} and {passwd} with those for your SQL Server user. Note that I am using SQL Server authentication here.

It's also worth noting that this did not open my server up to the internet (as expected), as I still can't get at it from the outside world, so it remains secured within the VNet this way.

Hopefully this will help someone in the future.