How to connect to SQL on BI servers in a Dynamics 365 for Finance and Operations environment
Another quick one - I had trouble this week connecting to the local SQL Server instance on the BI server in my Dynamics 365 for Finance and Operations cloud environment.
I was investigating an SQL Server Reporting Services (SSRS) issue and I wanted to be able to look at the execution logs in the SSRS database.
Looking at the SSRS configuration on the box it appeared that SSRS itself was connecting to the database as Network Service, but that didn't help me when trying to connect using SQL Server Management Studio (SSMS) myself, so I was doubting whether I could ever access the local SQL instance there.
In the end I realized there is a very simple solution - if you're logged on as the local Admin account, and run SSMS normally, login to SQL will fail, but if you run SSMS as Administrator, then login to SQL will work fine (just using Windows authentication, the local Admin account is a SQL admin).