Microsoft.SQLServer.Windows.RunAs.Addendum.HealthServiceAsSA.WA (WriteActionModuleType)

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<WriteActionModuleType ID="Microsoft.SQLServer.Windows.RunAs.Addendum.HealthServiceAsSA.WA" Accessibility="Internal" Batching="false">

  <Configuration>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" minOccurs="1" name="SQLServer" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" minOccurs="1" name="TcpPorts" type="xsd:string"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="SQLServer" Selector="$Config/SQLServer$" ParameterType="string"/>

    <OverrideableParameter ID="TcpPorts" Selector="$Config/TcpPorts$" ParameterType="string"/>

  </OverrideableParameters>

  <ModuleImplementation Isolation="Any">

    <Composite>

      <MemberModules>

        <WriteAction ID="PSWA" TypeID="Windows!Microsoft.Windows.PowerShellWriteAction">

          <ScriptName>Microsoft.SQLServer.Windows.RunAs.Addendum.HealthServiceAsSA.WA.ps1</ScriptName>

          <ScriptBody><Script>

#=================================================================================

#  Script to create SQL Login for HealthService and grant Sysadmin

#

#  Author: Kevin Holman

#  v1.3

#=================================================================================

param([string]$SQLServer,[string]$TcpPorts)





# Manual Testing section - put stuff here for manually testing script - typically parameters:

#=================================================================================

# [string]$SQLServer = "WS2012R2\INST1"

# [string]$TcpPorts = "49304"

#=================================================================================





# Constants section - modify stuff here:

#=================================================================================

# Assign script name variable for use in event logging.  

$ScriptName = "Microsoft.SQLServer.Windows.RunAs.Addendum.HealthServiceAsSA.WA.ps1"

$EventID = "8701"



[string]$SQLQuery = "sp_addsrvrolemember 'NT SERVICE\HealthService', 'sysadmin'"

#=================================================================================





# Starting Script section - All scripts get this

#=================================================================================

# Gather the start time of the script

$StartTime = Get-Date

#Set variable to be used in logging events

$whoami = whoami

# Load MOMScript API

$momapi = New-Object -comObject MOM.ScriptAPI

#Log script event that we are starting task

$momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Script is starting. `n Running as ($whoami).")

#=================================================================================





# Begin MAIN script section

#=================================================================================

Write-Host "Script is starting. `nRunning as ($whoami). `nSQLServer: ($SQLServer)."



#Clear any previous errors

IF($Error){$Error.Clear()}



##### Begin Source SQL Query Section

# Connect to SQL and then Query the database

$SQLConnection = New-Object System.Data.SqlClient.SqlConnection

$SQLConnection.ConnectionString = "Server=$SQLServer;Integrated Security=True"

TRY

{

  Write-Host "Opening Connection using connect string: ($SQLServer)."

  $SQLConnection.Open()

}

CATCH

{

  Write-Host "ERROR opening connection to: ($SQLServer).  Will Attempt using Name,Port"

  IF ($SQLServer -match "\\")

  {

    #Drop the backslash and named instance

    $SQLServer = ($SQLServer.Split("\"))[0]

  }

  #Use only the first port if passed a comma seperated list

  IF ($TcpPorts -match ",")

  {

    $TcpPorts = ($TcpPorts.Split(","))[0]

  }

  #Make a new connect string with the port  

  $SQLServer = $SQLServer + "," + $TcpPorts

  #Connect attempt

  TRY

  {

    Write-Host "Opening Connection using connect string: ($SQLServer)."

    $SQLConnection.ConnectionString = "Server=$SQLServer;Integrated Security=True"

    $SQLConnection.Open()

  }

  CATCH

  {

    Write-Host "ERROR opening connection to: ($SQLServer). `nTERMINATING. `nError is: ($Error)."

    EXIT

  }

}



#Clear any previous errors

IF($Error){$Error.Clear()}   



Write-Host "Connected to SQL Server: ($SQLServer). `nRunning SQL command to Create SQL Login and assign permissions for Healthservice."



$SQLCmd = New-Object System.Data.SqlClient.SqlCommand

$SQLCmd.CommandText = $SQLQuery

$SQLCmd.Connection = $SQLConnection

$SQLCmd.ExecuteNonQuery() | Out-Null

$SQLConnection.Close()



# Check for errors connecting to SQL

IF ($Error)

{

  Write-Host "ERROR executing query on: ($SQLServer). `nTERMINATING. `nError is: ($Error)."

  EXIT  

}

ELSE

{

  Write-Host "SUCCESS executing SQL command"

}

#=================================================================================

# End MAIN script section





# End of script section

#=================================================================================

#Log an event for script ending and total execution time.

$EndTime = Get-Date

$ScriptTime = ($EndTime - $StartTime).TotalSeconds

$momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Script Completed. `n Script Runtime: ($ScriptTime) seconds.")

#=================================================================================

# End of script

                </Script></ScriptBody>

          <Parameters>

            <Parameter>

              <Name>SQLServer</Name>

              <Value>$Config/SQLServer$</Value>

            </Parameter>

            <Parameter>

              <Name>TcpPorts</Name>

              <Value>$Config/TcpPorts$</Value>

            </Parameter>

          </Parameters>

          <TimeoutSeconds>120</TimeoutSeconds>

        </WriteAction>

      </MemberModules>

      <Composition>

        <Node ID="PSWA"/>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.BaseData</OutputType>

  <InputType>System!System.BaseData</InputType>

</WriteActionModuleType>