Connect a Local Gateway to a Data Source

A data source contains the name, connection string, and database provider for the database of your choice. You can set up a PostgreSQL, SQL, Oracle, OleDb, MySQL, ODP.net, or Microsoft ODBC connection. The data source is configured using the Local Gateway Configuration Utility. The utility was installed as part of the Smart Integration Connector Local Gateway installation.

  1. Start the OneStream Local Gateway Configuration.

  2. The existing XFGatewayConfiguration.xml opens by default.

  3. Click More next to Local Gateway Connections to set up the Data Sources to local databases, APIs, or other on-premises resources.

  4. Click Add Item to add a new data source.

  5. If you have a password for the connection string, enter it in the Connection String Password field. The password is masked for security. Then, when you need to enter your connection string password, use the substitution variable: |password|
    Example: Data Source=localhost;Initial Catalog=Sales_DB;Persist Security Info=True;User ID=sa;Password=|password|;

  6. Enter the Data Source Name, Connection String, and select a Database Provider.

    NOTE: For security purposes, we recommend using the Connection String Password field and the substitution variable to ensure the password is not shown on screen. However, you can also embed the password directly within your connection string. For example: Server = localhost;Port=3306;uid=root;pwd=my_password;database=gatewaymysql;.

    You can add as many data sources as necessary. The Data Source Name must be unique for each connection defined within a specific OneStream Smart Integration Connector Local Gateway Server. Names can be re-used across deployed instances of the Windows Service across your network. See below for connection string examples to a variety of relational data sources such as PostgreSQL, SQL, and ODBC, and Oracle. Connection Strings are encrypted automatically. You can edit the plain text string by clicking the ellipsis.

    NOTE: Oracle databases require drivers and specific configuration provided by Oracle.

  1. Click OK to save your configuration.

    IMPORTANT: The connection strings below include user IDs and the password substitution variable. You can also use integrated security to remove plain text user IDs and passwords from connection strings in Smart Integration Connector. See Remove UserID and Passwords by Integrated Security.

Microsoft SQL Server

Below is an example for setting up a SQL database using the SqlClient provider.

  1. Click More next to Local Gateway Connections.

  2. Click Add Item to add the data source.

  3. Data Source Name: Northeast_Sales

  4. Connection String:
    with UserID / Password:
    Server=localhost;Initial Catalog=Sales_DB;User ID=sa;Password=|password|;Max Pool Size=1000;Connect Timeout=60;

  5. Enter your Connection String Password.

    NOTE: For security purposes, we recommend using the Connection String Password field and the substitution variable to ensure the password is not shown on screen. However, you can also embed the password directly within your connection string. For example: Server = localhost;Port=3306;uid=root;pwd=my_password;database=gatewaymysql;.

  6. From Database Provider, select SqlClient Data Provider.

  7. Click Test Connection to test the data source.

  8. Click OK to save.

MySQL Data Provider

Below is an example for setting up a MySQL Data Provider.

  1. Click More next to Local Gateway Connections.

  2. Click Add Item to add a new data source.

  3. Data Source Name: Sales_UK

  4. Connection String:
    Server = localhost;Port=3306;uid=root;pwd=|password|;database=gatewaymysql;

  5. Enter your Connection String Password.

    NOTE: For security purposes, we recommend using the Connection String Password field and the substitution variable to ensure the password is not shown on screen. However, you can also embed the password directly within your connection string. For example: Server = localhost;Port=3306;uid=root;pwd=my_password;database=gatewaymysql;.

  6. From Database Provider, select MySQL Data Provider.

  7. Click Test Connection to test the data source.

  8. Click OK to save.

Oracle Database Examples

Connecting to Oracle requires the download and configuration of the Oracle Data Access Components (ODAC) obtained directly from Oracle’s website. Follow the steps below to get access to these drivers and files.

  1. Go to the latest web page for Oracle .NET and Visual Studio ODAC Downloads for Oracle Database.

  2. After installation, the ODP.NET Provider will display as an available Database Provider in the utility when adding a new data source.

  3. The connection string for Oracle databases can be set up to either reference or require a locally defined tnsnames.ora file for the requested data sources.

Example Connection Strings:

  • Oracle Data Provider for .NET: Data Source=oracletest;User Id=OneStream1;Password=|password|;

  • Oracle Data Provider without TNSNames.ora: Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID))); User Id=myUsername;Password=|password|;

OracleClient Database Provider

Below is an example for setting up a OracleClient database provider.

  1. Click More next to Local Gateway Connections.

  2. Click Add Item to add the data source.

  3. Data Source Name: Sales_EMEA

  4. Connection String: Data Source=oracletest;User Id=OneStream1;Password=|password|

  5. Enter your Connection String Password.

    NOTE: For security purposes, we recommend using the Connection String Password field and the substitution variable to ensure the password is not shown on screen. However, you can also embed the password directly within your connection string. For example: Server = localhost;Port=3306;uid=root;pwd=my_password;database=gatewaymysql;.

  6. From Database Provider, select OracleClient Data Provider.

  7. Click Test Connection to test the data source.

  8. Click OK to save.

Oracle Data Provider for .NET

Below is an example for setting up a Oracle Data Provider for .NET.

  1. Click More next to Local Gateway Connections.

  2. Data Source Name: Sales_SouthAmerica

  3. Connection String:
    Data Source=oracletest;User Id=OneStream1;Password=|password|

  4. Enter your Connection String Password.

    NOTE: For security purposes, we recommend using the Connection String Password field and the substitution variable to ensure the password is not shown on screen. However, you can also embed the password directly within your connection string. For example: Server = localhost;Port=3306;uid=root;pwd=my_password;database=gatewaymysql;.

  5. From Database Provider, select Oracle Data Provider for .NET.

  6. Click Add Item to add a new data source.

  7. Click Test Connection to test the data source.

  8. Click OK to save.

PostgreSQL (Npgsql Data Provider)

Below is an example for setting up a PostGres database.

  1. Click More next to Local Gateway Connections.

  2. Click Add Item to add the data source.

  3. Data Source Name: RevenueMgmtPostGres

  4. Connection String: Server=localhost;Port=5432;Database=revmgt;User Id=onestream;Password=|password|;

  5. Enter your Connection String Password.

    NOTE: For security purposes, we recommend using the Connection String Password field and the substitution variable to ensure the password is not shown on screen. However, you can also embed the password directly within your connection string. For example: Server = localhost;Port=3306;uid=root;pwd=my_password;database=gatewaymysql;.

  6. From Database Provider, select Npgsql Data Provider.

  7. Click Test Connection to test the data source.

  8. Click OK to save.

OleDb Data Provider

Below is an example for setting up an Oracle database. This does not require additional download and configurations.

  1. Click More next to Local Gateway Connections.

  2. Click Add Item to add the data source.

  3. Data Source Name: Sales_Asia

  4. Connection String: Provider=OraOLEDB.Oracle;Data Source=localhost:1521/XE;Initial Catalog=myDataBase;User Id=myUsername;Password=|password|;

  5. Enter your Connection String Password.

    NOTE: For security purposes, we recommend using the Connection String Password field and the substitution variable to ensure the password is not shown on screen. However, you can also embed the password directly within your connection string. For example: Server = localhost;Port=3306;uid=root;pwd=my_password;database=gatewaymysql;.

  6. From Database Provider, select OleDb Data Provider.

  7. Click Test Connection to test the data source.

  8. Click OK to save.

ODBC Data Provider

ODBC data sources can be defined (using a system DSN) to remove credentials from the configuration file. For ODBC connections, most ODBC drivers will allow you to set up a system DSN entry on the server, then the connection string in the gateway will be to only point to the DSN entry. See Administer ODBC data sources for more information. Below is an example for setting up an ODBC data source for Oracle.

  1. Click More next to Local Gateway Connections.

  2. Click Add Item to add the data source.

  3. Data Source Name: Sales_Europe

  4. Connection String: Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=199.199.199.199)(PORT=1523))(CONNECT_DATA=(SID=dbName)));Uid=myUsername;Pwd=|password|;

  5. Enter your Connection String Password.

    NOTE: For security purposes, we recommend using the Connection String Password field and the substitution variable to ensure the password is not shown on screen. However, you can also embed the password directly within your connection string. For example: Server = localhost;Port=3306;uid=root;pwd=my_password;database=gatewaymysql;.

  6. From Database Provider , select Odbc Data Provider.

  7. Click Test Connection to test the data source.

  8. Click OK to create the new source.

  9. Click Save.

(Optional) Remove UserID and Passwords by Integrated Security

You can remove UserIDs and Passwords from connection strings in Smart Integration Connector if your organization has concerns over credential storage in the Smart Integration Connector Gateway configuration file. This requires running the Windows Service under a Service Account identity and using integrated security to connect to remote data sources, which eliminates local storage of any plain text credentials. Additionally, ODBC data sources can be defined (using a system DSN) to remove credentials from the configuration file.

Update the Local Gateway Connection String

  1. Open your OneStream Local Gateway Configuration.

  2. Open a Local Gateway Connection.

  3. Navigate to the Connection String and use an Integrated or Trusted Security string. For example: Data Source=localhost,Initial Catalog=OneStream_GolfStreamDemo_2022;Trusted_Connection=True;

    NOTE: Trusted Connections use the UserID and password you use to log into the Windows Server.

    NOTE: The example above is for SQL server. Trusted connections vary by Data Provider type.

  4. Click OK.

  5. Save your Data Source.

Update Permissions on the OneStream Smart Integration Connector Gateway Service

Next, you need to update the service to run as the user. If the service is not updated, the connection does not update and errors will occur.

  1. Open Windows Services.

  2. Navigate to OneStream Smart Integration Connector Gateway. The service should be running.

  3. Right-click and open Properties.

  4. Click the Log On tab. Typically, this will default to the Local System account.

    IMPORTANT: Before moving to the next step, ensure that you have the appropriate permissions and approvals from your IT Administrators to complete the Log On change. The service account used will require local Administrative rights to access resources on the Windows server, such as the machine certificate store and private keys used for encryption. This account will also require the appropriate permissions to access the database such as Microsoft SQL Server.

  5. Change log on from Local System account to This account and enter your domain or login that has access to the data source. Depending on how your SSO is configured, your account could require your domain name, UserID, and password. Contact your IT Administrator if you have questions about your account domain.

  6. Click Apply.

  7. Click OK.

  8. Right-click and select Restart to restart and update the service.

Test the Updated Integrated Connection String

You should test your connection through a Data Adapter query to verify your access to Smart Integration Connector. An alternate SQL Query to pulling the first 10-50 rows is sufficient. See Data Adapters Example.

Microsoft Entra Authentication for Azure SQL

The ability to use Microsoft Entra using service principal authentication to access Azure SQL is supported.

  1. Open your OneStream Local Gateway Configuration.

  2. Open a Local Gateway Connection.

  3. Enter a Data Source Name of MicrosoftEntra.

  4. Navigate to the Connection String and enter a connection string. Example: Server=demo.database.windows.net; Authentication=Active Directory Service Principal; Encrypt=True; Database=testdb; User Id=AppId; Password=|password|;

  5. Enter your Connection String Password.

    NOTE: For security purposes, we recommend using the Connection String Password field and the substitution variable to ensure the password is not shown on screen. However, you can also embed the password directly within your connection string. For example: Server = localhost;Port=3306;uid=root;pwd=my_password;database=gatewaymysql;.

  6. Select MS Data SQL Provider as your Database Provider.

  7. Click Test Connection to test the data source.

  8. Click OK.

  9. Click Save.