I try to connect FROM my ON-PREMISE SQL Server SSRS 2019 TO an Online Microsoft Azure SQL DB.
It works fine, when I connect locally from my DBMS to the online DB.
Yet everything is secured with MFA though.
Unfortunately I cannot get the connection string right, to connect to my Azure SQL DB from SSRS 2019.
The following setup:
Local Connection with DBMS – works fine
Local Connection with Power BI Report Builder – works fine
Shared Connection for SQL Server Reporting Services – Error:
Typ:
Microsoft Azure SQL Database
Authentication:
Azure Active Directory – Universal with MFA
Connection String:
Server=tcp:myDomain.dynamics.com,1234;Authentication=Active Directory Integrated;Database=myDb;
Error:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)
Do you know how to fix the connection string to connect to Azure SQL DB?
Do you know where to put the Username and Password into the connection string?
Should I try to get a DB connection without MFA for SSRS only?
How would you solve this issue?
2
Answers
Unfortunately Azure SQL does not support Active Directory Interactive Authentication when added as a shared data source for SSRS, as described here: Documentation.
The only solution seems to be, to authenticate the Azure SQL DB from SSRS with classic SQL Server Authentication - or perhaps move reporting to Power BI ;)
It is possible to connect from On-Premise SSRS to Azure DB:
In case you are running into the same issue, please refer to:
discussion azure forum