In this article we will learn how to create a Virtual Machine with SQL Server and access the SQL Server from a local SQL Server Management Studio (SSMS). Go to the Azure portal and select the required Configuration of the Virtual Machine along with the SQL Server. In this case, I selected SQL Server 2014 Enterprise on Windows Server 2012 R2 as shown below.
![]()
The next is to select the "Deploy model" and click on the "Create" button as shown below.
![]()
Following are the five steps for creating and configuring the SQL Server on a Virtual Machine. Below are the five different steps.
![]()
The first step is to configure the basic setting of the Virtual Machine. Please use meaningful names so that it is easy for us to identify the details in the future.
![]()
Now, click on OK button to go to the next step.
In the 2nd Step, we will select the required VM. I am selecting A0 Standard Size as shown in the below screen capture and click on "Select" button.
![]()
The 3rd step is to create all the important settings, like selecting the storage account for storing the data (including the OS), Network, IPAddress along with any other extensions like Antivirus etc For now, we will leave with all the default settings.
![]()
The 4th step is to provide all the details related to SQL Server. As initially mentioned, our idea is to access the SQL Server over internet, we would select "Public (Internet)" in the SQL Connectivity dropdown as shown below. we will leave all other settings and click on "OK".
![]()
Important Point
In this case, we haven't selected any "Availability Set". However, in Production Environments, you should have at least 2 VMs configured in an Availability set in order to be eligible for the Azure SLA of three 9's. For more details please check the Azure documentation.
Below is the summary of the all the details. Click on the "OK" button.
![]()
That's it. We have created a Virtual Machine with a SQL Server. It would take couple of minutes to create the Virtual Machine. As shown below, I am able to successfully connect to the VM.
![]()
Now, Let's try to connect to the SQL Server instance as shown in the below screen capture. It's not allowing us to connect to the SQL Server.
![]()
I got an error while trying to access the SQL Server located in the Virtual Machine that I just created. The reason why we are able to connect to the Virtual Machine but not the SQL Server is because the port (3389) required by the Remote Login is by default opened. However, the port required for SQL Server (1433) is not allowed by default. It's getting blocked by the Firewall So, we need to explicitly allow the port for external access.
Let's configure the firewall to allow the traffic to the 1433 port in the Virtual Machine that we have just created.
Go to Server Manager and click on the "Tools -> Windows Firewall with Advanced security" as shown in the below screen shot.
![]()
Once the "Windows Firewall with Advanced security" is opened, click on the "In Bound Rules" on the left hand side of the below screen capture. And now click on the "New Rule" button which is located in the right hand side of the following screen capture.
![]()
There are a couple of steps for creating the InBound rules. In the first step "Rule Type"select the "port" radio button and click on Next button as shown in the following screen capture.
![]()
In the 2nd Step "Protocol and Port" step, select "TCP" and provide "1433" in the "Specific local ports" textbox as shown below and click on "Next" button,
![]()
In the Action step, select "Allow the connection" radio button as shown below to allow the connections. In production environments, you might have to allow only secured connections.
![]()
In the following "When does this rule apply?" step, check all the check boxes for now and click on "Next".
![]()
The final step is to provide a name for the rule. Let's name this to MSSQL 1433 as shown below and click on "Finish".
![]()
That's it. Our new InBound rule is successfully created as shown below.
![]()
Now, in order to have our SQL Server to be connected from remote machines, we need to enable "SQL Authentication mode". Connect to the SQL Server on the Virtual Machine and go to properties as shown below.
![]()
In the properties window, select "Security" in the left hand side and select "SQL Server and Windows Authentication Mode" of the "Server Authentication" section and click on OK Button.
In order to get the changes effected, we would need to restart SSMS. A prompt will be displayed as shown below.
![]()
So, we will restart the SQL Server as shown in the below screen capture.
![]()
Now, Let's go ahead and create a new login by right clicking on "Security" tab in the left hand side and provide the credentials as shown below.
![]()
before clicking on "OK" button, select "Server Roles" located in the left hand side of the above screen capture and select "dbcreator" role as shown below,
![]()
That's it. We have successfully enabled SQL Authentication mode on the SQL Server of the new VM and also created a new user for remotely connecting the SQL Server.
Now, Let's try to connect to our Remote SQL Server from our local machine using SQL Server Management Studio.
![]()
Here is the screen capture of my SSMS after clicking on the "Connect" button in my previous step.
![]()
Now, It's connected :-). You can go-ahead and create a database from your local machine as we have given the user db-creater role. Let's summarize the points.