Login Encrypted Connection
-
I'm trying to use encryption for only a handful of logins I have that are SQL authenticated and are used by applications to connect to a SQL instance. I've been experimenting with creating a certificate using PowerShell so that I do not have to go through CA for a signed certificate but I'm not having much luck. I can get the certificate to show up in the Microsoft Management Console under the personal certificates folder but that certificate does not show up in SSCM. I've toggled the ForceEncryption option in the Flags tab and have also placed the thumbprint of the certificate in the registry at this path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate. However, none of these work and the registry setting prevented my instance from starting up. Long story short is that I'm back to the drawing board and hoping someone can give me some direction. Is what I'm trying to do even possible? Should I use a different strategy given that I only need a few of the logins to use encryption?
- RonMexico - Thursday, November 1, 2018 11:57 AM
Not sure what you have run in Powershell but it's doable. I just tested this again and this one showed up in SQL Server Configuration Manager:
New-SelfSignedCertificate -DnsName ServerName -CertStoreLocation cert:\LocalMachine\My -FriendlyName TestCert -KeySpec KeyExchangeThat's the same way it's done in this blog - try walking through that and see if it works:
Creating and Registering SSL CertificatesSue
-
I've previously attempted to follow that but I get this error when I try to run the command
New-SelfSignedCertificate -DnsName <serverFQDN> -CertStoreLocation cert:\LocalMachine\My -FriendlyName test99 -KeySpec KeyExchange
If I remove the -FriendlyName parameter to runNew-SelfSignedCertificate -DnsName <serverFQDN> -CertStoreLocation cert:\LocalMachine\My -KeySpec KeyExchangethen I get this errorI'm running PSVersion 5.0.10586.117 on Windows Server 2012 R2 if that helps at all.
- RonMexico - Friday, November 2, 2018 6:02 AMI've previously attempted to follow that but I get this error when I try to run the command
New-SelfSignedCertificate -DnsName <serverFQDN> -CertStoreLocation cert:\LocalMachine\My -FriendlyName test99 -KeySpec KeyExchange
If I remove the -FriendlyName parameter to runNew-SelfSignedCertificate -DnsName <serverFQDN> -CertStoreLocation cert:\LocalMachine\My -KeySpec KeyExchangethen I get this errorI'm running PSVersion 5.0.10586.117 on Windows Server 2012 R2 if that helps at all.
Unfortunately, it does matter what OS, powershell version, etc. Version issues are becoming a bit of a problem to manage for things in Powershell but they have been adding drop downs to the documentation so you can change versions. If you change the version to 2012 for the documentation on New-SelfSignedCertificate, you can see why you got the last error:
New-SelfSignedCertificateSo KeySpec isn't available - neither is friendlyname. Try it without either.
Sue
-
If I leave those off then it works and that's where I've been getting stuck. I can see it in personal certificates in MMC but I read it needs to be have the KeySpec option of AT_KeyExchange. To check this is run
certutil -v -store "my" "<FQDN>"from a command line and get this snippet
This should be set to = 1. I don't know if that has anything to do with the certificate not showing up in SSCM. I haven't had any success with trying to change that value after certificate creation as well. -
I went through this a few months back. One snag that I initially ran into when installing a certificate on the server is that the SSL certificate must be installed and configured under the context of the MSSQL service account (not simply a local Administrator account). Otherwise, SQL Server can't read the certificate from the registry when starting.
Also, as far as I know, and I researched a lot of msdn articles and blog posts, there is actually no advantage to installing an unsigned certificate that you create. By default, whenever SQL Server starts without a signed certificate, it will create an unsigned certificate. If a client connects with the 'Encrypt Connection' setting turned on, SQL Server will encrypt the channel using that certificate. This works without any network or protocol configuration on the server. Also, in this scenario the client needs to enable the 'Trust Server Certificate' setting to bypass the certificate authetntication step, or else the connection will fail. This is conceptually the same as when you browse to a HTTPS web page with an unsigned or expired certificate but still have the option to continue. For both SSL database connection or HTTPS web connections, the channel is still encrypted using a certificate, it's just that the certificate authentication step is bypassed. It's not as secure, but still better than an unencrypted channel. I think that's what you're trying to achieve here.
You can confirm your database connections are encrypted by querying sys.dm_exec_connections. The column encrypt_option should have the value 'TRUE' for each encrypted connection.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Eric - That's exactly what I need and was definitely over-thinking it. Thanks everyone for the help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply