Overview

Microsoft SQL Server is the dominant relational database on Windows estates and the back end of most of the management tooling that runs on them. Configuration Manager holds its hardware and software inventory in a SQL Server site database, and so do the majority of Windows-based ITSM, CMDB, asset management, HR and finance applications. For many organisations it is the single richest source of device and application data that ReadyWorks can reach without an API.

The ReadyWorks connector connects directly to a SQL Server instance over the network using the mssql.php driver and a SQL login. A job carries a T-SQL statement in its Data Selection field, ReadyWorks runs it, and the result set is written to the destination staging table with one column per selected column. Where a product exposes reporting views over its schema, those views are usually the right thing to query.

This is how ReadyWorks reads inventory that the vendor’s API either does not expose or exposes too slowly to be useful. A Configuration Manager site database answers questions about installed software, hardware specification and last hardware scan that no agent-level API answers as cheaply. Staged against Active Directory computer objects and the CMDB, it becomes the backbone of a device refresh or an operating system migration programme.

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

NOTE: Give ReadyWorks a dedicated login with read access only to the tables or views it needs. Query reporting views rather than base tables where the product provides them, since base table schemas change between product releases.

Connector Properties

Authentication Methods

One method ships: a direct network connection with a SQL Server login and password.

NOTE: This method requires a SQL Server authentication login. The form has no option for Windows Integrated authentication, so an instance configured for Windows authentication only cannot be read through this connector. Either enable mixed mode and create a SQL login, or use the Powershell: Microsoft SQL connector, which runs on a Windows host.

NOTE: The form has no field for TLS, certificate trust or instance encryption settings. Place the ReadyWorks server and the SQL Server on a network path you trust.

Method 1: Username / Password (MS SQL_user)

Connect to a MS SQL Server

Username / Password connects to the SQL Server instance named in Source Server on the port given in Source Server Port (the default is 1433), authenticates with the supplied credentials, and selects the database named in Source DB Name. All five fields are required. The password is stored encrypted.

Connection Configuration Fields (5)

No authentication recipe is stored. The driver script authenticates natively using the fields above.

Inbound Job Fields Enabled (13)

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

Inbound Job Templates (1)

One inbound template ships as a worked example. It arrives disabled and carries a placeholder query, so it is a pattern to copy rather than a job to enable.

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.