This is the final part of my SQL 2008 R2 Secure and Automate your Installations series. The first two scripts, covered here, and here perform an unattended installation of SQL 2008 R2 and then modify some key SQL parameters. The final script to make all of this work is below. This PowerShell script disables some SQL AdHoc object access, configures connection encryption, moves SQL system databases and log files, then sets the NTFS permissions on the directories.
Again, SQL lockdowns can always have the possibility of breaking your applications. If you merely want to automate your install without lockdown down security, just comment out anything that makes you uncomfortable.
-------
# SQL08R2-Lockdown.ps1
# SQL 2008 R2 Lockdown Script No. 2. Run after
# This script disables Ad Hoc provider access and moves system databases.
# This section disables 'AdHoc Access' to several object types.
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\ADSDSOObject" -name DisallowAdHocAccess -value 1
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\MSDASQL" -name DisallowAdHocAccess -value 1
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\MSIDXS" -name DisallowAdHocAccess -value 1
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\MSOLAP" -name DisallowAdHocAccess -value 1
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\SQLNCLI10" -name DisallowAdHocAccess -value 1
# These keys do not exist so they need to be created prior to setting values.
md "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\SQLOLEDB"
md "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\MSDAOSP"
# Set values
New-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\SQLOLEDB" -Name "DisallowAdHocAccess" -Value 1 -PropertyType "DWord"
New-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\MSDAOSP" -Name "DisallowAdHocAccess" -Value 1 -PropertyType "DWord"
# Stop all SQL services so locks on databases and logs are released.
stop-service SQLserverAgent -force
stop-service MSSQLServer -force
write-host "Pausing for 30 seconds to ensure SQL services stop..."
Start-Sleep -s 30
write-host "Resuming script..."
# Configure connection encryption
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib" -name ForceEncryption -value 1
# Reconfigure SQL startup parameters
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters" -name SQLArg0 -value "-dK:\Microsoft SQL Server\MSSQL\DATA\master.mdf"
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters" -name SQLArg1 -value "-eD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG"
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters" -name SQLArg2 -value "-lL:\Microsoft SQL Server\MSSQL\DATA\Logs\mastlog.ldf"
# Enable large pages, for system with 8GB or more RAM
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters" -name SQLArg3 -value "-T834"
# Move System Databases
move-item "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf" "K:\Microsoft SQL Server\MSSQL\Data"
move-item "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\msdbdata.mdf" "K:\Microsoft SQL Server\MSSQL\Data"
move-item "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf" "K:\Microsoft SQL Server\MSSQL\Data"
move-item "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf" "L:\Microsoft SQL Server\MSSQL\Data\Logs\"
move-item "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\msdblog.ldf" "L:\Microsoft SQL Server\MSSQL\Data\Logs\"
move-item "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf" "L:\Microsoft SQL Server\MSSQL\Data\Logs\"
# Copy NTFS security settings for the directories
# Note!!! Change : to the Pipe symbol
get-ACL "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA" : set-ACL "K:\Microsoft SQL Server\MSSQL\Data\"
# Note!! Change : to the Pipe symbol
get-ACL "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA" : set-ACL "L:\Microsoft SQL Server\MSSQL\Data\Logs\"
# Restart SQL services
start-service MSSQLServer
start-service SQLserverAgent
-------
No comments:
Post a Comment