MSSQL Server Setup - designed to be run via ssh, ie vagrant under opentable/win-2012r2-standard-amd64-nocm

setup_mssql.ps1

$ErrorActionPreference = "Stop"
$client = new-object System.Net.WebClient

# Reset vagrant password so it's not expired
([adsi]"WinNT://vagrant-2012-r2/vagrant").SetPassword("P@55w0rd!")

# Setup UAC wrapper ;(
if(!(Test-Path -Path "C:\uacts_x64.zip")) {
	Write-Output "Setting up UAC wrapper"
    $client.DownloadFile("http://www.itknowledge24.com/files/uacts_x64.zip", "C:\uacts_x64.zip")

	$shell = new-object -com shell.application
	$zip = $shell.NameSpace("C:\uacts_x64.zip")
	foreach($item in $zip.items())
	{
		$shell.Namespace("C:\").copyhere($item)
	}

	# Install
	$p = [System.Diagnostics.Process]::Start('C:\setup.exe')
	$p.WaitForExit()
}

# Disable the firewall
Write-Output "Disabling the firewall"
netsh advfirewall set allprofiles state off

# Install .NET
Write-Output "Installing .NET"
import-module servermanager
add-windowsfeature as-net-framework

# Download SQL server
if(!(Test-Path -Path "C:\SQLEXPR_x64_ENU.exe")) {
	Write-Output "Downloading SQL server"
    $client.DownloadFile("http://download.microsoft.com/download/8/D/D/8DD7BDBA-CEF7-4D8E-8C16-D9F69527F909/ENU/x64/SQLEXPR_x64_ENU.exe", "C:\SQLEXPR_x64_ENU.exe")
}

# Install SQL server
Write-Output "Installing SQL server"

# This crap is to work around UAC and no admin rights authing via ssh key
$service = new-object -ComObject("Schedule.Service")
$service.Connect()

$TaskDefinition = $service.NewTask(0)
$TaskDefinition.RegistrationInfo.Description = "Install SQL server"
$TaskDefinition.Settings.Enabled = $true
$TaskDefinition.Settings.AllowDemandStart = $true
 
$triggers = $TaskDefinition.Triggers
$trigger = $triggers.Create(1)
$trigger.StartBoundary = [datetime]::Now.AddMinutes(0.5).ToString("yyyy-MM-dd'T'HH:mm:ss")
$trigger.Enabled = $true
 
$Action = $TaskDefinition.Actions.Create(0)
$action.Path = 'C:\SQLEXPR_x64_ENU.exe'
$action.Arguments = '/Q /IACCEPTSQLSERVERLICENSETERMS /HIDECONSOLE /ENU /ACTION=INSTALL /FEATURES=SQL,Tools /INSTANCENAME=SQLEXPRESS /INDICATEPROGRESS /SQLCOLLATION=Latin1_General_BIN /SQLSVCSTARTUPTYPE=Automatic /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /TCPENABLED=1 /SECURITYMODE="SQL" /SAPWD="P@55w0rd!" /UpdateEnabled="false"'
 
$rootFolder = $service.GetFolder("\")
$rootFolder.RegisterTaskDefinition("Install SQL server",$TaskDefinition,6,"System",$null,5)

# Wait for SQL server install to finish
while ($true) {
    if (Get-Service 'MSSQL$SQLEXPRESS' -ErrorAction SilentlyContinue | Where-Object {$_.status -eq "running"}) {
        break
    }

    # Wait 10 seconds
    Write-Output "Waiting for SQL server install to finish...."
    Start-Sleep -s 10
}

# Wait to make sure everything is in place
Start-Sleep -s 10

Write-Output "SQL server install finished, setting up port"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$MachineObject = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') .
$ProtocolUri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='SQLEXPRESS']/ServerProtocol"

$tcp = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Tcp']")
$tcp.IsEnabled = $true
$tcp.alter()

Start-Sleep -s 2
restart-service -f "SQL Server (SQLEXPRESS)"
Write-Output "Done!"