I am trying to explore the Azure Connect service provided by Windows Azure. So I have a simple ASP.NET application running as a Web Role in Azure which is trying to connect to a SQL database hosted on my local system. I followed all the steps provided in the Windows Azure Platform Training Kit and also the steps given here. After doing all this, I am able to ping the Azure Role VM hostname from my local system. I am able to ping my local system from within the VM (checked this by logging into then VM using mstsc). I am able to connect to my local system on port 1433 (opened for SQL communication) using telnet.
But after all this when I try to access the db hosted on my system from the Azure hosted application, I get 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
My connection string looks something like below:
<add name="MyConnectionString" connectionString="Data Source=My-PC\SQLEXPRESS;Initial Catalog=Student;Persist Security Info=True;User ID=user;Password=pass" providerName="System.Data.SqlClient"/>
I have done most of the settings mentioned in those tutorials like enabling remote connections on the SQL Server, enabling both Windows & SQL authentication on the SQL server, enabling Network DTC Access for remote client and opened port 1433 for incoming TCP requests on my firewall.
But I still get the same error. Anyone have any clues?
It has been a while since I played with Azure connect - but as I recall you need to specify the port number in your connection string when accessing an on-premises SQL Server instance from an Azure role.
Please try to modify your connection string adding the port to the Data Source:
Data Source=My-PC\SQLEXPRESS,1433