SQL Server network encryption with SAP

Reason

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.

Prerequisites

For this tutorial we use the following versions:

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.

Landscape

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.

How do SSL Certificates for SQL Server Work?

SQL Server communicates between the Database server and the client via a protocol called TDS.

Further information about TDS can be found here https://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)

Server configured encryption VS client configured encryption

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.

Server configured encryption

If you configure the encryption on the server side every connection will be encrypted using the configured certificate or the default certificate.

Client configured encryption

If you configure the encryption on the client side every client can decide if he wants to encrypt the communication or not.

We will use client configured encryption because we also want to exclude Man-In-The-Middle attacks.

Walkthrough

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.

Certificate

To install and configure the certificates you have to follow these steps which are described in details below

1. Create the certificate
2. Deploy the certificate on the DBMS servers
3. Deploy on the Application servers

For the requirement of the certificate see https://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 1.3.6.1.5.5.7.3.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 1.3.6.1.5.5.7.3.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 https://msdn.microsoft.com/en-us/library/aa386968(VS.85).aspx

You have to install the certificate to:

1. Local computer store of all database server of the Windows Failover Cluster (DBServer1 and DBServer2)

   1. Open the Microsoft Management Console (MMC) as an Administrator.

   2. Choose File -> Add/Remove Snap-in…

   3. On the left side in the list of available snap-ins double-click on Certificates or select Certificates and click Add >

      

   4. In the Certificates snap-in dialog select Computer account and click Next >

      

   5. Make sure that Local computer is selected and click Finish

      

   6. Right click on Personal, go to All Tasks and select Import…

      

   7. Follow the wizard to import the certificate. Make sure that the certificate contains the private key

      

   8. Now we have to change the permissions for the private key of the certificate. We need to add the SQL Server Service account.

   9. Right click on the certificate, go to All Tasks and select Manage Private Keys… If you do not have this entry in the context menu, make sure that you imported the certificate with the private key.

      

   10. Add the SQL Server Service account with Read permissions.

       

2. The certificate must be valid on the client machines i.e. SAP application servers (CIServer and DIServer). If you want to check if the certificate is valid, export the certificate and copy the file to the application servers. If you open the certificate and go to the Certification Path tab you should see something similar to the following screenshot.

   

   If the certificate is not valid you need to add the certificate of the Certificate Authority (CA) or the certificate itself to the Trusted Root Certification Authorities of the Local Computer store of the application servers.

   1. Perform step a. to e. of 1. (Open MMC and add the certificate snap-in for the Local Computer) on CIServer and DIServer

   2. Right click on Trusted Root Certification Authorities, go to All Tasks and select Import…

      

   3. Import the public key certificate of your CA or the certificate itself. Remember to only import the public key of the certificate.

Database Server

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

1. Log in as an user with administrative privileges on the Failover Node that currently hosts the SQL Server. It is very important to start with the active node because the configuration can be overwritten when failing over to another node.

2. Open the registry editor (Run -> regedit)

3. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<SQL version>.<Instance Name>\MSSQLServer\SuperSocketNetLib
   In our case this would be HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib

4. Edit the key Certificate and enter the thumbprint of the certificate that should be used by SQL Server. Remove the spaces from the thumbprint. The thumbprint is displayed if you open the certificate and go to the tab Details, see https://msdn.microsoft.com/en-us/library/ms734695.aspx for details on how to retrieve the thumbprint of a certificate.

   

5. Move the SQL Server cluster resource to the other nodes to distribute the configuration.

Single database server

1. Open SQL Server Configuration Manager

2. On the left side, expand SQL Server Network Configuration

3. Right click on the Protocols for your SQL Server instance and select Properties

4. On the Certificate tab select the certificate you want to use for encrypting the communication between SQL Server and the SAP application servers.

   

   Since this scenario is not in the scope of this walkthrough, the name of the certificate is not the name of one of the servers used in this walkthrough.

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.

SAP application servers

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.

1. Open SQL Server Configuration Manager

2. Right click on SQL Native Client 10.0 Configuration for a 64-bit SAP application server and select Properties.

3. Select Yes for Force Protocol Encryption

   

4. Restart the SAP application server

Test the communication

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

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.

Known Restrictions, Limitations & Troubleshooting

1. Additional CPU overhead is the most frequent concern raised by customers with respect the use of SSL certificates.  There is an additional round trip and some addition CPU consumption.  Due to exponential increases in the CPU power of modern Intel & AMD servers this overhead is not more than a few percent. A conservative approach to SAP sizing would involve adding an additional 5% to the required SAPS numbers, though in practice the difference in CPU consumption on a medium sized SAP system would be below the margin of error.

Provided the following conditions are met there are no adverse performance impacts even on large busy systems:

1. Use Modern Intel Nehalem 55xx, 56xx or 75xx or higher or AMD Opteron 61xx processors
2. Ensure SAP application servers and SQL Database servers have ~6GB RAM per core
3. Ensure the network between the SQL Database server and the SAP application server is highly performing:
   1. Bandwidth is at least 1Gbps (or 10Gbps or higher)
   2. Ensure the Network Card configuration is optimal (RSS Ring configuration etc)
   3. Test the network stability and performance with NIPING see OSS Note 500235 - Network Diagnosis with NIPING and OSS Note 1100926 - FAQ: Network performance
   4. Check the SAP application log with transaction SM21 and ensure there are not a large number of network related errors
   5. Review OSS Note 392892
   6. SQL Database and Application servers must be on the same switch, same VLAN and same subnet
4. There are a very large number of SAP components that use SQL Server besides the core ABAP & Java components.  This solution has been tested for ABAP and Java stacks of SAP NetWeaver on SQL Server 2008 R2 and Windows Server 2008 R2.
5. SAP Java Components based on Java 1.4.2xx standard may be impacted by the error in OSS Note 1428134 - sqljdbc-driver fails to establish secure connection.  This problem applies to SAP components with version 7.00, 7.01 (Enhp 1), 7.02.  The note describes how to fix this problem on SAP components based on 7.10 or 7.11 by upgrading the JDBC driver.  SAP 7.1x these components use a newer version of Java.
6. It is possible to use the Windows SDK utility called makecert.exe to create a test certificate.  This type of certificate is not suitable for Production systems.  Also note there are multiple versions of makecert.exe and older versions do not work as they do not export the private key. The best version of makecert.exe comes with the "Windows SDK for Windows Server 2008 and .NET Framework 3.5".  This can be obtained from download.microsoft.com.  It is recommended to install the SDK on a non-SAP server and then copy only makecert.exe to the SAP server.  The SDK will install several hundred MB of utilities that are not needed to operate a SAP server.
7. Error message when you use SSL for connections to SQL Server: "The certificate received from the remote server was issued by an untrusted certificate authority” – see https://support.microsoft.com/kb/2007728 for details on how to fix this issue