IPSec through NAT: Securing SQL Server remote connections

This quick tutorial will show you how to secure SQL Server access (or any other service) leveraging “raw” IPSec encryption in transport mode. Two batch scripts are used to setup server-side and client-side configuration of IPSec policy. This setup is not using VPN of any kind (L2TP) and no RRAS is involved.

Present config

Local clients are accessing SQL Server on the LAN by directly connecting to it. Remote clients are using SSH port forwarding to make encrypted tunnels to the DB server. This is done in the client application by shelling plink.exe from PuTTy and CopSSH (OpenSSH for Windows) on the server side. Everything is routed through a Linksys router with dd-wrt firmware.

IPSec through NAT Traversal

As per KB926179 for NAT-T to be consided by IPSec there is a registry value that has to be added — AssumeUDPEncapsulationContextOnSendRule (REG_DWORD) = 2 in HKLM\SYSTEM\CurrentControlSet\Services\PolicyAgent for Windows Server 2003 and above or HKLM\SYSTEM\CurrentControlSet\Services\IPSec for plain XPs. Don’t forget to restart IPsec Policy Agent/IPSEC Services service for the changes to take effect (on XP restart the whole machine). Note that this registry value has to be set both on client and server machines. Value of 2 means that both client and server can be behind NAT devices.

Adding a second listening port to SQL Server

Because we want to secure connection only from the remote users, we have to add one more port SQL Server will listen to. We’ll setup IPSec to secure connections only to this port.

Adding listeners can be done with additional T-SQL endpoints in SQL Server, but easiest way is using SQL Server Configuration Manager, properties on TCP/IP protocol, second tab bottom in TCP Dynamic Ports enter comma-separated ports (orig_port,second_port). After publishing this second TCP port on the border router’s external IP it’s time to test if remote clients can access the SQL Server from internet without IPSec encryption.

Setting IPSec publishing on the router

When IPSec detects remote endpoint’s IP address differ from actual address the packets are coming from, it concludes that there is one or more NAT devices in the way and tries to switch to NAT-T by encapsulating encrypted traffic in UDP packets. Namely NAT-T tries a new connection on port 4500/udp to transfer ESP payload. So on the NAT device in front of SQL Server you have to forward ports 4500/udp and 500/udp to the SQL Server behind it, besides the TCP port SQL Server is listening to. Nothing else needs to be forwarded for NAT-T (neither 1701/udp nor IP protocol 50 packets). You can publish only one SQL Server on a single external IP address of the border router because NAT-T can not be multiplexed on a single IP.

If using ISA Server for publishing IPSec make sure that IPsec Policy Agent/IPSEC Services is stopped on the ISA machine. The reason for this is that otherwise the IPSec filter on the router machine will handle 500/udp and 4500/udp traffic before ISA can forward it to the SQL Server.

Setting IPSec on the SQL Server

We’ll leave original port unencrypted and will setup IPSec to encrypt traffic only on the second port. We’ll use ipsec_srv.bat like this

c:>ipsec_srv 1618 preshared_password

where 1618  is the second port we made SQL Server listen to and preshared_password is a preshared key we’ll use to encrypt traffic.

The whole ipsec_src.bat looks like this:

@echo off
if "%2"=="" goto :usage
setlocal
set ipsec=netsh ipsec static
set pol_name=SQL Servers policy
set faction_name=ESP[3DES,SHA1] encryption
set flist_name=Port %1
set preshared_key=%2

if not "%3"=="/a" (
    call :service_registry PolicyAgent AssumeUDPEncapsulationContextOnSendRule 2
    echo Clearing %pol_name%...
    %ipsec% delete policy name="%pol_name%"
    %ipsec% add policy name="%pol_name%" activatedefaultrule=no mmsecmethods="3DES-SHA1-2"
    %ipsec% set policy name="%pol_name%" assign=yes
    %ipsec% add filteraction name="%faction_name%" qmpfs=yes action=negotiate qmsecmethods="ESP[3DES,SHA1]:3600s"
    )
echo Adding %flist_name% filter list...
%ipsec% add filter filterlist="%flist_name%" protocol=tcp srcaddr=me srcport=%1 dstaddr=any mirrored=yes
%ipsec% add rule name="%flist_name%" filterlist="%flist_name%" psk="%preshared_key%" policy="%pol_name%" filteraction="%faction_name%"
goto :eof

:service_registry
reg query HKLM\SYSTEM\CurrentControlSet\Services\%1 /v %2 > nul 2>&1 && goto :eof
echo Setting %2 value to %3...
reg add HKLM\SYSTEM\CurrentControlSet\Services\%1 /v %2 /t REG_DWORD /d %3 /f
net stop PolicyAgent
net start PolicyAgent
goto :eof

:usage
echo usage: ipsec_srv.bat ^<port^> ^<pass^> [/a]
echo.
echo    ^<port^> - TCP port server is listening on
echo    ^<pass^> - preshared key
echo    /a     - (optional) append to %pol_name% a local port filterset.
echo             if not specified previous policy settings are cleared.

Setting IPSec on client machines

We’ll setup IPSec on connections to the external IP of the router in front of the SQL Server by using ipsec_client.bat like this:

c:>ipsec_client db_srv.company.com 1618 preshared_password

where db_srv.company.com is the external address of the router in front of the SQL Server, 1618 is the forwarded port to the encrypted port of the SQL Server. The script ipsec_client.bat looks like this:

@echo off
if "%3"=="" goto :usage
setlocal
set ipseccmd=ipseccmd.exe
set ipsec=netsh ipsec static
set pol_name=SQL Servers policy
set faction_name=ESP[3DES,SHA1] encryption
set flist_name=%1 (%2)
set preshared_key=%3

netsh ipsec > nul || goto :use_ipseccmd

if not "%4"=="/a" (
    call :service_registry PolicyAgent AssumeUDPEncapsulationContextOnSendRule 2
    echo Clearing %pol_name%...
    %ipsec% delete policy name="%pol_name%"
    %ipsec% add policy name="%pol_name%" activatedefaultrule=no mmsecmethods="3DES-SHA1-2"
    %ipsec% set policy name="%pol_name%" assign=yes
    %ipsec% add filteraction name="%faction_name%" action=negotiate qmsecmethods="ESP[3DES,SHA1]:3600s" qmpfs=yes > nul
    )
echo Adding %flist_name% filter list...
%ipsec% add filter filterlist="%flist_name%" dstaddr=%1 dstport=%2 srcaddr=me protocol=tcp mirrored=yes
%ipsec% add rule name="%flist_name%" filterlist="%flist_name%" psk="%preshared_key%" policy="%pol_name%" filteraction="%faction_name%"
goto :eof

:use_ipseccmd
if not "%4"=="/a" (
    call :service_registry IPsec AssumeUDPEncapsulationContextOnSendRule 2
    echo Clearing %pol_name%...
    %ipseccmd% -w reg -p "%pol_name%" -o 2> nul
    )
echo Adding %flist_name% filter list...
%ipseccmd% -w reg -p "%pol_name%" -r "%flist_name%" -f 0+%1:%2:TCP -n esp[3des,sha]3600SPFS2 -1s 3des-sha-2 -a p:"%preshared_key%" -x
goto :eof

:service_registry
reg query HKLM\SYSTEM\CurrentControlSet\Services\%1 /v %2 > nul 2>&1 && goto :eof
echo Setting %2 value to %3...
reg add HKLM\SYSTEM\CurrentControlSet\Services\%1 /v %2 /t REG_DWORD /d %3 /f
net stop PolicyAgent
net start PolicyAgent
goto :eof

:usage
echo usage: ipsec_client.bat ^<addr^> ^<port^> ^<pass^> [/a]
echo.
echo    ^<addr^> - server ip or name
echo    ^<port^> - TCP port server is listening on
echo    ^<pass^> - preshared key
echo    /a     - (optional) append to %pol_name% a new server port filterset.
echo             if not specified previous policy settings are cleared.

The script uses netsh ipsec command to setup IPSec policy if available and falls back to ipseccmd.exe on XPs. It takes care of PolicyAgent’s AssumeUDPEncapsulationContextOnSendRule registry setting too.

The script sets up IPSec transport mode to the server port using ESP (AH can’t be used with NAT-T) with 3DES and SHA1 only and rekeying after an hour (3600 seconds). The added IPSec filter action does not allow fallback to unsecured communication so encryption happens at all times.

The script can use an optional /a parameter to append IPSec settings for another SQL Server or another TCP service. Not providing this parameter will clear IPSec policy before recreating it with a single filter set to the server address and port.

TCP Chimney Offload

Sometimes chimney offload might not work correctly. This depends on NIC hardware and NIC drivers. We’ve been having problems on an IBM System x3650 server with a Broadcom BCM5708C NetXtreme II GigE (NDIS VBD Client) ethernet card where IPSec with chimney failed establishing connections to the SQL Server.

To show current chimney settings use

c:>netsh int tcp show global
Querying active state...
TCP Global Parameters
----------------------------------------------
Receive-Side Scaling State          : enabled
Chimney Offload State               : automatic
NetDMA State                        : enabled
Direct Cache Acess (DCA)            : disabled
Receive Window Auto-Tuning Level    : normal
Add-On Congestion Control Provider  : ctcp
ECN Capability                      : disabled
RFC 1323 Timestamps                 : disabled

To disable TCP Chimney support use

c:>netsh int tcp set global chimney=disabled

Conclusion

Replacing SSH with IPSec to our SQL Servers is an ongoing project. All the interesting bits will be shared here.

Links

How to pass IPSec traffic through ISA Server

Netsh commands for Internet Protocol security

ipseccmd.exe parameters explained

Using Netsh Commands to Enable or Disable TCP Chimney Offload

Advertisement