connect to SQL server fails

[Jeanxyz]

I have a Power BI report, which uses data from SQL data warehouse. The reports works when I run the query from my local PC or log into a  virtual machine using another user account. However, when I try to run the same report use my own account on the same virtual machine, I had an error message. By the way, I can access the same SQL database use my own account on that virtual machine. So assume both Power BI and SQL are functioning, it's something related to my account set upon that virtual machine. 

Has anyone enounter this before? 

[v-luwang-msft]

Hi @Jeanxyz ,

If you're getting this error after January 2022, possibly after migrating from System.Data.SqlClient to Microsoft.Data.SqlClient or just updating Microsoft.Data.SqlClient to version 4.0.0 or later, it's because MS has introduced a breaking change::

https://docs.microsoft.com/en-us/sql/connect/ado-net/introduction-microsoft-data-sqlclient-namespace...

Breaking changes in 4.0

Changed Encrypt connection string property to be true by default.

The default value of the Encrypt connection setting has been changed from false to true. With the growing use of cloud databases and the need to ensure those connections are secure, it's time for this backwards-compatibility-breaking change.

Ensure connections fail when encryption is required

In scenarios where client encryption libraries were disabled or unavailable, it was possible for unencrypted connections to be made when Encrypt was set to true or the server required encryption.

Rick Strahl has posted a detailed explanation on his blog

The change was made in this SqlClient pull-request in August 2021, where there is additional discussion about the change.

The quick-fix is to add Encrypt=False to your connection-strings.

Similar question refer:

https://stackoverflow.com/questions/17615260/the-certificate-chain-was-issued-by-an-authority-that-i...

https://community.powerbi.com/t5/Report-Server/Microsoft-SQL-A-connection-was-successfully-establish... 

https://community.powerbi.com/t5/Desktop/Error-while-connecting-to-SQL-database-after-PBI-last-updat... 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[James-Harpin · Accepted solution]

@Jeanxyz 

File - Options and settings - Data source settings

Then select your SQL database and select 'edit permisions' 

Selec Edit

And there you can change your connection type 

[Jeanxyz]

Thank you  so much, James! the problem is solved now. It looks once I changed the connection from Microsoft to Windows, it changes the setup of other Power BI reports using the same SQL database.. 

[James-Harpin]

Hi @Jeanxyz 

Try changing your datasource setting from Microsoft Account to Windows Login then try to refresh data.

[James-Harpin]

Hi @Jeanxyz 

Try changing your datasource setting from Microsoft Account to Windows Login then try to refresh data.

[Jeanxyz]

@James-Harpin 

How to make the change in account? Do you have a post or printscreen?