Intended Audience

Users who are trying to connect either SQL Server Management Studio (SSMS) or Power BI to a SQL Server who are receiving an encryption error.

Problem

SQL Databases support encrypted connections. Using encrypted connections are a best practice and are being enabled on some of our databases with more sensitive information. There can be an issue when trying to connect to a SQL Server who requires encrypted connections when the client does not trust the cert used.

In Power BI, you may receive an error that looks like this:

In SSMS, you may receive an error that looks like this:

Solution


This solution should only be used on SQL Servers within our CWF network.

If using Power BI

  1. Search windows for "Environment Variables" from the windows search bar in the start menu

    There are two options that come up. One of them is for adding variables for the system and the other is to add variables to your account.

    Select the option to Edit environment variables for your account.

  2. Click New... to add a new environment variable for your user.


  3. Add a variable with the name "PBI_SQL_TRUSTED_SERVERS"

    1. The variable value is a comma separated list of server names. If you needed to connect to SQLProdDb and SQLProdDb2, you can enter "SQLProdDb1,SQLProdDB2"
    2. The asterisk (*) can be used as a wildcard for a server. In this example, "SQL*Db" would match SQLTestDb, SQLProdDb, SQLServerDb, etc.
  4. Click OK

After applying this setting, restart Power BI Desktop. You may also need to recreate your datasource connection through Home → Transform data → Data source settings.

If you have multiple Power BI desktop files open, you may need to close all of them before reopening.

If using SSMS

  1. On the Connection Screen, click on Options
  2. Go to the Connection Properties Tab
  3. Check both Encrypt connection and Trust server certificate
  4. Connect to the database