PoshCode Archive  Artifact Content

Artifact 753fdcb407d34569529b7a0e7b87077e1ec055a97ff90c3830a992b2ed05803c:

  • File Setup-SQLVM3.ps1 — part of check-in [eb4bb599fe] at 2018-06-10 13:37:37 on branch trunk — ############################################################################################# (user: SQLDBAwithabeard size: 3608)

# 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  Its 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