Configuring DataSunrise Sniffer for MS SQL Server
The key trait of Microsoft SQL Server is that its main client application, SQL Server Management Studio, always requires encryption even if Encrypt connection checkbox is unchecked.
It means for any sniffer, that it is impossible to listen on encrypted traffic or the sniffer will require a private server key for its decryption. The DataSunrise sniffer can decrypt SSL traffic if it has the private key, so we will dwell on configuring a server for DataSunrise operation in sniffer mode.
On default, the server is configured to work with ephemeral keys — there are no static keys and certificates established for it. The certificate and the key are generated for each connection. Such a strategy guarantees a high level of security of all server connections. Thus it’s clear that integrated Microsoft cryptoprovider on the newest Windows versions increased priority level of all its ephemeral ciphers. And now it is becoming more difficult to switch on ciphers more appropriate for sniffing without additional server configuring.
Certificate
To disable ephemeral ciphers and get static private key it is necessary to install a certificate. It could be done via SQL Server Configurations Manager (Protocols for MSSQLSERVER features, SQL Server Network Configuration settings, Certificate tab):
Here we can select a certificate out of the list which is uploaded from local certificate Windows store.
There are some Microsoft requirements for preparing the certificate.
- The certificate must be in either the local computer certificate store or the current user certificate store.
- The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
- The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
- The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE.
- The Subject property of the certificate must indicate that the common name (CN) is the same as the hostname or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
To create a certificate which complies with these conditions, you can use Make Cert utility included in Windows SDK.
makecert -r -pe -n "CN= HERE24322118" -b 01/09/2016 -e 01/09/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 In this example “HERE24322118” certificate will be created and placed in local certificate store. At this stage this certificate can be selected from certificate list of SQL Server Configuration Manager. And after the server reboot it could be used to secure network connections.
Server Key
The next step is to get the server key. To do this it is necessary to export it from a certificate store, and retrieve key.pem out of certname.pfx:
openssl pkcs12 -in certname.pfx -nocerts -out key.pem -nodes Key.pem is the private key which the sniffer requires.
<>The server is configured and its private key is got, but it still uses ephemeral algorithms because of the cryptoprovider. To disable the ephemeral key exchange algorithms it is necessary to refer to the corresponding Microsoft guide or its GUI interpretation — IIScrypto.
Here you need to disable 2 key exchange algorithms:Diffie Hellman and ECDH. The changes will come into effect after the host server is rebooted.
Key Installation in DataSunrise
The final step is to install the key into DataSunrise. To do this, we open the Configurations tab, select the required database, open Certificates window, PrivateKey tab and paste the private key copied from the file.
The configuring of the server and the firewall for SQL Server sniffing is complete. And we will think how to make the protection of your infrastructure simpler and better.
