Automation & Integration Connectors

ScriptRunner Connectors are interfaces that allow you to connect third-party systems to the ScriptRunner Server in order to functionally extend the ScriptRunner platform.

A distinction is made between Automation Connectors and Integration Connectors.

Overview of ScriptRunner Connectors

Overview of ScriptRunner Connectors


Automation Connectors

Automation Connectors are used to allow external systems to start actions in ScriptRunner and receive the results of these actions.
The following connectors are Automation Connectors:

  • Web Service Connector
  • Email Inbound Connector

Integration Connectors

Integration Connectors are used to allow ScriptRunner to access or modify external systems and their resources within actions.
The following connectors are Integration Connectors:

  • Password Server Connector
  • SQL Connector
  • Email Notification Connector

Web Service Connector

The Web Service Connector enables connections to the ScriptRunner instance through a REST API. External systems can access ScriptRunner through this interface and call defined actions in ScriptRunner.
Note: An additional license must be purchased and installed in order to use the Web Service Connector.
Functionality of the Web Service Connector

Functionality of the Web Service Connector

Functionality

Functionality of the Web Service Connector

Functionality of the Web Service Connector

  • An Action started via Web Service Connector runs in ScriptRunner like a manually started Action.
  • After the Action is finished, the result is logged and listed as a report in the dashboard of the Admin App.
  • Procedure:
    1. A POST Request is sent by the third-party system
      Note: The Web Service Connector is an OData REST Web Service. SOAP or WSDL are not supported.
    2. The ScriptRunner Web Service Connector waits for requests
      • The POST packet data is in JSON format
      • The general format rule of OData v4 is supported
    3. Request arrives
      • The validity of the IP address is checked
      • Authorization of the sender is checked
      • JSON packet is evaluated
      • Parameters are passed to the Action
    4. Action is started
    5. Result comes back
  • Report in ScriptRunner:
    • Reason: Corresponding Web Service Connector and the transferred Reason
    • Started By: User under which the Web Request was sent

Webhooks

Webhooks are a common means of communication between servers. They are used to inform the message recipient that a certain event has occurred.

The basic concept is that a payload URL is registered on the source system that is to be called at a certain event. NTLM or Kerberos, or Basic Auth if necessary, can be used for authentication to the Web Service Connector by the caller.

The ScriptRunner Web Service Connector also supports calling and starting ScriptRunner actions via registered webhooks. A payload URL can be registered on the Webhook with the connector.

Action-ID & Payload-URL für Webhooks

Action ID & Payload URL for webhooks

The webhook URL to be registered on the source system is specified in the following format

[http(s)://FQDN]/ScriptRunner/api2/PostWebhook/[ID]

Note: [ID] stands for the ID of the action. This is displayed on the first page of the action in the wizard from version 2020R1.

Configuration

The Web Service Connector receives the data in JSON format from registered webhook POST requests.

  1. The JSON data structure packets are passed to the action as a parameter.
  2. For this purpose, the PowerShell script must define the parameter [string] $bodyString in the parameter block.
  3. This contains the JSON payload of the source system
    • is parsed by using the Cmdlet ConvertFrom-JSON, decomposed, and the parts are assigned to the PowerShell parameters that are used.
  4. Result: PS Custom Object in the data structure of the JSON object.

Configuration

  1. Open ScriptRunner and log on with a user from the administrator group you have defined.
    • You are in the dashboard.
  2. Switch to the Automation section.
  3. Click on Create in the lower navigation bar.
  4. The wizard opens.
    • Select the type of Automation Connector to create: Select “Web Service Connector”.
      • Click on Next.
    • Web Service Connector Properties: Customize the configuration page according to your environment:
      • Set the check mark at Enabled
      • Display Name: Assign a display name
      • Application Scenario: The UI changes according to your selection.
        1. System / Application Backend: (Default)
          • The input of a fixed IP address is required.
          • The number of actions is unlimited, but can be limited by delegation.
          • Can be delegated to any group.
            Note: If you use Basic Authentication, the Web Service Connector must be delegated to a person and not to a group.
        2. User Application / Computers directly:
          • Field is locked for input.
          • Can be accessed from any IP address.
          • Can only be delegated to SelfService users.
        3. MicroServices / Kubernetes: Requires special license!
          • Field is locked for input.
          • Can be accessed from any IP address.
          • Can be delegated to any group or user.

Testing

ScriptRunner comes with the script CallASRWebSvcConnector.ps1 with complete sample code for addressing the Web Service Connector from the PowerShell. This script is used for initial setup, testing or troubleshooting:

  1. Open the PowerShell on the third party machine.
  2. Open the Script CallASRWebSvcConnector.ps1 and adjust the $server parameter according to the ScriptRunner Server’s FQDN.
  3. Start the Script CallASRWebSvcConnector.ps1 with the same parameters as the third-party system.
  4. If execution is successful, the Web Service Connector can also be used by the third-party system.
Error Analysis
  • <200 success> successful completion of the Action
  • <400 bad request> faulty request
  • <401 unauthorized> user is not authorized
  • <403 forbidden> user is not entitled
  • <404 not found> faulty URI
Possible causes of errors
  • Connection to third-party system does not work / is blocked (firewall, proxy, etc.)
  • Wrong IP address of the sender was stored
  • Incorrect username/password was stored within the third-party system
  • Action can/may not be executed (missing delegation, user permissions)

Note: For detailed error analysis, the ScriptRunner logs can be used under <C:\ProgramData\ScriptRunner\Service\Local\Log>.

Authentication

By default, Windows integrated authentication is used for the Web Service Connector. For third-party systems that cannot use this authentication, Basic Authentication can be configured.

The POST Request starts the execution of an Action and transfers the required data as a JSON body. The authentication of the sending user takes place.

The POST Request is sent to a fixed URL to the ScriptRunner Server.

http(s)://<hostname>:<port>/ScriptRunner/ActionContextItem/StartASRNamedAction

By default, ScriptRunner works with built in Windows authentication. This is compared with the created delegation elements and guarantees the authorization of the caller.

If a request is sent via PowerShell with Invoke-WebRequest, the integrated Windows authentication is activated by the parameter -UseDefaultCredentials:

PowerShell:

Invoke-WebRequest -UseDefaultCredentials […]

C#:

HttpWebRequest request = […]; request.UseDefaultCredentials = true;

CURL:

curl --negotiate -u :<URL…>

Basic Authentication

If a third-party system does not have Windows integrated authentication via NTLM or Kerberos, the ScriptRunner Web Service Connector can be accessed with Basic Authentication.

Note: Since Basic Authentication transmits the username and password in plain text, it is to be considered unsafe and preferably to avoid.

The plain text passwords must be secured via https, which requires a server certificate for the ScriptRunner host.

Requirements

  • User license in ScriptRunner
  • Web Service Connector license
  • User account to delegate and run (local account on the ScriptRunner server or an AD user)
    Notes:
    – The local user must be set up on the ScriptRunner server
    – The AD user and the ScriptRunner server must be in the same domain.
    – Cannot be delegated to groups, as ScriptRunner only checks the correctness of the account for each request and not its group membership in the AD.
  • Access via https: SSL server certificate in the Windows Certificate Store

Configuration

Important notes before configuration:

  • Parallel operation of Basic Authentication for the Web Service Connector with other STS-Access types are not possible in the STS access port.
    Note: Standard port: 8091; STS access port for Basic Auth.: 8092
  • Since the ScriptRunner service runs under the machine account, this is the certificate store of the machine.
  • To determine the thumbprint of your installed certificate, type the following command in an administrative PowerShell:
    dir Cert:LocalMachineMy

    Note: In the Console, this value is specified with spaces. The spaces must be removed before you use the cmdlet.

  • ScriptRunner does not store the password inside ScriptRunner.
  • Each account requires a separate ScriptRunner user license if you use Basic Authentication.
  • The following notation is recommended: DOMAIN\username or COMPUTERNAME\username -> changing spelling will result in multiple license entries.

ScriptRunner

In the following, the configuration of the Basic Authentication Connector is described. First in ScriptRunner, then in PowerShell.

  1. Open ScriptRunner and log in with a user of your defined admin group.
    • You are in the Dashboard.
  2. Switch to the Delegation section and create a delegation.
    Note: Delegate to a Service Desk Users group and deposit the previously determined user.
    • When creating the delegation for Basic Authentication are subsequent fields required:
      • Authorization Method: Claim-based identity
      • Claim Type: ScriptRunner-WebServiceConnector-Claim
        Note: Type in the ClaimType manually, errors may occur during copying (binding stitch as special character).
      • Claim Value: User account of the user. DEVSTAR\luke
        Note: Domain and username as on the third-party system (AD: DOMAIN\username; local: COMPUTERNAME\username)

      Configured Delegation

      Configured Delegation

    • Delegated Actions: Select the Actions to delegate; Local: Add two values
      Select Action

      Select Action


  3. Switch to the Automation section.
    • Create a new WebService Connector by clicking on Create.
    • Fill in the fields according to your requirements.
    • Restrict this Connector to a certain Delegation: Select the delegation you have just configured.
      Note: This restriction can be temporarily removed for error analysis.

      Configured Web Service Connector

      Configured Web Service Connector

    • Click on OK to save your configuration.

PowerShell

  1. Open the Windows PowerShell (ISE) on the ScriptRunner server with administrative privileges.
  2. Run the Set-AsrSTSOptions cmdlet with the following parameters: *AuthMode, *BasicLocalAccounts, *LocalPort, *SSLCertThumbprint, *Restart-AsrService
Set-AsrSTSOptions -AuthMode WINBasic -BasicLocalAccounts 1 -LocalPort 8092 -SSLCertThumbprint 'a909502dd82ae41433e6f83886b00d4277a32a7b' Restart-AsrService
    • BasicLocalAccounts specifies the type of user; 0=AD user, 1=local user
    • 8092 designates the IP port that the third-party system addresses with Basic Authentication
    • a909502dd82ae41433e6f83886b00d4277a32a7b is the thumbprint of the certificate
  1. The ScriptRunner service is restarted.
  2. Use the Get-AsrSTSOptions cmdlet to verify the authentication method.

    Cmdlet: Get-AsrsSTSOptions

    Cmdlet: Get-AsrSTSOptions

Testing

The following is an example script for testing the Basic Auth. connection.

[PowerShell]

$baseURI = 'https://FQDN:BackendPort:8092/ScriptRunner/JobControl/' $user = "DEVSTARluke" $pass = 'password' $pair = "$($user):$($pass)" $encodedCreds = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes($pair)) $basicAuthValue = "Basic $encodedCreds" $Headers = @{ Authorization = $basicAuthValue } Invoke-WebRequest -Uri $baseURI -Headers $Headers -UseBasicParsing | Select-Object statuscode

Error analysis

  • <200 success> successful completion of the action
  • <400 bad request> faulty request
  • <401 unauthorized> user is not authorized
  • <403 forbidden> user is not entitled
  • <404 not found> faulty URI

Note: For detailed error analysis, the ScriptRunner logs can be used under <C:\ProgramData\ScriptRunner\Service\Local\Log>.

Reset configuration

  1. Open the PowerShell (ISE) with administrative rights and enter the following command:
Set-AsrSTSOptions -AuthMode WIN
  1. Open ScriptRunner and log in with a user, the administrator group you have defined.
    • You are in the dashboard.
    • (Optional) Switch to the Delegation section and remove the delegation.
    • (Optional) Remove AD or, on the machine, the user.

POST Request

Data structure of the POST Request:

  • ActionName (Data type: string):
    Name of the action
  • ParamNames (data type: string[]):
    List of parameter names (without $)
  • ParamValues (data type: string[]):
    List of parameter values. In order of the ParamNames.
  • Options (data type: string[]):
    (Optional) List of options. Only the second entry is currently used. Cause for report can be specified, e.g. ticket number, workflow case, etc.
  • RunFlags (datatype: int[]):
    (Optional) List of numbers. Only first entry is currently in use. Wait time in seconds can be specified
    Note: The Web Service waits until the configured wait time expires for the result of the thread. The execution of parallel running calls should be avoided, they block the execution of further threads.

Note: Parameters that are preset in the action can be omitted in the request.

POST Response

Data structure of the POST Response:

  • ID (data type: int64):
    The ID of the status object. Is required to wait for Running=FALSE.
  • Running (data type: boolean):
    Returns to see if the script is still running. If TRUE, the following fields are not yet finally filled.
  • IsError (data type: boolean):
    Return indicates whether script execution in PowerShell has resulted in an error.
  • OutResultMessage (data type: string):
    The result ot the Action, as in the script to $SRXEnv.ResultMessage handed over.
  • OutReportString (datatype: string):
    Complete PowerShell report as in the Admin App.
  • OutSerializedPSObjects (data type: string):
    The result the Action as in the script to $SRXEnv.ResultObjectJSON handed over.
  • OutNumErrors (data type: int32):
    IsError
    =TRUE: Number of PowerShell errors.
  • OutErrorString (data type: string):
    IsError=TRUE: The PowerShell error messages as shown in the report.

For returning more data, as a JSON object, the entry ResultObjectJSON of the ScriptRunner environment variable $SRXEnv can be used.

Note:

  • More information about the environment variable can be found in our Knowledge Base.
  • More information about the Web Service Connector can be found in our Knowledge Base.

Use Cases

In the following, various use cases regarding the Web Service Connector are explained.

PRTG Network Monitor

PRTG Network Monitor monitors the availability and performance of the entire IT infrastructure. In case of failures or errors PRTG sends warnings to ScriptRunner based on individually configurable threshold values.

Requirements

To use PRTG together with ScriptRunner you need the following components:

  • Existing ScriptRunner instance
  • Existing PRTG instance
  • Activated Web Service Connector license
  • Configured inbound/outbound firewall rules

Functionality

Funktionsweise PRTG

Functionality of PRTG

  1. Sensor triggers notification.
  2. Notification starts the script for communication with ScriptRunner, with parameter transfer from PRTG.
  3. The script triggers a Web Service POST call to ScriptRunner.
  4. The POST call starts the configured Action on the ScriptRunner backend with the necessary parameters.
  5. In the script the result can be returned to the monitoring system, e.g. an “Add Comment” function call and passing $SRXEnv.ResultMessage to the monitoring system.

Configuration

In the following the configuration of ScriptRunner and then the PRTG system is described.

ScriptRunner

  1. Open the Admin interface in ScriptRunner
  2. Switch to the Automation section
  3. Create a new Web Service Connector; click Next
    1. Activate the Connector
    2. Assign a Display name
    3. Select the Application Scenario
    4. Enter the IP address of the PRTG system
    5. Optional: Set up a delegation

      Configuration Web Service Connector

      Configuration Web Service Connector

    6. Click on OK
  4. Switch to the Action section
  5. Create a new action
    1. Select a Script: Select the Script CheckWebServiceConnector; click Next
    2. Action Properties: (Optional) Edit the display name, tags, and description; click Next
    3. Select Targets: Select the target system, [LOCAL] Direct Service Execution is selected by default; confirm your configuration with OK

PRTG

  1. Copy the Script Callasrwebsvcconnector.ps1 (ScriptRunner Server) from C:\ProgramData\ScriptRunner\ScriptMgr\EXPL\Common and place it on the PRTG Server under C:\Program Files (x86)\PRTG Network Monitor\Notifications\EXE
    Note: The PRTG system only checks this folder to add scripts to the selection.
  2. Open the script in edit mode (e.g. PowerShell ISE)
    1. Adjust the $server parameter by specifying the IP address or FQDN of the ScriptRunner Server; save the script

      Anpassung des $server Parameters

      Adaptation of the $server parameter

  3. Open the web browser and connect to the PRTG system
  4. Create a new notification
    1. Go to the area >Setup >Account Settings >Notification Templates
    2. Add a template for notifications
    3. (Optional) Edit the display name, tags, status, schedule, etc.
    4. Open the Execute Program area
    5. Select the file Callasrwebsvcconnector.ps1 and fill in the remaining fields according to your data; save your settings
      1. Program File: Name of the caller script; Callasrwebsvcconnector.ps1
      2. Parameters:ActionNameCallasrwebsvcconnector.ps1 -ParamNames Param1,Param2,Param3 -ParamValues ‘one’, ‘2’, ‘three
        Hint: An overview of the available PRTG variables can be found here.
      3. Domain or Computer Name: Domain of the calling user; devstar.org
      4. Username: Username of the calling user; ani
        Note: The specified user requires a license in ScriptRunner
      5. Password: Password of the calling user
      6. Timeout: Time out for the call -> you have to test how long it takes to start the action in the respective environment; 60 seconds

        Configuration PRTG

        Configuration PRTG

Testing

  1. Open the web browser and connect to the PRTG system
  2. Go to the area >Setup> Account Settings > Notification Templates
    1. Select your created notification and click on Send test notification

      Send test notification

      Send test notification

  3. Switch to the area >Logs >System events >Notifications Related to view the status of the sent test notification
    1. If the notification was sent successfully, you will find the message“Status when sending EXE:OK”under Message
      Feedback test notification

      Feedback test notification

       

  4. Switch to the ScriptRunner Server and log in to the Admin App
    1. Next to the Action Check-WebServiceConnector you will find a green bar, this indicates the successful execution of the action.

      Feedback in ScriptRunner

      Feedback in ScriptRunner

    2. A red bar indicates that there was an error in the execution of the action.
      Note: A detailed error description can be found in the Windows Event Log.

Additional Information

Functionality of the Caller Script
  • Input – ActionName = Name of the action in ScriptRunner to be called; Callasrwebsvcconnector.ps1
  • Input – ParamNames = parameter names of the action script; Param1,Param2,Param3
  • Input – ParamValues = variable contents of PRTG; ‘one’, ‘2′, ‘three’.
  • Output – Report ID, ResultMessage and Report
Important notes
  • Name of the monitoring sensor must correspond to the name of the monitored service for the example script.
  • Action name when calling the caller script must correspond to the action name in ScriptRunner and be adjusted on both sides.
  • ParamNames and ParamValues form matching pairs. The names of the parameters in ParamNames must correspond to those in the script. The transferred ParamValues can then be further processed in the script.
Information about PRTG

Call via Execute Command

https://www.paessler.com/manuals/prtg/notifications_settings#program

Configure Notification

https://www.paessler.com/manuals/prtg/notifications_settings

https://www.paessler.com/manuals/prtg/sensor_notifications_settings

https://www.paessler.com/manuals/prtg/setting_up_a_notification_example

Available variables in PRTG

https://kb.paessler.com/en/topic/373-what-placeholders-can-i-use-with-prtg

Password Server Connector

The Password Server Connector is used to ensure the secure management and centralization of passwords. This allows ScriptRunner to automatically access the securely stored passwords while performing actions. This eliminates the need to store credentials within PowerShell scripts or on the ScriptRunner server.

Credential Management with Password Servers

Supported Password Servers:

  • Pleasant Password Server
  • Thycotic Secret Server
  • CyberArk Password Vault

Pleasant Password Server

To connect the Pleasant Password Server you need the following components:

  • Licensed ScriptRunner Password Server Connector
  • Licensed Pleasant Password Server
  • Valid certificate on the ScriptRunner server, for setting up the https connection
  • Configured safe, with a stored user account, on the Pleasant Password Server

Note: For more information on how to set up the Pleasant Connector, please refer to our Knowledge Base.

Functionality

  • Action is started via the ScriptRunner web interface.
  • ScriptRunner recognizes that the action is being run from an account managed by a Password Server and requests the password from the Password Server.
  • The Password Server passes the password to ScriptRunner.
  • ScriptRunner executes the action with the detecting user account.

Note: ScriptRunner credentials from the Windows Credential Store can be managed parallel with the credentials from the Password Server Connector.

Best Practice
  • Create your own ScriptRunner service account with read access to the Password Server.
  • Create your own Password Safe for the ScriptRunner credentials.
  • Store all service accounts for ScriptRunner in this safe.

Configuration

To set up the Password Server Connector, follow these steps:

  1. Go to the ScriptRunner Server
  2. Open the PowerShell (ISE) as administrator
  3. Enter the following command:
Set-AsrPasswordServerConnector -API 'Pleasant Password Server (APIv4)' -Host $host -Port 10001 -User $userName -ClearPassword $password -UseSSL yes -On -Restart

Note: Adjust the $host, $userName, and $password (users with read-only access in Pleasant) parameters to suit your environment. You get an error message only in ScriptRunner under Settings and not in the console.

You can use the Get-AsrPasswordServerConnector command to verify that the Password Server Connector is enabled.

  1. Open ScriptRunner and log in with a user, the administrator group you have defined.
    • You are in the Dashboard.
  2. In the left navigation bar, click on Settings
    • Here you can see if a connection to the Pleasant Server exists.
Possible error causes
  • Hostname is not valid, Pleasant Server cannot be found
  • Certificate is not valid or has expired
  • Certificate is stored in the wrong place (local machine\my)
  • Deposited user has no access to the Password Safe
  • User ID does not match
  • Missing authorizations of the determined user account

Configuration of the credentials

  1. Open the web browser and log in to the Pleasant Server
  2. Open the credential details of the corresponding user
  3. Under Direct Link you will find the ID of the user:

    Credential Details in Pleasant

    Credential Details in Pleasant

  4. Copy the ID
  5. Open the ScriptRunner Admin App and log in with a user, the administrator group you have defined.
    • You are in the dashboard
  6. In the left navigation bar, click on the Credentials section
  7. Click on Create
  8. The wizard opens:
    • Display name: Assign a display name
    • Retrieve Password from the Password server: Enter the ID of the user you have just copied

      Konfiguriertes Credential in ScriptRunner

      Configured Credential in ScriptRunner

  9. Confirm your entries with OK
  10. In the overview, the credential is marked if the credential is retrieved from a Password Server safe.

    Kennzeichnung des Credentials

    Identification of the credential

Testing

For this test the Action Get-ADUserProperties with the corresponding Target ADC remote is used.

  1. Open the ScriptRunner Admin App.
  2. Log in with a user, the administrator group you have defined.
    • You are in the Dashboard.
  3. Click on theTargets section in the left navigation bar.
  4. Select the Target ADC remote and click on Edit in the lower toolbar.
  5. The wizard opens.
    • Select the just created Admin Ani Credential in PS Remoting credential.

      Target Konfiguration

      Target Configuration

    • Confirm your selection with OK.
  6. Switch to the section Actions
  7. Click on the action to be executed; Get-ADUser Properties
    • The action is configured to run on the Target ADC remote.
  8. In the lower toolbar, click Run
  9. Execute the Action
  10. The green bar next to the action indicates that the action has been executed successfully.
    Note: A red bar indicates that an error occurred during the execution of the action.
  11. Report / Display report: Click on the bar next to the Action or on the ticker in the bottom right corner.
    • The result of the action is displayed.
Credential as script variable

The following is an example script that returns whether the Pleasant-credential was read correctly.

[PowerShell]

Param(
[pscredential] $cred 
)
Write-Output $cred.username

SQL Connector

The SQL Connector allows you to additionally store all information of the action reports in an external SQL database. With this long-term storage you can ensure that no report is lost and you meet the requirements of governance and compliance.

Requirements

  • Existing ScriptRunner instance
  • SQL Connector License
  • Existing SQL database and user account for DB
    Note: Information on setting up the user account can be found under Configuration

Functionality

Functionality SQL Connector

  1. An action is started and executed in SR
  2. When the action has finished, an XML file is generated under C:\Program Data\ScriptRunner\Service\Settings\Queues.
    • The XML file contains PoSH Report data of the executed action.
  3. The XML files are transferred to the database.
    • The ScriptRunner service writes the generated XML file to the ScriptRunner table in the database using the appropriate user account.
      Note: The connector can be seen as an SQL client.
  4. If the connection to the database is interrupted, an error message is written to the Windows Event Log.
    • In this case, additional reports are cached on the SR Server under C:\Program Data\ScriptRunner\Service\Settings\Queues and automatically transferred from the service to the database when the connection is re-established.
      Note: A maximum of 10,000 entries can be stored in the folder.

Best Practice

  • Database Name: ScriptRunner
  • User account on the DB as Windows authentication
  • Give the user account only read/write rights
  • For a version update of SR always install and execute the ExtDb_UpdateTables.sql script on the database
    Notes:
    – The update file is located at C:\Program Files\ScriptRunner\Service\Support
    – To execute the update, follow the instructions under Update.

Configuration

The following describes the configuration of the database following that of ScriptRunner. For database users with Windows authentication follow 4A, for SQL Server authentication users follow the steps in 4B.

Database

  1. Go to the ScriptRunner Server
    • Open Windows Explorer and go to C:\Program Files\ScriptRunner\Service\Support
    • Copy the scripts ExtDb_CreateTables.sql and ExtDb_UpdateTables.sql
    • Connect to the SQL Server
    • Place the copied scripts on the server
  2. Open the SQL Server Management Studio
  3. Create a new database; ScriptRunner

    ScriptRunner Database

  4. A: Users with Windows authentication
    • Create a new user
    • Assign a login name and the property Windows authentication

      Database user with Windows authentication

    • Authorize the user to the appropriate database; ScriptRunner
    • Assign the permission db datareader and db datawriter

      Properties of the database user

    • Confirm the configuration with OK
  5. B: Users with SQL Server authentication
    • Create a new user
    • Assign a login name and the property SQL Server authentication

      Database user with SQL Server authentication

    • Authorize the user to the appropriate database; ScriptRunner
    • Assign the permission db datareader and db datawriter

      Properties of the database user

    • Confirm the configuration with OK
  6. Navigate in the DB menu bar to Open File
    Note: Make sure that the correct database is selected before execution

    Control of Database

    • Select the copied Script ExtDb_CreateTables.sql
    • Click on Open

      Open script: Create Tables

    • Click on Execute
    • In the footer of the Management Studio you will receive a feedback (“Query was successfully executed”).
    • Two new tables have been created in the database >ScriptRunner >Tables; BaseEntities_AppConfigSet and BaseEntities_JobControlSet
      Note:
      AppConfigSet: Shows the current Schema Version; JobControlSet: Report data of executed actions.

      Created tables: AppConfigSet & JobControlSet

  7. In the DB menu bar, navigate to Open File
    Note: Before executing the query, make sure that the correct database is selected
    • Select the copied Script ExtDb_UpdateTables.sql
    • Click on Open

      Open script: Update Tables

    • Click on Execute
    • In the footer of the Management Studio you will receive a feedback (“Query was successfully executed”).
    • By querying the Tabele dbo.BaseEntities_AppConfigSet the Schema Version can be viewed
      Note: The schema version may differ from the screenshot.

      AppConfigSet: Schema Version

ScriptRunner

  1. Go to the ScriptRunner server and open the PowerShell (ISE) with administration rights
  2. Run the Set-AsrSQLConnectorm cmdlet with the following parameters: *On, *ConnectionString, *UserName, *Password (Clearpassword), *Restart
    • On: Activates the connector
    • ConnectionString: Connection to the SQL server
      Data Source = FQDN of the SQL server;
      Initial Catalog = name of the database (ScriptRunner);
      4A: For Windows integrated user : Integrated Security = true (runs in the context of the specified AD user);
      4B: For local SQL user: Integrated Security = false (SQL user is used in the connection string);
      Note: For more information about the Connection String, see the Microsoft Docs.
    • UserName: Username of the user stored in the DB; DEVSTAR\ani
    • Password: Password of the user stored in the DB
    • Restart-AsrService: Restarts the SR Service
    • Note: If the default port (1433) in your infrastructure differs, it must also be specified, separated by a comma. (Example: <FQDN-DB-Server> ,<PORT>)
  3. Check the configuration:
    • To verify that the connector is turned on, run the cmdlet Get-AsrSqlConnector.
    • Open the Windows Event Logs
    • Open the SR Admin App and navigate to the Settings section. The configured connection to the SQL server is marked here:
      ScriptRunner

      Settings: Configured connection to SQL Server

      Note: Click on >SQL Report/Audit Database, click on >View in the lower navigation bar to view the connection string.

Testing

  1. Open ScriptRunner and log in with a user of your defined admin group.
    • You are in the dashboard.
  2. Switch to the Actions section.
  3. Click on the Action >Get Process HTML
    • Click on >Run
    • Execute the action
    • A green bar appears next to the action when it has been successfully executed
  4. Switch to the SQL server
    • Open the Microsoft SQL Server Management Studio
    • Navigate to the database ScriptRunner
    • Navigate in Tables
    • Query the Top 1000 rows of the BaseEntities_JobControlSet
    • Under Results, the successfully executed action can be viewed

      PowerShell Report of the Action in the SQL Database

Update

Attention! Before you start the update, make sure that you have a current backup of the database.
Note: When a ScriptRunner database update occurs, the associated database schema must be updated.

  1. Go to the ScriptRunner Server
    • Open Windows Explorer and go to C:\Program Files\ScriptRunner\Service\Support
    • Copy the Script ExtDb_UpdateTables.sql
    • Connect to the SQL Server
    • Place the copied script on the server
  2. Open the SQL Server Management Studio
  3. In the DB menu bar, navigate to Open File
    Note: Make sure that the correct database is selected before running

    Control of Database

    • Select the copied Script ExtDb_UpdateTables.sql
    • Click on Open

      Open script: Update Tables

    • Click on Execute
    • In the footer of the Management Studio you will receive a feedback (“Query was successfully executed”).
  4. By querying the Table dbo.BaseEntities_AppConfigSet the schema version can be viewed
    Note: The schema version may differ from the screenshot.

    AppConfigSet: Schema Version

Suggest Edit