Defense, Security, Public Safety, Government and Financial customers sometimes require additional protection from Man-in-the-Middle and other threats. SQL Server like other commercially available databases supports encrypting the communication channel between the SQL Server database and the client application.
The encryption mechanism is handled between the SQL Native Access Client (SNAC) and SQL Server and is therefore transparent to the application layer.
The CPU overhead of SSL encryption used to be significant many years ago, however with the advances in the performance of Intel and AMD commodity servers this is usually no longer more than a few percent.
For this tutorial we use the following versions:
|Database version||Microsoft SQL Server 2008 R2 Enterprise|
|Operating Systems||Windows Server 2008 R2 Enterprise|
|SAP||NetWeaver 7.00 SR3|
The basics should also apply to other version e.g. Microsoft SQL Server 2008 Enterprise. However, the screenshots will exclusively show the screens of the versions above.
For this tutorial we use the following landscape
We have two Windows Failover Clusters. The first cluster contains the database and consists of two nodes. The second cluster contains the SAP Central Services and also consists out of two nodes.
For the HA scenario to be complete we use two SAP application servers. Every machine is joined to the cluster.local domain.
SQL Server communicates between the Database server and the client via a protocol called TDS.
Further information about TDS can be found here http://msdn.microsoft.com/en-us/library/ms191220.aspx
The SQL Client software on the Database Server encrypts the TDS packages before these are passed to the Windows Network layer. The SQL Native Access Client on the application server decrypts the TDS packages and passes the results to the application. The process is completely transparent to the application.
SSL Certificate does not compress or encrypt the data stored in the SQL Server data files on disk.
OSS Note 1380493 discusses SQL Server Transparent Data Encryption
OSS Note 1488135 discusses how to use SQL Server Page compression
SQL Server SSL Certificate encryption is a completely different technology than IPsec, which is a more versatile and generic solution to secure network communication of any type. Microsoft & SAP fully support the use of IPsec for SAP applications. IPSec or SQL Server SSL Certificate encryption can be used in combination with SQL Server TDE (as per OSS Note 1380493) and Compress the Database to save space (as per OSS Note 1488135)
The encryption of the communication can be configured on two sides. Depending on the place where you configure the encryption you can have different prerequisites e.g. for the certificate and different benefits.
If you configure the encryption on the server side every connection will be encrypted using the configured certificate or the default certificate.
Communication between client and server is encrypted
You only have to configure this on the database servers
|Disadvantages||Client does not validate the server certificate i.e. Man-In-The-Middle attacks can occur|
If you configure the encryption on the client side every client can decide if he wants to encrypt the communication or not.
|Prerequisites||The client must trust the certificate of the server|
Communication between client and server is encrypted
The client is protected from Man-In-The-Middle attacks by checking the server's certificate
|Disadvantages||All clients have to be configured separately by using SQL Server Configuration Manager|
We will use client configured encryption because we also want to exclude Man-In-The-Middle attacks.
There are several steps that you have to perform in order to get encryption to work. We will start with the installation and configuration of the certificates. Then we will configure the database server and last but not least configure the SAP application servers.
To install and configure the certificates you have to follow these steps which are described in details below
For the requirement of the certificate see http://msdn.microsoft.com/en-us/library/ms189067.aspx
For testing purposes you can also create a self-signed certificate with the following command line
makecert -r -pe -n CN="<MySAP_SQLServerName.fully.qualified.domain.name>" -b 01/01/2000 -e 01/01/2036 -eku 126.96.36.199.188.8.131.52.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 d:\path\certificatefile.cer
In our test scenario the command line would be
makecert -r -pe -n CN="VNAME-SQL.cluster.local" -b 01/01/2000 -e 01/01/2036 -eku 184.108.40.206.220.127.116.11.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 d:\path\certificatefile.cer
If you run this tool on all database servers, makecert will install the certificate in the certificate store of the local computer. You can therefor skip step 3 to 8 of step 1 below. For more information about makecert see http://msdn.microsoft.com/en-us/library/aa386968(VS.85).aspx
You have to install the certificate to:
The configuration of the certificate differs if you have a clustered SQL Server or if you only have a single database server.
Clustered SQL Server
Single database server
Check the SQL Server Error Log after the restart:
If this message is seen in the SQL Server Error Log, this indicates the certificate is invalid or cannot be accessed.
2011-02-06 20:24:49.48 Server Error: 17182, Severity: 16, State: 1.
2011-02-06 20:24:49.48 Server TDSSNIClient initialization failed with error 0xd, status code 0x38. Reason: An error occurred while obtaining or using the certificate for SSL. Check settings in Configuration Manager. The data is invalid.
This message refers to the self signed certificate that SQL automatically generates whenever it is started. This is for encrypting logon information only. This is not sufficient to encrypt all TDS traffic. Ignore this message
2011-02-06 20:43:31.39 Server A self-generated certificate was successfully loaded for encryption.
If the certificate is correctly installed, a message similar to this should appear
2011-02-07 00:38:26.29 Server The certificate [Cert Hash(sha1) "E26156487D20B9C88F815622E6F64FAA1C9123DE"] was successfully loaded for encryption.
You have to configure the SQL Server Native Access Client on every SAP application server ( CIServer and DIServer ). You have to have SQL Server Configuration Manager installed on these servers. SQL Server Configuration Manager is not part of the SQL Server Native Access Client installation. You have to install the basic Management Tools using SQL Server setup.
To configure SQL Server Native Access Client on your application servers, perform the following steps on every application server.
If your SAP application servers are running, you can test if the communication is encrypted by executing the following query:
select encrypt_option, client_net_address from sys.dm_exec_connections order by encrypt_option, client_net_address
|FALSE||10.10.10.1||<-- Database Server (DBServer1)|
|FALSE||10.10.10.2||<-- Database Server (DBServer2)|
|TRUE||10.10.10.3||<-- SAP Application Server (CIServer)|
|TRUE||10.10.10.4||<-- SAP Application Server (DIServer)|
Since we did not configure the SQL Server Native Client for the database servers the connections from the database servers are still unencrypted. The failover cluster connects to the SQL Server to test if the SQL Server is still alive. This is fine since there's no user data transferred.
Provided the following conditions are met there are no adverse performance impacts even on large busy systems:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.