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


Sriram Varadarajan
Enterprise Architect
Published On :   10 Sep 2016
Visit Count
Today :  2    Total :   2506
Plan, Migrate, Secure, Report
SharePoint & Office 365 Tool. Simple & Easy to Use. 15-Day Trial!

SharePoint Office 365 Tool
Simple & Powerful Tool for Migration, Security & Reporting. Free Trial


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:

clip_image002

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

clip_image004

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.

clip_image006

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

clip_image008

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

clip_image010

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

clip_image012

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

clip_image013

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)

clip_image015

Select the SQL instance and click FIREWALL settings;

clip_image017

Click ADD CLIENT IP

clip_image019

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.

SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Categories

Protect Your SharePoint