Direct Query, SQL-server 2012 publishing error

[FunDeckHermit]

Hello everyone,

I have a local machine running SQL-server 2012, Power-BI Desktop and the On-premises data gateway.

They are all running under my personal user-account with local admin rights. 

The report consist of two datasets, one with a custom visual and one normal bargraph. The data is a retrieved using Direct Querys

In power BI Desktop I get the following warning:

In App.powerbi.com:

Testing the HTTPS connection to microsoft:

I can PM the loggs if someone want to have alook at them. 

Thanks!

[v-yuezhe-msft]

@FunDeckHermit,

What server name and database name do you use when adding the SQL data source within your gateway? Please make sure that the server name and database name are same as that you use in Power BI Desktop.

In addition, do you use "."  as server name in Power BI Desktop? Do you install gateway and SQL Server in a same machine?

Regards,
Lydia

[FunDeckHermit]

Hello Lydia, @v-yuezhe-msft

I use ServerName: .

And databasename: CompanyProduct nx Datalogger

These settings were used everywhere, power BI desktop, query builder and even the gateway.

With regards,

Roy

[v-yuezhe-msft]

@FunDeckHermit,

Do you install gateway on the same machine that installing SQL Server? If not, please change your ServerName to machine name or IP address in your PBIX file and gateway.

Regards,
Lydia

[FunDeckHermit]

@v-yuezhe-msft

Hello Lydia,

I have changed the "." to the actual server-name. The error is still the same. 

Everything is running on the same machine with name: BEE-LT-0400

Everything is running on my user-account (local admin):

 

 

 

The network might be restricted, is port 443 the only port needed?

I am running in HTTPS-mode. 

With regards,

FunDeckHermit

[v-yuezhe-msft]

@FunDeckHermit,

Could you please post the connection string you use to connect to SQL Server in gateway and Power BI Desktop?

Regards,
Lydia

[FunDeckHermit]

Hello Lydia,

Do you mean the string inside the advanced editor? 

Power BI Desktop

= Sql.Database("BEE-LT-0400", "CostumerNX DataLogger", [Query="select * from [dbo].[FN_S_GetOEE] ('"& DateTime.ToText(BeginTimePeriod,"yyyy-MM-dd HH:mm:ss") &"','"& DateTime.ToText(EndTimePeriod,"yyyy-MM-dd HH:mm:ss") &"')", HierarchicalNavigation=true])

Where do I find this connection string?

With regards,

FunDeckHermit

[v-yuezhe-msft]

@FunDeckHermit,

I test the whole process as yours, everything works well. In your scenario, please post the screenshot about data source connection string within gateway. There is an example for your reference. Make sure the highlighted connection strings are same as that you use in Power BI Desktop.


Regards,
Lydia

[FunDeckHermit]

Hello Lydia,

I still cant seem to find that settings page. Do I need to update something?

I am sorry for wasting your time with this problem. 

[v-yuezhe-msft · Accepted solution]

@FunDeckHermit,

Go to Settings->Manage Gateways, add SQL Server data source within gateway following the guide in the article.


Regards,
Lydia

[FunDeckHermit]

Hello Lydia,

This solved my problem! 

Thank you very much!