MS-SQL HA database backup using Powershell and Ansible, Step by Step - 2 -
Setting up WinRM
Unlike the integration with Linux or UNIX like servers and Ansible, Windows machines cannot use SSH for Ansible integration, just yet(*1) . Thus, you will have to use Windows Remote Manager (WinRM) , instead.
1. Install WinRM Memory Hotfix on Windows server
Run the following PowerShell script to install the hotfix:
$url = "https://raw.githubusercontent.com/jborean93/ansible-windows/master/scripts/Install-WMF3Hotfix.ps1" $file = "$env:temp\Install-WMF3Hotfix.ps1" (New-Object -TypeName System.Net.WebClient).DownloadFile($url, $file) powershell.exe -ExecutionPolicy ByPass -File $file -Verbose
2. Configure WinRM
Run the following PowerShell script to setup WinRM HTTP/HTTPS listeners with a self-signed certs and the basic authentication.
$url = "https://raw.githubusercontent.com/ansible/ansible/devel/examples/scripts/ConfigureRemotingForAnsible.ps1" $file = "$env:temp\ConfigureRemotingForAnsible.ps1" (New-Object -TypeName System.Net.WebClient).DownloadFile($url, $file) powershell.exe -ExecutionPolicy ByPass -File $file
e.g.
Now, check WinRM's status:
winrm enumerate winrm/config/Listener
e.g.
Take note of the CertificateThumbprint (red rectangle above), and run below getting the details of the certificate:
$thumbprint = "<your certificateThumbprint>"
Get-ChildItem -Path cert:\LocalMachine\My -Recurse | Where-Object { $_.Thumbprint -eq $thumbprint } | Select-Object *
e.g.
To check the current WinRM service configuration, run the following commands:
winrm get winrm/config/Service winrm get winrm/config/Winrs
3. Create an service account for Ansible
To connect Windows machine, set up a service account for Ansible access.
Add user:
net user <username> <password> /add
Add the created user to the Administrator group:
net localgroup administrators <username> /add
e.g.
4. Add the created user to MS-SQL server's sysadmin.
Login to the Windows machine and launch up MS SQL Server Management Studio:
Navigate toSecurity > Logins, right click and New login.
Enter user name that has been created in the previous step:
Set Check sysadmin.
Check if SQL connection below
Click OK to close, and log off from MSSMS.
Note: if you don't like to give a database sa permission to the ansible user, modify the permission as required. (This particular user should be visible to the all databases under this SQL host server as it will search and backup them all. )
5. Test HTTP/HTTPS connection
- These tests can be done on the localhost or a remote host that WinRM ports(5985/5986) is opened.
- Username and password are the ones you have created at the step 3.
- Testing's Windows server name will be the "Issure Name" of certificate (CN field value of certificate's details ) .
HTTP test
winrs -r:http://<Windows server hostname>:5985/wsman -u:<username> -p:<password> ipconfig
HTTPS test
winrs -r:https://<Windows server hostname>:5986/wsman -u:<username> -p:<password> -ssl ipconfig
Note: This HTTPS test will fail if the certs is not verifiable.
e.g.
If you get that error, setup below: HTTPStest, ignoring certificate verification
$username = "<username>"
$password = ConvertTo-SecureString -String "<password>" -AsPlainText -Force
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $password
$session_option = New-PSSessionOption -SkipCACheck -SkipCNCheck -SkipRevocationCheck
Invoke-Command -ComputerName <Windows server hostname> -UseSSL -ScriptBlock { ipconfig } -Credential $cred -SessionOption $session_option
e.g.
Reference
Setting up a Windows Host — Ansible Documentation
How to install Ansible on Ubuntu Server 18.04 - TechRepublic
experimental SSH connection for Windows managed nodes (*1) Looks like there is an experimental SSH integration for Windows servers. But do it at your own risk :)
To be continued... (Next would be steps on the Ansilbe server.)
