Order a virtual server

A virtual server of the following specification is suitable for a bastion host and can be ordered using the instructions at Creating virtual server instances by using the UI

• Profile: bx2-2x8
• Type: Public
• OS: WIndows 2019
• NIC Qty: 1
• Data Volumes: None

Connect to the server

After the virtual server has been deployed you need to connect a Floating IP address to it so that you can access the server remotely, refer to Adding a floating IP address.

Refer to Connecting to Windows instances to access the Windows Administrator's password, however, in short the following commands are used from your laptop, where the instances command returns the <INSTANCE_ID> of the virtual server:

ibmcloud is instances
ibmcloud is instance-initialization-values <INSTANCE_ID> --private-key @~/.ssh/id_rsa

Download required files

In this deployment pattern we are using the following products which should be downloaded to the bastion host:

• MS SQL Server Developer Edition which can be downloaded from SQL Server products & resources.
• SQL Server Management Studio (SSMS) which can be downloaded from Download SQL Server Management Studio (SSMS).
• Diskspd is a storage subsystem testing tool and can be downloaded from DISKSPD 2.0.21a.
• PowerShell can interact with SQL Server instances, however, requires a module to be installed. If this module must be installed on the non-Internet connected SQL sever then download this module. Refer to SqlServer for more details.

You may find that Windows Explorer a little difficult to download files and want to disable IE Enhanced Security Configuration:

function Disable-InternetExplorerESC {
    $AdminKey = "HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A7-37EF-4b3f-8CFC-4F3A74704073}"
    $UserKey = "HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A8-37EF-4b3f-8CFC-4F3A74704073}"
    Set-ItemProperty -Path $AdminKey -Name "IsInstalled" -Value 0
    Set-ItemProperty -Path $UserKey -Name "IsInstalled" -Value 0
    Stop-Process -Name Explorer
}
Disable-InternetExplorerESC

$client = new-object System.Net.WebClient
$client.DownloadFile("https://aka.ms/ssmsfullsetup","C:\Users\Administrator\Downloads\SSMS-Setup-ENU.exe")

$client = new-object System.Net.WebClient
$client.DownloadFile("https://github.com/microsoft/diskspd/releases/download/v2.0.21a/DiskSpd.zip","C:\Users\Administrator\Downloads\DiskSpd-2.0.21a.zip")
Expand-Archive -LiteralPath C:\Users\Administrator\Downloads\DiskSpd-2.0.21a.zip -DestinationPath C:\Users\Administrator\Downloads\DiskSpd

Create an SMB share

The following PowerShell commands are used to accomplish the following:

• Check to see the status of the SMB2, typically this protocol is disabled in the virtual server image. If disabled it can be enabled, as it is required for SMB to operate.

• A local user <smbuser> is created to access the share remotely. Insert your required password at <password>.

• Share the C:\Users\Administrator\Downloads directory is shared.

  $user = "<smbuser>"
  $password = "<password>"
  Get-SmbServerConfiguration | Select EnableSMB2Protocol
  Set-SmbServerConfiguration -EnableSMB2Protocol $true -Force
  $secpassword = ConvertTo-SecureString $password -AsPlainText -Force
  New-LocalUser $user -Password $secpassword -FullName "Share User" -Description "User for SMB share"
  New-SmbShare -Name "Downloads" -Path "C:\Users\Administrator\Downloads" -ReadAccess $user
  

  The share will now be accessible from servers in your VPC as long as the security group allows the traffic. For more information on SMB, see Overview of file sharing using the SMB 3 protocol in Windows Server.

Install SQL Server Management Studio

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure and provides tools to configure, monitor, and administer instances of SQL Server and databases. To install SSMS using a command prompt script.

start "" /w C:\Users\Administrator\Downloads\SSMS-Setup-ENU.exe /Quiet SSMSInstallRoot=C:\SSMS

SSMS will be installed at C:\SSMS\Common7\IDE\Ssms.exe.

Join the domain

This task should not be started until after the AD server has been installed.

At a PowerShell prompt on the bastion host, enter the following commands that enable the server to join the domain:

• The Get-DnsClientServerAddress captures the Interface Index for the IPv4 Ethernet interface, so that the DNS can be changed from the IBM Cloud DNS server to the ADDNS server. The Add-Computer command will fail if this step is missed as the server will not be able to locate the domain controller. The Add-Computer -Server only accepts FQDN.
• The Add-Computer command adds the server to the domain <domain> using the ADDNS server <ad_server_fqdn> and then restarts the server to make the change effective.

$dns = "<ADDNS_IP_Address>"
$adserver = "<ad_server_fqdn>"
$domain = "<domain>"
$user = $domain + "\Administrator"
$password = "<password>"
$out=Get-DnsClientServerAddress -InterfaceAlias Ethernet -AddressFamily IPv4 | Select-Object -Property InterfaceIndex
Set-DnsClientServerAddress -InterfaceIndex $out.InterfaceIndex -ServerAddresses ($dns)
$password = ConvertTo-SecureString $password -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($user, $password)
Add-Computer -DomainName $domain -Server $adserver -Restart -Credential $credential

Add the PowerShell AD module

In Windows Server 2019, you can optionally install the Active Directory module for Windows PowerShell, using the following PowerShell commands. By adding the module, you can access AD information from the bastion host rather then RDP to the AD server:

Import-Module ServerManager
Add-WindowsFeature -Name "RSAT-AD-PowerShell" –IncludeAllSubFeature
Install-Module -Name WindowsCompatibility

Add the PowerShell SQL module

To install the latest version of the SQLServer module, use the following command on an Internet connected device, from a PowerShell prompt Install-Module -Name SQLServer -Force -AllowClobber.

Configure the NTP server

To synchronize time automatically from the AD domain hierarchy, run the following commands:

w32tm /config /syncfromflags:domhier /update
net stop w32time
net start w32time
w32tm /query /status