Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LucianoS
Frequent Visitor

Microsoft SQL: A connection was successfully established with the server, but then an error occurred

Estimados usuarios, he recurrido al foro porque creo que estoy en un problema que para mi es bastante grande, el dia de hoy intente  ingresar a mi sql server management studio v18.4 una nueva base de datos, a la cual queria importar un archivo excel, al darme un error, instale un Microsoft Access Database Engine 2016 Redistributable, logre subir el excel pero al momento de actualizar el reporte y de esta manera tambien descargar el excel desde el Powerbi (como tablas) tengo el  siguiente problema: Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - La cadena de certificación fue emitida por una entidad en la que no se confía.)

 

ayuda.png

No tengo problemas para conectarme desde Sql server management studio, pero sí desde powerbi al momento de actualizar.

ayuda2.png

 Creo que al instalar el Microsoft Access Database Engine 2016 Redistributable, me elimino el certificado.. o algo así, no soy expero pero necesito us ayuda porfavor!

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

HI @LucianoS ,

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...

 

 

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


Best Regards

Lucien

 

 

View solution in original post

4 REPLIES 4
MichaelKhalsa
New Member

Yes, the change of encryption policy was very helpful.

 

However, there were other hickups when converting a project to net standard 2.0 and then using it in a winforms 4.8 project. I included my reply in another forum below in case it is of help to anyone.

---

I had the same issue in a net 4.8 WinForms project, which fixed by adding the latest System.Buffers nuget.

-- History

I converted a set of libraries used in common for winforms and asp.net from .net 4.8 to standard 2.0, which required replacing System.Data.SqlClient with Microsoft.Data.SqlClient.

Winforms however is still on 4.8. It would run fine locally, but the installed would not. After opening a connection, touching anything on a property, such as the connection property, connection state, etc., would throw an instance of object not referenced... exception.

I added the above idea (modified the path) to include the SNI artificats in the build for installer, but this was of no help.
So i manually copied over all Microsoft.*.dll assemblies from my local bin over to the installed version on local machine. Then it gave a Systems.buffer assemblies version error. While the System.Buffers versions were identical on both the local bin and the installed program files (even the revision and patch parts), the file size was different (seriously Microsoft, follow best practices).

So i added a latest nuget of System.Buffers to my winforms project, created a new build on installer (to clear out my experiments) and did a new install onto local machine, and works now.

This took a lot of trial and error. I also noticed that System.Tuples.Values (yes that one), has a different file size. So the local build used in visual studio 2022 for winforms has some secret sauce that the Project deployer does not. I also noticed that the local project bin in vs 2022 was using the full version of Microsoft.Data.SqlClient (at about 2 MB), while the installer pulled over a 72kb version (from targets).





RBFarfanV
New Member

Thank you man, I'm very glad with you. You saved my life man.

Anonymous
Not applicable

Reinstall Power BI Desktop solved this error message.

v-luwang-msft
Community Support
Community Support

HI @LucianoS ,

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...

 

 

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


Best Regards

Lucien

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.