Cloud Databases In Azure - Part Two

In the first article of this series, I explained how to create SQL database in Azure. I recommend reading the first article (link given below).

• Cloud Databases In Azure - Part One

In this article, we will see how we can connect to the SQL Database created in Azure using Visual Studio and SQL Server Management Studio.

Let’s get started.

You can see the connection string associated with the database (Vipul-aspnetmvcDB, created in the previous article) by clicking on the database.



If you see the above image closely, you will find that the connection string does not show the username and password for the database. We have to fill in those details. So, what username and password do we use? Because the username and password we used in the Azure portal are for an administrative login, we cannot use those in our application. We want to have a more restricted login for the application.

So, we have to create a new user for the application (this option is not available in the Azure portal), which we can create either from Visual Studio or SQL Server Management Studio.

NOTE

We can get the server name from the Azure Portal.



Let’s first check in Visual Studio 2015.

Go to Visual Studio -> View -> SQL Server Object Explorer. Right click on SQL Server and click on “Add SQL Server”.

A dialog will open and if you have signed in with your Azure credentials, then you can see a list of databases you can connect with. Select the database and fill in the password which you entered in the Azure portal.



Now, when you click on Connect, you will be able to access the schema and then you can execute the queries against that database.

Now, let’s check how we can connect using SQL Server Management Studio.

Connect to a Database Engine and give the server name, which we copied from Azure portal. Authentication will always be SQL Authentication because there is no Windows Authentication in Azure.



Now, the interesting part: When you click on Connect button on the above screen in SQL Server Management Studio, you will get the below error.

• Explanation
  We are getting the above error because, by default, your SQL Database will not be reachable by anything outside of Azure. SQL Server Management Studio is telling that the firewall is blocking this connection.
  
  
• Solution
  Go to Azure portal and click on Overview. At the top right, you will find an option – “Set server firewall”.
  
  
  

Click on the link and you will find the firewall settings and there, you will find an option – “Add Client IP”. Once you click on that link, it will add the client IP into the Azure firewall settings.

When these settings are done and you try to connect to the Azure database in SQL Server Management Studio, you will find that your Azure database will be connected without any error.

Summary

In this article, we saw how we could connect to Azure SQL Database using Visual Studio and SQL Server Management Studio.