Does sqllogship.exe have anything to do with web servers in the internet? Story behind CRL check for certificates ...

  • Article

Hi there,

In today’s blog post I’m going to talk about a network trace analysis scenario where I was requested to analyze a few network traces to understand why a server was trying to contact external web servers when sqllogship.exe was run on it.

Our customer’s security team noticed that there were http connection attempts coming from internal SQL servers where those servers wouldn’t supposed to be connecting any external servers. The only thing they were running was something like that:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqllogship.exe" -Backup 1B55E77D-A000-1EE8-9780-441096E2151 -server PRODDB

And in every attempt there were blocked http connections seen on the firewall. Since we didn’t know what the server would do after establishing such an HTTP connection to an external network we weren’t able to make much comment on this. I requested our customer to let the firewall allow such an http connection so that we would be able to get more information after the connection was established, this is a method (method 5) I mentioned in one of my earlier posts

After our customer made such a change and re-collected a network trace on the SQL server, it was now more clear why the SQL server was attempting to connect to a remote web server: To verify if the certificate was revoked or not by downloading the CRL (certificate revocation list):

=> SQL server first resolves the IP address for the name: crl.microsoft.com

No. Time Source Destination Protocol Info

23519 2010-06-26 09:23:14.560786 10.11.1.11 10.1.1.1 DNS Standard query A crl.microsoft.com

23520 2010-06-26 09:23:14.561000 10.1.1.1 10.11.1.11 DNS Standard query response CNAME crl.www.ms.akadns.net

|-> crl.microsoft.com: type CNAME, class IN, cname crl.www.ms.akadns.net

|-> crl.www.ms.akadns.net: type CNAME, class IN, cname a1363.g.akamai.net

|-> a1363.g.akamai.net: type A, class IN, addr 193.45.15.18

|-> a1363.g.akamai.net: type A, class IN, addr 193.45.15.50

=> SQL server establishes a TCP session to port 80 at the remote web server running on 193.45.15.50:

No. Time Source Destination Protocol Info

  69679 2010-06-26 09:24:37.466403 10.11.1.11 193.45.15.50 TCP 2316 > 80 [SYN] Seq=0 Win=65535 Len=0 MSS=1460

  69697 2010-06-26 09:24:37.554390 193.45.15.50 10.11.1.11 TCP 80 > 2316 [SYN, ACK] Seq=0 Ack=1 Win=5840 Len=0 MSS=1460

  69698 2010-06-26 09:24:37.554407 10.11.1.11 193.45.15.50 TCP 2316 > 80 [ACK] Seq=1 Ack=1 Win=65535 [TCP CHECKSUM INCORRECT] Len=0

=> After the TCP 3-way handshake, the SQL server sends an HTTP GET request to the web server to retrieve the CSPCA.crl file

No. Time Source Destination Protocol Info

  69699 2010-06-26 09:24:37.554603 10.11.1.11 193.45.15.50 HTTP GET /pki/crl/products/CSPCA.crl HTTP/1.1

    |-> GET /pki/crl/products/CSPCA.crl HTTP/1.1\r\n

    |-> User-Agent: Microsoft-CryptoAPI/5.131.3790.3959\r\n

    |-> Host: crl.microsoft.com\r\n

  69729 2010-06-26 09:24:37.642219 193.45.15.50 10.11.1.11 TCP 80 > 2316 [ACK] Seq=1 Ack=199 Win=6432 Len=0

  69731 2010-06-26 09:24:37.645483 193.45.15.50 10.11.1.11 PKIX-CRL Certificate Revocation List

    |-> HTTP/1.1 200 OK\r\n

...

    |-> Certificate Revocation List

    |-> signedCertificateList

    |-> algorithmIdentifier (shaWithRSAEncryption)

Note: It looks like this is done due to the following: (Taken from https://support.microsoft.com/kb/944752)

“When the Microsoft .NET Framework 2.0 loads a managed assembly, the managed assembly calls the CryptoAPI function to verify the Authenticode signature on the assembly files to generate publisher evidence for the managed assembly.”

=> Similarly the server sends another HTTP GET request to retrieve CodeSignPCA.crl:

No. Time Source Destination Protocol Info

  77631 2010-06-26 09:24:52.642968 10.11.1.11 193.45.15.50 HTTP GET /pki/crl/products/CodeSignPCA.crl HTTP/1.1

  77747 2010-06-26 09:24:52.733106 193.45.15.50 10.11.1.11 PKIX-CRL Certificate Revocation List

  78168 2010-06-26 09:24:53.011176 10.11.1.11 193.45.15.50 TCP 2316 > 80 [ACK] Seq=403 Ack=1961 Win=65535 [TCP CHECKSUM INCORRECT] Len=0

...

Note: Again it looks like this is done due to the following: (Taken from https://support.microsoft.com/kb/947988 You cannot install SQL Server 2005 Service Pack 1 on a SQL Server 2005 failover cluster if the failover cluster is behind a firewall)

“When the Microsoft .NET Framework starts SSIS, the .NET Framework calls the CryptoAPI function. This function determines whether the certificates that are signed to the SQL Server assembly files are revoked. The CryptoAPI function requires an Internet connection to check the following CRLs for these certificates:

https://crl.microsoft.com/pki/crl/products/CodeSignPCA.crl

https://crl.microsoft.com/pki/crl/products/CodeSignPCA2.crl”

It looks like there’re a number of solutions to prevent such CRL checks like changing “generatePublisherEvidence” or “Check for publisher’s certificate revocation” as explained in KB944752 or KB947988.

Hope this helps

Thanks,

Murat