3

If I have an on-premise SSRS instance on the same domain as my Azure AD, can I configure SSRS to connect to Azure SQL and pass through credentials so that I can determine (at the database) who has logged in and is running the report (for row level security)?

In other words, can I connect from SSRS to Azure SQL as you would using SSMS AD universal or integrated authentication to flow credentials?

Murray Foxcroft
  • 12,785
  • 7
  • 58
  • 86

1 Answers1

1

You can, if you have the following:

  • SSRS 2016 or later
  • The Active Directory Authentication Library for SQL Server (ADALSQL) (https://www.microsoft.com/en-us/download/details.aspx?id=48742) installed on the report server
  • ADFS to federate across on-premises AD and Azure AD
  • Kerberos Constrained Delegation (KCD) configured from the report server to the ADFS server

Then you can configure the report/data source to authenticate to Azure SQL Database "as the user viewing the report" and the other components should do the rest.

Riccardo Muti
  • 86
  • 2
  • Fantastic - sounds good. I didn't know you could plug ADALSQL in. Thanks! – Murray Foxcroft Aug 01 '17 at 20:48
  • Quick one - would ADALSQL still be needed if we built the SSRS from scratch on a new Azure (domain joined) VM? – Murray Foxcroft Aug 02 '17 at 07:33
  • @MurrayFoxcroft Did this answer actually work? Confusion on this continues (see the discussion in [this question](https://stackoverflow.com/questions/73051259/connect-to-online-sql-azure-db-from-on-premise-sql-server-ssrs)). My understanding was that KCD allows you to use an AAD login to connect to an AD authenticated resource. But situation in your question and the linked question is the opposite: The user is accessing SSRS with an AD login, but wants to connect to an AAD authenticated resource. – allmhuran Jul 21 '22 at 12:22
  • Wow, this was a few years ago. I marked it as accepted back then, so it either resolved the issue or pointed me in the right direction. – Murray Foxcroft Jul 21 '22 at 13:40
  • Roger, thanks for coming back. – allmhuran Jul 21 '22 at 19:38