December 12, 2022 at 3:54 pm
Over the weekend I was doing some development on a project at home. I've use SQL Server 2019 Developer edition on an old machine. Anyway, I was presented with a notice as I got into Azure Data Studio, about an update to ADS, which affects connections to SQL Server using ADS and also affecting Microsoft.Data.SqlClient, which would (I think) affect Entity Framework. It will require a certificate to make the connection. I think this is a good idea, but I can hear the ranting and raving of my coworkers, once they learn of this. Anyway, I'm wondering if any of you have encountered this and if I've interpreted this correctly? Here's the URL I was directed to when I tried connecting to my local SQL Server Dev Edition: https://learn.microsoft.com/en-us/sql/azure-data-studio/connect?view=sql-server-ver16
Kindest Regards, Rod Connect with me on LinkedIn.
December 12, 2022 at 4:11 pm
I do not think this is a big deal either way. If the server has a self-signed certificate then just alter the connection to trust it. This just means the connection is encrypted but you are still open to man in the middle attacks. Obviously if the server has a CA certificate then make sure that Trust Server Certificate is set to false.
December 13, 2022 at 2:15 pm
First time I've hit. Just a little test, since I'm only running on a local machine with a container for a database, turning off Encrypt and you don't need the certificate. I'm not saying that's a good choice outside the scenario I'm in, but it is a choice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 13, 2022 at 4:06 pm
Thank you, Ken and Grant, for your responses. I'm wondering how prevalent this change is. Is it specific to a recent upgrade to ADS? Or is it related to a certain version of SQL Server, like SQL 2019 which I use at home (the Developer edition)? And I also wonder if this is true of Management Studio as well. In my work environment most of the SQL instances are moving to SQL 2016. We have some really old versions of SQL (like 2003), which are being migrated to newer versions of SQL Server. But if this only affects SQL 2019 and above, or the most recent upgrade to ADS and Management Studio, then there's a good chance no one will see it here for a while yet.
Kindest Regards, Rod Connect with me on LinkedIn.
December 13, 2022 at 5:01 pm
Not SQL version/edition specific -- it happened w/ the ADS 1.40 release: https://learn.microsoft.com/en-us/sql/azure-data-studio/release-notes-azure-data-studio?view=sql-server-ver16#november-2022
December 13, 2022 at 5:26 pm
As mentioned it is only a different default for the latest version of ADS. As I presume you do not use CA certificates on your servers it is easy enough to right click and edit connection. Encrypt can then be set to false or Trust server certificate set to true.
ps It is probably a different default for the latest Microsoft.Data.SqlClient too but I have not actually looked into it. ie I presume if you do not want encryption you have to explicitly set Encrypt to false.
December 14, 2022 at 3:04 pm
Thank you everyone for your feedback. You're correct, Ken, that in our environment our databases are primarily on-prem, so using Trust server certificate is working. We are moving some of our databases into Azure VMs, so I'm not sure if that will change things or not.
My bigger concern is how this might affect Microsoft.Data.SqlClient connections. I am working on an app which uses Microsoft.Data.SqlClient. So far it hasn't complained, but I suspect at some update to Microsoft.Data.SqlClient there will begin to be complaints.
Kindest Regards, Rod Connect with me on LinkedIn.
December 14, 2022 at 4:35 pm
The following suggests it is just another change in the default value:
You need to make sure you do not rely on the defaults for your connection. ie Explicitly set Encrypt and Trust server certificate.
March 15, 2023 at 3:34 pm
ps FYI I have just learnt that our sysadmin team now have a CA server from which to issue certificates for our LAN. They are also happy to handle the certificate rotation. It seems this push towards multi level security is being driven by our insurers and the need for certification to handle certain types of third party data. As a similar nudge could happen in your environment it seems Microsoft were correct to change the default connection settings.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply