Connect from Power BI to Azure SQL Database using AAD authentication


Currently Power BI doesn’t support AAD authentication when connecting to Azure SQL Database, but using Microsoft ODBC Driver 13 it is possible to do it.

You can download the driver from here (ODBC Driver 13.1) and install it.

After installing the driver, you can connect to the Azure SQL using ODBC connector.

1

Select ODBC and introduce the connection string that should have the following format:

Driver={ODBC Driver 13 for SQL Server};Server=tcp: <Server Name > ,1433;Database=<Database name>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword

2

You will be requested to introduce the user and password for the database.

3

You can find more information about AAD authentication and users here.

Now you are ready to build your report and publish it to the Power BI Service.

If you want to keep your data updated on the Power BI Service you will need to use the Power BI Gateway.   

For using the Gateway you need to add the Data Source on the Mange Gateways menu.

4

For the connection string use the same connection string as in the Power BI Desktop file:

Driver={ODBC Driver 13 for SQL Server};Server=tcp: <Server Name > ,1433;Database= <Database name>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword

Now you will be able to select the gateway and perform the refresh.

 

Beatriz García

@BeRoces

 


Comments (7)

  1. Antonio Abalos says:

    Thanks Beatriz for an interesting post. I have tried to connect from Power BI Desktop (x64) to Azure SQL it and did not work.
    Found that a newer version of the driver (msodbcsql.msi) was already installed in my PC (maybe because I have SSMS 2017) and I have had to install two more small applications (msoidcli_64bit.msi and adalsql.msi).

    However, I wonder what are we supposed to enter in the Database authentication window. Is that our corporate email or a domain user ID like domain\user?

    Details: “ODBC: ERROR [08001] [Microsoft][ODBC Driver 13 for SQL Server]SQL Server Network Interfaces: Unable to locate the registry entry for adalsql.dll file path. Verify that Active Directory Authentication Library for SQL Server is properly installed. For details, please refer to: http://go.microsoft.com/fwlink/?LinkID=513072
    ERROR [08001] [Microsoft][ODBC Driver 13 for SQL Server]Client unable to establish connection
    ERROR [01S00] [Microsoft][ODBC Driver 13 for SQL Server]Invalid connection string attribute”

    Thanks!!

  2. Antonio Abalos says:

    Wait!! I just tried one more time unistalling Power BI x64 and using Power BI x86 instead. Then it worked!!

    I have used my corporate email to authenticate myself.

    So in the end, it looks like ODBC can still confuse some people (including me), when multiple x32 and x64 tools and drivers are installed in the same PC.

    In any case, thanks for sharing this info Beatriz!!

    1. Clint says:

      Antonio, what do you mean you installed power bi x86, you mean the odbc driver , because I do not see a power bi desktop version x86?

  3. Antonio Abalos says:

    One more comment though. Datetimeoffset data types from Azure SQL are imported into Power BI as “binary”. Data is not understood.
    Lack of support in the ODBC driver?

    1. Antonio says:

      I got a response from the SQL Server team. We can build a Power BI function to parse the binary data that is not understood by Power BI. More details in the link below.
      https://community.powerbi.com/t5/Desktop/DateTimeOffset-data-type-not-understood-by-Power-BI-ODBC-Driver/td-p/225519

  4. RudyCo says:

    Details: “ODBC: ERROR [HY000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot open server “.com” requested by the login. The login failed.

  5. Thanks for the interesting article. Did you also try this in Direct Mode?

Skip to main content