Authenticating Connections from the Client Side (Power BI & More)

When connecting to a data source using CData ODBC Drivers and Power BI Connectors, authentication is typically configured through the ODBC DSN settings screen. However, some users may prefer to authenticate directly from the client, such as Power BI or other reporting tools.

This article outlines a method for authenticating to a service or application directly from a client tool without relying on the ODBC DSN settings. It provides step-by-step instructions on configuring Salesforce connection settings within Power BI, but the principals apply to any data source or tool.

Dynamic authentication to Salesforce from Power BI without configuring ODBC DSN settings

The upcoming sections provide step-by-step instructions on configuring data connection settings within Power BI, using it as the client tool while accessing Salesforce as the data source.

Set Up ODBC DSN with Empty Credentials

Download and install the CData ODBC Driver for Salesforce.

When the DSN settings screen opens, you typically authenticate to Salesforce from this screen. However, since we want to authenticate from the client tool (Power BI), we configure only the minimum authentication settings here and leave the credentials empty.

DSN Configuration:

1. Auth Scheme: Basic
2. User: (leave blank)
3. Password: (leave blank)
4. Security Token: (leave blank)

At this stage, do not click the Test Connection button. Instead, click OK to save the settings.

Dynamically authenticate to Salesforce from Power BI and Connect via ODBC

• Open Power BI and navigate to the Get Data section.
• Select ODBC and choose the ODBC DSN name created in the previous step (e.g., "CData Salesforce Source") from the dropdown menu.
• The ODBC Driver Authentication screen will appear. Select the "Database" tab on the left side.
• Enter your Salesforce credentials as follows:
  • Username: Your Salesforce username
  • Password: Your Salesforce login password
  • Credential connection string properties (optional): SecurityToken=[your account's Security Token]
  
• Click Connect to authenticate dynamically to Salesforce using the credentials set in Power BI and establish the ODBC connection.

Once connected, you can seamlessly retrieve Salesforce data authenticated from the Power BI side.

Overwriting credentials from Power BI is possible

Even if you have previously connected to a specific Salesforce account using ODBC DSN, you can enter the credentials of a different Salesforce account on the Power BI client side. This will establish the connection based on the credentials entered in Power BI.

Caution: OAuth authentication is not an option in Power BI

It is to be noted that Power BI does not support OAuth authentication. If you configure the authentication scheme as "OAuth" instead of "Basic" in the ODBC DSN, you will encounter the following error in Power BI:

"ODBC: ERROR [08001] The driver requires a user prompt for OAuth authentication, which is not allowed by this application. Please use the DSN manager to authenticate before using the drivers in this application."

When connecting via ODBC on the client side, you can enter credentials directly. This applies to tools like Power BI and SAP Crystal Reports. You can use Basic authentication with just a Username and Password, and if additional properties such as a Security Token are required, you can easily add them to the client-side credentials screen.

Free Trial & More Information

CData supports ODBC connections to over 270 data sources. For data sources that utilize Basic authentication, dynamic authentication from the client side is possible and you can use this method as needed.

Get started with a free 30-day trial of Salesforce or any other ODBC Driver. As always, let us know if you have any questions during your evaluation, feel free to reach out. Our CData Support Team is always here to help!