Driver configurations

You can set driver configurations using the microsoft.sparkodbc.ini file which can be found in the ODBC Drivers\Simba Spark ODBC Driver directory. The absolute path of the microsoft.sparkodbc.ini directory depends on whether you are using Power BI Desktop or on-premises Power BI Gateway:

• Power BI Desktop: C:\Program Files\Microsoft Power BI Desktop\bin\ODBC Drivers\Simba Spark ODBC Driver\microsoft.sparkodbc.ini
• Power BI Gateway: m\ODBC Drivers\Simba Spark ODBC Driver\microsoft.sparkodbc.ini, where m is placed inside the gateway installation directory.

Set driver configurations

1. Check if the microsoft.sparkodbc.ini file was already created. If it is then jump to step 3.
2. Open Notepad or File Explorer as Run As Administrator and create a file at ODBC Drivers/Simba Spark ODBC Driver/microsoft.sparkodbc.ini.
3. Add the new driver configurations to the file below the header [Driver] by using the syntax =. Configuration keys can be found in the manual provided with the installation of the Databricks ODBC Driver. The manual is located at C:\Program Files\Simba Spark ODBC Driver\Simba Apache Spark ODBC Connector Install and Configuration Guide.html.

Configuring a proxy

To configure a proxy, add the following configurations to the driver configuration in the microsoft.sparkodbc.ini file:

[Driver]
UseProxy=1
ProxyHost=<proxy.example.com>
ProxyPort=<port>
ProxyUID=<username>
ProxyPWD=<password>

Depending on the firewall configuration it might also be necessary to add:

[Driver]
CheckCertRevocation=0

Troubleshooting

Error: SSL_connect: certificate verify failed

When SSL issues occur, the ODBC driver returns a generic error SSL_connect: certificate verify failed. You can get more detailed SSL debugging logs by setting in the ODBC Drivers/Simba Spark ODBC Driver/microsoft.sparkodbc.ini file the following two configurations:

[Driver]
AllowDetailedSSLErrorMessages=1
EnableCurlDebugLogging=1

Diagnose issues by analyzing CryptoAPI logs

Most issues can be diagnosed by using Windows CryptoAPI logs, which can be found in the Event Viewer. The following steps describe how to capture these logs.

1. Open Event Viewer and go to Applications and Services Logs > Microsoft > Windows > CAPI2 > Operational.
2. In Filter Current Log, check the boxes Critical, Error, and Warning and click OK.
3. In the Event Viewer, go to Actions > Enable Log to start collecting logs.
4. Connect Power BI to Azure Databricks to reproduce the issue.
5. In the Event Viewer, go to Actions > Disable Log to stop collecting logs.
6. Click Refresh to retrieve the list of collected events.
7. Export logs by clicking Actions > Save Filtered Log File As.

Diagnose Build Chain or Verify Chain Policy event errors

If the collected logs contain an error on the Build Chain or Verify Chain Policy events, this likely points to the issue. More details can be found by selecting the event and reading the Details section. Two fields of interest are Result and RevocationResult.

• The revocation status of the certificate or one of the certificates in the certificate chain is unknown.
  • CAPI2 error: RevocationResult: [80092013] The revocation function was unable to check revocation because the revocation server was offline.
  • Cause: The revocation check failed due to an unavailable certificate revocation server.
  • Resolution: Disable certificate revocation checking.
• The certificate chain is not complete.
  • CAPI2 error: Result: [800B010A] A certificate chain could not be built to a trusted root authority.
  • Cause: The certificate advertised by the VPN or proxy server is incomplete and does not contain a full chain to the trusted root authority.
  • Resolution: The preferred solution is to configure the VPN or proxy server to advertise the full chain. If this is not possible, a workaround is to obtain the intermediate certificates for the Databricks workspace, and install these in the Intermediate Certification Authorities store, to enable Windows to find the unadvertised certificates. If possible, it is recommended to install these certificates for all Power BI users using a group policy in Windows. This has to be set up by the system administrator.

Certificate configurations

Disable certificate revocation checking

If the ODBC driver is unable to reach the certificate revocation list server, for example because of a firewall configuration, it will fail to validate the certificate. This can be resolved by disabling this check. To disable certificate revocation checking, set the configuration CheckCertRevocation=0 to the microsoft.sparkodbc.ini file.

Install intermediate certificates

1. Open your Azure Databricks workspace URL in Chrome and go to View site information by clicking the padlock icon in the address bar.
2. Click Certificate > Certificate Path and repeat steps 3 to 6 for every intermediate certificate in the chain.
3. Choose an intermediate certificate and go to Details > Copy to File > Next to export the certificate.
4. Select the location of the certificate and click Finish.
5. Open the exported certificate and click Install Certificate > Next.
6. From the Certificate Import Wizard click Place all certificates in the following store > Browse and choose Intermediate Certification Authorities.