Setting SQL Server certificate using Ansible

this us usually implemented as Ansible role

ansible-role-win-sql-certificate
├── defaults
│   └── main.yml
├── handlers
│   └── main.yml
├── tasks
│   ├── get-first-certificate-thumbprint-issued-by.yml
│   ├── get-first-certificate-thumbprint.yml
│   └── main.yml

./defaults/main.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
---
# defaults file for ansible-role-win-sql-certificate
sql_server_version_major_path_prefix: 'MSSQL12'
sql_server_instance_name: MSSQLSERVER
sql_server_registry_supersocketnetlib: 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\{{ sql_server_version_major_path_prefix }}.{{ sql_server_instance_name }}\MSSQLServer\SuperSocketNetLib'
sql_server_service_account: 'NT Service\{{ sql_server_instance_name }}'
sql_server_service_name: '{{ sql_server_instance_name }}'
sql_server_certificate_machinekey_dir: 'C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys'
 
# define sql_server_ad_service_account_name if running using an AD account.
# otherwise fallback to sql_server_service_account
# sql_server_ad_service_account_name: "MYDOMAIN\\somesqlserviceaccount"
 
# If you define sql_server_certificate_thumbprint then this certificat must exist
# sql_server_certificate_thumbprint: 'd673b6b2d7768eff649fb06fa16e3a13b15bb255'
 
# if you define sql_server_first_certificate_issued_by then the first certificate in the
# certificate store matching the criteria is used
# sql_server_first_certificate_issued_by: 'LAB-RootCA'

./handlers/main.yml

1
2
3
4
5
6
---
# handlers file for ansible-role-win-sql-certificate
- name: restart sql service
  ansible.windows.win_service:
    name: '{{ sql_server_service_name }}'
    state: restarted

./tasks/get-first-certificate-thumbprint-issued-by.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
---
- name: Obtain information about a particular certificate in the computer's personal store
  community.windows.win_certificate_info:
    store_location: 'LocalMachine'
  register: result_machine_certificates
 
# - debug:
#     var: result_machine_certificates
 
- set_fact:
    sql_server_certificate_thumbprint: '{{ result_machine_certificates.certificates | selectattr("issued_by", "equalto", sql_server_first_certificate_issued_by) | map(attribute="thumbprint") | first | lower }}'
  when:
    - sql_server_first_certificate_issued_by is defined
    - result_machine_certificates is defined       
    - result_machine_certificates.exists
    - result_machine_certificates.certificates is defined
    - result_machine_certificates.certificates | length > 0
 
- debug:
    var: sql_server_certificate_thumbprint
  when: sql_server_certificate_thumbprint is defined

./tasks/get-first-certificate-thumbprint.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
---
- name: Obtain information about a particular certificate in the computer's personal store
  community.windows.win_certificate_info:
    store_location: 'LocalMachine'
  register: result_machine_certificates
 
# - debug:
#     var: result_machine_certificates
 
- set_fact:
    sql_server_certificate_thumbprint: '{{ result_machine_certificates.certificates | map(attribute="thumbprint") | first | lower }}'
  when:
    - result_machine_certificates is defined       
    - result_machine_certificates.exists
    - result_machine_certificates.certificates is defined
    - result_machine_certificates.certificates | length > 0
 
- debug:
    var: sql_server_certificate_thumbprint
  when: sql_server_certificate_thumbprint is defined

./tasks/main.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
---
- ansible.builtin.include_tasks: "get-first-certificate-thumbprint-issued-by.yml"
  when:
    - sql_server_certificate_thumbprint is not defined
    - sql_server_first_certificate_issued_by is defined
 
- ansible.builtin.include_tasks: "get-first-certificate-thumbprint.yml"
  when:
    - sql_server_certificate_thumbprint is not defined
    - sql_server_first_certificate_issued_by is not defined
  
- name: set sql certificate thumbprint in registry
  ansible.windows.win_regedit:
    path: '{{ sql_server_registry_supersocketnetlib }}'
    name: 'Certificate'
    data: '{{ sql_server_certificate_thumbprint }}'
    type: string
  notify:
    - restart sql service
  when: sql_server_certificate_thumbprint is defined
 
- name: set sql force ssl in registry
  ansible.windows.win_regedit:
    path: '{{ sql_server_registry_supersocketnetlib }}'
    name: 'ForceEncryption'
    data: 1
    type: dword
  notify:
    - restart sql service
  when: sql_server_certificate_thumbprint is defined
 
- name: get certificate machine keyname
  win_shell: |
    $certThumbprint = "{{ sql_server_certificate_thumbprint }}"
    $cert = Get-ChildItem -Path cert:\LocalMachine\My | Where-Object {$_.Thumbprint -eq ($certThumbprint -replace '\s','')}
    $keyName = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName
    write-host $keyName
  register: result_cert_machine_key
  changed_when: false
  when: sql_server_certificate_thumbprint is defined
 
- debug:
    var: result_cert_machine_key
  when: result_cert_machine_key is defined
 
- name: set acl on certificate machine keyname
  win_acl:
    path: '{{ sql_server_certificate_machinekey_dir }}\{{ result_cert_machine_key.stdout_lines[0] }}'
    user: '{{ sql_server_ad_service_account_name | default(sql_server_service_account) }}'
    rights: FullControl
    type: allow
    state: present
  notify:
    - restart sql service
  when:
    - result_cert_machine_key is defined
    - result_cert_machine_key.stdout_lines is defined
    - result_cert_machine_key.stdout_lines | length > 0