Connect to SQL Server 2012 - Invalid connection credentials

[peterj]

I'm trying to setup a scheduled refresh for an Excel workbook with a data model that connects to SQL.  I have installed the Data Gateway and am able to get "Connection Successful" when setting up the SQL data source, but I keep getting an error of "Invalid connection credentials" when I try to refresh my report.  I'm able to refresh the workbook successfully from my computer using the same SQL credentials.  Any ideas why this might be?  Below is a copy of the error.

Details Type Start End Status Message

Hide	On demand	11/20/2017, 3:59:44 PM	11/20/2017, 4:00:21 PM	Failed	Invalid connection credentials.
Failed Connections: Query - Job_Master Cluster URI: WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net Activity ID: dee66e43-a2f7-1017-e4e9-ec3237442ab2 Request ID: 0333f95d-da6f-19b0-301a-6205444e1bb6 Time: 2017-11-20 21:00:21Z

[v-jiascu-msft]

Hi @peterj,

It works fine in my test. Though your description is clear, I have to ask a few questions.

1. Is it a data model or a query in Excel workbook? (A model is in the Power Pivot)

2. What is the data source exactly? SSAS or SQL DB?

3. Did you import the excel model to Power BI Desktop first then publish it to the Service? Or you import the file from the PBI Service?

4. Is the status of the gateway data source good?

5. Is the status of the setting of the dataset good too?

6. What's the credential exactly? Windows or basic like 'SA'?

Best Regards!

Dale

[peterj]

Hey Dale - Thanks for your response.  Below are the answers to your questions.

Is it a data model or a query in Excel workbook? (A model is in the Power Pivot)

I've actually tried both and can't get either to work.  The one I'd primarily like to get working is a data model in an Excel workbook (that uses Power Query queries).

What is the data source exactly? SSAS or SQL DB?

SQL DB

Did you import the excel model to Power BI Desktop first then publish it to the Service? Or you import the file from the PBI Service?

I always publish the files through either Excel or Power BI, using Fille->Publish->Upload.

Is the status of the gateway data source good?

Yes, I'm not seeing any issues with the data source, gateway, etc. I don't have any issue with connecting or using the data source until I try to refresh through PowerBI.

Is the status of the setting of the dataset good too?

I'm not exactly sure what you mean by this, but I believe so.  I'm able to refresh the data in desktop Excel and PowerBI.

What's the credential exactly? Windows or basic like 'SA'?

Basic

Thank you!

Peter

[v-jiascu-msft]

Hi Peter,

Could you please check the status as the pictures I posted earlier show? And share some snapshots of the error messages.

Best Regards!

Dale

[peterj]

Hey Dale - see below.  Thanks, Peter

[peterj]

And here are the error messages...