How can we pass users credentials through a data gateway to the data source?
I understand that this can only be done with Direct Query. To try this I created a simple Power BI report that connects to the data source via direct query using my current windows credentials. I then published it and proceeded to connect the data source to the data gateway.
Normally we specify a user account (Basic or Windows) that allow us to sign into our SQL Server databases. This way if user Mike.Smith@ms.com runs a report and refreshes the data, the gateway will use the hard coded credentials created during the initial setup and not the Mike.Smith@ms.com user account.
This looks like Kerebos would have to be set up. For authentication I used OAuth2 and under single sign-on I chose "Use SSO via Kerberos for DirectQuery queries". The create button was greyed out so I clicked on "Edit Credentials" under Authentication and chose my account under Office 365. I then clicked "Create" but it fails and the message at the top was "Unable to create data source. Invalid connection credentials."
I am sure that others have run into situations where it is not permitted for everyone to use the same (fixed) credentials. How can the user credentials be passed through the gateway to the database?
