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 http://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 http://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:

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

http://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