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

SQL Server in Windows Azure Virtual Machines

 

Originally posted at http://blogs.msdn.com/b/sqlosteam/