1

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

enter image description here

Local Connection with Power BI Report Builder - works fine enter image description here

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;

enter image description here

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?

Bender.
  • 71
  • 6
  • 1
    `Authentication=Active Directory Interactive` see https://learn.microsoft.com/en-us/azure/azure-sql/database/active-directory-interactive-connect-azure-sql-db?view=azuresql – Charlieface Jul 20 '22 at 12:27
  • 1
    "Active Directory Integrated" is indeed also a thing if your on premise AD is federated with Azure AD (eg, via ADFS). But even when that is the case, there is the question of MFA. Active directory interactive will allow the user to generate a token (by completing the sign in process) which can be cached and reused until it expires. But "integrated" is using what you might call "single sign on". What if your organisation has set a policy that requires MFA? I agree with trying the suggestion from Charlieface. But it's all very confusing, and MS doesn't seem to want to make it clear. – allmhuran Jul 20 '22 at 12:48
  • Instead I could use an application user which works without MFA, I suppose that's the way to go. I just wonder how the connection string will look like in case of application user... – Bender. Jul 20 '22 at 13:01
  • 1
    The practical workaround is to simply use SQL Authentication, which is simply a username and a password. – Nick.Mc Jul 20 '22 at 13:11
  • 2
    Yes, we use the workaround suggested by Nick. This is Bad (tm), because the advice *from microsoft themselves* for two decades has been "use integrated security not sql auth". There are obvious advantages to integrated (what if you have row level security or other logic that requires knowing who the actual user is??) But here we are. If your org has a policy of "no sql auth" you're in for a few difficult conversations with your sysadmin/devsecops team. – allmhuran Jul 20 '22 at 13:13
  • Furthermore, SSRS does not support AD Authentication. Thus, an application user is necessary as it seems. Indicated here: https://stackoverflow.com/questions/70106613/integrating-azure-ad-with-ssrs-in-azure-vm – Bender. Jul 20 '22 at 13:51

2 Answers2

1

It is possible to connect from On-Premise SSRS to Azure DB:

  • service user without MFA
  • or SSRS on-prem server is in a trusted location
  • for Dataverse use Active Directory Password authentication
  • for Azure SQL server you can use SQL authentication as well

In case you are running into the same issue, please refer to: discussion azure forum

Bender.
  • 71
  • 6
0

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 ;)

Bender.
  • 71
  • 6
  • 1
    I think _SQL Server SSRS does not support Active Directory Authentication_ should actually be _Azure SQL doesn't support Active Directory Authentication_ – Nick.Mc Jul 21 '22 at 02:11
  • 1
    While that statement is true, I don't think it's the pertinent statement in this case. The correct set of statements are these: 1) SSRS supports SQL auth and windows auth, but not AAD auth. 2) Azure SQL Database supports SQL auth and AAD auth, but not windows auth. 3) Therefore the only auth that is supported for a connection from SSRS to Azure SQL Database is SQL auth, because it is the only one common to both. BUT! See the answer to [this question](https://stackoverflow.com/q/45442670/7165279). It's all a bit murky. – allmhuran Jul 21 '22 at 12:05
  • ... murky because my understanding (in relation to that question/answer) was that KCD is about SSO *from* an AAD login *to* AD authenticated resources, whereas the situation here is *from* an AD login *to* an AAD authenticated resource. I therefore don't think the answer in my linked question is correct. – allmhuran Jul 21 '22 at 12:18
  • Actually I'm looking for a way to use Dataverse as an additional data source for my SSRS reports. But so far I cannot see a way on how to connect to Dataverse without ADFS... thank you in any case! – Bender. Jul 21 '22 at 12:20
  • 1
    Haha, it sounds like you're in a similar situation to the nightmare we are currently going through trying to migrate Dynamics from on-prem to cloud, along with a bunch of SSRS reports that used to be able to read from the CRM database, but now that it's "Dataverse" (vomit) everything is complicated. – allmhuran Jul 21 '22 at 12:24