PowerShell: Microsoft SQL

Overview

This connector reads Microsoft SQL Server tables by running a PowerShell script on a Windows host instead of connecting from the ReadyWorks application server. SQL Server is the dominant relational database on Windows estates and the back end of most Windows-based management tooling, including Configuration Manager site databases and the majority of Windows ITSM, CMDB and asset platforms.

The connection names the SQL Server instance, a port (the default is 1433), an optional source database, optional credentials, and a temporary working path on the Windows host. Each job’s Data Selection field carries the T-SQL statement to run, and the result set is written to the named staging table. The script CX_MSSQL.ps1 performs the connection and the query.

The deployment difference is the point of this connector. Running from a Windows host puts the query inside the Windows security and network boundary, which matters for two reasons. It avoids exposing a database port to the ReadyWorks application server when the instance sits in a restricted segment. And because Username and Password are optional on this form, where the direct Microsoft SQL connector requires them, it offers a path for instances that do not accept SQL Server authentication logins.

The connector is inbound only. It reads query results and writes staging tables, and it issues no writes to the source database.

NOTE: windows_only is set on this connector. The ReadyWorks ETL host that runs these jobs has to be a Windows machine with PowerShell and a SQL client available to it, and it needs network reachability to the SQL Server instance. A Linux-hosted ReadyWorks deployment cannot run this connector.

Connector Properties

Authentication Methods

One method ships: a PowerShell connection to a SQL Server instance from the Windows ETL host.

NOTE: Source DB Name, Username and Password are all optional here, where the direct Microsoft SQL connector requires all three. Leaving the credentials blank means the script connects under whatever Windows identity the ETL process runs as, so confirm with your DBA which login the run actually presents and that it holds the read permissions the query needs.

NOTE: Source DB Name is stored in the auth_data_node column, which is labelled Authentication Data Node on other connectors. The value is the database name.

Method 1: Username / Password (RWS_MSSQL_17bb6b78c0)

Connect to a MS SQL Server

Username / Password runs CX_MSSQL.ps1 against the instance in Source Server on the port in Source Server Port (the default is 1433). Username, Password and Source DB Name are all optional on this form. Server Temporary Working Path is required and names the directory on the Windows host where the script stages its output.

Connection Configuration Fields (6)

Authentication Configuration

cURL options:

Inbound Job Fields Enabled (13)

These are the job-level settings exposed for a PowerShell Microsoft SQL extract. Data Selection holds the T-SQL statement.

Inbound Job Templates (1)

One inbound template ships. It is enabled by default and carries a placeholder query, so deal with it before the first scheduled run.

Job Template Configuration

Settings

Job Parameters and Enumeration

MS SQL Table Data

Data Selection: SELECT * FROM {tablename}

Outbound Job Templates (0)

This connector ships no outbound templates and does not support outbound jobs.