I know pre-configured SQL Server VMs are available on Azure. But for one reason or another, I was not able to provision one w/ my free Azure developer account. I resorted to creating a VM and then installing SQL Server manually. This article will discuss some of the things I learned while setting up the VM.
I was using two different PCs to do database development. Each had its own copy of SQL Server running. Keeping both instances in sync was becoming a headache. I wanted a centralized SQL Server server I could access from both PCs. I also wanted to learn and utilize Azure. This led me to the decision to host the database in Azure.
I looked into a few of the options available for hosting SQL Server in Azure, including Azure SQL Database service. I settled on going the VM route because it was the cheapest option. Using the free Azure account and credits I get from a Visual Studio Professional Subscription license, I was able to set up a development SQL Server VM in Azure for very little out of pocket costs, if any.
Provisioning the VM is a straightforward process using the Azure portal. I'm not going to get into the details of setting up the VM. For this discussion, I will assume you have the VM provisioned and set up. For my development VM, I provisioned a relatively small VM; a B2s instance which is 2 vCPUs with 4GB RAM. I added an additional hard drive which was a 128GB Managed Standard HDD. I also enabled the Remote Desktop port so I could access the server. Mind you, opening this port to the Internet isn't a best security practice. In my case, I had a simple database on the VM w/ no sensitive data whatsoever. I don't recommend opening the RDP port on your VM to the Internet unless you understand the potential consequences of doing so.
VM Network Configuration
My bare bones VM needed a few things before I could open a SQL Server connection to it from either of my developer PCs. First, I needed to download and install SQL Server. I opened a RDP connection to my new VM and installed SQL Server 2019's Developer Edition. I installed the Database Engine only and used all the preset defaults specified by the installer. I also configured SQL Server to used mixed-mode authentication and set up a strong password for my SQL user account. Next, using the Azure portal, I set up a DNS name for my VM. The DNS name would become the server name for my connections to the SQL Server hosted on my VM. Here's an example of a DNS name.
To connect to the SQL Server from my PC using SSMS, I had to open port 1433 to the Internet. Port 1433 is the default port SQL Server listens on for new connections. Using the Azure portal's Networking settings, I added a new inbound port rule for port 1433 on my VM. Immediately afterwards, I noticed the following message.
Microsoft was warning me that nefarious folks doing port scans on public IP addresses could find my SQL Server and attempt to open a connection. Obviously, I wouldn't do this in a production environment, or any environment containing sensitive data for that matter. In fact, you shouldn't be exposing SQL Server to the public Internet on any port. If you have the need to expose your SQL Server, do as MS recommends and only do so inside your virtual network. One way to do this would be with a site-to-site VPN. Azure has a handful of services to help you do just, including their Virtual network gateway.
I decided that my database was inconsequential enough that I would leave it available publicly but not on the default port. So I set about figuring out how to expose SQL Server on the non-default port. The rest of this discussion will describe the steps I took to get there. Before you go trying to hack my development database, you should know I've since changed the port number SQL Server is listening on.
Configuring SQL Server to listen on a non-default port
After the rule was created, I navigated back to Network settings and removed the inbound rule for port 1433. I then created a new inbound rule to port 57500 to allow for TCP. Now my VM would allow TCP requests to port 57500, a random, generally unused port. The next step was to configure SQL Server to listen on port 57500. I opened the Sql Server Configuration Manager and expanded the SQL Server Network Configuration node as in Figure 1.
Double clicking on the Protocols for MSSQLSERVER node displayed the list of allowed protocols in the right pane. First I made sure TCP/IP was enabled. Then I configured TCP/IP to listen on port 57500. Double clicking on the TCP/IP node opened the dialog shown in Figure 2.
Towards the bottom of the dialog is the IPAll node. Expanding that node exposes two settings. The one I was most interested in was the TCP Port setting. This instructs SQL Server which port to listen for connections made via TCP. I changed the value to 57500 and restarted the service. At this point I thought I was done. I opened SSMS and attempted to connect to my SQL Server database running on my Azure VM. To connect to SQL Server using a non-default port, I had to specify the port number in my connection string. When entering the value for the Server name, I supplied the DNS name of my VM that I created earlier in the Azure portal and appended the port number, separating the two values by a comma. The format I used when making the connection is shown in Figure 3.
I entered my SQL Server user name account and password and clicked the Connect button. My first connection attempt to the database was unsuccessful. By default, SSMS was trying to connect to SQL Server using Named Pipes. I realized this because the error message notified me that the connection was being made using Named Pipes. Figure 4 shows a message like the one I received.
Fixing this required me to set the network protocol SSMS uses to connect to my database. Clicking on the Connect to Database Engine dialog's Options button expanded the connection dialog and displayed several tabs. I navigated to the Connection Properties tab and selected the TCP/IP option from the Network Protocol drop down control, as shown in Figure 5.
I tried a second time and was still unable to connect. After some searching, I learned that I needed to open a port on the Windows firewall running on my Azure VM. I headed back to my VM via an RDP connection to configure the firewall.
Configuring the Windows Firewall for the Non-Default Port
Configuring the firewall was a pretty straightforward affair. From within the VM, I opened the Windows Firewall MMC applet, right clicked on the Inbound Rules node and choose New Rule... from the context menu. From there, I selected the Port radio button and clicked Next. I chose to apply the rule to the TCP protocol, entered the port number 57500, and clicked Next. I chose to Allow the Connection and clicked Next. I applied the rule to all network locations, clicked Next, named it "SQL Port" and clicked Finish. Figure 6 shows the completed rule.
With my new firewall rule setup, I set out to connect to SQL Server for the third time. As they say, "third time is the charm"! All my hard work had paid off when I saw a successful connection made to my database.
Conclusion
It took a little elbow grease, but I was able to provision an Azure VM, install SQL Server onto it, configure it to connect to it over the public Internet over a non-default port, and eventually complete a connection. I hope you've learned a few things from my experience. I know I have.
Top comments (0)