SQL Query from Azure Web Role to SQL Server 2008 R2 Express hosted on VM Role

Question

I have SQL Server 2008 R2 Express installed on Azure VM Role (1 extra small instance)

Because of hosting very old banking system, which is not able to connect to SQL Azure. Data persistency is reached via Azure Storage Page Blob. I have written a Windows Service, which can mount VHD file and also can attach MDF database file to SQL Server.

My problem is that I need to SQL query from Azure Web Role to SQL Server hosted on Azure VM Role.
I tried nearly everything:

I have opened TCP 1433, UDP 1434, added SQL Browser to Windows Firewall.
I have enabled remote connection, SQL Server Browser, TCP/IP, Named Pipes on SQL Server.
I have changed TCP/IP port to 1433 and I have disabled dynamic port.

I have already read:
http://social.msdn.microsoft.com/Forums/en-US/windowsazuremanagement/thread/b5f2967e-57e6-4099-9077-fb6d74897dbf
http://social.msdn.microsoft.com/Forums/en/windowsazuremanagement/thread/2b64a56f-fda7-4247-b016-9d25bbeebe8b

If you were so kind and wanted to help me, I would send you all the screenshot from my settings.

Thank you for your time.

Regards,

Martin David

Answer 1

Hello Martin,

Thank you posting!

This is a quick note to let you know that I am performing research on this issue and will get back to you as soon as possible. I appreciate your patience.

Thanks,

---

Wengchao Zeng
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact [e-mail].
Microsoft One Code Framework

Answer 2

Hello Martin,

Are the VM role and the web role in the same azure service project or not? Different suggestion will be provided depending on this.

Basically, if you expose the SQL Service service via input endpoint, you may try connnecting to it using Microsoft SQL Server Management Studio from your machine. The server name is the same as the DNS name of your azure service project. And the definition of the endpoint element will be:

<InputEndpoint name="SqlServerPort" protocol="tcp" port="1433" localPort="1433" />

If you expose it via internal endpoint, you may need to enable remote desktop for the web role and install Microsft SQL Server Management in a service instance to have a check. Since the IP addresses for internal conmmunication are dynamic, we need to use the following code to get the IP address of the VM role.

RoleEnvironment.Roles["RoleName"].Instances[0].InstanceEndpoints["EnpointName"].IPEndpoint.Address.ToString();

If you need further assistance, please let me know.

Thanks,

---

Wengchao Zeng
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact [e-mail].
Microsoft One Code Framework