cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dzeipii Frequent Visitor
Frequent Visitor

Certifiticate problem w enterprise gateway import mode, when direct mode works ok

We are using on-premise SQL server on a virtual machine and connecting using the enterprise gateway. The PowerBI online dataset and reports are built using PBI Desktop. For testing purposes I've used a single table and a single value column for a visual.

 

After setting a scheduled refresh for the dataset we receive the following error (I havent been able to change the system language to English, sorry for some Finnish on the table):

 

Pohjana oleva virhesanoma:The server name provided doesn't match the server name on the SQL Server SSL certificate. Please see https://go.microsoft.com/fwlink/?LinkId=394782. (Source at bilotreport1;bilotdw.)
DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259
Pohjana oleva virhekoodi:-2147467259
Microsoft.Data.Mashup.CredentialError.Reason:PrincipleNameMismatch
Microsoft.Data.Mashup.CredentialError.DataSourceKind:SQL
Microsoft.Data.Mashup.CredentialError.DataSourcePath:...
Klusterin URI:WABI-NORTH-EUROPE-redirect.analysis.windows.net
Toiminnon tunnus:...
Pyynnön tunnus:...
Aika:2016-03-24 10:21:44Z

 

The odd thing is that with the very same setup direct query mode works fine without any certificate issues!

1 ACCEPTED SOLUTION

Accepted Solutions
datanerd Frequent Visitor
Frequent Visitor

Re: Certifiticate problem w enterprise gateway import mode, when direct mode works ok

I found the link within the refresh history on the gateway to be helpful.  https://msdn.microsoft.com/en-us/library/ex6y04yf%28V=VS.110%29.aspx?f=255&MSPPError=-2147217396

 

In short, I was using the server shortname but the cert had the FQDN.  So i used the FQDN in the connection so that the connection string matches the cert.  Was a very simple fix.

View solution in original post

10 REPLIES 10
datanerd Frequent Visitor
Frequent Visitor

Re: Certifiticate problem w enterprise gateway import mode, when direct mode works ok

I found the link within the refresh history on the gateway to be helpful.  https://msdn.microsoft.com/en-us/library/ex6y04yf%28V=VS.110%29.aspx?f=255&MSPPError=-2147217396

 

In short, I was using the server shortname but the cert had the FQDN.  So i used the FQDN in the connection so that the connection string matches the cert.  Was a very simple fix.

View solution in original post

Power BI Team dimazaid
Power BI Team

Re: Certifiticate problem w enterprise gateway import mode, when direct mode works ok

Could you send your Gateway Logs to hybridbi@microsoft.com , logs can be found at: C:\Users\PBIEgwService\AppData\Local\Microsoft\Power BI Enterprise Gateway\EnterpriseGateway*.log

Power BI Team dimazaid
Power BI Team

Re: Certifiticate problem w enterprise gateway import mode, when direct mode works ok

dzeipii Frequent Visitor
Frequent Visitor

Re: Certifiticate problem w enterprise gateway import mode, when direct mode works ok

That was it and as said, very simple fix.

 

For some reason the unmatching connection string was working ok in the direct query, but that does not matter.

jameslo30 Member
Member

Re: Certifiticate problem w enterprise gateway import mode, when direct mode works ok

I'm having the same issue, exept I am using the FQDN in the connection string. But I still get the same error message. No reply from PowerBI support yet despite rasing a ticket.

Kbersani Regular Visitor
Regular Visitor

Re: Certifiticate problem w enterprise gateway import mode, when direct mode works ok

The above link does not give me information on how to determine the FDQN, or at least I am missing or not able to understand how to get it. Is there somewhere on the SQL server that can identify this?

jameslo30 Member
Member

Re: Certifiticate problem w enterprise gateway import mode, when direct mode works ok

You can obtain your FQDN by typing the following into PowerShell:

 

[System.Net.Dns]::GetHostByName(($env:computerName))

 

If you run that from the SQL server host, it will give the FQDN.

Kbersani Regular Visitor
Regular Visitor

Re: Certifiticate problem w enterprise gateway import mode, when direct mode works ok

Thank you. I was able to find the FQDN (assuming it is the HostName) - I have entered this as my server but I am now getting an error that it is unable to connect "cannot connect to the server". Again it works fine when using direct query, just not on import. looks like you were having the same issue, did you find resolution?

Highlighted
jameslo30 Member
Member

Re: Certifiticate problem w enterprise gateway import mode, when direct mode works ok

I had to set an SSL certificate against the SQL Server. When you install the Enterprise Gateway, it creates an SSL certificate on the local server. If you have installed the Enterprise Gateway on the SQL server, you can assign the SSL certificate through SQL Server Configuration Manager.

 

SQL Server Network Configuration > Right click > Properties > Certificate > Select "Data Management Gateway SSL Certificate" > OK

 

You'll have to restart SQL services.

 

Should be OK after that!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Top Kudoed Authors
Users Online
Currently online: 163 members 1,546 guests
Please welcome our newest community members: