February 7, 2012 at 8:51 am
Hi,
I'm trying to setup my SQL instance to encrypt connections.
My plan was to get a certificate, install it, then goto SQL Config Manager, turn on Force Encryption = Yes and pick the certificate from the tab.
The FQDN that clients currently use (for example) is dsource.companyname.com.
The server name is SVR122.internaldomain.co.uk
I have bought a certificate for an issuing authority for dsource.companyname.com and installed it on the local certificate store for MSSQLSERVER on SVR122.internaldomain.co.uk. When I log in as the service account and load SQL Configuration Manager, Protocols for MSSQLSERVER, I can't see the certificate, although with Force Encryption = yes, I can still connect to SQL Server.
So...my questions are this:
Do I need to get a certificate for SVR122.internaldomain.co.uk instead? And if so, will this affect clients connecting using the friendly FQDN?
thanks for any help...
February 8, 2012 at 2:26 am
anyone any thoughts on this one?
thanks,
February 13, 2012 at 11:10 am
wmt (2/7/2012)
Hi,I'm trying to setup my SQL instance to encrypt connections.
My plan was to get a certificate, install it, then goto SQL Config Manager, turn on Force Encryption = Yes and pick the certificate from the tab.
The FQDN that clients currently use (for example) is dsource.companyname.com.
The server name is SVR122.internaldomain.co.uk
I have bought a certificate for an issuing authority for dsource.companyname.com and installed it on the local certificate store for MSSQLSERVER on SVR122.internaldomain.co.uk. When I log in as the service account and load SQL Configuration Manager, Protocols for MSSQLSERVER, I can't see the certificate, although with Force Encryption = yes, I can still connect to SQL Server.
So...my questions are this:
Do I need to get a certificate for SVR122.internaldomain.co.uk instead? And if so, will this affect clients connecting using the friendly FQDN?
thanks for any help...
If you have a limited budget, you should experiment with self-signed certificates first. These can take awhile to get right. It's quite possible you're correct, and you need to use a different FQDN; it's very picky about that.
Did you tell the issuing authority this was for SQL Server encryption, not for a web site? There are some flags that can be set, that may or may not be strictly required, including (but not necessarily limited to) nsCert, extendedKeyUsage, and keyUsage.
Here's an example of a section of an OpenSSL config file used to generate a SQL Server encryption certificate; it's very possible some critical sections are missing, and it's very possible (almost certain, in fact) that some of these options are not actually required. Further, it's very possible that some of these options should actively not be used!
I don't have enough experience in the area; if you can, find a real expert or call up the issuing authority you use and ask for, specifically, a certificate set up solely for SQL Server encryption, but if you want to see if you can get these working, and take away options one at a time to see which aren't actually required, go for it!
nsCertType = server, client, email
extendedKeyUsage = critical, serverAuth,clientAuth
keyUsage = nonRepudiation, digitalSignature, keyEncipherment, dataEncipherment
February 13, 2012 at 11:23 am
Is this SQL Server 2008 or R2? There are some differences in the documentation. This shows that R2 has stricter validation requirements.: http://msdn.microsoft.com/en-us/library/ms189067.aspx
I haven't done this in a few years, but IIRC, the certificate secures a host name and validates it is who the client thinks it is. So a certificate for dsource.companyname.com does not secure SVR122.internaldomain.co.uk.
For testing, you can use a self-signed certificate. Create one with makecert (Windows SDK), or create one in SQL Server and back it up. It must be installed in Windows. The subject must be the SQL Server common name, so you can do some testing here before purchasing another certificate.
February 13, 2012 at 2:36 pm
Thanks for the replies, folks.
I finally managed to get this to work today using the original certificate, dsource.companyname.com, even though the server name is something different. What helped me was this blog post:
I don't have a cluster but editing the registry worked. When I start up SQL Server, I can now see it loading the correct certificate in the Event Log. I've filtered on port 1433 on Wireshark and I can see the data being encrypted so I'm reasonably happy. I still can't see the certificate in SQL Configuration Manager, even if I'm logged in as the service account, which a few of the guides suggest. I'm going to get a few of the users to check their client applications tomorrow, but initial testing seems to be positive.
3 days it took me to sort this out 🙂
Best regards,
February 13, 2012 at 3:34 pm
Excellent, and thanks for the followup and blog reference.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply