First or rather easy step in any application migration would be to migrate our application as is. This would mean that we would have a Azure virtual machine hosting Sql Server for our databases. This is actually as simple as it sounds. Now once we have this ready, requirements like connecting on premise SSMS (Sql Server management studio) to Sql Server on Azure VM or connecting other on premise Admin related pages or application to Sql Server on Azure VM would popup. In this article we will tackle these requirements and achieve connection to Sql Server on Azure VM from on premise entities.
So let us start with creating a Azure VM hosting Sql Server. For simplicity we will name this VM as VM-SqlServer12
Once we have this machine, we will create a end point which would allow us to connect to Sql Server.
Go to End Points tab and click on Add End Points
Select the default Add EndPoint option
Ensuring correct port numbers is very important here. The public port is used to access the virtual machine from the Internet. The private port is used to control internal access to the virtual machine through the firewall
Once our end point is defined we will actually connect (RDP) into this VM and update the firewall settings so that it allows us to connect to Sql Server installed in it
While connecting we will be prompted to enter the password that we entered while creating this VM.
We will open up Sql Server configuration manager and ensure that Named Pipes and TCP/IP are enabled. To enable a protocol, right-click the protocol name and select Enable.
Next step would be to make a firewall rule such that incoming request on port 1433 for Sql Server are allowed
Go to Start | All Programs | Administrative Tools and open Windows Firewall.
Change default Rule Type to Port
Keep the protocol as TCP but enter specific port as 1433
For the remaining part of New Inbound Rule wizard select the default and add the rule.
Final step would be to create a user in Sql Server which would allow us to make Sql Authentication.
Open SSMS and create a new Login
In new Login window select Sql Server authentication and create a testuser
Next step would be to enable Sql authentication. Right click on sql server and click on properties.
Go to security tab and select Sql Server and Windows Authentication mode.
Well now we are almost there. In order to ensure that all these configuration changes are applied we will restart the Sql server and try to connect it from on premise SSMS. In order to connect to Sql server from on premise SSMS we would form the server name as per following rule
tcp:<DNS Name>,Public Port ----> tcp:vm-sqlserver12.cloudapp.net,55555