Hello, here I want to discuss an issue we had last days. We finally managed to solve it, but not at the level we wanted, especially because we think we will suffer it again.
The issue we dealt with was that we had some failing connections through Data GateWay for Power BI in the Microsoft’s Power BI App portal, specifically, whenever we attempt to configure a data source in the Data GateWay administration section.
They connected to several data sources, among them, an on-premises SQL Server not exposed to Internet, with a particular set of credentials (an AD account, let's say DOMAIN\john.doe), hence the use of Data GateWay. The authentication method we wanted to use was “Windows authentication”. Then, when we filled the relevant fields (server name, database name, username and password fields) and hit Next, the error message that portal threw was “Invalid credentials”. This was not accurate because we were able to connect to the server and, in particular, the relevant database from three locations:
- A domain-joined client machine using the aforementioned credentials
- The SQL Server hosting the database
- And the server hosting the Data GateWay
Besides, there is no conditional access policy, nor any type of firewall policies that hypothetically could block logins attempts or so, either in Azure platform or on-premises infrastructure.
It’s worth to mention a particular fact: in the Microsoft’s Power BI App portal, specifically in the connection for data source form, when we put the SQL server name and the database name, immediately the Username and Password fields are filled with “[ENCRYPTED]” string and (we think) the password associated to this “encrypted account”, respectively. Then we hit Next button and connection fails; ok, this was the experienced behavior. From here, if we try to connect with the appropriate account, it also fails. But it's worth also to mention that other connections to the same SQL server, with the same account but to another databases, were (and are) successful.
Now, this was the weird solution: having already all fields filled with the [ENCRYPTED] string at Username field and (again, we think) the associated password, if we change ONLY the Username field writing the relevant account DOMAIN\john.doe, and NOT the password field, we saw that in this last field the password is somehow automatically changed (we can't see the exact password as it is expected, but we count the asterisk characters and they match with the correct password). Finally, if we hit Next button, the connection is successfully established.
So, we think that there would be some sort of problem with the cached credentials and/or the form itself. We should expect that, if we fill the four fields, then the connection should be successfully established, nothing hard to do.
I want to know if someone has experienced the same issue, at least in these last days. Thanks for reading this and regards.
