Configuring Firewall Access for SQL Database on Azure

Its quite common that the first time you work with a feature in a cloud interface, it can appear so foreign until you’ve done it a few times. If it’s a task that you may not do often or only once, it can be downright painful. It doesn’t matter who the cloud provider is or the application, we all have felt the pain of this type of situation and why some of us even started blogging…:) Until its familiar, it may not feel comfortable and you may not even like how it works.

As a newer employee at Microsoft, I’m often learning right along with my customers. Lucky for me, I learn quick, but I feel their pain and if something slows me down, then I know it will for the customer, too. One of the tasks that I noticed tripped us up in configuring SQL Database on Azure was configuring the firewall for SSMS, (SQL Server Management Studio) or Visual Studio, (VS) access. We all know that having access for SSMS is important for many DBAs to have a single pane of glass for database management and for me, well, I like to do everything in Visual Studio- I’m just strange that way.

To configure access for SSMS to access SQL Database, log into your Azure portal, then click on the database you wish to configure access for, (sorry folks, I need this play environment for a while, so I’ve removed some info from the screenshots):

Click on Set Server Firewall at the top, which will bring you to the following screen:

The one thing I really like about Azure is that it displays your IP address in the console, assuming this may be the information you require. If you are attempting to add this to access a local installation of VS or SSMS, simply enter in an identifying name for your rule and then add the IP address or a range of IP Addresses in the Start IP and End IP field, then click Save.

If you need to retrieve the IP address for another host or workstation, you can do this by opening the command prompt and type in ipconfig. From there, look at the IPv4 address:

Now know that if you work from numerous WiFi, MiFi and other network connections, your IP Address will change. If your IP address changes, you’ll need to add a new rule or if it’s a temporary location, just remove the rule and create a new one for your new access point to keep things uncluttered.

Once this is performed, it’s time to connect to your Azure SQL Database. If you’re asking me how you find that information, let’s go back to our main page for the database:

Clock on the link for Show database connection strings and it will take you to the following, displaying the JDBC.net connection info in the second tab:

Well, look there, all you need is the first thing displayed after Server=. Note that it’s a “,” and not a “:” between the server name and the port, as you’ll need to do the same in SSMS or VS when you enter the connection. Your user name has an addition too: Username@domain.com@sql database name.

After you enter this, use the password you configured your database with. If you forgot, the main page in the portal has a “reset password” link for you to update it.

You’ll then connect with something that looks like the following:

I can then switch to a user database and manage Azure SQL Databases just as I have if they were on-prem. Enjoy!

Related posts:

Let this blog guide you more about this high-tech, portable and multifunctional device. Watches have been there for decades in our world, but as humans evolved with time so did the watches. Here I am…

In a letter published in the Detroit Free Press, five faculty members of the Hillsdale College Economics Department presented a plan to prevent panic buying during the coronavirus emergency. They…

Over the past five years, the Shared Spaces market has the surprisingly rapid pace of development. The traditional model “one company, one office” was completely overturned. Today, the shared offices…