Contents x
    Connection Strings
    • 13 Dec 2024
    • 3 Minutes to read
    • PDF
    Contents

    Connection Strings

    • Updated on 13 Dec 2024
    • 3 Minutes to read
    • PDF
    Article summary

    Setup Note

    The parameters of a connection string can change depending on the version of the database engine or driver. Reference your database provider's documentation for the correct connection string syntax if you do not see it listed here.

    Overview

    Connecting a data source to Validatar is done using connection strings. It specifies information about the data source and the means of connecting to it.

    The connection string you use depends on the database engine you're connecting to, the chosen Validatar connection type, and whether or not you decide to use Windows Authentication.

    Password Note

    Always encrypt your password using the placeholder [[PASSWORD]] in the connection string and typing your password in the provided password box.

    Validatar uses three different connection types: SQL Server, ODBC, and OLE DB.

    Connecting using SQL Server

    Use the SQL Server connection type to connect to SQL Server and Azure SQL databases. Note: Windows comes with the SQL Server driver already installed.

    To escape any special characters (semicolons, single-quote character, double-quote character):

    • The value must be enclosed in double quotation marks.

    • If the value contains both a semicolon and double-quote character, the value must be enclosed in single quotation marks.

    SQL Server Authentication

    Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=[[PASSWORD]];

    Windows Authentication

    Integrated Security specifies that you are using Windows Authentication. User credentials are not required. When using Integrated Security, acceptable values are 'true', 'false', 'yes', or 'no'.

    Server=myServerAddress;Database=myDatabase;Integrated Security=yes;

    Windows Authentication Note

    Validatar uses the service (domain) account Validatar is running under for Windows Authentication not the current user's domain credentials. Check with your web server admin and database admin to verify that the Validatar service account has read-access to the databases you want to connect to via Windows Authentication.

    Connecting to a SQL Server Instance

    If there are multiple instances on the server, you must identify the instance name in the connection string.

    Server=myServerName\myInstanceName;Database=myDatabase;User Id=myUsername;Password=[[PASSWORD]];

    Specifying a Port

    You can specify the port number after the server name.

    Server=myServerName,portNumber;Database=myDatabase;User Id=myUsername;Password=[[PASSWORD]];

    Synonyms

    Some connection string keywords are interchangeable with other synonyms.

    Keyword

    Synonym(s)

    Server

    Data Source, Address, Addr, Network Address

    Database

    Initial Catalog

    Integrated Security

    Trusted_Connection

    Password

    PWD

    User ID

    UID

    Connecting using ODBC

    Prerequisite Required

    The appropriate ODBC driver must be installed on the server before connecting to a data source. Be sure to use the correct driver name in the connection string.

    Netezza

    Driver={NetezzaSQL};Server=myServerAddress;Port=myPortNumber;Database=myDatabase;Username=myUsername;Password=[[PASSWORD]];

    Snowflake

    Driver={SnowflakeDSIIDriver};Server=myServerAddress;Warehouse=myWarehouse;Database=myDatabase;Uid=myUsername;Pwd=[[PASSWORD]];

    Optional parameters

    • Schema

    • Role

    Syntax Note

    Make sure there are no spaces between the parameter value and semicolon in the connection string, or the connection will fail.

    DB2

    Driver={IBM DB2 ODBC DRIVER};Hostname=myServerAddress;Port=50000;Protocol=TCPIP;Database=myDatabase;Uid=myUsername;Pwd=[[PASSWORD]];

    Oracle

    The simplest way to connect to an Oracle database is to create a system DSN on the server. The user must have admin rights to create a system DSN.

    • Open ODBC Data Sources 64-bit in Windows

    • Click the System DSN tab

    • Click Add

    • Select the Oracle ODBC driver

    • Fill out all relevant information

    • Create the DSN

    DSN=dsnName;Uid=myUsername;Pwd=[[PASSWORD]];

    PostgreSQL

    Driver={PostgreSQL};Server=xxx.xxx.xxx.xxx;Port=5432;Database=myDatabase;Uid=myUsername;Pwd=[[PASSWORD]];

    Teradata

    Driver={Teradata Database ODBC Driver 16.20};DBCName=xxx.xxx.xxx.xxx;UID=myUsername;PWD=[[PASSWORD]];

    Connecting using OLE DB

    Prerequisite Required

    The appropriate OLE DB driver must be installed on the server before connecting to a data source.

    Netezza

    Provider=NZOLEDB;Data Source=myServerAddress;Port=myPortNumber;Initial Catalog=myDatabase;User ID=myUsername;Password=[[PASSWORD]];

    DB2

    Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=xxx.xxx.xxx.xxx;Initial Catalog=myDatabase;Package Collection=myCollection;Default Schema=mySchema;User ID=myUsername;Password=[[PASSWORD]];

    Oracle

    Oracle Provider

    Provider=OraOLEDB;Data Source=myOracleDB;User Id=myUsername;Password=[[PASSWORD]];

    Microsoft Provider

    Provider=MSDAORA;Data Source=myOracleDB;User Id=myUsername;Password=[[PASSWORD]];

    PostgreSQL

    Provider=PostgreSQL OLE DB Provider;Data Source=myServerAddress;Location=myDatabase;User ID=myUsername;Password=[[PASSWORD]];

    Teradata

    Provider=TDOLEDB;Data Source=myServerAddress;User ID=myUsername;Password=[[PASSWORD]];

    Troubleshooting Errors

    Error

    Possible Solutions

    Invalid username or password

    Verify the credentials by connecting to the database via DBMS or creating a DSN. If using Windows Authentication, check with your server admin and database admin that the service account Validatar is running under has permissions to the appropriate databases.

    Could not connect to the database server

    If the timeout takes a couple of seconds this is probably an access issue. If the timeout takes a few more seconds this probably is a networking or firewall issue. Check with your networking team to ensure Validatar can connect to the database server.

    The connection string is incorrect

    Verify that all required parameters for that database engine are included in the connection string. If you're connecting to Snowflake, be sure there are no spaces between the parameter value and semicolon as this will cause the connection to fail.

    Was this article helpful?
    What's Next