Manage SQL Server Connectivity through Windows Azure Virtual Machines Remote PowerShell
Manage SQL Server Connectivity through Windows Azure Virtual Machines Remote PowerShell Blog
This blog post comes from Khalid Mouss, Senior Program Manager in Microsoft SQL Server.
Overview
The goal of this blog is to demonstrate how we can automate through PowerShell connecting multiple SQL Server deployments in Windows Azure Virtual Machines. We would configure TCP port that we would open (and close) though Windows firewall from a remote PowerShell session to the Virtual Machine (VM). This will demonstrate how to take the advantage of the remote PowerShell support in Windows Azure Virtual Machines to automate the steps required to connect SQL Server in the same cloud service and in different cloud services.
Scenario 1: VMs connected through the same Cloud Service
2 Virtual machines configured in the same cloud service. Both VMs running different SQL Server instances on them.
Both VMs configured with remote PowerShell turned on to be able to run PS and other commands directly into them remotely in order to re-configure them to allow incoming SQL connections from a remote VM or on premise machine(s).
Note: RDP (Remote Desktop Protocol) is kept configured in both VMs by default to be able to remote connect to them and check the connections to SQL instances for demo purposes only; but not actually required.
Step 1 – Provision VMs and Configure Ports
Provision VM1; named DemoVM1 as follows (see examples screenshots below if using the portal):
Provision VM2 (DemoVM2) with PowerShell Remoting enabled and connected to DemoVM1 above (see examples screenshots below if using the portal):
After provisioning of the 2 VMs above, here is the default port configurations for example:
Step2 – Verify / Confirm the TCP port used by the database Engine
By the default, the port will be configured to be 1433 – this can be changed to a different port number if desired.
1. RDP to each of the VMs created below – this will also ensure the VMs complete SysPrep(ing) and complete configuration
2. Go to SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for <SQL instance> -> TCP/IP - > IP Addresses
3. Confirm the port number used by SQL Server Engine; in this case 1433
4. Update from Windows Authentication to Mixed mode
5. Restart SQL Server service for the change to take effect
6. Repeat steps 3., 4., and 5. For the second VM: DemoVM2
Step 3 – Remote Powershell to DemoVM1
Enter-PSSession -ComputerName condemo.cloudapp.net -Port 61503 -Credential <username> -UseSSL -SessionOption (New-PSSessionOption -SkipCACheck -SkipCNCheck)
Your will then be prompted to enter the password.
Step 4 – Open 1433 port in the Windows firewall
netsh advfirewall firewall add rule name="DemoVM1Port" dir=in localport=1433 protocol=TCP action=allow
Output:
netsh advfirewall firewall show rule name=DemoVM1Port
Rule Name: DemoVM1Port
----------------------------------------------------------------------
Enabled: Yes
Direction: In
Profiles: Domain,Private,Public
Grouping:
LocalIP: Any
RemoteIP: Any
Protocol: TCP
LocalPort: 1433
RemotePort: Any
Edge traversal: No
Action: Allow
Ok.
Step 5 – Now connect from DemoVM2 to DB instance in DemoVM1
Step 6 – Close port 1433 in the Windows firewall
netsh advfirewall firewall delete rule name=DemoVM1Port
Output:
Deleted 1 rule(s).
Ok.
netsh advfirewall firewall show rule name=DemoVM1Port
No rules match the specified criteria.
Step 7 – Try to connect from DemoVM2 to DB Instance in DemoVM1
Because port 1433 has been closed (in step 6) in the Windows Firewall in VM1 machine, we can longer connect from VM3 remotely to VM1.
Scenario 2: VMs provisioned in different Cloud Services
2 Virtual machines configured in different cloud services. Both VMs running different SQL Server instances on them. Both VMs configured with remote PowerShell turned on to be able to run PS and other commands directly into them remotely in order to re-configure them to allow incoming SQL connections from a remote VM or on on-premise machine(s).
Note: RDP (Remote Desktop Protocol) is kept configured in both VMs by default to be able to remote connect to them and check the connections to SQL instances for demo purposes only; but not actually needed.
Step 1 – Provision new VM3
Provision VM3; named DemoVM3 as follows (see examples screenshots below if using the portal):
After provisioning is complete, here is the default port configurations:
Step 2 – Add public port to VM1 connect to from VM3’s DB instance
Since VM3 and VM1 are not connected in the same cloud service, we will need to specify the full DNS address while connecting between the machines which includes the public port. We shall add a public port 57000 in this case that is linked to private port 1433 which will be used later to connect to the DB instance.
Step 3 – Remote Powershell to DemoVM1
Enter-PSSession -ComputerName condemo.cloudapp.net -Port 61503 -Credential <UserName> -UseSSL -SessionOption (New-PSSessionOption -SkipCACheck -SkipCNCheck)
You will then be prompted to enter the password.
Step 4 – Open 1433 port in the Windows firewall
netsh advfirewall firewall add rule name="DemoVM1Port" dir=in localport=1433 protocol=TCP action=allow
Output:
Ok.
netsh advfirewall firewall show rule name=DemoVM1Port
Rule Name: DemoVM1Port
----------------------------------------------------------------------
Enabled: Yes
Direction: In
Profiles: Domain,Private,Public
Grouping:
LocalIP: Any
RemoteIP: Any
Protocol: TCP
LocalPort: 1433
RemotePort: Any
Edge traversal: No
Action: Allow
Ok.
Step 5 – Now connect from DemoVM3 to DB instance in DemoVM1
RDP into VM3, launch SSM and Connect to VM1’s DB instance as follows. You must specify the full server name using the DNS address and public port number configured above.
Step 6 – Close port 1433 in the Windows firewall
netsh advfirewall firewall delete rule name=DemoVM1Port
Output:
Deleted 1 rule(s).
Ok.
netsh advfirewall firewall show rule name=DemoVM1Port
No rules match the specified criteria.
Step 7 – Try to connect from DemoVM2 to DB Instance in DemoVM1
Because port 1433 has been closed (in step 6) in the Windows Firewall in VM1 machine, we can no longer connect from VM3 remotely to VM1.
Conclusion
Through the new support for remote PowerShell in Windows Azure Virtual Machines, one can script and automate many Virtual Machine and SQL management tasks. In this blog, we have demonstrated, how to start a remote PowerShell session, re-configure Virtual Machine firewall to allow (or disallow) SQL Server connections.
References