diff options
Diffstat (limited to 'ansible/scripts/setup-mssql.ps1')
| -rw-r--r-- | ansible/scripts/setup-mssql.ps1 | 32 |
1 files changed, 18 insertions, 14 deletions
diff --git a/ansible/scripts/setup-mssql.ps1 b/ansible/scripts/setup-mssql.ps1 index 032490f..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_mssql01", - [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" @@ -57,34 +58,37 @@ FTSVCACCOUNT="NT Service\MSSQLFDLauncher" try { Start-Process -FilePath "C:\setup\SQL2019-SSEI-Expr.exe" -ArgumentList "/configurationfile=C:\setup\sql_conf.ini /IACCEPTSQLSERVERLICENSETERMS /MEDIAPATH=C:\setup\media /QUIET /HIDEPROGRESSBAR" -Wait - Write-Host "[INFO] Installed SQL Server Express" + Write-Host "[inf] Installed SQL Server Express" } catch { - Write-Host "[ERR] Failed to install SQL Server Express" + Write-Host "[err] Failed to install SQL Server Express" } try { Set-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" -Name "TcpPort" -Value "1433" -Force - Write-Host "[INFO] Set MSSQL port to 1433" + Write-Host "[inf] Set MSSQL port to 1433" } catch { - Write-Host "[ERR] Failed to set MSSQL port to 1433" + Write-Host "[err] Failed to set MSSQL port to 1433" } 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" - Write-Host "[INFO] Added $NetBiosName\$SvcUsername as MSSQL login and sysadmin" - Write-Host "[INFO] Enabled SA login" + + 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 enable SA login" + Write-Host "[err] Failed to add $NetBiosName\$SQLSvcUsername as MSSQL login and sysadmin" + Write-Host "[err] Failed to enable SA login" } New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow -Stop-Transcript
\ No newline at end of file +Stop-Transcript |