Share via

Connection to Azure SQL Database via .net Application

  • Article

Question

Thursday, September 11, 2014 5:38 AM

I want to build a plain vanilla vb.net application with cloud database storage. So I created a SQL Database in Azure, designed some tables, created user roles... Now, I want to build the application and recognize connections to the database are limited by the serverlevel firewall rules. I can create a filewall rule for my current IP and all works fine. However, if I plan to deploy to end users with dynamic IPs they won't be able to connect to the db.

My question is: How should I implement the Azure SQL Database in my application? Do I need another Azure product?

All replies (5)

Friday, September 12, 2014 5:02 PM ✅Answered | 1 vote

The risk of opening the firewall to everybody is that you are allowing the possibility that anyone can try to connect to your system & try to attack your system (for example, attempt to brute force your passwords).

I would recommend the following article:

In a direct connection model, your client application would connect directly to the database:

[ client app ]  > [ Azure SQL Database ]

In which case you would need to allow your customer through the firewall. If the number of clients is limited (you described ~20), it should be possible to manage direct connections & restrict the firewall to only the known IP addresses for these clients.

If you cannot determine the origin on the client IP address, I would recommend using a middle-tier architecture (which it sounds like you are already considering).

If you use a middle-tier approach, and probably you don’t need to open the database firewall so broadly. The middle-tier scenario would probably look something like this:

[ client app ]  > [ middle-tier app ] > [ Azure SQL Database ]

Under this architecture model, typically the middle-tier application authenticates the client and acts on behalf of the end user.

The middle-tier application would typically connect to the database using a single account (or a limited set of accounts) and then perform any operation on the database on behalf of the client.

Configuring the firewall in this case would be easy, you just need to allow the middle-tier host IP address(es). If your app is hosted on Azure, the portal should allow to configure this setting quite easily: http://msdn.microsoft.com/en-us/library/azure/ee621782.aspx#ConnectingFromAzure

I hope this information helps.

  -Raul Garcia

   SQL Server Security

This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, September 11, 2014 5:51 AM | 1 vote

Hi,

I assume that you have specified the connection string in your application config in a correct way as designated below -

Server=tcp:YourServerName.database.windows.net,1433;Database=YourDBName;User <ID=YourUserName@YourServerName;Password=%7Byour_password_here%7D;Trusted_Connection=False;Encrypt=True;Connection> Timeout=30;

Assuming your application is web application developed in vb.net -

  • If you are hosting on Azure cloud service(as a web role) - you don't need any firewall rule to be added in azure management portal. Only connection string would suffice.
  • If you are hosting on-premise - you need to provide access to 1433 outbound from firewall of your on premise network(to which your hosting server is connected) so that connections are not blocked.

Assuming your application is windows application developed in vb.net-

  • You will need outbound firewall rule for port 1433 from your network(to which your desktop that will run this vb.net windows app is connected) if at all it is blocked. Then once firewall rule is in place you just need to use connection string and you should be able to connect.

More guidance resource - http://msdn.microsoft.com/en-us/library/azure/ee336282.aspx

Hope this helps.

http://sanganakauthority.blogspot.com/

Thursday, September 11, 2014 6:04 AM | 1 vote

Hi Kunal,

I beg to disagree on this one.

"Firewall rules are required to be added from Azure portal only if you are planning to access the browser based light weight SQL Azure management from Azure portal."

the ip firewall restriction is not restricted to  Azure portal access to an Azure SQL Database.

It is required for any access to the Azure SQL Database regardless of where you access it.

To the original poster - i suggest you write middle layer deployed in Azure (Azure Website, Azure Webrole, either WCF/WebAPI), have that talking to your database, and then have your client apps connect to that middle layer rather than accessing the database directly.

Thursday, September 11, 2014 6:33 AM

Thanks JuneT for correcting my understanding. Removed the incorrect line from my post above.

http://sanganakauthority.blogspot.com/

Friday, September 12, 2014 5:26 AM

Hi, 

Thanks for your answers.

My project is a Windows Forms vb.net Application and I use the odbc-connection string and ado namespace to connect to the database. The application will have 20 End-Users and will be running just a couple of times a month. (Less then 200 connections to the database, small database 2 tables 100 and 12000 records). It is my first experiment with cloud backed programming.

First, what is the risk to open the firewall rules to 0.0.0.0 to 255.255.255.255 if I use the Encrypted:=True whithin the connection string?

Second, Do you know any tutorial or book for a sample of this middle layer architecture? Could you suggest which of the solutions may be the easiest and cheapest way for above objective?