Cloud Connections

BimlStudio supports connections to various cloud data platforms. This guide covers configuration patterns for the most common cloud services.

Azure SQL Database

Using Microsoft OLE DB Driver

The recommended provider for Azure SQL Database is Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL):

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="AzureSQL"
      ConnectionString="Provider=MSOLEDBSQL;
        Data Source=your-server.database.windows.net;
        Initial Catalog=YourDatabase;
        Authentication=ActiveDirectoryInteractive;" />
  </Connections>
</Biml>

Authentication Options

Authentication Type	Connection String Parameter	Use Case
SQL Authentication	User ID=xxx;Password=xxx	Legacy applications
Azure AD Interactive	Authentication=ActiveDirectoryInteractive	Development
Azure AD Password	Authentication=ActiveDirectoryPassword;User ID=xxx;Password=xxx	Service accounts
Azure AD Service Principal	Authentication=ActiveDirectoryServicePrincipal;User ID=xxx;Password=xxx	CI/CD
Azure AD Managed Identity	Authentication=ActiveDirectoryManagedIdentity	Azure-hosted apps

SQL Authentication Example:

<OleDbConnection Name="AzureSQL"
  ConnectionString="Provider=MSOLEDBSQL;
    Data Source=your-server.database.windows.net;
    Initial Catalog=YourDatabase;
    User ID=your-username;
    Password=your-password;
    Encrypt=yes;" />

Managed Identity Example:

<OleDbConnection Name="AzureSQL"
  ConnectionString="Provider=MSOLEDBSQL;
    Data Source=your-server.database.windows.net;
    Initial Catalog=YourDatabase;
    Authentication=ActiveDirectoryManagedIdentity;" />

Azure Synapse Analytics

Dedicated SQL Pool

<Connections>
  <OleDbConnection Name="SynapseDedicated"
    ConnectionString="Provider=MSOLEDBSQL;
      Data Source=your-workspace.sql.azuresynapse.net;
      Initial Catalog=YourPool;
      Authentication=ActiveDirectoryInteractive;" />
</Connections>

Serverless SQL Pool

<Connections>
  <OleDbConnection Name="SynapseServerless"
    ConnectionString="Provider=MSOLEDBSQL;
      Data Source=your-workspace-ondemand.sql.azuresynapse.net;
      Initial Catalog=master;
      Authentication=ActiveDirectoryInteractive;" />
</Connections>

Snowflake

ODBC Connection

Snowflake requires the Snowflake ODBC driver. Download from Snowflake Downloads.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OdbcConnection Name="Snowflake"
      ConnectionString="Driver={SnowflakeDSIIDriver};
        Server=your-account.snowflakecomputing.com;
        Database=YOUR_DATABASE;
        Schema=PUBLIC;
        Warehouse=YOUR_WAREHOUSE;
        Role=YOUR_ROLE;" />
  </Connections>
</Biml>

Authentication Options

Password Authentication:

<OdbcConnection Name="Snowflake"
  ConnectionString="Driver={SnowflakeDSIIDriver};
    Server=your-account.snowflakecomputing.com;
    Database=YOUR_DATABASE;
    UID=your-username;
    PWD=your-password;" />

Key Pair Authentication:

<OdbcConnection Name="Snowflake"
  ConnectionString="Driver={SnowflakeDSIIDriver};
    Server=your-account.snowflakecomputing.com;
    Database=YOUR_DATABASE;
    UID=your-username;
    Authenticator=SNOWFLAKE_JWT;
    PRIV_KEY_FILE=C:\path\to\rsa_key.p8;" />

External Browser (SSO):

<OdbcConnection Name="Snowflake"
  ConnectionString="Driver={SnowflakeDSIIDriver};
    Server=your-account.snowflakecomputing.com;
    Database=YOUR_DATABASE;
    Authenticator=externalbrowser;" />

Databricks

Using ODBC Driver

Download the Databricks ODBC driver from the Databricks website.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OdbcConnection Name="Databricks"
      ConnectionString="Driver={Simba Spark ODBC Driver};
        Host=adb-xxxx.azuredatabricks.net;
        Port=443;
        HTTPPath=/sql/1.0/warehouses/your-warehouse-id;
        ThriftTransport=2;
        SSL=1;
        AuthMech=3;" />
  </Connections>
</Biml>

Personal Access Token Authentication

<OdbcConnection Name="Databricks"
  ConnectionString="Driver={Simba Spark ODBC Driver};
    Host=adb-xxxx.azuredatabricks.net;
    Port=443;
    HTTPPath=/sql/1.0/warehouses/your-warehouse-id;
    ThriftTransport=2;
    SSL=1;
    AuthMech=3;
    UID=token;
    PWD=your-personal-access-token;" />

Unity Catalog Connection

When connecting to Unity Catalog:

<OdbcConnection Name="DatabricksUnityCatalog"
  ConnectionString="Driver={Simba Spark ODBC Driver};
    Host=adb-xxxx.azuredatabricks.net;
    Port=443;
    HTTPPath=/sql/1.0/warehouses/your-warehouse-id;
    ThriftTransport=2;
    SSL=1;
    AuthMech=3;
    Catalog=your_catalog;
    Schema=your_schema;" />

Google BigQuery

Using ODBC Driver

Install the Simba ODBC Driver for Google BigQuery.

<Connections>
  <OdbcConnection Name="BigQuery"
    ConnectionString="Driver={Simba ODBC Driver for Google BigQuery};
      Catalog=your-project-id;
      OAuthMechanism=0;
      [email protected];
      KeyFilePath=C:\path\to\service-account-key.json;" />
</Connections>

Amazon Redshift

Using ODBC Driver

Download the Amazon Redshift ODBC driver from AWS.

<Connections>
  <OdbcConnection Name="Redshift"
    ConnectionString="Driver={Amazon Redshift (x64)};
      Server=your-cluster.region.redshift.amazonaws.com;
      Port=5439;
      Database=your-database;
      UID=your-username;
      PWD=your-password;
      SSL=true;" />
</Connections>

Connection Best Practices

Security

1. Never hardcode credentials in Biml files checked into source control
2. Use environment variables or configuration files for sensitive data
3. Prefer managed identities for Azure-hosted solutions
4. Use key vault references where supported

Parameterizing Connections

Use BimlScript to parameterize connection strings:

<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System" #>
<#
  var server = Environment.GetEnvironmentVariable("SQL_SERVER") ?? "localhost";
  var database = Environment.GetEnvironmentVariable("SQL_DATABASE") ?? "Dev";
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Target"
      ConnectionString="Provider=MSOLEDBSQL;
        Data Source=<#=server#>;
        Initial Catalog=<#=database#>;
        Integrated Security=SSPI;" />
  </Connections>
</Biml>

Testing Connections

Use BimlStudio's Connection Editor to test connections before building:

Troubleshooting

Common Issues

Provider not found:

• Ensure the correct ODBC/OLE DB driver is installed
• Verify 32-bit vs 64-bit driver matches your BimlStudio installation

Authentication failures:

• Verify credentials are correct
• Check firewall rules allow connection from your IP
• Ensure Azure AD tenant is correctly configured

Timeout errors:

• Increase connection timeout in connection string
• Check network connectivity
• Verify cloud service is accessible

Driver Installation

Platform	Driver	Download Location
Azure SQL	MSOLEDBSQL	Microsoft Download
Snowflake	Snowflake ODBC	Snowflake Downloads
Databricks	Simba Spark ODBC	Databricks Downloads
BigQuery	Simba BigQuery ODBC	Simba Downloads
Redshift	Amazon Redshift ODBC	AWS Downloads

Related Topics

• Microsoft Fabric Integration - Working with Fabric
• Creating a New Connection - Connection basics
• Configuring Project Settings - Project configuration