On Premise Gateway / SQL Server DataFlow

[jhcannon]

I'm connecting to an on premise gateway on a remote server.  In Manage Gateways, connection succeeds.

I want to create a dataflow from the sql server.  When I try to connect, Power BI throws an "Invalid Credentials" error.  I am using the exact same username/password that is used in the Manage Gateways.  My gut is there is a setting/something easy I'm missing somewhere because I have done this with other servers in the past.  

Any ideas?

[v-yetao1-msft]

Hi @jhcannon 

Can you provide the screenshots of dataflow configuration and the datasource configuration ?

As you said, when you configure the datasource in Manage gateways, it shows that the connection is successful, then the SQL server and credentials should be correct . Perhaps you can create a dataset whose data source is SQL, and then check whether the refresh is successful in Service, to determine whether it is a SQL problem ?

Best Regard

Community Support Team _ Ailsa Tao

[bcdobbs]

When you go to create the dataflow in the service mine auto completed the login details:

Guessing yours isn't doing that?

Things to check:

1) Is the server name you're enterering matched "exactly" with what's in the gateway connection. eg if gateway is setup with servername.domain and you're entering servername it won't work. I think it might even be case sensitive.

2) In the data source setup in manage gateways are you listed as a user of that data source:

[jhcannon]

It does autopopulate the credentials.  I've tried leaving as is, I've tried typing them in.  For the server name/database name, I've typed and I've copy pasted.  I thought maybe I had typed something funny when I set up the gateway.  I literally installed the gateway on the server, set the gateway up on Power BI and then imported a dataflow, changed the parameter values to the new server name.  That didn't work so I tried a new dataflow thinking it didn't like the import.  I've tried encrypted/not encrypted.  I've even tried doing it in a browser on the server itself.  I can log in to SQL management studio with the credentials I'm using so I know they're good.  I've checked remote connections - that's good.  

[bcdobbs]

That's odd. If you have SSMS installed you probably also have sql server profiler installed? If so start a trace on your sql server and then try and connect your data flow. You should hopefully see some activity and a login attempt. Might give some clue.

Other thing to check is the logging in the on premise gateway client.