working iis + mssql setup

13 files changed, 98 insertions, 76 deletions

diff --git a/ansible/group_vars/all/main.yaml b/ansible/group_vars/all/main.yaml
index 2a2246c..1969a09 100644
--- a/ansible/group_vars/all/main.yaml
+++ b/ansible/group_vars/all/main.yaml
@@ -34,8 +34,8 @@ tree_dc02_hostname              : "{{ lookup('ansible.builtin.env', 'tree_dc02_h
 tree_dc02_vmid                  : "{{ lookup('ansible.builtin.env', 'tree_dc02_vmid') }}" 
 child_dc03_hostname             : "{{ lookup('ansible.builtin.env', 'child_dc03_hostname') }}" 
 child_dc03_vmid                 : "{{ lookup('ansible.builtin.env', 'child_dc03_vmid') }}" 
-main_mssql01_hostname           : "{{ lookup('ansible.builtin.env', 'main_mssql01_hostname') }}"
-main_mssql01_vmid               : "{{ lookup('ansible.builtin.env', 'main_mssql01_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_web01_hostname             : "{{ lookup('ansible.builtin.env', 'main_web01_hostname') }}"
@@ -50,7 +50,7 @@ kali_attackbox_vmid             : "{{ lookup('ansible.builtin.env', 'kali_attack
 main_dc01_ip_address            : "{{ lookup('ansible.builtin.env', 'main_dc01_ip_address') }}"
 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_mssql01_ip_address         : "{{ lookup('ansible.builtin.env', 'main_mssql01_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_web01_ip_address           : "{{ lookup('ansible.builtin.env', 'main_web01_ip_address') }}"
 main_adcs01_ip_address          : "{{ lookup('ansible.builtin.env', 'main_adcs01_ip_address') }}"
diff --git a/ansible/main.yaml b/ansible/main.yaml
index 4e84f2e..340c51a 100644
--- a/ansible/main.yaml
+++ b/ansible/main.yaml
@@ -88,27 +88,27 @@
         ansible_winrm_server_cert_validation: ignore
       changed_when: false
 
-    - name: "deploy {{ main_mssql01_hostname }}.{{ main_domain_name }} vm on {{ proxmox_hostname }}"
+    - name: "deploy {{ main_websql01_hostname }}.{{ main_domain_name }} vm on {{ proxmox_hostname }}"
       include_role:
         name: proxmox_vm
       vars:
         os_type                             : "windows"
         template                            : "{{ windows_server_template_name }}"
         id                                  : "{{ windows_server_template_id }}"
-        vm                                  : "{{ main_mssql01_hostname }}.{{ main_domain_name }}"
-        newid                               : "{{ main_mssql01_vmid }}"
-        vmid                                : "{{ main_mssql01_vmid }}"
-        ip                                  : "{{ main_mssql01_ip_address }}"
+        vm                                  : "{{ main_websql01_hostname }}.{{ main_domain_name }}"
+        newid                               : "{{ main_websql01_vmid }}"
+        vmid                                : "{{ main_websql01_vmid }}"
+        ip                                  : "{{ main_websql01_ip_address }}"
         gateway                             : "{{ network_gateway }}"
         dns                                 : "{{ main_dc01_ip_address }}"
-        hostname                            : "{{ main_mssql01_hostname }}"
+        hostname                            : "{{ main_websql01_hostname }}"
         domain                              : "{{ main_domain_name }}"
-        fqdn                                : "{{ main_mssql01_hostname }}.{{ main_domain_name }}"
+        fqdn                                : "{{ main_websql01_hostname }}.{{ main_domain_name }}"
     
-    - name: "add {{ main_mssql01_hostname }}.{{ main_domain_name }} to in-memory inventory"
+    - name: "add {{ main_websql01_hostname }}.{{ main_domain_name }} to in-memory inventory"
       add_host:
-        name                                : "{{ main_mssql01_hostname }}.{{ main_domain_name }}"
-        ansible_host                        : "{{ main_mssql01_ip_address }}"
+        name                                : "{{ main_websql01_hostname }}.{{ main_domain_name }}"
+        ansible_host                        : "{{ main_websql01_ip_address }}"
         ansible_connection                  : "{{ win_connector }}"
         ansible_user                        : "{{ default_win_username }}"
         ansible_password                    : "{{ default_win_password }}"
@@ -136,7 +136,7 @@
         name: adcs01
       when: inventory_hostname == main_adcs01_hostname + '.' + main_domain_name
 
-    - name: "configure {{ main_mssql01_hostname }}.{{ main_domain_name }}"
+    - name: "configure {{ main_websql01_hostname }}.{{ main_domain_name }}"
       include_role:
-        name: mssql01
-      when: inventory_hostname == main_mssql01_hostname + '.' + main_domain_name
+        name: websql01
+      when: inventory_hostname == main_websql01_hostname + '.' + main_domain_name
diff --git a/ansible/roles/mssql01/tasks/set_hostname.yaml b/ansible/roles/mssql01/tasks/set_hostname.yaml
deleted file mode 100644
index de974a4..0000000
--- a/ansible/roles/mssql01/tasks/set_hostname.yaml
+++ /dev/null
@@ -1,2 +0,0 @@
-- name: set hostname
-  win_shell: Rename-Computer -NewName "{{ main_mssql01_hostname }}" -Force
diff --git a/ansible/roles/proxmox_vm/tasks/set_network.yaml b/ansible/roles/proxmox_vm/tasks/set_network.yaml
index c75aa0f..da809d5 100644
--- a/ansible/roles/proxmox_vm/tasks/set_network.yaml
+++ b/ansible/roles/proxmox_vm/tasks/set_network.yaml
@@ -1,4 +1,4 @@
-- name: "{{ fqdn }} : set up static ip address on windows"
+- name: "{{ fqdn }} : (windows) set up static ip address on"
   win_shell: |
     Start-Transcript -Path C:\set_domain_network_log.txt -Append
     Get-NetIpAddress -InterfaceAlias 'Ethernet' | Remove-NetIPAddress -Confirm:$false
@@ -18,36 +18,36 @@
     ansible_winrm_transport: basic
     ansible_winrm_server_cert_validation: ignore
 
-- name: "{{ fqdn }} : configure network for linux"
+- name: "{{ fqdn }} : (linux) configure network"
   block:
-    - name: "{{ fqdn }} : get default interface"
+    - name: "{{ fqdn }} : (linux) get default interface"
       ansible.builtin.shell: ip route get 8.8.8.8 | sed -n 's/.*dev \([^\ ]*\).*/\1/p'
       register: interface_result
 
-    - name: "{{ fqdn }} : set default interface variable"
+    - name: "{{ fqdn }} : (linux) set default interface variable"
       ansible.builtin.set_fact:
         linux_interface_name: "{{ interface_result.stdout }}"
         netplan_ip_address: "{{ ip }}"
 
-    - name: "{{ fqdn }} : find netplan configs in /etc/netplan"
+    - name: "{{ fqdn }} : (linux) find netplan configs in /etc/netplan"
       ansible.builtin.find:
         paths: /etc/netplan
         recurse: yes
       register: yaml_configs
 
-    - name: "{{ fqdn }} : remove all netplan configs in /etc/netplan"
+    - name: "{{ fqdn }} : (linux) remove all netplan configs in /etc/netplan"
       ansible.builtin.file:
         path: "{{ item.path }}"
         state: absent
       loop: "{{ yaml_configs.files }}"
 
-    - name: "{{ fqdn }} : set netplan static ip address"
+    - name: "{{ fqdn }} : (linux) set netplan static ip address"
       ansible.builtin.template:
         src: static_ip_netplan.yaml.j2
         dest: /etc/netplan/01-netcfg.yaml
         mode: '0644'
 
-    - name: "{{ fqdn }} : apply netplan configuration"
+    - name: "{{ fqdn }} : (linux) apply netplan configuration"
       ansible.builtin.command: netplan apply
       async: 15
       poll: 0
diff --git a/ansible/roles/mssql01/tasks/cleanup.yaml b/ansible/roles/websql01/tasks/cleanup.yaml
index 0e59407..0e59407 100644
--- a/ansible/roles/mssql01/tasks/cleanup.yaml
+++ b/ansible/roles/websql01/tasks/cleanup.yaml
diff --git a/ansible/roles/mssql01/tasks/init.yaml b/ansible/roles/websql01/tasks/init.yaml
index a75d6cc..a75d6cc 100644
--- a/ansible/roles/mssql01/tasks/init.yaml
+++ b/ansible/roles/websql01/tasks/init.yaml
diff --git a/ansible/roles/mssql01/tasks/install_software.yaml b/ansible/roles/websql01/tasks/install_software.yaml
index a5018a8..a5018a8 100644
--- a/ansible/roles/mssql01/tasks/install_software.yaml
+++ b/ansible/roles/websql01/tasks/install_software.yaml
diff --git a/ansible/roles/mssql01/tasks/join_domain.yaml b/ansible/roles/websql01/tasks/join_domain.yaml
index 6736ba2..6736ba2 100644
--- a/ansible/roles/mssql01/tasks/join_domain.yaml
+++ b/ansible/roles/websql01/tasks/join_domain.yaml
diff --git a/ansible/roles/mssql01/tasks/main.yaml b/ansible/roles/websql01/tasks/main.yaml
index 3822369..f176701 100644
--- a/ansible/roles/mssql01/tasks/main.yaml
+++ b/ansible/roles/websql01/tasks/main.yaml
@@ -17,8 +17,8 @@
 - name: join domain and reboot
   import_tasks: join_domain.yaml
 
-- name: execute setup-mssql.ps1
-  import_tasks: setup_mssql.yaml
+- name: execute setup-websql.ps1
+  import_tasks: setup_websql.yaml
 
 - name: reboot after mssql setup
   import_tasks: reboot.yaml
diff --git a/ansible/roles/mssql01/tasks/reboot.yaml b/ansible/roles/websql01/tasks/reboot.yaml
index a7266d0..a7266d0 100644
--- a/ansible/roles/mssql01/tasks/reboot.yaml
+++ b/ansible/roles/websql01/tasks/reboot.yaml
diff --git a/ansible/roles/websql01/tasks/set_hostname.yaml b/ansible/roles/websql01/tasks/set_hostname.yaml
new file mode 100644
index 0000000..7c53a16
--- /dev/null
+++ b/ansible/roles/websql01/tasks/set_hostname.yaml
@@ -0,0 +1,2 @@
+- name: set hostname
+  win_shell: Rename-Computer -NewName "{{ main_websql01_hostname }}" -Force
diff --git a/ansible/roles/mssql01/tasks/setup_mssql.yaml b/ansible/roles/websql01/tasks/setup_websql.yaml
index 4602242..ea527b6 100644
--- a/ansible/roles/mssql01/tasks/setup_mssql.yaml
+++ b/ansible/roles/websql01/tasks/setup_websql.yaml
@@ -1,7 +1,7 @@
-- name: setup mssql
+- name: setup websql
   ansible.windows.win_powershell:
-    script: C:\scripts\setup-mssql.ps1
+    script: C:\scripts\setup-websql.ps1
     parameters:
       DomainName: "{{ main_domain_name }}"
-      SvcUsername: svc_mssql01
+      SvcUsername: svc_mssql02
       SvcPassword: "{{ default_win_svc_password }}"
diff --git a/ansible/scripts/setup-websql.ps1 b/ansible/scripts/setup-websql.ps1
index 9f5db5c..7865091 100644
--- a/ansible/scripts/setup-websql.ps1
+++ b/ansible/scripts/setup-websql.ps1
@@ -1,6 +1,7 @@
 param (
     [string]$DomainName  = "contoso.com",
-    [string]$SvcUsername = "svc_websql01",
+    #[string]$SvcUsername = "svc_websql01",
+    [string]$SvcUsername = "svc_mssql02",
     [string]$SvcPassword = "Svc1234!"
 )
 
@@ -13,7 +14,69 @@ $wwwroot3 = "C:\inetpub\wwwroot3"
 
 Start-Transcript -Path $logFile -Append
 
-# --- IIS Setup ---
+try {
+    New-Item -Path "C:\setup\media" -ItemType "Directory" -Force
+    @"
+;SQL Server Configuration File
+[OPTIONS]
+IACCEPTSQLSERVERLICENSETERMS="True"
+ACTION="Install"
+ENU="True"
+QUIET="True"
+QUIETSIMPLE="False"
+UpdateEnabled="False"
+ERRORREPORTING="False"
+USEMICROSOFTUPDATE="False"
+FEATURES=SQLENGINE,FULLTEXT
+UpdateSource="MU"
+HELP="False"
+INDICATEPROGRESS="False"
+X86="False"
+INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
+INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
+INSTANCENAME="SQLEXPRESS"
+SQMREPORTING="False"
+INSTANCEID="SQLEXPRESS"
+RSINSTALLMODE="DefaultNativeMode"
+INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
+AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
+AGTSVCSTARTUPTYPE="Automatic"
+COMMFABRICPORT="0"
+COMMFABRICNETWORKLEVEL="0"
+COMMFABRICENCRYPTION="0"
+MATRIXCMBRICKCOMMPORT="0"
+SQLSVCSTARTUPTYPE="Automatic"
+FILESTREAMLEVEL="0"
+ENABLERANU="False"
+SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
+SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
+SAPWD="$SvcPassword"
+SQLSYSADMINACCOUNTS="BUILTIN\Administrators"
+ADDCURRENTUSERASSQLADMIN="True"
+TCPENABLED="1"
+NPENABLED="0"
+BROWSERSVCSTARTUPTYPE="Disabled"
+RSSVCSTARTUPTYPE="manual"
+FTSVCACCOUNT="NT Service\MSSQLFDLauncher"
+"@ | Out-File "C:\setup\sql_conf.ini"
+
+    Start-Process -FilePath "C:\setup\SQL2019-SSEI-Expr.exe" -ArgumentList "/configurationfile=C:\setup\sql_conf.ini /IACCEPTSQLSERVERLICENSETERMS /MEDIAPATH=C:\setup\media /QUIET /HIDEPROGRESSBAR" -Wait
+    Set-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" -Name "TcpPort" -Value "1433" -Force
+    Restart-Service -Name "MSSQL`$SQLEXPRESS"
+    New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
+
+    $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 "ALTER LOGIN sa ENABLE"
+    SqlCmd -E -Q "ALTER LOGIN sa WITH PASSWORD = '$SvcPassword', CHECK_POLICY=OFF"
+    Write-Host "[INFO] Added $NetBiosName\$SvcUsername as MSSQL login and sysadmin"
+    Write-Host "[INFO] Enabled SA login"
+} catch {
+    Write-Host "[ERR] SQL Server setup failed"
+}
+
 try {
     Install-WindowsFeature -Name Web-Server -IncludeManagementTools
     Install-WindowsFeature -Name Web-Asp-Net45
@@ -22,7 +85,6 @@ try {
     Write-Host "[ERR] Failed to install IIS and ASP.NET"
 }
 
-# Upload form content
 @"
 using System;
 using System.IO;
@@ -83,7 +145,6 @@ public partial class UploadPage : Page
 </configuration>
 "@ | Out-File "$wwwroot1\Web.config" -Force
 
-# Default site - port 80
 try {
     New-WebSite -Name "MyASPXSite" -Port 80 -PhysicalPath $wwwroot1 -ApplicationPool "DefaultAppPool"
     Set-ItemProperty "IIS:\AppPools\DefaultAppPool" -Name processModel -Value @{userName="$SvcUsername";password="$SvcPassword";identityType=3}
@@ -94,7 +155,6 @@ try {
     Write-Host "[ERR] Failed to create site 1"
 }
 
-# ACLs for wwwroot1
 try {
     $svcRule = New-Object System.Security.AccessControl.FileSystemAccessRule("$DomainName\$SvcUsername", "Modify", "ContainerInherit,ObjectInherit", "None", "Allow")
     $acl = Get-Acl $wwwroot1
@@ -105,7 +165,6 @@ try {
     Write-Host "[ERR] Failed to set ACL for $wwwroot1"
 }
 
-# Second site - port 8080
 try {
     Copy-Item $wwwroot1 -Destination $wwwroot2 -Recurse -Force
     New-WebAppPool -Name "DefaultAppPool2"
@@ -122,42 +181,6 @@ try {
     Write-Host "[ERR] Failed to create site 2"
 }
 
-# SQL Server Express setup
-try {
-    New-Item -Path "C:\setup\media" -ItemType "Directory" -Force
-    @"
-;SQL Server Configuration File
-[OPTIONS]
-IACCEPTSQLSERVERLICENSETERMS="True"
-ACTION="Install"
-ENU="True"
-QUIET="True"
-FEATURES=SQLENGINE,FULLTEXT
-INSTANCENAME="SQLEXPRESS"
-SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
-SQLSYSADMINACCOUNTS="BUILTIN\Administrators"
-ADDCURRENTUSERASSQLADMIN="True"
-TCPENABLED="1"
-NPENABLED="0"
-SAPWD="$SvcPassword"
-"@ | Out-File "C:\setup\sql_conf.ini"
-
-    Start-Process -FilePath "C:\setup\SQL2019-SSEI-Expr.exe" -ArgumentList "/configurationfile=C:\setup\sql_conf.ini /IACCEPTSQLSERVERLICENSETERMS /MEDIAPATH=C:\setup\media /QUIET" -Wait
-    Set-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" -Name "TcpPort" -Value "1433"
-    Restart-Service -Name "MSSQL`$SQLEXPRESS"
-    New-NetFirewallRule -DisplayName "SQLServer 1433" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
-
-    $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 "ALTER LOGIN sa ENABLE"
-    SqlCmd -E -Q "ALTER LOGIN sa WITH PASSWORD = '$SvcPassword', CHECK_POLICY=OFF"
-    Write-Host "[INFO] SQL Server installed/configured"
-} catch {
-    Write-Host "[ERR] SQL Server setup failed"
-}
-
-# Third site - port 9090 with upload + SQL query page
 try {
     Copy-Item $wwwroot1 -Destination $wwwroot3 -Recurse -Force
     New-WebAppPool -Name "SqlQueryAppPool"
@@ -165,7 +188,6 @@ try {
     Set-ItemProperty "IIS:\AppPools\SqlQueryAppPool" -Name processModel -Value @{userName="$SvcUsername";password="$SvcPassword";identityType=3}
     New-NetFirewallRule -DisplayName "HTTP (9090)" -Direction Inbound -Protocol TCP -LocalPort 9090 -Action Allow
 
-    # SQL Query Page
     @"
 <%@ Page Language="C#" Debug="true" %>
 <%@ Import Namespace="System.Data" %>
@@ -191,7 +213,7 @@ try {
                     litResults.Text = "<table border='1'><tr>";
                     foreach (DataColumn col in dt.Columns)
                     {
-                        litResults.Text += $"<th>{col.ColumnName}</th>";
+                        litResults.Text += string.Format("<th>{0}</th>", col.ColumnName);
                     }
                     litResults.Text += "</tr>";
 
@@ -200,7 +222,7 @@ try {
                         litResults.Text += "<tr>";
                         foreach (var item in row.ItemArray)
                         {
-                            litResults.Text += $"<td>{item}</td>";
+                            litResults.Text += string.Format("<td>{0}</td>", item);
                         }
                         litResults.Text += "</tr>";
                     }