Connection String Builder

1SQL Server — Windows Authentication (Trusted)

Uses the PAD runner's Windows identity. No username/password needed. Most common in enterprise environments where the service account has database access. Variables: varServer (SQL Server hostname or IP), varDatabase (database name), varCurrentMessage (logging), varConnectionString (output — connection string)

1
**REGION SQL Server — Windows Auth
2
SET varServer TO $'''sql-server-01.contoso.com'''
3
SET varDatabase TO $'''ProductionDB'''
4
 
5
SET varConnectionString TO $fx'Server=${varServer};Database=${varDatabase};Trusted_Connection=True;'
6
 
7
SET varCurrentMessage TO $fx'Connection string built (Windows Auth): ${varServer}/${varDatabase}'
8
**ENDREGION

2SQL Server — SQL Authentication

Uses a SQL Server login with username and password. Always pull credentials from Key Vault in production. Variables: varServer (SQL Server hostname), varDatabase (database name), varDbUsername (SQL login username), varDbPassword (SQL login password — use Key Vault), varCurrentMessage (logging), varConnectionString (output)

1
**REGION SQL Server — SQL Auth
2
SET varServer TO $'''sql-server-01.contoso.com'''
3
SET varDatabase TO $'''ProductionDB'''
4
SET varDbUsername TO $'''svc_rpa_sql'''
5
SET varDbPassword TO $'''REPLACE_WITH_KEYVAULT'''
6
 
7
SET varConnectionString TO $fx'Server=${varServer};Database=${varDatabase};User Id=${varDbUsername};Password=${varDbPassword};'
8
 
9
SET varCurrentMessage TO $fx'Connection string built (SQL Auth): ${varServer}/${varDatabase}'
10
**ENDREGION

3SQL Server — Named Instance with Port

Connects to a named SQL Server instance on a non-default port. Variables: varServer (server hostname), varInstance (instance name), varPort (port number), varDatabase (database name), varCurrentMessage (logging), varConnectionString (output)

1
**REGION SQL Server — Named Instance
2
SET varServer TO $'''sql-server-01.contoso.com'''
3
SET varInstance TO $'''MSSQLSERVER2019'''
4
SET varPort TO $'''1434'''
5
SET varDatabase TO $'''ProductionDB'''
6
 
7
SET varConnectionString TO $fx'Server=${varServer}\\\\${varInstance},${varPort};Database=${varDatabase};Trusted_Connection=True;'
8
 
9
SET varCurrentMessage TO $fx'Connection string built: ${varServer}\\\\${varInstance}:${varPort}'
10
**ENDREGION

4Oracle — TNS Name

Connects to an Oracle database using a TNS name configured in tnsnames.ora. Variables: varTnsName (Oracle TNS name), varOracleUser (Oracle username), varOraclePassword (Oracle password), varCurrentMessage (logging), varConnectionString (output)

1
**REGION Oracle — TNS Name
2
SET varTnsName TO $'''PRODDB'''
3
SET varOracleUser TO $'''svc_rpa_oracle'''
4
SET varOraclePassword TO $'''REPLACE_WITH_KEYVAULT'''
5
 
6
SET varConnectionString TO $fx'Data Source=${varTnsName};User Id=${varOracleUser};Password=${varOraclePassword};'
7
 
8
SET varCurrentMessage TO $fx'Oracle connection string built: ${varTnsName}'
9
**ENDREGION

5ODBC — Generic DSN

Uses a pre-configured ODBC Data Source Name (DSN). The DSN is set up in Windows ODBC Data Source Administrator. Variables: varDsnName (ODBC DSN name), varOdbcUser (username — empty for Windows Auth DSNs), varOdbcPassword (password), varCurrentMessage (logging), varConnectionString (output)

1
**REGION ODBC — Generic DSN
2
SET varDsnName TO $'''MyOdbcDsn'''
3
SET varOdbcUser TO $'''svc_rpa'''
4
SET varOdbcPassword TO $'''REPLACE_WITH_KEYVAULT'''
5
 
6
SET varConnectionString TO $fx'DSN=${varDsnName};Uid=${varOdbcUser};Pwd=${varOdbcPassword};'
7
 
8
SET varCurrentMessage TO $fx'ODBC connection string built: DSN=${varDsnName}'
9
**ENDREGION

6Test Connection via PowerShell

Tests the connection string by opening and immediately closing a SQL connection. Returns success or error. Use this to validate the connection string before running queries. Variables: varConnectionString (connection string from any pattern above), varCurrentMessage (logging), varTestOutput (PowerShell output), varTestError (PowerShell error)

1
**REGION Test Connection
2
SET varCurrentMessage TO $'''Testing database connection'''
3
 
4
Scripting.RunPowershellScript.RunScriptAndFailOnTimeout Script: $fx'try {\\\\n  $conn = New-Object System.Data.SqlClient.SqlConnection(\\\\\"${varConnectionString}\\\\\")\\\\n  $conn.Open()\\\\n  Write-Output \\\\\"Connected: $($conn.ServerVersion)\\\\\"\\\\n  $conn.Close()\\\\n} catch {\\\\n  Write-Error $_.Exception.Message\\\\n}' Timeout: $fx'=15' ScriptOutput=> varTestOutput ScriptError=> varTestError
5
ON ERROR REPEAT 1 TIMES WAIT 2
6
END
7
 
8
IF IsNotBlank(varTestError) THEN
9
    SET varCurrentMessage TO $fx'Connection test FAILED: ${varTestError}'
10
ELSE
11
    SET varCurrentMessage TO $fx'Connection test PASSED: ${varTestOutput}'
12
END
13
**ENDREGION

Variable Reference Summary

Notes

• Credentials in production. Patterns 2, 4, and 5 use placeholder passwords. Always retrieve from Azure Key Vault or Dataverse config table. Never store database credentials in PAD flow variables visible in the designer.
• Windows Auth (Pattern 1) is preferred. No credentials to manage — the PAD runner's service account authenticates directly. Requires the service account to have a SQL login and appropriate database permissions.
• Named instance backslash (Pattern 3). Inside $fx'...', a single \\\\ produces a literal backslash. The connection string Server=host\\INSTANCE becomes $fx'...${varServer}\\\\${varInstance}...'.
• Pattern 6 test uses SqlClient. This works for SQL Server. For Oracle, replace SqlClient.SqlConnection with OracleClient.OracleConnection (requires Oracle client installed). For ODBC, use Odbc.OdbcConnection.
• No ON ERROR on SET. Connection string building is pure string concatenation — no error handling needed. Pattern 6 (test connection) has ON ERROR on the PowerShell action.