Bulk-Adding Proxied Database Devices with add-sql-proxied-devices.ps1

[pavel.palenzuela]

Bulk-Adding Proxied Database Devices with add-sql-proxied-devices.ps1

Applies to: Netwrix Change Tracker (Gen7 API)
Script files: add-sql-proxied-devices.ps1, gen7-utilities.ps1, add-sql-proxied-devices.csv

Overview

Adding proxied SQL Server or Oracle database devices to Change Tracker one at a time through the UI is slow when you’re onboarding dozens or hundreds of databases at once. add-sql-proxied-devices.ps1 automates this by reading a CSV file - one row per database - and registering each one as a proxied device through the Change Tracker API.

Despite the “sql” in the filename, the script supports both SQL Server and Oracle databases. The name is a holdover from the script’s original scope; it now builds the correct connection string for whichever DatabasePlatform you specify per row.

This article covers:

• What the script does and how it works
• The four connection string scenarios it supports (SQL Server standard, SQL Server Windows Authentication, Oracle direct, Oracle TNS)
• CSV field reference
• Step-by-step usage instructions
• Verifying results in the Change Tracker UI
• Common troubleshooting tips

Use cases

This script is a good fit when you need to:

• Onboard a batch of databases during initial deployment - e.g., a customer with 50 SQL Server instances and 20 Oracle databases across several proxy agents.
• Standardize how databases are added - the CSV forces consistent field entry (credential keys, group names, connection types) instead of manual UI entry that’s prone to typos.
• Re-run the same registration after a CSV update - useful when a customer sends you a spreadsheet of database inventory and you need to translate it into Change Tracker devices with minimal manual work.
• Mix connection types in a single run - SQL Server (standard or Windows Authentication) and Oracle (direct or TNS) rows can all live in the same CSV; the script figures out the right connection string per row.

It is not intended for editing devices that already exist - it only calls the agents/register (create) endpoint. Running it twice against the same DeviceName will show and error.

How it works

For each row in the CSV, the script:

1. Looks up the named proxy agent (ProxyAgentName) via the Change Tracker API to get its AgentId. Lookups are cached, so if 50 rows share the same proxy, only one API call is made for that proxy.
2. Builds the correct ConnectionString based on DatabasePlatform, ConnectionCredentialType, and a few platform-specific flags (see below).
3. Resolves any GroupNames from their display name to their internal Change Tracker name.
4. Calls POST /agents/register to create the device as a proxied database, attaching it to the resolved groups.
5. Prints a per-row result and a final summary (Total, Success, Failed).

Sample run:

Connection string scenarios

The script builds one of four connection string formats depending on the CSV row. This is the part of the script most likely to need explaining to a customer, so it’s worth understanding each case.

1. SQL Server - standard (SQL login)

Used when DatabasePlatform = SqlServer and WindowsAuth is False or blank.

Server=<IPAddress>;
Server=<IPAddress>,<Port>;
Server=<IPAddress>,<Port>; <AdditionalParameters>

• Port is only included if Port is populated in the CSV.
• AdditionalParameters is optional free text appended after the server clause - e.g. ApplicationIntent=READONLY; for an Always On read-only replica.
• Authentication uses the SQL login stored in the credential referenced by CredentialKey.

2. SQL Server - Windows Authentication

Used when DatabasePlatform = SqlServer and WindowsAuth = True.

Data Source=<IPAddress>;Initial Catalog=<DBName>;Trusted_Connection=True;[TrustServerCertificate=True;]

• IPAddress holds the SQL Server hostname or IP (or HOST\INSTANCENAME for a named instance).
• DBName holds the target database (catalog) name.
• TrustServerCertificate=True; is appended only when the CSV’s TrustServerCertificate column is True - needed when the target SQL Server presents a self-signed certificate.
• The credential referenced by CredentialKey must have blank username and password fields. Windows Authentication uses the identity the Change Tracker agent/proxy service is running as, not a stored SQL login.
• Port and AdditionalParameters are ignored for this scenario.

Example result in the Edit Device dialog:

3. Oracle - direct connection

Used when DatabasePlatform = Oracle and DirectConnection = True.

Server=<IPAddress>; Port=<Port>; Direct=True;

Example result in the Edit Device dialog:

4. Oracle - TNS connection

Used when DatabasePlatform = Oracle and DirectConnection = False (or blank).

Data Source=<DBName>

• DBName holds the TNS alias as defined in the tnsnames.ora file on the machine resolving the connection (typically the proxy agent).
• IPAddress and Port are ignored for this scenario - the TNS alias resolves the actual host/port/service internally.

CSV field reference

Column	Required	Applies to	Description
ProxyAgentName	Yes	All	Name of the existing Change Tracker agent that will proxy this database.
DeviceName	Yes	All	The device name to create in Change Tracker.
DBName	Yes	All	Meaning varies by scenario - see below.
IPAddress	Yes	All	Meaning varies by scenario - see below.
Port	Conditional	SQL Server standard, Oracle direct	Database port. Ignored for Windows Auth and Oracle TNS.
CredentialKey	Yes	All	Name of an existing Change Tracker database credential. Must be blank username/password for Windows Auth.
ConnectionCredentialType	Yes	All	V1 (Legacy) or V2. V1 ignores DatabasePlatform and ConnectionString entirely.
TrustedConnection	Yes	All	True/False - passed through to the API’s TrustedConnection field (distinct from Windows Auth; check your platform’s requirements).
DatabasePlatform	Yes (V2 only)	All	SqlServer, Oracle, or other supported platforms.
GroupNames	Yes	All	Pipe-separated list of group display names, e.g. `test1
DirectConnection	Conditional	Oracle only	True = direct connection, False/blank = TNS connection.
AdditionalParameters	No	SQL Server standard only	Extra connection string parameters appended verbatim.
WindowsAuth	Conditional	SQL Server only	True = Windows Authentication connection string.
TrustServerCertificate	No	SQL Server + WindowsAuth=True only	True appends TrustServerCertificate=True;.

DBName and IPAddress meaning by scenario

Because these two columns are reused across scenarios, their meaning changes:

Scenario	IPAddress means	DBName means
SQL Server standard	Server IP/hostname	Not used in connection string (used as IPv4/HostName in the API payload)
SQL Server Windows Auth	Server IP/hostname (Data Source)	Catalog/database name (Initial Catalog)
Oracle direct	Server IP/hostname	Not used in connection string
Oracle TNS	Not used in connection string	TNS alias (Data Source)

Sample CSV with one SQL Server (Windows Auth) row and one Oracle (direct) row:

Prerequisites

Before running the script, confirm:

1. gen7-utilities.ps1 is in the same folder as add-sql-proxied-devices.ps1.
2. Each ProxyAgentName already exists in Change Tracker as an agent capable of proxying (CanProxy=True).
3. Each CredentialKey already exists as a Database credential in Change Tracker. For Windows Authentication rows, this credential must have blank username and password.
4. Each group listed in GroupNames already exists in Change Tracker. The script resolves display names to internal names but does not create missing groups.
5. You have Change Tracker admin credentials to run the script with.

Running the script

1. Place add-sql-proxied-devices.ps1, gen7-utilities.ps1, and your completed add-sql-proxied-devices.csv in the same folder.

2. Open PowerShell and navigate to that folder.

3. Run:

   .\add-sql-proxied-devices.ps1
   

4. When prompted, enter:

   • The Change Tracker host URL (e.g. https://192.168.1.1/api)
   • An admin username
   • An admin password

5. The script processes each row and prints progress, then a final summary.

Verifying results

After the script completes, confirm the devices in the Change Tracker UI:

• The device list shows each new database device with its assigned group(s):

  

• Open each device’s Edit dialog to confirm the connection string matches what you expected for that scenario (see the screenshots in the “Connection string scenarios” section above).

Troubleshooting

Symptom	Likely cause
ERROR: Proxy agent '<name>' not found. Skipping row.	The ProxyAgentName doesn’t exist, is misspelled, or the agent isn’t proxy-capable (CanProxy=True).
ERROR (Bad Request) with a JSON body	The API rejected the payload - check the printed JSON for the specific field. Common causes: CredentialKey doesn’t exist, a group in GroupNames doesn’t exist, or the connection string is malformed for the platform.
Windows Auth device fails to connect (after successful registration)	Confirm the credential has blank username/password, and that the Change Tracker agent/proxy’s Windows service account has access to the target SQL Server. Consider setting TrustServerCertificate=True if the server uses a self-signed certificate.
Oracle TNS device fails to connect	Confirm the TNS alias in DBName exists in the tnsnames.ora used by the proxying agent, and that the agent can resolve it.
Rows silently use the wrong connection format	Double-check DatabasePlatform, DirectConnection, and WindowsAuth spelling/casing - the script matches the literal string "True".

Related files

• add-sql-proxied-devices.ps1 - main script
• gen7-utilities.ps1 - shared helper functions (host/session handling); edit this to set defaults if desired
• add-sql-proxied-devices.csv - sample/template CSV; copy and edit per deployment

add-sql-proxied-devices.csv (310 Bytes)

gen7-utilities.ps1 (2.5 KB)

add-sql-proxied-devices.ps1 (13.7 KB)