SQL 2008 R2: Lockdown Script (Part 4)

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:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERProvidersADSDSOObject” -name DisallowAdHocAccess -value 1

set-itemproperty -path “HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERProvidersMSDASQL” -name DisallowAdHocAccess -value 1

set-itemproperty -path “HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERProvidersMSIDXS” -name DisallowAdHocAccess -value 1

set-itemproperty -path “HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERProvidersMSOLAP” -name DisallowAdHocAccess -value 1

set-itemproperty -path “HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERProvidersSQLNCLI10″ -name DisallowAdHocAccess -value 1

# These keys do not exist so they need to be created prior to setting values.



# Set values

New-ItemPropertyHKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERProvidersSQLOLEDB” -Name “DisallowAdHocAccess” -Value 1 –PropertyTypeDWord

New-ItemPropertyHKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERProvidersMSDAOSP” -Name “DisallowAdHocAccess” -Value 1 –PropertyTypeDWord

# 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:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLServerSuperSocketNetLib” -name ForceEncryption -value 1

# Reconfigure SQL startup parameters

set-itemproperty -path “HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLServerParameters” -name SQLArg0 -value “-dK:Microsoft SQL ServerMSSQLDATAmaster.mdf

set-itemproperty -path “HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLServerParameters” -name SQLArg1 -value “-eD:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLogERRORLOG

set-itemproperty -path “HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLServerParameters” -name SQLArg2 -value “-lL:Microsoft SQL ServerMSSQLDATALogsmastlog.ldf

# Enable large pages, for system with 8GB or more RAM

set-itemproperty -path “HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLServerParameters” -name SQLArg3 -value “-T834”

# Move System Databases
move-item “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmodel.mdf” “K:Microsoft SQL ServerMSSQLData”

move-item “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmsdbdata.mdf” “K:Microsoft SQL ServerMSSQLData”

move-item “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmaster.mdf” “K:Microsoft SQL ServerMSSQLData”

move-item “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmodellog.ldf” “L:Microsoft SQL ServerMSSQLDataLogs”

move-item “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmsdblog.ldf” “L:Microsoft SQL ServerMSSQLDataLogs”

move-item “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmastlog.ldf” “L:Microsoft SQL ServerMSSQLDataLogs”

# Copy NTFS security settings for the directories
# Note!!! Change : to the Pipe symbol

get-ACL “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA” : set-ACL “K:Microsoft SQL ServerMSSQLData”

# Note!! Change : to the Pipe symbol

get-ACL “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA” : set-ACL “L:Microsoft SQL ServerMSSQLDataLogs”

# Restart SQL services
start-service MSSQLServer
start-service SQLserverAgent

Print Friendly, PDF & Email

Related Posts

Notify of
Inline Feedbacks
View all comments