I have seen a lot of questions in the Power BI Community asking how to connect to a SQL Server Read-Only or Secondary replica, and whilst this is not currently available in the Sql.Database M connector, it is available in the OLE DB connector.

NOTE: This could potentially be used for a whole host of other OLE DB providers

NOTE II: This only applies to Imported datasets at the time of writing this.

In my example i am going to be using the OLE DB Connector for SQL Server Native Client 11.0

I will explain below how to create the connection, as well as how to configure it in the Power BI On-Premise Data Gateway.

Connecting to the Data in Power BI using the OLE DB Connector

  • The first thing I do, is to connect to the data, by clicking on Get Data and then finding my OLE DB Connector
    • I then click on Connect
  • The great thing about Power BI, is that after I click on Connect, there is a button to Build the connection string, so I click on Build
  • This brings up the Data Link Properties Window
    • I click on SQL Server Native Client 11.0
    • I click on Next
  • I then put in my Server Name, select Use Windows NT Integrated Security and select my database
    • NOTE: Depending on how your Gateway authenticates to SQL Server will depend on what you put in here.
    • NOTE: I always put in the FQDN name for the SQL Server instance.
    • I then click on Test Connector to make sure that I can connect.
  • I then click on All, where it says Application Intent, it will default to READWRITE
    • NOTE: This is the IMPORTANT part where I want to make sure that it will use the Read-Only replica or secondary replica
    • I clicked on Application Intent and then clicked on Edit Value
    • I then clicked on Ok to complete the changes
  • And then I clicked on Ok again.
  • I can now see my connection string built for me
    • NOTE: If I scroll across I can see and confirm that I have set the Application Intent to ReadOnly
    • NOTE: Make a copy of the connection string above. This will be used when creating the data source in the On-Premise Data Gateway.
  • There is an option under Advanced Options, if you want to put in a SQL Statement, that is totally up to you, or you can click Ok.
  • I was then prompted for the OLE DB Provider credentials.
    • As far as I understand this is so that the credentials are stored separately from the connection string and not stored in clear text.
    • I selected Windows and then “Use my current credentials”
    • I then clicked Connect.
  • I was then prompted with the Navigator which allowed me to select the table that I wanted
    • I then selected the table that I wanted and clicked Edit
    • NOTE: I always prefer to Edit the queries in the Power Query Editor before loading them into the data model.
  • I then added it to my data model
  • And uploaded it to the Power BI Service.

Creating the Data Source in the Gateway in the Power BI Service

NOTE: You will have to ensure that you have got the permissions to add data sources to the Gateway in the Power BI Service.

  • I went into the Power BI Service and clicked on Manage Gateways.
  • I found my associated Gateway, then clicked on the Ellipses and selected Add Data Source
  • I then configured it with following:
    • Data Source Name
    • Data Source Type
    • Connection String (NOTE: You can get this from your PBIX file if you did not copy it from the earlier step
    • Authentication Method, I chose Windows. Yours possibly will be what you connected to in Power BI Desktop.
    • I then put in the username and password
    • Finally, under Advanced settings I selected Privacy Level to None
    • I then clicked Add
  • I then got the confirmation that it was successfully created
  • I then went into my App Workspace where I had uploaded my PBIX, then into the settings and configured it to use the Gateway.
    • I then clicked Apply.
  • I tested the refresh and it successfully refreshed
  • My final test was to sit with the DBA and ensure that when my data was being refreshed that it was reading from the read-only/secondary replica which it was.
    • I always suggest double checking to make sure that it is working as expected, and to not assume that it is working.

Conclusion

In this blog post I have showed how you can now connect and import data from a read-only or secondary SQL Server replica. This has really been great for some of my customers who want to ensure that the data refresh queries will not impact the data warehouses or production systems.

Please feel free to leave any comments or suggestions, I do enjoy getting them.

Additional Note

I needed to ensure that I had the latest versions of the OLE DB Providers installed for this to work as expected.

I would suggest making sure if you are going to use the native OLE DB provider that you install the latest version from here: Microsoft OLE DB Driver for SQL Server