This is part 24 of azure tutorial. In this video, we'll discuss how to connect and access azure
sql database from your local machine using sql server management studio, visual studio or for
that matter, any client tool. As you can see from the page URL, this web application is deployed in
azure and the quotes that we see on this webpage are coming from a SQL server database that is also
deployed in azure. Since both, our web application and SQL server database are in azure, the web
application does not have any problem accessing the SQL server database. Now, what I want to do
is, access the SQL server database in azure from my client machine using SQL server management
studio. First, we need to specify the name of the SQL server that we want to connect to. If
you don't know the name of your SQL server, you can always get it from the azure portal.
At the moment, we are looking at my SQL server instance in azure and we are on the "Overview"
blade and we have the server name right here - quotesdbdbserver101.database.windows.net
and to copy this name to clipboard, click this button right here.
Paste the name of the server here, the authentication that we want to use,
in our case we want to use sql server authentication, the login username and password.
At the moment, my sql server management studio is trying to establish a connection to sql server
database in azure. There we go. Connection unsuccessful. By default, we cannot connect
to azure sql database from our local machine. It doesn't really matter what we are using to connect
sql server management studio, visual studio or some other client tool. By default, the connection
to azure sql database will be blocked and you'll see this error - Your client IP address does not
have access to the server. Sign into an azure account and create a new firewall rule to enable
access. This is for obvious security reasons. You don't want to allow everyone to be able
to connect to your sql database in azure. If for some reason, you want to allow access to someone,
you have to explicitly whitelist their IP address. You do this by adding a new firewall rule. So,
in the azure portal, make sure you are on your sql server instance and scroll all the way down,
under security you'll find this option "Firewalls and virtual networks". Click on that. We see
client IP address. This is the IP address of my local development machine. We want to allow my
local machine to be able to access sql server database in azure. For that, all we need to do
is click this button on the top here - "Add client IP". When I click this, notice we have a new rule
added, allowing my IP address to be able to access sql server database in azure. So, let's save this.
We are back in sql server management studio. Let's cancel this "New firewall rule" dialog
and then try to connect again. There we go. We are connected to our sql server instance in
azure. If we expand databases, we see our "QuotesDB" database and if we expand that,
we see our "Quotes" table. Let's fire a new query editor window.
Select our database QuotesB and then let's execute this query - Select * from Quotes.
There we go. We see the data as expected. Now, let's try to connect from visual studio. Get
to "SQL Server Object Explorer". For that, click on "View" and then "SQL Server Object Explorer".
At the moment, we have two sql server instances here. By looking at the name,
we can say both these are SQL Server LocalDb instances. We want to connect to sql server
in azure. So, click on this icon right here which says - "Add SQL Server". Make sure you're on the
"Browse" tab and then expand "Azure". Notice, we have two databases in azure - master and QuotesDB,
both of them on this SQL server instance. Remember the name, this is our SQL server instance
in azure. We want to connect to QuotesDB database. Notice, when I select this, the server name,
username, everything is populated automatically. All we need to do is, provide the password
and then click "Connect". There we go. We are connected. Let's expand the server and
then databases, we see our QuotesDB database and our Quotes table here.
Let's execute a query and for that click on this icon which says - "New query". To run this query
click this button and we see our data as expected. That's it in this video. Thank you for listening.