I am a retired network admin, I understand SQL and AD fairly well, but have never had to do anything with certs beyond following the bouncing ball and installing a basic certificate on a rather simple website.
Now I am helping a client/friend with PCI compliance. They have a pretty simple setup with a 2012R2 server running SQL2008R2. SQL does not face the Internet, and there is not a web service running on the server although IIS is installed. The firm does have a website hosted externally, which has a valid cert–so it is fine. I expected this to be simple and straightforward. Foolish me.
They got pinged on a couple of things which are easy enough, but these three item have me perplexed.
SSL Certificate Chain Not Trusted (External Scan)
SSL Certificate Common Name Does Not Validate (External Scan)
SSL Certificate is Not Trusted (External Scan)
OK, I get it, a self signed cert is not a good thing. So I am happy to get a trusted cert and install it. But I'm not sure what to do to make that happen.
I was told by a friend I generally trust to set up a simple web page using the server’s local name, install certbot, get a cert, and configure SQL and the workstations to use it.
My question is, how do I do that? I can do the web site, but I am not sure that is strictly necessary. Can’t I just get a cert for the server? I am not sure I should post the FQDN at this point, but say it is myserver.mydoman.local. The hosted site is www.meadorjohnsonlaw.com, but again, that is external and already secured. So I would add an A record to the DNS for myserver.meadorjohnsonlaw.com and then get the cert for that? It wouldn’t resolve internally, but I can add it to the internal DNS or put it in a host file.
Basically I am asking how to get a trusted cert for an internal local server.
What is scanning the SQL server's certificate if it's not exposed to the Internet? And why would the SQL server need a cert at all if the only thing talking to it is an IIS App running on the same box? You don't even have to configure SQL to listen on anything but the localhost address.
(Forgive me if this is a silly question. I'm not terribly familiar with PCI compliance.)
Most likely the SQL service (default: port 1433) is using TLS without a proper PCI compliant cert.
Or the IIS was scanned and that is what is non-compliant.
But I’m just guessing at this point…
[I kind of doubt that the IIS is linked to SQL]
Whoops. Yeah, missed the “there is not a web service running” bit in the original message.
In any case, more info is definitely needed. What is this SQL server’s purpose? As in, what is connecting to it and from where?
Getting a cert for an internal server in Windows using DNS auth is definitely doable. But ultimately, @Menneset may not actually need one. Or there might be a better way to go about doing things than trying to wrangle an LE cert on it.
No, I am sure it is not related to SQL. But it is causing the PCI test to fail. I attempted to be as through in my post, and SQL is a significant part of the system.
SQL is installed because it is. I did not set up the system, nor have I managed it for a significant period of time. Again I wanted my question to be through.
IIS is not linked to SQL. IIS is on the server because whoever installed 2012 did not uncheck that option. Although when I looked only the management tools are actually installed.
SQL is running a database that the firm uses to manage their clients and case files. Employees at the firm connect to it over the LAN through a .Net app on their workstations. To the best of my knowledge, nothing is speaking on port 80 or 443. SQL uses TCP 1443 and UDP 1434 and I guess TCP 135. Which are all configured in the firewall.To the best of my knowledge, nothing is speaking on ports 80 or 443.
Yet we are getting hit on those three items during PCI testing. and until we are not–the firm cannot accept credit cards,
I am perfectly OK with a trusted version of a local cert, I just don;t know where to start,
What is scanning the server is a PCI compliance certification authority. I can post the whole report if needed it is about 12 pages. They have an IP address to scan against, so I do not understand why SSL is even an issue much less why it is failing. We are in a position where we get a few more attempts to correct the problems and then it is back of the line.
This article describes what I want to do fairly well–except for acquiring the cert.
So traditionally in this sort of environment where you (the organization) manage both the server and clients, you stand up (or already have) your own internal Certificate Authority using something like Active Directory Certificate Services. You then install the CA certificate on all of the clients in the environment so they trust the certs you create. Finally, you generate a long-lived cert for your service are largely done. But all that can be a fairly heavy task for someone who is not terribly familiar with running a CA. And it’s even more cumbersome if all of the clients aren’t centrally managed already.
Your second option is a (likely paid) cert from a publicly trusted CA that lasts 1+ years. It’s a fairly manual process, you install it on your server, and you have to repeat every time it’s about to expire.
Your last option with Let’s Encrypt is free in monetary cost, but it has it’s own time cost as you’ve already probably found. The benefit is that the time cost is only up front and once it’s working it should hypothetically work without maintenance for the forseeable future.
The thing with Let’s Encrypt certs is that they only last 90 days. So you really need to automate the whole process of getting the cert, installing it into the service, and renewing every 60’ish days. But most of the existing clients are only really designed to automate that process against web servers, not MS SQL server. So you’re likely going to have to do at least a bit of scripting to make this work.
If you want to go this route, you should pick a Windows compatible client that ideally has DNS challenge support for the provider that hosts your meadorjohnsonlaw.com domain. Bonus points if it has built in ways to hook your own post-processing scripts. Then, it’s just a matter of configuring the client to get the cert using the DNS challenge against your provider and scripting the install of the cert into SQL Server.
Do you know what provider hosts the DNS for your domain?
1&2 … What I meant was IIS is just on the server. I did not put it there, and to my knowledge they have never locally hosted a website. So the answer to why is–for no particular reason. It is on the server, but not running.
3 … It looks like TCP 3381. I am not aware of anything using that port, but apparently it is open. I need to figure out why.
As to why SQL is on the server… that is the primary purpose of the server. This is a small law firm with one server and maybe a dozen workstations. The server handles SQL, AD, DHCP, DNS, and file shares.
You do need to find which system is the one servicing the non-compliant port 3381.
And which program/service is the one responsible for it.
It might not be related to SQL at all.
3381 is eerily close to 3389 which is the standard RDP port. RDP supports TLS, uses a self-signed cert by default, and can be configured to listen on a different or additional port. I wonder if some previous admin was trying to make his/her remote management life easier by exposing “the server” to the Internet via RDP and trying to avoid hack attempts by using a non-standard port. Do you have visibility into the office’s router to check for port fowarding rules?
You could also run this PowerShell to check the ports RDP is listening on.
gp 'HKLM:\System\CurrentControlSet\Control\Terminal Server\WinStations\*\' | ?{ $_.PortNumber } |
select PSChildName,PortNumber
Yes, I just did that and the port was forwarded to a workstation. I blocked the port on the router, I see no reason to have rdp on a workstation in a back office. That should take care of the SSL issue in the PCI report. Another scan is scheduled to run tonight so I will see in the morning where we are. I think I am still going to pursue a certificate for the sole purpose of connecting to SQL, but assuming this pci scan passes, I will have time to figure it out and do it correctly.
For now I am going to assume that the immediate issue is resolved. thank you.
Hmmm. Not at all related to SQL, but my personal situation is that I have both an internal network and an external connection to the Internet. I run split-horizon DNS (see
)
, That lets me have some subdomains internal to my network, not visible to the rest of the world. On the external facing equipment, I have acquired a “wildcard” LE cert. I copied it (rsync) to some of the internal servers that happened to need a certificate. They each have their own subdomain, and thus everything worked just fine. Incidentally, just for the “foo” of it and for no good reason, I believe all my servers are PCI-HIPAA-DSS compliant. I wouldn’t know how to test the internal ones without exposing them though. The external servers go through htbridge just fine.