Configuring the Azure VM for SQL Server connectivity

In the last SQL Bangalore UG meeting, I had talked about how to use the Custom Scripting component in Azure to run the post configuration operations on an Azure VM which was hosting a SQL Server instance. The post configuration options that I am going to talk about in this post are necessary for you to be able to connect to your SQL Server instance on an Azure VM from a Management Studio running on your on-premise machine.

Before you can connect to the instance of SQL Server from the Internet, the following tasks must be completed:

Configure SQL Server to listen on the TCP protocol and restart the Database Engine.
Open TCP ports in the Windows firewall.
Configure SQL Server for mixed mode authentication.
Create a SQL Server authentication login.
Create a TCP endpoint for the virtual machine. This would normally be done while providing the endpoint configuration if you are using the Azure Management Portal wizard.

If you had used an Image from the Image gallery, then you will get a default database engine installed with the TCP/IP port configured as 1433. I had written a post earlier which walks through an Azure VM creation using a SQL Server image from the image gallery.

Here I am going to talk about how to automate the bulleted points mentioned above using PowerShell and the Custom Script extension that the Azure provides. This is going to be a long read… So I suggest you get a coffee before you start reading further!

First, we will need to setup the firewall correctly. This is done using our favorite and faithful netsh commands.

# Configure the TCP port rule in Windows Firewall to allow connections
# Prepare the arguments for the NETSH command
$TCPPort = "<number>"
$Arguments = "advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = " + $TCPPort + " remoteip = ANY profile = PUBLIC"
# Execute the command silently
$p = Start-Process netsh -ArgumentList $Arguments -wait -NoNewWindow -PassThru
$p.HasExited
$p.ExitCode

The next part would be to enable the TCP/IP protocol for the default SQL Server instance and configure the instance to listen on the correct port. And yes, PowerShell scripts can help here as well.

# Configure the SQL Server TCP/IP protocol for the port that was configured in the endpoint
# Import the SQLPS module
Import-Module sqlps -DisableNameChecking
# Create a SMO object
$TCPPort = "<number>"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$MachineObject = New-Object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') .
$tcp = $MachineObject.GetSMOObject("ManagedComputer[@Name='" + (Get-Item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']")
if ($tcp.IsEnabled -ne "True")
{
    $tcp.IsEnabled = $true
    $tcp.alter
    $MachineObject.GetSMOObject($tcp.urn.Value + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = $TCPPort
    $tcp.alter()
}
else
{
    $MachineObject.GetSMOObject($tcp.urn.Value + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = $TCPPort
    $tcp.alter()
}

Now that you have configured the TCP/IP port and enabled connections to come through the firewall, you will need a SQL Authenticated user to connect from an on-premise installation. This is because the virtual machine is not joined to any domain due to which you do not have domain users that can be used to connect to the SQL Server instance using Windows Authentication.

So, the next configuration change required would be to enable mixed mode authentication for the SQL Server instance. You would need to change the Login mode to mixed which is what the Powershell code does below.

# Import the SQLPS module
Import-Module sqlps -DisableNameChecking
# Enabled MIXED mode authentication for the SQL Server instance
# Create a SMO object
$LoginObject = New-Object ('Microsoft.SqlServer.Management.Smo.Server') .
# Change the authentication mode to MIXED
$LoginObject.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed
# Persist the changes
$LoginObject.Settings.Alter()

The next task would be to run T-SQL scripts on the SQL Server instance to do the following:

Enable remote admin connections so that DAC can be utilized remotely
Create a new SQL Authenticated user which can be used to log into the SQL server instance from an on-premise machine
Add the newly created SQL Authenticated user to the sysadmin server role


# Enable Remote DAC configuration setting on the SQL Server instance

# Import the SQLPS module
Import-Module sqlps -DisableNameChecking
# Prepare the T-SQL script to be executed against the SQL Server instance
$TSQLScript = "
-- Enable advanced options
exec sp_configure 'show advanced options',1;
reconfigure with override
-- Enabled remote DAC connections
exec sp_configure 'remote admin connections',1;
reconfigure with override"
# Execute the T-SQL script against the SQL Server instance
Invoke-SqlCmd -ServerInstance . -Query $TSQLScript -Database "master"

You will need to restart the SQL Server instance for some of the above changes to take effect. So the last bit of the PowerShell script will stop the SQL Server service, wait for 30 seconds and then start the SQL Server service.


# Import the SQLPS module
Import-Module sqlps -DisableNameChecking 

# Stop and start the SQL Server instance
$MachineObject = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') .
# Pickup the default SQL Server instance  
$Mssqlserver = $MachineObject.Services['mssqlserver']
# Stop the service
$Mssqlserver.Stop()
# Wait for 30 seconds
start-sleep -s 30
# Start the service again
$Mssqlserver.Start()

Once the above is done, you will need to remote into your Azure Virtual Machine once and create a SQL Authenticated user which can be used to log into your SQL Server instance from a Management Studio running on an on-premise server.

# Map the password with the variable name
# Import the SQLPS module
Import-Module sqlps -DisableNameChecking
$SqlPwd = "<AStrongPassword!>"
$Pwd = "SqlPwd = '" + $SqlPwd + "'"
# Create login script to create a login named sqlazadmin
# After the login is created, it is added to the sysadmin server role
$TSQLScript = "
CREATE LOGIN sqlazadmin
WITH PASSWORD = `$(SqlPwd) MUST_CHANGE,
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON;

ALTER SERVER ROLE [sysadmin] ADD MEMBER [sqlazadmin];
"
# Execute the T-SQL script against the SQL Server instance
Invoke-SqlCmd -ServerInstance . -Query $TSQLScript -Database "master" -Variable $Pwd

The consolidated parameterized script is available on OneDrive and in the code block below.


<#

.SYNOPSIS

Runs post configuration installation tasks for a SQL Server instance on an Azure VM which was created using an Azure Gallery Image.

.DESCRIPTION

This PowerShell script does the following actions:

1. Configures the Windows Firewall to allow access to the SQL Server instance from on-premise or other clients

2. Enable TCP protocol for the SQL Server instance

3. Sets the configured private TCP port for the SQL Server instance

4. Enables mixed mode authentication

5. Stops and starts the SQL Server service

.EXAMPLE

.\Set-SqlConnectivity -TCPPort 1433 -DACPort 1434

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

Author: Amit Banerjee

Date: July 31, 2014

#>

param

(

# This is the public port that the SQL Server endpoint is listening on.

# The default port number is 1433

# This can be changed while deploying the virtual machine

[Parameter(Mandatory = $true)]

[String]

$TCPPort,
# This is the public DAC port that the SQL Server endpoint is listening on.

# The default port number is 1434

[Parameter(Mandatory = $true)]

[String]

$DACPort

)

# The script has been tested on Powershell 3.0

Set-StrictMode -Version 3

# Following modifies the Write-Verbose behavior to turn the messages on globally for this session

$VerbosePreference = "Continue"

# Configure the TCP port rule in Windows Firewall to allow connections

# Prepare the arguments for the NETSH command

$Arguments = "advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = " + $TCPPort + " remoteip = ANY profile = PUBLIC"

# Execute the command silently

$p = Start-Process netsh -ArgumentList $Arguments -wait -NoNewWindow -PassThru

$p.HasExited

$p.ExitCode

# Configure the TCP port rule in Windows Firewall remote for DAC connections

# Prepare the arguments for the NETSH command

$Arguments = "advfirewall firewall add rule name = SQLDACPort dir = in protocol = tcp action = allow localport = " + $DACPort + " remoteip = ANY profile = PUBLIC"

# Execute the command silently

$p = Start-Process netsh -ArgumentList $Arguments -wait -NoNewWindow -PassThru

$p.HasExited

$p.ExitCode

# Configure the SQL Server TCP/IP protocol for the port that was configured in the endpoint

# Import the SQLPS module

Import-Module sqlps -DisableNameChecking

# Create a SMO object

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

$MachineObject = New-Object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') .

$tcp = $MachineObject.GetSMOObject("ManagedComputer[@Name='" + (Get-Item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']")

if ($tcp.IsEnabled -ne "True")

{

$tcp.IsEnabled = $true

$tcp.alter

$MachineObject.GetSMOObject($tcp.urn.Value + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = $TCPPort

$tcp.alter()

}

else

{

$MachineObject.GetSMOObject($tcp.urn.Value + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = $TCPPort

$tcp.alter()

}

# Enabled MIXED mode authentication for the SQL Server instance

# Create a SMO object

$LoginObject = New-Object ('Microsoft.SqlServer.Management.Smo.Server') .

# Change the authentication mode to MIXED

$LoginObject.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed

# Persist the changes

$LoginObject.Settings.Alter()

# Enable Remote DAC configuration setting on the SQL Server instance

# Prepare the T-SQL script to be executed against the SQL Server instance

$TSQLScript = "

-- Enable advanced options

exec sp_configure 'show advanced options',1;

reconfigure with override

-- Enabled remote DAC connections

exec sp_configure 'remote admin connections',1;

reconfigure with override"

# Execute the T-SQL script against the SQL Server instance

Invoke-SqlCmd -ServerInstance . -Query $TSQLScript -Database "master"

# Stop and start the SQL Server instance

$MachineObject = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') .

# Pickup the default SQL Server instance

$Mssqlserver = $MachineObject.Services['mssqlserver']

# Stop the service

$Mssqlserver.Stop()

# Wait for 30 seconds

start-sleep -s 30

# Start the service again

$Mssqlserver.Start()

Happy Automation! Any suggestions and feedback about this script is always welcome!

Reference:
Getting Started with SQL Server in Azure Virtual Machines
http://msdn.microsoft.com/library/azure/dn133151.aspx