Connect TO Online SQL Azure DB FROM On-Premise SQL Server SSRS
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?
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
- Which of definitions of database schema is correct?
- How to get isolation level in EF for MSSQL?
- Generate SQL Create Scripts for existing tables with query
- Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?
- Index for two columns that will be used in an operation on where clause?
- How do you update a datetime field in a table every time another field in the same table gets updated?
- Pros & Cons of TRUNCATE vs DELETE FROM
- comparing and contrasting Sybase and SQL Server
- How does sql server choose values in an update statement where there are multiple options?
- How to disconnect all users in sql server except sa?
- Select Consecutive Numbers in SQL
- SQL Find consecutive numbers in groups
- Spring Quartz configuration for connecting MS SQL server
- The most elegant way to generate permutations in SQL server
- Check column has 'letter' or '-' then bring value from another column
- Reading child XML node from column
- Combine JSON objects from multiple rows into a single object
- Find all stored procedures that reference a specific column in some table
- Laravel 10 stopped working with SQL Server Database
- Using TransactionScope around a stored procedure with transaction in SQL Server 2014
- Entity storing it's status as a FK to a status table. Can I update the FK once the status changes?
- How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
- I need to replace old path's images to new path with the same image
- Format date as yyyy-mm-dd hh:mm:ss.000
- Does a foreign key automatically create an index?
- How do I select only the latest entry in a table for each group so each 'latest' is a single row?
- "The certificate chain was issued by an authority that is not trusted" when connecting DB in VM Role from Azure website
- Kafka Connect SqlServer Configuration (SSL)
- SQL-Server: Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?
- TSQL Email Validation (without regex)