How do you connect to a read-only replica created by Read Scale-Out through an endpoint rather than using ApplicationIntent=ReadOnly

Hey John! It sounds like you're running into some challenges connecting Power BI to your Azure SQL Database Read-Only replica. Typically, the standard approach to connect to a Read-Only replica is by configuring the connection string with the property ApplicationIntent=ReadOnly. Unfortunately, using just an endpoint alone isn't natively provided for routing to a Read-Only replica without that parameter.

However, if you want to create a workaround, here are a couple of ideas:

ApplicationIntent in Connection String: Ensure that your connection string in Power BI includes ApplicationIntent=ReadOnly. This enables automatic routing to the Read-Only replica.

Example connection string:

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<password>;Trusted_Connection=False;Encrypt=True;

Using Read-Only Listener: If using Azure SQL Managed Instance in a failover group, you might be able to set up a read-only listener, which would allow connections to be routed automatically to a Read-Only replica. The format for the service could look something like <fog-name>.secondary.<zone_id>.database.windows.net.

Unfortunately, Azure SQL databases do not directly support endpoints that lock to a Read-Only instance. If this problem persists, it could indicate an issue with how Power BI is resolving the connection internally or potential transient issues.

Possible Next Steps:

• Verify your connection string includes ApplicationIntent=ReadOnly.
• Ensure your Power BI is up to date, as older versions might have bugs or restrictions.
• Monitor the connection using the query SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');, which should return READ_ONLY if you're connected correctly.

Follow-Up Questions:

1. Could you confirm if your connection string currently includes ApplicationIntent=ReadOnly?
2. Are you using Azure SQL Database or Azure SQL Managed Instance?
3. What specific errors are you running into when trying to connect in Power BI?

Hope this helps, and let me know if you need any more assistance!

References

• Use read-only replicas to offload read-only query workloads
• Monitoring and troubleshooting read-only replicas
• Connectivity to geo-secondary instance

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

To connect to a read-only replica created by Read Scale-Out in Azure SQL Database, you must specify ApplicationIntent=ReadOnly in your connection string. Unfortunately, there is no dedicated endpoint for the read-only replica that can be used instead of this connection string parameter. The system does not support creating a dedicated endpoint for read-only replicas; connections must be made using the ApplicationIntent property to route to the appropriate replica.

If you are experiencing inconsistent connections in Power BI, you might want to ensure that your connection string consistently includes ApplicationIntent=ReadOnly. This is the only method to direct your connection to the read-only replica effectively.

References:

• Use read-only replicas to offload read-only query workloads
• Azure SQL Database Hyperscale FAQ