PORT configuration required to access IAAS / PAAS SQL remotely using SQL management studio

This article on port configuration for remote access of IaaS or PaaS SQL in Azure using SQL Management studio  is in continuation to our previous article on setting up SQL in IAAS

PORT configuration in AZURE SQL (IAAS)

Once you have the VM set up, every time you can’t get into the server to perform your day to day activity in SQL, you would expect to connect SQL (IAAS) from your SQL Management studio (installed in your local machine)

Without making any FIREWALL changes in VM, If you’re trying to connect to SQL (IAAS) from your SQL management studio, you would end up getting the below error.

Initial error:

Here is what you need to do to get this working; login to PORTAL.AZURE.COM

Go to ALL resources and look for .NSG file of your machine, in our case we need to look for SharePoint 2016 NSG which is our machine name.

Select SharePoint 2016 NSG file and select inbound security rules in it

Click ADD provide the name and select the right service and in this case do select MS SQL

And port would be automatically get added based on your service selection.

Now you should be able to access SQL from your machine.

Note: This also depends on your organization firewall setting, if you’re in open network for example (from home) you should be able to access SQL seamlessly.

Now let’s see how to access PAAS SQL from outside

A related kind of setting needs to be set in PAAS as well to access SQL (PAAS) from SQL management Studio (from your local machine)

When you try to access, AZURE SQL (PAAS) from your SQL management studio (2014), you would end up seeing the below error

Here is what you need to do to get this working; login to PORTAL.AZURE.COM

Go to ALL resources and look for the SQL Server (BYB you might have created a SQL Server (logical Server) for creating a PAAS SQL)

Select the SQL instance and click FIREWALL settings;

Click ADD CLIENT IP

And provide the IP which was shown in the SQL management studio (error) and click SAVE.

NOTE: You can also give range of IP’s if you want. Wait for 5 to 10 minutes to get this refreshed.

Go back to SQL management studio, you should be able to connect now.

Note: This also depends on your organization firewall setting, if you’re in open network for example (from home) you should be able to access SQL seamlessly.