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. DataSunrise sniffer can decrypt SSL traffic if it has the private key, so we will dwell on configuring 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 guaranties 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 became more difficult to switch on ciphers more appropriate for sniffing without additional server configuring. 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.
  1. The certificate must be in either the local computer certificate store or the current user certificate store.
  2. The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
  3. 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).
  4. The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE.
  5. The Subject property of the certificate must indicate that the common name (CN) is the same as the host name 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 th