Last active
November 25, 2015 20:38
-
-
Save PCfromDC/860261a627635ea596ee to your computer and use it in GitHub Desktop.
Ignite 2015 session Using Desired State Configuration to Deploy SQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $nodeName = "SQL03" | |
| Configuration SQL2014 { | |
| param ($nodeName) | |
| Node $nodeName { | |
| #region Set Parameters | |
| $isoName = "en_sql_server_2014_enterprise_edition_x64_dvd_3932700.iso" | |
| $isoLocation = "\\dsc02\DevOps\Microsoft\SQL Server 2014\iso\" + $isoName | |
| $sqlScripts = "\\dsc02\DevOps\Microsoft\SQL Server 2014\scripts" | |
| $farmConfig = "\\dsc02\DevOps\Microsoft\SQL Server 2014\config" | |
| $sxsPath = "\\dsc02\DevOps\Microsoft\Windows Server 2012R2\sources\sxs" | |
| $dscPath = "C:\temp" | |
| #endregion | |
| #region Install Features... | |
| #Install .Net Framework 3.5 | |
| WindowsFeature NetFramework35Core { | |
| Name = "NET-Framework-Core" | |
| Ensure = "Present" | |
| Source = $sxsPath | |
| } | |
| #Install .Net Framework 4.5 | |
| WindowsFeature NetFramework45Core { | |
| Name = "NET-Framework-45-Core" | |
| Ensure = "Present" | |
| Source = $sxsPath | |
| } | |
| #endregion | |
| #region Move Files from network location to local machine | |
| # Move Configuration Files | |
| File moveConfigFiles { | |
| SourcePath = $farmConfig | |
| DestinationPath = "$dscPath\config" | |
| Type= "Directory" | |
| Recurse = $true | |
| Ensure = "Present" | |
| } | |
| # Move SQL ISO File | |
| File moveSQLISO { | |
| SourcePath = $isoLocation | |
| DestinationPath = "$dscPath\iso\$isoName" | |
| Type = "File" | |
| Ensure = "Present" | |
| } | |
| # Move Scripts | |
| File moveScripts { | |
| SourcePath = $sqlScripts | |
| DestinationPath = "$dscPath\scripts" | |
| Type= "Directory" | |
| Recurse = $true | |
| Ensure = "Present" | |
| } | |
| #endregion | |
| #region Prepare Drives | |
| Script prepareDrives { | |
| # Are Drives "OffLine" | |
| GetScript = { | |
| $offline = (Get-Disk | ? isOffLine).Count | |
| @{Result = "Disks Provisioned"} | |
| } | |
| # Set- Remove SB 1.0 | |
| SetScript = { | |
| Write-Verbose("Provisioning and formatting disks...") | |
| $driveLabels = @("SQL Data","SQL Logs","SQL Temp Data","SQL Temp Logs","SQL Backups") | |
| Get-Disk | Where partitionstyle -eq 'raw' | Initialize-Disk -PartitionStyle MBR -PassThru | |
| Get-Disk | ? IsOffline | Set-Disk -IsOffline:$false | |
| Get-Disk | ? IsReadOnly | Set-Disk -IsReadOnly:$false | |
| $diskNumbers = Get-Disk | where {$_.Number -gt 0} | Select "Number" | Sort-Object Number | |
| $counter = 0 | |
| foreach ($diskNumber in $diskNumbers){ | |
| $number = $diskNumber.Number.ToString() | |
| New-Partition -DiskNumber $number -UseMaximumSize -AssignDriveLetter | Set-Partition -IsActive $true | |
| Get-Partition -DiskNumber $number | Format-Volume -FileSystem NTFS -NewFileSystemLabel $driveLabels[$counter] -AllocationUnitSize 65536 -Confirm:$false | |
| $counter ++ | |
| } | |
| Write-Verbose("Disks Provisioned and Formatted...") | |
| } | |
| # Test- Is SB 1.1 installed and running | |
| TestScript = { | |
| if ((Get-Disk | ? isOffLine).Count -gt 0) { | |
| Write-Verbose("Drives not online...") | |
| $res = $false | |
| } | |
| else { | |
| Write-Verbose("All drives are online...") | |
| $res = $true | |
| } | |
| $res | |
| } | |
| } | |
| #endregion | |
| #region Install SQL | |
| Script installSQL { | |
| # Get- Is SQL installed on server? | |
| GetScript = { | |
| $sqlInstances = gwmi win32_service -ComputerName localhost | ? {$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe" } | % { $_.Caption } | |
| @{Result="SQL Instances: $($sqlInstances.count)"} | |
| } | |
| # Set- Install SQL Server 2012 | |
| SetScript = { | |
| [Reflection.Assembly]::LoadWithPartialName("System.Web") | |
| function password { | |
| #Set up random number generator | |
| $newPassword = $null | |
| $rand = New-Object System.Random | |
| #Generate a new 18 character $newPassword | |
| 1..18 | ForEach { $newPassword = $newPassword + [char]$rand.next(40,123) } | |
| return $newPassword | |
| } | |
| $isoName = "en_sql_server_2014_enterprise_edition_x64_dvd_3932700.iso" | |
| $isoLocation = "C:\temp\iso\" + $isoName | |
| $drives = @(Get-Volume | Where-Object {($_.FileSystem -eq "CDFS")}).DriveLetter | |
| if ($drives.Count -gt 0) { | |
| foreach ($drive in $drives) { | |
| Dismount-DiskImage -ImagePath $isoLocation | |
| } | |
| } | |
| Mount-DiskImage -ImagePath $isoLocation | |
| $driveLetter = (Get-Volume | Where-Object {($_.FileSystem -eq "CDFS")}).DriveLetter + ":" | |
| if ($driveLetter -eq ":") { | |
| $driveLetter = (Mount-DiskImage -ImagePath $isoLocation -PassThru | Get-Volume).DriveLetter + ":" | |
| } | |
| # Prep SA Password | |
| $file = "C:\temp\config\spAccounts.csv" | |
| $users = Import-Csv $file | |
| # $SAPWD = password | |
| $SAPWD = [System.Web.Security.Membership]::GeneratePassword(16,4) | |
| $user = $users | Where-Object {($_.CN -like "SQL SA")} | |
| $user.Password = $SAPWD | |
| # Output Table | |
| $users | Export-CSV $file -Force | |
| $setup = $driveLetter + "\setup.exe" | |
| $domain = $env:USERDOMAIN | |
| $AGTSVCACCOUNT = $domain + "\" + ($users | Where-Object {($_.CN -like "SharePoint SQL SA")}).sAMAccountName # SQL Agent Account | |
| $AGTSVCPASSWORD = ($users | Where-Object {($_.CN -like "SharePoint SQL SA")}).Password # SQL Agent Password | |
| $SQLSVCACCOUNT = $domain + "\" + ($users | Where-Object {($_.CN -like "SharePoint SQL SA")}).sAMAccountName # SQL Service Account | |
| $SQLSVCPASSWORD = ($users | Where-Object {($_.CN -like "SharePoint SQL SA")}).Password # SQL Service Password | |
| $spAdmin = $domain + "\" + ($users | Where-Object {($_.CN -like "SharePoint Admin")}).sAMAccountName # SQL Service Account | |
| $SQLSYSADMINACCOUNTS = @($SQLSVCACCOUNT,$spAdmin) | |
| $SQLUSERDBDIR = "D:\SQL\Data" # SQL Data File location | |
| $SQLUSERDBLOGDIR = "E:\SQL\Logs" # SQL Log File location | |
| $SQLTEMPDBDIR = "F:\SQL\TempData" # SQL Temp Data location (should be on fastest drives) | |
| $SQLTEMPDBLOGDIR = "G:\SQL\TempLogs" # SQL Temp Log location (should be on fastest drives) | |
| $SQLBACKUPDIR = "H:\SQL\Backup" # Backup File location | |
| $configLocation = "C:\temp\scripts\SQL-2014-Settings.ini" | |
| Write-Verbose("Installing SQL Server 2014...") | |
| $cmd = "$setup /ConfigurationFile=$configLocation /AGTSVCACCOUNT=$AGTSVCACCOUNT /AGTSVCPASSWORD='$AGTSVCPASSWORD' /SQLSVCACCOUNT=$SQLSVCACCOUNT /SQLSVCPASSWORD='$SQLSVCPASSWORD' /SQLSYSADMINACCOUNTS=$SQLSYSADMINACCOUNTS /SAPWD='$SAPWD' /SQLBACKUPDIR=$SQLBACKUPDIR /SQLUSERDBDIR=$SQLUSERDBDIR /SQLUSERDBLOGDIR=$SQLUSERDBLOGDIR /SQLTEMPDBDIR=$SQLTEMPDBDIR /SQLTEMPDBLOGDIR=$SQLTEMPDBLOGDIR /Q" | |
| Invoke-Expression $cmd | Write-Verbose | |
| Write-Verbose("Dismounting ISO image...") | |
| $drives = @(Get-Volume | Where-Object {($_.FileSystem -eq "CDFS")}).DriveLetter | |
| if ($drives.Count -gt 0) { | |
| foreach ($drive in $drives) { | |
| Dismount-DiskImage -ImagePath $isoLocation | |
| } | |
| } | |
| } | |
| # Test- Is SQL installed on server? | |
| TestScript = { | |
| $sqlInstances = gwmi win32_service -ComputerName localhost | ? {$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe" } | % { $_.Caption } | |
| $res = $sqlInstances -ne $null -and $sqlInstances -gt 0 | |
| if ($res) { | |
| Write-Verbose "SQL Server is already installed" | |
| } else { | |
| Write-Verbose "SQL Server is not installed" | |
| } | |
| $res | |
| } | |
| #This is stating that these GET/SET/TEST commands won't work unless the Files for the installer are present. this makes sure those resources are present and functional first | |
| DependsOn = @("[File]moveSQLISO") | |
| } | |
| #endregion | |
| #region Create SQL spInstall Login | |
| Script createSPInstall { | |
| # Get- | |
| GetScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spInstall = ($users | Where-Object {($_.CN -like "SharePoint Install")}).sAMAccountName | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "Select IS_SRVROLEMEMBER ('dbcreator', '$spInstall');" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $sql -U $spSA -P $saPW | |
| @{Result="SP Install: $($created)"} | |
| } | |
| # Set- | |
| SetScript = { | |
| # http://technet.microsoft.com/en-us/library/cc281720.aspx | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $domain = $env:userdomain | |
| # Get SQL SA and spInstall Accounts | |
| $spInstall = ($users | Where-Object {($_.CN -like "SharePoint Install")}).sAMAccountName | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $spInstall = $domain + "\" + $spInstall | |
| $query0 = "CREATE LOGIN [" + $spInstall + "] FROM WINDOWS WITH DEFAULT_DATABASE=[MASTER], DEFAULT_LANGUAGE=[us_english]" | |
| $query0 | Write-Verbose | |
| $sql0 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query0 -U $spSA -P $saPW | write-verbose | |
| $query1 = "ALTER SERVER ROLE [securityadmin] ADD MEMBER [" + $spInstall + "]" | |
| $query1 | Write-Verbose | |
| $sql1 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query1 -U $spSA -P $saPW | write-verbose | |
| $query2 = "ALTER SERVER ROLE [dbcreator] ADD MEMBER [" + $spInstall + "]" | |
| $query2 | Write-Verbose | |
| $sql2 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query2 -U $spSA -P $saPW | write-verbose | |
| } | |
| # Test- | |
| TestScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "Select IS_SRVROLEMEMBER ('dbcreator', '$spInstall');" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $sql -U $spSA -P $saPW | |
| if ($created[2].Trim() -eq 1) { | |
| Write-Verbose("Accounts already created...") | |
| $res = $true | |
| } | |
| Else { | |
| Write-Verbose("Accounts need to be created...") | |
| $res = $false | |
| } | |
| $res | |
| } | |
| DependsOn = @("[Script]installSQL") | |
| } | |
| #endregion | |
| #region Create SQL spAccess Login | |
| Script createSPAccess { | |
| # Get- | |
| GetScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "Select IS_SRVROLEMEMBER ('dbcreator', '$spInstall');" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $sql -U $spSA -P $saPW | |
| @{Result="SP Install: $($created)"} | |
| } | |
| # Set- | |
| SetScript = { | |
| # http://technet.microsoft.com/en-us/library/cc281720.aspx | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $domain = $env:userdomain | |
| # Get SQL SA and spAccess Accounts | |
| $spAccess = ($users | Where-Object {($_.CN -like "SharePoint Access Service")}).sAMAccountName | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $spAccess = $domain + "\" + $spAccess | |
| $query3 = "CREATE LOGIN [" + $spAccess + "] FROM WINDOWS WITH DEFAULT_DATABASE=[MASTER], DEFAULT_LANGUAGE=[us_english]" | |
| $query3 | Write-Verbose | |
| $sql3 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query3 -U $spSA -P $saPW | write-verbose | |
| $query4 = "ALTER SERVER ROLE [securityadmin] ADD MEMBER [" + $spAccess + "]" | |
| $query4 | Write-Verbose | |
| $sql4 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query4 -U $spSA -P $saPW | write-verbose | |
| $query5 = "ALTER SERVER ROLE [dbcreator] ADD MEMBER [" + $spAccess + "]" | |
| $query5 | Write-Verbose | |
| $sql5 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query5 -U $spSA -P $saPW | write-verbose | |
| } | |
| # Test- | |
| TestScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $domain = $env:userdomain | |
| $spInstall = ($users | Where-Object {($_.CN -like "SharePoint Access Service")}).sAMAccountName | |
| $spInstall = $domain + "\" + $spInstall | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "Select IS_SRVROLEMEMBER ('dbcreator', '$spInstall');" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $sql -U $spSA -P $saPW | |
| if ($created[2].Trim() -eq 1) { | |
| Write-Verbose("Accounts already created...") | |
| $res = $true | |
| } | |
| Else { | |
| Write-Verbose("Accounts need to be created...") | |
| $res = $false | |
| } | |
| $res | |
| } | |
| DependsOn = @("[Script]installSQL") | |
| } | |
| #endregion | |
| #region Create SharePoint Administrators Group | |
| Script createSPAdmins { | |
| GetScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $domain = $env:userdomain | |
| # Get SQL SA and spAccess Accounts | |
| $spAdmins = $domain + "\" + ($users | Where-Object {($_.CN -like "SharePoint Administrators Group Name")}).sAMAccountName | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "Select IS_SRVROLEMEMBER ('sysadmin', '$spAdmins');" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $sql -U $spSA -P $saPW | |
| @{Result="SP Admins Group: $($created)"} | |
| } | |
| # Set- | |
| SetScript = { | |
| # http://technet.microsoft.com/en-us/library/cc281720.aspx | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $domain = $env:userdomain | |
| # Get SQL SA and spAccess Accounts | |
| $spAdmins = $domain + "\" + ($users | Where-Object {($_.adGroup -like "SharePoint Administrators Group Name")}).sAMAccountName | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $query6 = "CREATE LOGIN [" + $spAdmins + "] FROM WINDOWS WITH DEFAULT_DATABASE=[MASTER], DEFAULT_LANGUAGE=[us_english]" | |
| $query6 | Write-Verbose | |
| $sql6 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query6 -U $spSA -P $saPW | write-verbose | |
| $query7 = "ALTER SERVER ROLE [sysadmin] ADD MEMBER [" + $spAdmins + "]" | |
| $query7 | Write-Verbose | |
| $sql7 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query7 -U $spSA -P $saPW | write-verbose | |
| } | |
| # Test- | |
| TestScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $domain = $env:userdomain | |
| # Get SQL SA and spAccess Accounts | |
| $spAdmins = $domain + "\" + ($users | Where-Object {($_.CN -like "SharePoint Administrators Group Name")}).sAMAccountName | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "Select IS_SRVROLEMEMBER ('sysadmin', '$spAdmins');" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $sql -U $spSA -P $saPW | |
| if ($created[2].Trim() -eq 1) { | |
| Write-Verbose("Accounts already created...") | |
| $res = $true | |
| } | |
| Else { | |
| Write-Verbose("Accounts need to be created...") | |
| $res = $false | |
| } | |
| $res | |
| } | |
| DependsOn = @("[Script]installSQL") | |
| } | |
| #endregion | |
| #region Set MaxDop | |
| Script setMaxDop { | |
| # Get- | |
| GetScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "EXEC sp_configure 'show advanced options', 1 | |
| GO | |
| RECONFIGURE | |
| GO | |
| EXEC sp_configure 'max degree of parallelism' | |
| GO" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE'-Q $sql -U $spSA -P $saPW | |
| @{Result="SP Install: $($created)"} | |
| } | |
| # Set- | |
| SetScript = { | |
| # http://technet.microsoft.com/en-us/library/cc281720.aspx | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $domain = $env:userdomain | |
| # Get SQL SA | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $query6 = "EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE | |
| GO | |
| EXEC sys.sp_configure N'max degree of parallelism', N'1' | |
| GO | |
| RECONFIGURE WITH OVERRIDE | |
| GO | |
| EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE | |
| GO" | |
| $query6 | Write-Verbose | |
| $sql6 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query6 -U $spSA -P $saPW | |
| } | |
| # Test- | |
| TestScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "EXEC sp_configure 'show advanced options', 1 | |
| GO | |
| RECONFIGURE | |
| GO | |
| EXEC sp_configure 'max degree of parallelism' | |
| GO" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE'-Q $sql -U $spSA -P $saPW | |
| if ($created[3].Substring($created[3].Length-1,1) -ne 1) { | |
| Write-Verbose("Changing MaxDop value required...") | |
| $res = $false | |
| } | |
| Else { | |
| Write-Verbose("MaxDop already set...") | |
| $res = $true | |
| } | |
| $res | |
| } | |
| DependsOn = @("[Script]installSQL") | |
| } | |
| #endregion | |
| #region Set Nested Triggers | |
| Script setNestedTriggers { | |
| # Get- | |
| GetScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "EXEC sp_configure 'nested triggers' | |
| GO" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE'-Q $sql -U $spSA -P $saPW | |
| @{Result="SP Install: $($created)"} | |
| } | |
| # Set- | |
| SetScript = { | |
| # http://technet.microsoft.com/en-us/library/cc281720.aspx | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $domain = $env:userdomain | |
| # Get SQL SA | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $query7 = "EXEC sys.sp_configure N'nested triggers', N'1' | |
| GO | |
| RECONFIGURE WITH OVERRIDE | |
| GO" | |
| $query7 | Write-Verbose | |
| $sql7 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query7 -U $spSA -P $saPW | |
| } | |
| # Test- | |
| TestScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "EXEC sp_configure 'nested triggers' | |
| GO" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE'-Q $sql -U $spSA -P $saPW | |
| if ($created[2].Substring($created[2].Length-1,1) -ne 1) { | |
| Write-Verbose("Changing nested triggers value required...") | |
| $res = $false | |
| } | |
| Else { | |
| Write-Verbose("Nested triggers value already set...") | |
| $res = $true | |
| } | |
| $res | |
| } | |
| DependsOn = @("[Script]installSQL") | |
| } | |
| #endregion | |
| #region Set Contained Database Authentication | |
| Script setContainedAuthentication { | |
| # Get- | |
| GetScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "EXEC sp_configure 'contained database authentication' | |
| GO" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE'-Q $sql -U $spSA -P $saPW | |
| @{Result="SP Install: $($created)"} | |
| } | |
| # Set- | |
| SetScript = { | |
| # http://technet.microsoft.com/en-us/library/cc281720.aspx | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $domain = $env:userdomain | |
| # Get SQL SA | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $query8 = "EXEC sys.sp_configure N'contained database authentication', N'1' | |
| GO | |
| RECONFIGURE WITH OVERRIDE | |
| GO" | |
| $query8 | Write-Verbose | |
| $sql8 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query8 -U $spSA -P $saPW | |
| } | |
| # Test- | |
| TestScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "EXEC sp_configure 'contained database authentication' | |
| GO" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE'-Q $sql -U $spSA -P $saPW | |
| if ($created[2].Substring($created[2].Length-1,1) -ne 1) { | |
| Write-Verbose("Changing database authentication value required...") | |
| $res = $false | |
| } | |
| Else { | |
| Write-Verbose("Database authentication value already set...") | |
| $res = $true | |
| } | |
| $res | |
| } | |
| DependsOn = @("[Script]installSQL") | |
| } | |
| #endregion | |
| #region Change SA Name | |
| Script changeSAName { | |
| GetScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "Select name FROM sys.syslogins WHERE sid = 0x01;" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $sql -U $spSA -P $saPW | |
| @{Result="SP Install: $($created)"} | |
| } | |
| SetScript = { | |
| $file = "C:\temp\config\spAccounts.csv" | |
| $users = Import-Csv $file | |
| $user = $users | Where-Object {($_.CN -like "SQL SA")} | |
| $spSA = $user.sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $newSAName = "saTopSecret" | |
| $user.sAMAccountName = $newSAName | |
| $query9 = "alter login sa with name = $newSAName" | |
| $query9 | Write-Verbose | |
| $sql9 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query9 -U $spSA -P $saPW | |
| # Output Table | |
| $users | Export-CSV $file -Force | |
| } | |
| TestScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "Select name FROM sys.syslogins WHERE sid = 0x01;" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE'-Q $sql -U $spSA -P $saPW | |
| if ($created[2].Trim() -eq "sa") { | |
| Write-Verbose("Changing SA name...") | |
| $res = $false | |
| } | |
| Else { | |
| Write-Verbose("SA name already changed...") | |
| $res = $true | |
| } | |
| $res | |
| } | |
| DependsOn = @("[Script]installSQL") | |
| } | |
| #endregion | |
| #region Disable NEW SA Account | |
| Script disableSA { | |
| GetScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "USE [master] | |
| GO | |
| ALTER LOGIN [$spSA] DISABLE | |
| GO" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $sql -U $spSA -P $saPW | |
| @{Result="SP Install: $($created)"} | |
| } | |
| SetScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $domain = $env:userdomain | |
| # Get SQL SA Account | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $query9 = "USE [master] | |
| GO | |
| ALTER LOGIN [$spSA] DISABLE | |
| GO" | |
| $query9 | Write-Verbose | |
| $sql9 = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $query9 -U $spSA -P $saPW | write-verbose | |
| } | |
| TestScript = { | |
| $users = Import-Csv "C:\temp\config\spAccounts.csv" | |
| $spSA = ($users | Where-Object {($_.CN -like "SQL SA")}).sAMAccountName | |
| $saPW = ($users | Where-Object {($_.sAMAccountName -like $spSA)}).Password | |
| $sql = "select name | |
| from syslogins | |
| where name = '$spSA'" | |
| $created = & 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE' -Q $sql -U $spSA -P $saPW | |
| if ($created[2].Trim() -eq $spSA) { | |
| Write-Verbose("SA Account still enabled...") | |
| $res = $false | |
| } | |
| Else { | |
| Write-Verbose("SA account already disabled...") | |
| $res = $true | |
| } | |
| $res | |
| } | |
| DependsOn = @("[Script]installSQL") | |
| } | |
| #endregion | |
| #region Open Port 1433 | |
| Script openFirewallPorts { | |
| GetScript = { | |
| $port = "1433" | |
| $fw = New-Object -ComObject hnetcfg.fwpolicy2 | |
| $rules = $fw.rules | Where-Object { $_.enabled -and $_.LocalPorts -eq 1433 } | |
| $ports = ($rules.LocalPorts -eq 1433) | |
| @{Result="Ports open: $($ports)"} | |
| } | |
| SetScript = { | |
| $port = "1433" | |
| Write-Verbose("Opening port $port...") | |
| $fw = New-Object -ComObject hnetcfg.fwpolicy2 | |
| $rule = New-Object -ComObject HNetCfg.FWRule | |
| $rule.Name = "SQL Server Open Port $port" | |
| $rule.Protocol = 6 #NET_FW_IP_PROTOCOL_TCP | |
| $rule.LocalPorts = $port | |
| $rule.Enabled = $true | |
| $rule.Grouping = "@firewallapi.dll,-23255" | |
| $rule.Profiles = 7 # all | |
| $rule.Action = 1 # NET_FW_ACTION_ALLOW | |
| $rule.EdgeTraversal = $false | |
| $fw.Rules.Add($rule) | |
| } | |
| TestScript = { | |
| $port = "1433" | |
| Write-Verbose("Testing for Open Port $port") | |
| $fw = New-Object -ComObject hnetcfg.fwpolicy2 | |
| $rules = $fw.rules | Where-Object { $_.enabled -and $_.LocalPorts -eq 1433 } | |
| if ($rules.LocalPorts -eq 1433) { | |
| $res = $true | |
| Write-Verbose("Port $port is already open...") | |
| } | |
| ELSE { | |
| $res = $false | |
| Write-Verbose("Port $port is not open...") | |
| } | |
| $res | |
| } | |
| } | |
| #endregion | |
| #region Delete Temp Folder | |
| File deleteTempFolder { | |
| DestinationPath = $dscPath | |
| Type= "Directory" | |
| Recurse = $true | |
| Ensure = "Absent" | |
| Force = $true | |
| DependsOn = @("[Script]installSQL") | |
| } | |
| #endregion | |
| } | |
| } | |
| $SQL2014Path = "C:\mofFiles\$nodeName" | |
| SQL2014 -nodeName $nodeName -OutputPath $SQL2014Path | |
| Start-DscConfiguration -Path $SQL2014Path -Verbose -Wait -Force -ComputerName $nodeName |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment