I have created a basic Virtual Machine with SQL Server 2016 with the Azure portal by using a default image in the Azure gallery. This is an easy task and you can find a lot of articles and documentation out there in order to make it. That having said, I would like to talk about how to setup network rules in order to access to the Virtual Machine with Remote Desktop (RDP) and SQL Server with SQL Server Management Studio. Besides, I will talk a little bit about ping options. It usually triggers a lot of headaches due to security rules companies enforce to TCP communications so that, at least, let's be aware of what's going on.
Let's get started by displaying the inbound rules I configured in the Virtual Machine Networking settings:
Figure 1. Virtual Machine Networking settings.
Using PsPing to test ICMP connectivity
Before going ahead, I would like to talk a little bit about PsPing (use the link to download). It is a handy tool to test connectivity. It implements ping functionality, TCP ping, latency and bandwidth measurement. I prefer employing this tool because default windows Ping tool works by using ICMP (Internet Control Message Protocol). This way, we cannot try TCP connections with it. In any case, in order to make default windows Ping tool work, two actions must be done in Azure:
1) Create an inbound rule in Virtual Machine Networking settings to allow ICMP protocol. You can see it in the image above (line with priority 280). Note that we are receiving a warning due to I have not restricted the access from specific sources. This should be done for production environments. This case, for demo purposes I have not added any further restriction.
2) ICMP protocol is disabled by default in Windows Firewall. So, when creating the Virtual Machine that is precisely the provided behavior (disabled). We have to enable the following rules in the Windows Firewall depending on we are using ICMPv4 or/and ICMPv6 protocols:
Figure 2. Firewall inbound rules in VM concerning ICMP protocols
After completing previous steps and rebooting the VM, default windows Ping tool works fine:
Same result is achieved with PsPing:
Using Remote Desktop to access Virtual Machine
Remote Desktop Protocol (RDP) uses TCP port 3389 to operate so that we must create an inbound port rule in the Virtual Machine Networking settings to allow traffic through it. It is an easy task as you only have to include a rule similar I included in the Figure 1 with named "RDP".
Notice we are receiving a warning with message "RDP port 3389 is exposed to the Internet. This is only recommended for testing. For production environments, we recommend using a VPN or private connection". So, take it into account when dealing with production servers. This is only for demo purposes.
In order to test connectivity, let's see what happen with PsPing:
Yes, it works! We can then download the RDP quick link to access the virtual machine. This can be make by taking the following steps:
1) Go to the Virtual Machine in Azure Portal and click "Connect" button.
2) Enter the proper IP Address or DNS Name, port number (3389) in the "Connect to Virtual Machine" panel and then, download the quick access link with the "Download RDP File" button.
Using SQL Server Management Studio to access SQL Server in Virtual Machine
Deploy an SSIS project with .NET code (C#) - SQL Server Integration Services (SSIS) | Microsoft Docs
An Azure SQL Database server listens on port 1433. If you are trying to connect to an Azure SQL Database server from within a corporate firewall, this port must be open for you to connect successfully. So, similar steps for accessing to SQL Server engine should be done in comparison to RDP. We have to create a new inbound rule, this case with TCP port 1433 and then, test connectivity:
After checking it works fine, we can try with SSMS. Enter DNS name for your virtual machine, user and password. If it works fine you will see your SQL Server new connection within object explorer:
Figure 8. SQL Server Login Page.
Figure 9. SQL Server Object Explorer.
So, it is working fine. We can start running queries in our database!
Considerations for default VM - SQL Server 2016 installation
Concerning SQL Server, let me incorporate some information I consider very useful for you to know. It turns out that default installation includes SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS). You can access the VM, go to Administrative tools -> Services and check all of them are present:
Figure 10. Related SQL Server services in VM
In addition to this, a proper version of SQL Server Management Studio is also installed in the VM. You don't have to install by yourself.
How to enable ping (ICMP echo) on an Azure VM
Setup an Azure Development VM for Testing Power BI + SQL Server 2016 Integration