cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Direct Query, SQL-server 2012 publishing error

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!

1 ACCEPTED SOLUTION

@FunDeckHermit,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Microsoft
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

Hello Lydia,

 

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

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

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

Capture9.PNG

 Capture8.PNGCapture7.PNG

 Capture13.PNG

 capture15.PNG

 

 

 

 

 

 

 

 

 

 

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

I am running in HTTPS-mode. 

 

With regards,

FunDeckHermit

 

 

@FunDeckHermit,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

@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.
1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Lydia,

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

capture17.png

 

I am sorry for wasting your time with this problem. 

@FunDeckHermit,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hello Lydia,

This solved my problem! Smiley Very Happy

 

Thank you very much!

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors