Forum Discussion

PremK's avatar
PremK
Resolver I
2 years ago
Solved

EMERGENCY || Unable to connect SQL server with PBI

Kindly could you assist me in connecting SQL Server with PBI.

Pioneer Rx ( a pharmacy software ) which uses SQL Server as Data base. i have to connect this with PBI for reporting.

I had connected this SQL Server with SSMS and its connected well and able to interact with the data bases and tables in it.

But while i tried to connect the same SQL Server with Power BI to create reports its showing error like 

 

provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.

I had tried with these Certificate Revocation options in PBI Desktop Settings, but it doesn't help me out 

 


kindly provide me solution to Connect SQL Server and PBI...

Need Help
  • PremK's avatar
    PremK
    2 years ago

    Thanks to everyone who tryied to give solution for this error.
    I resolved this issue through 2 steps > 
    1. Adding / Editing Environment Variable  - the variable name should be Name of the SQL Server.
    2. Updating PBI Desktop Config file in this path
    C:\Users\<YourUsername>\AppData\Local\Microsoft\Power BI Desktop\User.zip
    updating this - <setting name="SqlTrustedServers" serializeAs="String"> <value>NAMES OF YOUR SQL SERVERS </value> </setting>

3 Replies

  • PremK's avatar
    PremK
    Resolver I
    2 years ago

    Thanks to everyone who tryied to give solution for this error.
    I resolved this issue through 2 steps > 
    1. Adding / Editing Environment Variable  - the variable name should be Name of the SQL Server.
    2. Updating PBI Desktop Config file in this path
    C:\Users\<YourUsername>\AppData\Local\Microsoft\Power BI Desktop\User.zip
    updating this - <setting name="SqlTrustedServers" serializeAs="String"> <value>NAMES OF YOUR SQL SERVERS </value> </setting>

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Your solution is great collinq 

    Hi, PremK 

    Has your current issue been resolved? If yes, you can share your workaround here and mark it as a solution or mark the reply that was helpful to you as a solution.

    When we connect to SQL Server with Windows authentication in Power BI desktop, the following dialog box pops up, and we need to select OK:

    In addition, you need to change the settings to basic or none:

    You can get this information from the following links:

    https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-certificate-revocation

    Also, if you keep getting the current error, then you can clear out your SQL Server credentials in the data source settings to reconnect to it:

     

     

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.