Unable to create connection with SQL Server DB from Power BI Service

Anonymous thanks for explaning the scenarios. Unfortunately I cannot jump on a call as I'm also at work. I just hang around in the forums in my downtime which lately seems to be a fair bit lol.

So to start with, your gateway being able to reach flat files on the server machine is completely different from it being able to reach a SQL database hosted on the same computer. I strongly suspect this is a SQL server configuration issue. As in - not the computer that is hosting SQL Server but the application itself. Part of your error message also supports this -

Unable to create connection for the following reason: Logging into Sql server failed. Possible reasons for this error include an invalid authentication mode, a missing login in Sql server, a login from an untrusted domain or a problem contacting the domain controller for the authentication request.

My SQL capabilities stop at writing queries so perhaps a DBA might be able to chime in here? I'd suggest taking a look at the security configurations for your SQL server instance as well as the particular database you're trying to hit.

Also keep in mind SSMS is not SQL Server, it's just a management tool. You can have SSMS installed on an entirely different computer compared to where SQL server is being hosted, so long as proper authentication has been setup, you'll still be able to manage your databases from outside the SQL server machine.

I know this is not a solution but I hope it points you in the right direction.