7 files changed, 40 insertions, 16 deletions
diff --git a/ansible/group_vars/all/main.yaml b/ansible/group_vars/all/main.yaml
index 1969a09..353eef5 100644
--- a/ansible/group_vars/all/main.yaml
+++ b/ansible/group_vars/all/main.yaml
@@ -36,8 +36,8 @@ child_dc03_hostname             : "{{ lookup('ansible.builtin.env', 'child_dc03_
 child_dc03_vmid                 : "{{ lookup('ansible.builtin.env', 'child_dc03_vmid') }}" 
 main_websql01_hostname          : "{{ lookup('ansible.builtin.env', 'main_websql01_hostname') }}"
 main_websql01_vmid              : "{{ lookup('ansible.builtin.env', 'main_websql01_vmid') }}"
-main_mssql02_hostname           : "{{ lookup('ansible.builtin.env', 'main_mssql02_hostname') }}"
-main_mssql02_vmid               : "{{ lookup('ansible.builtin.env', 'main_mssql02_vmid') }}"
+    #main_mssql02_hostname           : "{{ lookup('ansible.builtin.env', 'main_mssql02_hostname') }}"
+    #main_mssql02_vmid               : "{{ lookup('ansible.builtin.env', 'main_mssql02_vmid') }}"
 main_web01_hostname             : "{{ lookup('ansible.builtin.env', 'main_web01_hostname') }}"
 main_web01_vmid                 : "{{ lookup('ansible.builtin.env', 'main_web01_vmid') }}"
 main_adcs01_hostname            : "{{ lookup('ansible.builtin.env', 'main_adcs01_hostname') }}"
@@ -51,7 +51,7 @@ main_dc01_ip_address            : "{{ lookup('ansible.builtin.env', 'main_dc01_i
 tree_dc02_ip_address            : "{{ lookup('ansible.builtin.env', 'tree_dc02_ip_address') }}"
 child_dc03_ip_address           : "{{ lookup('ansible.builtin.env', 'child_dc03_ip_address') }}"
 main_websql01_ip_address        : "{{ lookup('ansible.builtin.env', 'main_websql01_ip_address') }}"
-main_mssql02_ip_address         : "{{ lookup('ansible.builtin.env', 'main_mssql02_ip_address') }}"
+    #main_mssql02_ip_address         : "{{ lookup('ansible.builtin.env', 'main_mssql02_ip_address') }}"
 main_web01_ip_address           : "{{ lookup('ansible.builtin.env', 'main_web01_ip_address') }}"
 main_adcs01_ip_address          : "{{ lookup('ansible.builtin.env', 'main_adcs01_ip_address') }}"
 main_workstation01_ip_address   : "{{ lookup('ansible.builtin.env', 'main_workstation01_ip_address') }}"
diff --git a/ansible/roles/adcs01/tasks/init.yaml b/ansible/roles/adcs01/tasks/init.yaml
index 418bb5d..e329b0c 100644
--- a/ansible/roles/adcs01/tasks/init.yaml
+++ b/ansible/roles/adcs01/tasks/init.yaml
@@ -10,9 +10,14 @@
 - name: upload ADCSTemplate module
   ansible.builtin.copy:
     src: ../../../files/adcs/ADCSTemplate
-    dest: C:\Program Files\WindowsPowerShell\Modules\ADCSTemplate
+    dest: C:\Program Files\WindowsPowerShell\Modules
 
 - name: upload adcs templates
   ansible.builtin.copy:
     src: ../../../files/adcs/templates
     dest: C:\setup
+
+- name: copy mssql installer
+  ansible.builtin.copy:
+    src: files/SQL2019-SSEI-Expr.exe
+    dest: C:\setup\SQL2019-SSEI-Expr.exe
diff --git a/ansible/roles/adcs01/tasks/main.yaml b/ansible/roles/adcs01/tasks/main.yaml
index e3f8923..4e44dc9 100644
--- a/ansible/roles/adcs01/tasks/main.yaml
+++ b/ansible/roles/adcs01/tasks/main.yaml
@@ -33,6 +33,16 @@
 - name: reboot after adcs esc setup
   import_tasks: reboot.yaml
 
+- name: execute setup-mssql.ps1
+  import_tasks: setup_mssql.yaml
+
+- name: reboot after mssql setup
+  import_tasks: reboot.yaml
+
+- name: pause 5 minutes for mssql setup to complete
+  pause:
+    minutes: 5
+
 - name: execute install-software.ps1
   import_tasks: install_software.yaml
 
diff --git a/ansible/roles/adcs01/tasks/setup_mssql.yaml b/ansible/roles/adcs01/tasks/setup_mssql.yaml
new file mode 100644
index 0000000..4ed0a86
--- /dev/null
+++ b/ansible/roles/adcs01/tasks/setup_mssql.yaml
@@ -0,0 +1,8 @@
+- name: execute setup-mssql.ps1
+  ansible.windows.win_powershell:
+    script: C:\scripts\setup-mssql.ps1
+    parameters:
+      DomainName: "{{ main_domain_name }}"
+      IISSvcUsername: svc_iis01
+      SQLSvcUsername: svc_mssql02
+      SvcPassword: "{{ default_win_svc_password }}"
diff --git a/ansible/roles/websql01/tasks/setup_mssql_link.yaml b/ansible/roles/websql01/tasks/setup_mssql_link.yaml
index 1227d62..4320889 100644
--- a/ansible/roles/websql01/tasks/setup_mssql_link.yaml
+++ b/ansible/roles/websql01/tasks/setup_mssql_link.yaml
@@ -2,4 +2,4 @@
   ansible.windows.win_powershell:
     script: C:\scripts\setup-mssql-link.ps1
     parameters:
-      LinkServer: mssql02
+      LinkServer: adcs01
diff --git a/ansible/scripts/setup-adcs-esc.ps1 b/ansible/scripts/setup-adcs-esc.ps1
index 44fc8d5..29848d4 100644
--- a/ansible/scripts/setup-adcs-esc.ps1
+++ b/ansible/scripts/setup-adcs-esc.ps1
@@ -2,7 +2,7 @@ param (
     [string]$DomainName = "contoso.com"
 )
 $scriptName = $MyInvocation.MyCommand.Name
-$logFile = "C:\$scriptName_log.txt"
+$logFile = "C:\Logs\${scriptName}_log.txt"
 Start-Transcript -Path $logFile -Append
 
 Import-Module ADCSTemplate
@@ -17,4 +17,4 @@ Get-ChildItem -Path "C:\setup\templates" -Filter *.json | % {
             -Publish
     }
 }
-Stop-Transcript
-\ No newline at end of file
+Stop-Transcript
diff --git a/ansible/scripts/setup-mssql.ps1 b/ansible/scripts/setup-mssql.ps1
index c37ee42..5b4c1a5 100644
--- a/ansible/scripts/setup-mssql.ps1
+++ b/ansible/scripts/setup-mssql.ps1
@@ -1,8 +1,9 @@
 param
 (
-    [string]$DomainName   = "contoso.com",
-    [string]$SvcUsername  = "svc_mssql02",
-    [string]$SvcPassword  = "Svc1234!"
+    [string]$DomainName      = "contoso.com",
+    [string]$SQLSvcUsername  = "svc_mssql02",
+    [string]$IISSvcUsername  = "svc_iis01",
+    [string]$SvcPassword     = "Svc1234!"
 )
 $scriptName = $MyInvocation.MyCommand.Name
 $logFile = "C:\Logs\${scriptName}_log.txt"
@@ -73,18 +74,18 @@ Restart-Service -Name "MSSQL`$SQLEXPRESS"
 
 try {
     $env:Path += ";C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"
-    SqlCmd -E -Q "CREATE LOGIN [$NetBiosName\$SvcUsername] FROM WINDOWS"
-    SqlCmd -E -Q "SP_ADDSRVROLEMEMBER '$NetBiosName\$SvcUsername', 'SYSADMIN'"
+    SqlCmd -E -Q "CREATE LOGIN [$NetBiosName\$SQLSvcUsername] FROM WINDOWS"
+    SqlCmd -E -Q "SP_ADDSRVROLEMEMBER '$NetBiosName\$SQLSvcUsername', 'SYSADMIN'"
 
     SqlCmd -E -Q "ALTER LOGIN sa ENABLE"
     SqlCmd -E -Q "ALTER LOGIN sa WITH PASSWORD = '$SvcPassword', CHECK_POLICY=OFF"
 
-    SqlCmd -E -Q "CREATE LOGIN [CONTOSO\svc_iis01] FROM WINDOWS;"
-    SqlCmd -E -Q "ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\svc_iis01];"
-    Write-Host "[inf] Added $NetBiosName\$SvcUsername as MSSQL login and sysadmin"
+    SqlCmd -E -Q "CREATE LOGIN [$NetBiosName\$IISSvcUsername] FROM WINDOWS;"
+    SqlCmd -E -Q "ALTER SERVER ROLE sysadmin ADD MEMBER [$NetBiosName\$IISSvcUsername];"
+    Write-Host "[inf] Added $NetBiosName\$SQLSvcUsername as MSSQL login and sysadmin"
     Write-Host "[inf] Enabled SA login"
 } catch {
-    Write-Host "[err] Failed to add $NetBiosName\$SvcUsername as MSSQL login and sysadmin"
+    Write-Host "[err] Failed to add $NetBiosName\$SQLSvcUsername as MSSQL login and sysadmin"
     Write-Host "[err] Failed to enable SA login"
 
 }