-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSQL-PS-Extension.ps1
executable file
·144 lines (120 loc) · 5.41 KB
/
SQL-PS-Extension.ps1
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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
#
# SQL_PS_Extension.ps1
#
# Actions:
# - Add the Domain Admins Groups to the SysAdmins Group in SQL Server
# Setting variables
$AdminUser = $args[0]
$adminPassword = $args[1]
$netbiosADDS = $args[2]
$ADDSServer = $args[3]
$FQDN = $args[4]
$SCCMServiceAccountPassword = ConvertTo-SecureString -String $args[5] -AsPlainText -Force
$SQLServiceAccountPassword = ConvertTo-SecureString -String $args[6] -AsPlainText -Force
$sccmHostName = $args[7]
#Capture Script Location (because Extension versions may vary)
$CurrentScriptLocation = (Get-Location).path
#Let the server settle down before config
Start-Sleep -s 180
#Configure logging
function log
{
param([string]$message)
"`n`n$(get-date -f o) $message"
}
#Disable Defender RealTime Scanning
Set-MpPreference -DisableRealtimeMonitoring $true
#Start SQL Server
net start MSSQLSERVER
#Enable CredSSP to allow Multiple HOP Remote PowerShell
log "Configuring WSMAN"
Enable-WSManCredSSP -Role Server -Force
Enable-WSManCredSSP -Role Client -DelegateComputer ("*."+$FQDN) -Force
Enable-PSRemoting -force
Set-Item WSMan:\localhost\Client\TrustedHosts * -Force
#Set policy "Allow delegating fresh credentials with NTLM-only server authentication"
log "Configuring CREDSSP"
$allowed = @('WSMAN/*.'+ $FQDN)
$key = 'hklm:\SOFTWARE\Policies\Microsoft\Windows\CredentialsDelegation'
if (!(Test-Path $key)) {
md $key
}
New-ItemProperty -Path $key -Name AllowFreshCredentialsWhenNTLMOnly -Value 1 -PropertyType Dword -Force
$key = Join-Path $key 'AllowFreshCredentialsWhenNTLMOnly'
if (!(Test-Path $key)) {
md $key
}
$i = 1
$allowed |% {
# Script does not take into account existing entries in this key
New-ItemProperty -Path $key -Name $i -Value $_ -PropertyType String -Force
$i++
}
#Create Credentials
$mycreds = New-Object System.Management.Automation.PSCredential ($AdminUser, (ConvertTo-SecureString -String $adminPassword -AsPlainText -Force))
# This creates the SCCM Service accounts pages 61 through 65 FB
$ScriptBlockServiceAccounts= {
param ($fqdn, $SCCMServiceAccountPassword,$SQLServiceAccountPassword)
function log
{
param([string]$message)
"`n`n$(get-date -f o) $message"
}
#Set UPN for admin account
Get-ADUser ($env:UserName) | Set-ADUser -UserPrincipalName (($env:UserName)+"@"+((Get-ADDomain | select forest).forest))
#Configure the CN for the Service account users
$CN = "CN=Managed Service Accounts,DC=" + $FQDN.Split('.')[0] + ",DC=" + $FQDN.Split(".")[1]
#Create the SCCM Netaccess account
log "Create the SCCM Netaccess account"
$SCCMServiceAccountName = "SCCM Netaccess"
New-ADUser -Name $SCCMServiceAccountName -SamAccountName $SCCMServiceAccountName -UserPrincipalName ($SCCMServiceAccountName+"@"+$FQDN) -Path $CN -AccountPassword $SCCMServiceAccountPassword -Enabled $true -PasswordNeverExpires $true
#Create the SQL Service account
log "Create the SQL Service account"
$SQLServiceAccountName = "SQL.service"
New-ADUser -Name $SQLServiceAccountName -SamAccountName $SQLServiceAccountName -UserPrincipalName ($SQLServiceAccountName+"@"+$FQDN) -Path $CN -AccountPassword $SQLServiceAccountPassword -Enabled $true -PasswordNeverExpires $true
}
$session = New-PSSession -cn ($ADDSServer+"."+$FQDN) -Credential $mycreds -Authentication Credssp
Invoke-Command -Session $session -ScriptBlock $ScriptBlockServiceAccounts -ArgumentList $fqdn, $SCCMServiceAccountPassword, $SQLServiceAccountPassword
Remove-PSSession -VMName ($ADDSServer+"."+$FQDN)
#Wait for creation of service accounts and start SQL
Start-Sleep -s 30
net start MSSQLSERVER
#Set SQL Permissions for admins
log "Set SQL Permissions for admins"
if ($netbiosADDS -ne "contoso")
{
$filePath = $CurrentScriptLocation+'\'+'AddDomainAdminsToSysAdmins.sql'
$tempFilePath = "$env:TEMP\$($filePath | Split-Path -Leaf)"
$find = 'contoso'
$replace = $netbiosADDS
(Get-Content -Path $filePath) -replace $find, $replace | Add-Content -Path $tempFilePath
Remove-Item -Path $filePath
Move-Item -Path $tempFilePath -Destination $filePath
}
if ($sccmHostName -ne "sccm-01")
{
$filePath = $CurrentScriptLocation+'\'+'AddDomainAdminsToSysAdmins.sql'
$tempFilePath = "$env:TEMP\$($filePath | Split-Path -Leaf)"
$find = 'sccm-01'
$replace = $sccmHostName
(Get-Content -Path $filePath) -replace $find, $replace | Add-Content -Path $tempFilePath
Remove-Item -Path $filePath
Move-Item -Path $tempFilePath -Destination $filePath
}
cd "\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn"
& sqlcmd -S ($env:computername) -U $AdminUser -P $adminPassword -i $CurrentScriptLocation'\AddDomainAdminsToSysAdmins.sql'
#Log Change SQL Server Service account
log "Change SQL Server Service account"
$ServiceAccountobject = $netbiosADDS+"\sql.service"
& sc.exe config "MSSQLSERVER" obj= $ServiceAccountobject password= $args[5]
net stop MSSQLSERVER
net start MSSQLSERVER
#Change SQL Server Collation
net stop MSSQLSERVER
CD "\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn"
.\sqlservr -m -T4022 -T3659 -s"SQLEXP2014" -q"SQL_Latin1_General_CP1_CI_AS"
net start MSSQLSERVER
#Open File and Printer Sharing for the SCCM Installation
netsh advfirewall firewall set rule group="File and Printer Sharing" new enable=Yes
#Add the SCCM Computer Account to the Local Admins Group on SQL
Add-LocalGroupMember -Group "Administrators" -Member ($sccmHostName+"$")