New Encryption and Certificate Defaults in Microsoft's SQL Server Connection Provider

[utterances-bot]

New Encryption and Certificate Defaults in Microsoft's SQL Server Connection Provider | netnerds.net

Note: This blog post is going to borrow a bit from Microsoft's official documentation in "Connect with Azure Data Studio" and from our book, Learn …

https://blog.netnerds.net/2023/03/new-defaults-for-sql-server-connections-encryption-trust-certificate/

[blaarghy]

Love the article, big thanks. One note:
dbatools.io/newdefaults is a 404 error right now.

[potatoqualitee]

Thank you, @blaarghy ! I have added the link.

[charlesjpalmer]

When I run 'New-DbaComputerCertificate -Computername MySQLServer -Dns MySQLServer.my.domain, MySQLServer', I am getting the following error:

MethodInvocationException:
Line |
87509 | … $cert.Import($certCrt, $null, [System.Security.Cryptograp …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Exception calling "Import" with "3" argument(s): "X509Certificate is immutable on this platform. Use the equivalent constructor instead."

When I open the Certficates msc, I do see a newly issues CA that has Server Authentication as part of the 'Intended Purposes'
THis cert is issued from my internal CA (ADCS)
I have verified that my PKI Root server cert is listed under "Trusted Root Certification Authorities"
I have verified that my Issuing CAs are listed under "Intermediate Certification Authorities"

Assuming a false error, I run 'Get-DbaComputerCertificate -ComputerName MySQLServer', it lists the certificate and all looks good

Ok, let me run the next command (Set-DbaNetworkCertificate -SqlInstance MySQLServer -Thumbprint MyCertThumbprintFromPreviousCommand). When that runs, I get two warnings:

WARNING: Read-only permissions could not be granted to certificate, unable to determine private key path.
WARNING: [10:47:34][Set-DbaNetworkCertificate] New certificate will not take effect until SQL Server services are restarted for MySQLServer

The second warning makes sense and a reboot should be done. The first one is concerning. I didn't test by trying to export the cert including the private key and I have the option so it is a complete certificate
I don't know why it couldn't grant permissions.

At this stage, I have rebooted my SQL server before I run the next command. Just to make sure there aren't any issues lingering

I then run 'Enable-DbaForceNetworkEncryption -SqlInstance MySQLServer', to which I get:
ComputerName : MYSQLSERVER
InstanceName : MSSQLSERVER
SqlInstance : mysqlserver
ForceEncryption : True
CertificateThumbprint :

10:54:39Enable-DbaForceNetworkEncryption] Force encryption was successfully set on MYSQLSERVER.my.domain for the MSSQLSERVER instance. You must now restart the SQL Server for changes to take effect.

Seems perfectly reasonable. So I reboot again.

Log back in and re-open PowerShell (7.3.4 running dbatools 2.0.0) and run 'Test-DbaComputerCertificateExpiration -ComputerName mysqlserver' and 'Test-DbaComputerCertificateExpiration -ComputerName mysqlserver -Threshold 1000' and get no errors (my cert has 729 days to expiration)

All is looking good. Now let's get to the commands I am trying to use that brought me here in the first place.

Set-DbaTempDbConfig -SqlInstance mysqlserver -DataFileCount 8 -DataFileSize 30000 -DisableGrowth
07:23:29dbatools.psm1]
/ / / /
| O | | O |
To disable this message, run:

Set-DbatoolsConfig -Name Import.EncryptionMessageCheck -Value $false -PassThru |
Register-DbatoolsConfig
WARNING: [07:23:44][Set-DbaTempDbConfig] Failure | The system cannot find the file specified.

Ok, maybe I am missing a parameter. Let me just test the config first:

Test-DbaTempDBConfig -SqlInstance mysqlserver

WARNING: [11:04:43][Test-DbaTempDbConfig] Failure | The certificate chain was issued by an authority that is not trusted.

I don't want to do it because I have a valid, trusted certificate installed, but I need this to work so I ran the following:
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true
Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false

Now if I run Test-DbaTempDbConfig command, I get the results as expected
I am also able to change my TempDB config and that is all working now, but disabling encryption is not an acceptable solution.

[potatoqualitee]

@charlesjpalmer -- that is not a result of our toolset, but rather, Microsoft's. I imagine your certificate isn't trusted entirely. What's it look like when you connect with SSMS and trust is marked as false and encrypt is marked as true? Do you encounter the same issue with the latest version of the SqlServer module?

[charlesjpalmer]

I don't disagree that it probably isn't dbatools that is the problem. I agree it is the certificate, but everything looks fine. I have done this on two servers and one of them in SSMS I had to check the "trust the server cert" and the other I didn't so that is inconsistent. The problem is that there isn't any guidance on fixing it when there is a problem and most guidance just says override it and back down security. Trying not to do that because security is important. Thanks, Charles Charles Palmer Sr. Systems Engineer IV ***@***.*** Wright National Flood Insurance Services, LLC: Florida E100548 Wright National Flood Insurance Services of New York, LLC: New York 1459183 Doing Business As: Wright Flood Insurance Services, LLC in California ​California License: 0D26879 Agency, 2D6879 Adjusting Entity PO Box 33003 ​St. Petersburg, FL 33733 Office: (727) 568-5599 www.wrightflood.com Please remember that insurance coverage cannot be bound, amended or canceled by leaving an electronic or voice mail message. ​CONFIDENTIALITY NOTICE: The information contained in this communication, including attachments, may contain privileged and confidential information that is intended only for the exclusive use of the addressee. ​ ​If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, ​distribution or copying of this communication is strictly prohibited. If you have received this communication in error please notify us by telephone immediately. From: Chrissy LeMaire ***@***.***> Sent: Wednesday, May 3, 2023 12:58 PM To: potatoqualitee/blog-comments ***@***.***> Cc: Charles Palmer ***@***.***>; Mention ***@***.***> Subject: Re: [potatoqualitee/blog-comments] New Encryption and Certificate Defaults in Microsoft's SQL Server Connection Provider (Issue #17) [External] @charlesjpalmer<https://github.com/charlesjpalmer> -- that is not a result of our toolset, but rather, Microsoft's. I imagine your certificate isn't trusted entirely. What's it look like when you connect with SSMS and trust is marked as false and encrypt is marked as true? Do you encounter the same issue with the latest version of the SqlServer module? - Reply to this email directly, view it on GitHub<#17 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/A3F3UO3G2TGCOJ67Q6QZ5PTXEKFCFANCNFSM6AAAAAAVSSKU4M>. You are receiving this because you were mentioned.Message ID: ***@***.******@***.***>>

[potatoqualitee]

ahh got it, the issue is with New-DbaComputerCertificate

can you please fill out a bug issue with all the required info so that i can replicate your issue? https://dbatools.io/issues

[potatoqualitee]

Fixed in dataplat/dbatools#8897!

[charlesjpalmer]

You are the bomb. Sorry I didn't get back to you with further information. I will update and test. Thanks, Charles Charles Palmer Sr. Systems Engineer IV ***@***.*** Wright National Flood Insurance Services, LLC: Florida E100548 Wright National Flood Insurance Services of New York, LLC: New York 1459183 Doing Business As: Wright Flood Insurance Services, LLC in California ​California License: 0D26879 Agency, 2D6879 Adjusting Entity PO Box 33003 ​St. Petersburg, FL 33733 Office: (727) 568-5599 www.wrightflood.com Please remember that insurance coverage cannot be bound, amended or canceled by leaving an electronic or voice mail message. ​CONFIDENTIALITY NOTICE: The information contained in this communication, including attachments, may contain privileged and confidential information that is intended only for the exclusive use of the addressee. ​ ​If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, ​distribution or copying of this communication is strictly prohibited. If you have received this communication in error please notify us by telephone immediately. From: Chrissy LeMaire ***@***.***> Sent: Thursday, May 4, 2023 6:51 AM To: potatoqualitee/blog-comments ***@***.***> Cc: Charles Palmer ***@***.***>; Mention ***@***.***> Subject: Re: [potatoqualitee/blog-comments] New Encryption and Certificate Defaults in Microsoft's SQL Server Connection Provider (Issue #17) [External] Fixed in dataplat/dbatools#8897<dataplat/dbatools#8897>! - Reply to this email directly, view it on GitHub<#17 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/A3F3UO6A3SUYKED576HN4F3XEOC3DANCNFSM6AAAAAAVSSKU4M>. You are receiving this because you were mentioned.Message ID: ***@***.******@***.***>>

[potatoqualitee]

happy to help 💣 the fix will be included in 2.0.1 which will be released when i figure out another problem, likely within the week.

[ShaunJStu]

Had to rebuild a server from scratch and am now getting the "The certificate chain was issued by an authority that is not trusted". Using dbatools 2.1. Don't get the error when running the command myself, but do when it's running as a SQL job. SQL agent account has admin on the server. Any suggestions? Tried "Set-DbatoolsInsecureConnection".