Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jhcannon
Frequent Visitor

On Premise Gateway / SQL Server DataFlow

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?

4 REPLIES 4
v-yetao1-msft
Community Support
Community Support

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
Super User
Super User

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

bcdobbs_0-1639421479956.png

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:

bcdobbs_1-1639421693715.png

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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.  

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors