# encoding: utf-8 # api: powershell # title: Setup SQLVM3 # description: ############################################################################################# # version: 0.1 # type: script # author: SQLDBAwithabeard # license: CC0 # x-poshcode-id: 4160 # x-archived: 2014-08-18T21:33:17 # x-published: 2014-05-14T17:37:00 # # # # NAME: SetupVMSQL3.ps1 # AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com # DATE:10/05/2013 # # # # # # COMMENTS: This script will set up the SQL3 VM ready for use and enable SQL Authentication # Add a user called SQLAdmin with a password of P@ssw0rd # and enable PS Remoting # Restart SQL Service # ———————————————————————————————————— # ############################################################################################# # # NAME: SetupVMSQL3.ps1 # AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com # DATE:10/05/2013 # # # COMMENTS: This script will set up the SQL3 VM ready for use and enable SQL Authentication # Add a user called SQLAdmin with a password of P@ssw0rd # and enable PS Remoting # Restart SQL Service # ------------------------------------------------------------------------ # Run on SQL3 # Configure PowerShell Execution Policy to Run all Scripts – It’s a one time Progress Set-ExecutionPolicy –ExecutionPolicy Unrestricted netsh advfirewall firewall add rule name=SQL-SSMS dir=in action=allow enable=yes profile=any netsh advfirewall firewall add rule name=SQL-SSMS dir=out action=allow program=any enable=yes profile=any netsh advfirewall firewall set rule group="Remote Administration" new enable=yes netsh advfirewall firewall set rule group="File and Printer Sharing" new enable=yes netsh advfirewall firewall set rule group="Remote Service Management" new enable=yes netsh advfirewall firewall set rule group="Performance Logs and Alerts" new enable=yes Netsh advfirewall firewall set rule group="Remote Event Log Management" new enable=yes Netsh advfirewall firewall set rule group="Remote Scheduled Tasks Management" new enable=yes netsh advfirewall firewall set rule group="Remote Volume Management" new enable=yes netsh advfirewall firewall set rule group="Remote Desktop" new enable=yes netsh advfirewall firewall set rule group="Windows Firewall Remote Management" new enable =yes netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable =yes #Extra one for PS Remoting netsh advfirewall firewall add rule name="Port 5986" dir=in action=allow protocol=TCP localport=5986 # To Load SQL Server Management Objects into PowerShell [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’) | out-null [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null SQLPS $Name = 'SQL3' Invoke-Sqlcmd -ServerInstance $Name -Database master -Query "USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2 GO " Invoke-Sqlcmd -ServerInstance $Name -Database master -Query "USE [master] GO CREATE LOGIN [SQLAdmin] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master] GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [SQLAdmin] GO " get-Service -ComputerName $Name -Name MSSQLSERVER|Restart-Service -force Enable-PSRemoting -force