revereted to old svc users, running websql with svc_mssql and svc_iis

4 files changed, 26 insertions, 29 deletions

diff --git a/ansible/scripts/populate-ad.ps1 b/ansible/scripts/populate-ad.ps1
index 3d8917a..17801e0 100644
--- a/ansible/scripts/populate-ad.ps1
+++ b/ansible/scripts/populate-ad.ps1
@@ -125,7 +125,6 @@ Write-Host "[inf] Created computers: $($created_computers -Join ', ')"
 $svc_users = @{
     "svc_mssql01"           = @{"type" = "spn"; "value" = "MSSQLSVC"}
     "svc_mssql02"           = @{"type" = "spn"; "value" = "MSSQLSVC"}
-    "svc_websql01"          = @{"type" = "spn"; "value" = @("MSSQLSVC", "HTTP")}
     "svc_cifs01"            = @{"type" = "spn"; "value" = "CIFS"}
     "svc_cifs02"            = @{"type" = "spn"; "value" = "CIFS"}
     "svc_iis01"             = @{"type" = "spn"; "value" = "HTTP"}
@@ -162,21 +161,12 @@ ForEach ($user in $svc_users.keys) {
                     -Enabled $true `
                     -PasswordNeverExpires $true `
                     -PassThru
+                Set-ADUser -Identity "$u" -ServicePrincipalNames @{Add="$value/$comp"}
+                Set-ADObject $u -Description "SPN on $value/$comp"
 
-                $spns = @()
-                if ($value -is [string]) {
-                    $spns += "$value/$comp"
-                } else {
-                    foreach ($v in $value) {
-                        $spns += "$v/$comp"
-                    }
-                }
-
-                Set-ADUser -Identity "$u" -ServicePrincipalNames @{Add=$spns}
-                Set-ADObject $u -Description ("SPNs: " + ($spns -join ", "))
-                $created_svc_users += "$user ($($spns -join ', '))"
+                $created_svc_users += "$user ($value/$comp)"
             } catch {
-                Write-Host "[err] Failed to create SPNs for $user"
+                Write-Host "[err] Failed to create $value/$comp for $user"
             }
         }
         "group" {
diff --git a/ansible/scripts/setup-mssql-link.ps1 b/ansible/scripts/setup-mssql-link.ps1
index db1e76c..614bd67 100644
--- a/ansible/scripts/setup-mssql-link.ps1
+++ b/ansible/scripts/setup-mssql-link.ps1
@@ -1,6 +1,6 @@
 param
 (
-    [string]$LinkServer = "mssql01"
+    [string]$LinkServer = "websql01"
 )
 $scriptName = $MyInvocation.MyCommand.Name
 $logFile = "C:\Logs\${scriptName}_log.txt"
@@ -15,4 +15,4 @@ try {
 } catch {
     Write-Host "[err] Failed to link $LinkServer to mssql02"
 }
-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 0be1575..f7bcf3b 100644
--- a/ansible/scripts/setup-mssql.ps1
+++ b/ansible/scripts/setup-mssql.ps1
@@ -1,7 +1,7 @@
 param
 (
     [string]$DomainName   = "contoso.com",
-    [string]$SvcUsername  = "svc_mssql01",
+    [string]$SvcUsername  = "svc_mssql02",
     [string]$SvcPassword  = "Svc1234!"
 )
 $scriptName = $MyInvocation.MyCommand.Name
@@ -87,4 +87,4 @@ try {
 }
 
 New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
-Stop-Transcript
\ No newline at end of file
+Stop-Transcript
diff --git a/ansible/scripts/setup-websql.ps1 b/ansible/scripts/setup-websql.ps1
index 7881291..77377c3 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]$IISSvcUsername = "svc_iis01",
+    [string]$SQLSvcUsername = "svc_mssql01",
     [string]$SvcPassword = "Svc1234!"
 )
 
@@ -65,12 +66,12 @@ FTSVCACCOUNT="NT Service\MSSQLFDLauncher"
     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 "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"
-    Write-Host "[inf] Added $NetBiosName\$SvcUsername as MSSQL login and sysadmin"
+    SqlCmd -E -Q "ALTER LOGIN sa WITH PASSWORD = '$SQLSvcPassword', CHECK_POLICY=OFF"
+    Write-Host "[inf] Added $NetBiosName\$SQLSvcUsername as MSSQL login and sysadmin"
     Write-Host "[inf] Enabled SA login"
 } catch {
     Write-Host "[err] SQL Server setup failed"
@@ -146,7 +147,7 @@ public partial class UploadPage : Page
 
 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}
+    Set-ItemProperty "IIS:\AppPools\DefaultAppPool" -Name processModel -Value @{userName="$IISSvcUsername";password="$SvcPassword";identityType=3}
     New-NetFirewallRule -DisplayName "HTTP (80)" -Direction Inbound -Protocol TCP -LocalPort 80 -Action Allow
     Restart-WebAppPool -Name "DefaultAppPool"
     Write-Host "[inf] Site 1 created on port 80"
@@ -155,9 +156,9 @@ try {
 }
 
 try {
-    $svcRule = New-Object System.Security.AccessControl.FileSystemAccessRule("$DomainName\$SvcUsername", "Modify", "ContainerInherit,ObjectInherit", "None", "Allow")
+    $svcAcl = New-Object System.Security.AccessControl.FileSystemAccessRule("$DomainName\$IISSvcUsername", "Modify", "ContainerInherit,ObjectInherit", "None", "Allow")
     $acl = Get-Acl $wwwroot1
-    $acl.SetAccessRule($svcRule)
+    $acl.SetAccessRule($svcAcl)
     Set-Acl -Path $wwwroot1 -AclObject $acl
     Write-Host "[inf] ACL set for $wwwroot1"
 } catch {
@@ -170,9 +171,9 @@ try {
     New-WebSite -Name "MyASPXSite2" -Port 8080 -PhysicalPath $wwwroot2 -ApplicationPool "DefaultAppPool2"
     Set-ItemProperty "IIS:\AppPools\DefaultAppPool2" -Name processModel -Value @{identityType=2}
     New-NetFirewallRule -DisplayName "HTTP (8080)" -Direction Inbound -Protocol TCP -LocalPort 8080 -Action Allow
+    $svcAcl = New-Object System.Security.AccessControl.FileSystemAccessRule("IIS_IUSRS", "Modify", "ContainerInherit,ObjectInherit", "None", "Allow")
     $acl = Get-Acl $wwwroot2
-    $rule = New-Object System.Security.AccessControl.FileSystemAccessRule("IIS_IUSRS", "Modify", "ContainerInherit,ObjectInherit", "None", "Allow")
-    $acl.SetAccessRule($rule)
+    $acl.SetAccessRule($svcAcl)
     Set-Acl -Path $wwwroot2 -AclObject $acl
     Restart-WebAppPool -Name "DefaultAppPool2"
     Write-Host "[inf] Site 2 created on port 8080"
@@ -184,7 +185,7 @@ try {
     Copy-Item $wwwroot1 -Destination $wwwroot3 -Recurse -Force
     New-WebAppPool -Name "SqlQueryAppPool"
     New-WebSite -Name "SqlQuerySite" -Port 9090 -PhysicalPath $wwwroot3 -ApplicationPool "SqlQueryAppPool"
-    Set-ItemProperty "IIS:\AppPools\SqlQueryAppPool" -Name processModel -Value @{userName="$SvcUsername";password="$SvcPassword";identityType=3}
+    Set-ItemProperty "IIS:\AppPools\SqlQueryAppPool" -Name processModel -Value @{userName="$IISSvcUsername";password="$SvcPassword";identityType=3}
     New-NetFirewallRule -DisplayName "HTTP (9090)" -Direction Inbound -Protocol TCP -LocalPort 9090 -Action Allow
 
     @"
@@ -248,7 +249,13 @@ try {
 </html>
 "@ | Out-File "$wwwroot3\sqlquery.aspx" -Force
 
+
+    $svcAcl = New-Object System.Security.AccessControl.FileSystemAccessRule("$DomainName\$IISSvcUsername", "Modify", "ContainerInherit,ObjectInherit", "None", "Allow")
+    $acl = Get-Acl $wwwroot3
+    $acl.SetAccessRule($svcAcl)
+    Set-Acl -Path $wwwroot3 -AclObject $acl
     Restart-WebAppPool -Name "SqlQueryAppPool"
+
     Write-Host "[inf] Site 3 created on port 9090 with SQL query page"
 } catch {
     Write-Host "[err] Failed to create SQL query site"