Signed Ghostscript PDF printer driver

When trying to install stock Ghostscript PDF print diver on a 64-bit Windows the OS requires the print driver to be signed and the installation fails (Add Printer dialog cannot be confirmed). You can disable this OS protection feature with a special system restart but the setting will last until the next restart.

Here is a faster way to install the printer on x64

So following instructions in this stackoverflow answer I managed to produce a signed ghostpdf.cat for Ghostscript PDF print diver. Our code-signing certificate will expire on Aug 29th, 2014 but I hope to have the time to update the certificate by then.

Enjoy!

Links

Signed Ghostscript PDF print driver

Posted in Articles | Tagged , , | Leave a comment

How to use fail2ban with Terminal Servers (RDSH) farm

We are using haproxy for token redirected load balancing of a Remote Desktop Session Hosts farm and because the farm is exposed on the default RDP port (3389) it is subjected to constant brute-force attacks. These attacks besides being a nuisance do slow down the terminal servers and cause Remote Desktop Services Manager to constantly refresh users list which annoys support people even more.

How we use fail2ban to block IPs based on failed logon attempts

First we created a simple monitoring script on the terminal servers which listens for failed logon attempts and transfers these to a log file (/var/log/rdp/audit.log) on the gateway machine where the haproxy is installed. The monitoring script is based on ts_block script by EvanAnderson but instead of blocking IPs using the local firewall it uses plink.exe to append log entries to /var/log/rdp/audit.log on the gateway.

The script logs three types of entries based on whether the user is recognized (from local Remote Desktop Users group), whether it’s strictly denied (most brute-forced users like Admin, Administrator, etc.) and whether it’s unknown (not in Remote Desktop Users group). This allows fail2ban to organize two jails: a soft one for recognized users (ban for 10 minutes) and a hard one for unknown/denied users (ban for a week). The format of the entries includes date/time, process name/id and originating terminal server name so it can be easily parsed and matched by fail2ban.

This is the configuration of the jails in /etc/fail2ban/jail.local we use

[rdp-hard-iptables]
enabled  = true
filter   = rdp-hard
action   = iptables-multiport[name=rdp-hard, port="3389,3390,3391", protocol=tcp]
logpath  = /var/log/rdp/audit-hard.log
maxretry = 1
bantime  = 604800 ; 1 week
ignoreip = ...

[rdp-soft-iptables]
enabled  = true
filter   = rdp-soft
action   = iptables-multiport[name=rdp-soft, port="3389,3390,3391", protocol=tcp]
logpath  = /var/log/rdp/audit.log
maxretry = 5
ignoreip = ...

The hard one is blocking access to ports 3389, 3390 and 3391 from the source IP for a week on first failed attempt. The soft jail is blocking access after 5 failed attempts for 10 minutes only. Something we found out is that two jails cannot use the same log file. That’s why the second one is using a hard-linked /var/log/rdp/audit-hard.log instead. You can populate ignoreip entries with you local subnets (e.g. 192.168.10.0/24) and with legitimate users known static IPs (e.g. 34.56.78.90)

Here is the denied/unknown users matching regular expression in rdp-hard.conf from /etc/fail2ban/filter.d

# Fail2Ban configuration file
#
# Author: Wqw
#

[Definition]

# Option: failregex
# Notes.: regex to match the password failures messages in the logfile. The
#          host must be matched by a group named "host". The tag "<HOST>" can
#          be used for standard IP/hostname matching and is only an alias for
#          (?:::f{4,6}:)?(?P<host>[\w\-.^_]+)
# Values: TEXT
#
# Sample log: Nov 8 14:55:13 TS03 rdplog.vbs[30296]: Unknown user name admino from 10.10.10.2
#
failregex = Unknown user name .* from <HOST>\s*$
            Denied user name .* from <HOST>\s*$

# Option:  ignoreregex
# Notes.:  regex to ignore. If this regex matches, the line is ignored.
# Values:  TEXT
#
ignoreregex =

And rdp-soft.conf

# Fail2Ban configuration file
#
# Author: Wqw
#

[Definition]

# Option: failregex
# Notes.: regex to match the password failures messages in the logfile. The
#          host must be matched by a group named "host". The tag "<HOST>" can
#          be used for standard IP/hostname matching and is only an alias for
#          (?:::f{4,6}:)?(?P<host>[\w\-.^_]+)
# Values: TEXT
#
# Sample log: Nov 5 17:39:10 TS02 rdplog.vbs[10896]: Login failed for administrator from 88.84.162.198
#
failregex = Login failed for .* from <HOST>\s*$

# Option:  ignoreregex
# Notes.:  regex to ignore. If this regex matches, the line is ignored.
# Values:  TEXT
#
ignoreregex =

Additionally we modified iptables-multiport.conf in action.d to use -m state --state NEW iptables option so that upon soft ban existing connections are not dropped. We have multiple users NATed behind a single source IP so this modifications prevents existing connection being dropped when someone fails to remember his/her password and blocks their common source IP.

...
actionstart = iptables -N fail2ban-<name>
              iptables -A fail2ban-<name> -j RETURN
              iptables -I <chain> -m state --state NEW -p <protocol> -m multiport --dports <port> -j fail2ban-<name>
...
actionstop = iptables -D <chain> -m state --state NEW -p <protocol> -m multiport --dports <port> -j fail2ban-<name>
             iptables -F fail2ban-<name>
             iptables -X fail2ban-<name>

We had some troubles with logrotate on audit.log. Apparently fail2ban does not recognize when the monitored file is rotated and stops matching entries. Tried copytruncate logrotate option without luck. Tried gamin backed on fail2ban with no success too.

Setting up monitoring script on the terminal servers

The rdplog.vbs script expects couple of parameters: use /s to specify the gateway server (where sshd and fail2ban are running) use /l for ssh user /pw for password or /i for key file. Optionally use /p for sshd port number if not the standard one.

The easiest way to start the script as unattended service on the Windows boxes is by using Task Scheduler. Just create a task for cscript.exe with parameters //nologo {full_path}\rdplog.vbs /s:{ssh_server} /l:{ssh_user} /i:{key_file.ppk} and with with At system startup trigger. This task can be started manually too.

To test the script just start it in a cmd console with cscript.exe //nologo rdplog.vbs /s:.... Note that the script shells plink.exe to connect to ssh server and its executable must be available in local folder or reachable in a folder in PATH.

Notes on SSL connections

Newest RDP clients use SSL to encrypt client connections to RDSH servers. It turns out that only connections with RDP Security do log the source IP in the system’s security log. There is no work-around for this problem, besides lowering the TCP listener encryption on the RDSH servers to RDP Security (which works but is not recommended). Still, our fail2ban solution does an excellent job because most of the brute-force attacks don’t use the more CPU intensive SSL connections yet.

Links

rdplog.vbs audit log transferring script

Posted in Articles | Tagged , , , | Leave a comment

Implementing preg_replace in VBScript

Here are a couple of wrapper VBScript functions that make using script’s built-in RegExp class similar to PHP’s preg_xxx family of functions.

First a private helper function to initialize an instance of RegExp:

Private Function preg_init(find_re)
    Set preg_init = New RegExp
    With preg_init
        .Global = True
        If Left(find_re, 1) = "/" Then
            Dim pos: pos = InStrRev(find_re, "/")
            .Pattern = Mid(find_re, 2, pos - 2)
            .IgnoreCase = (InStr(pos, find_re, "i") > 0)
            .Multiline = (InStr(pos, find_re, "m") > 0)
        Else
            .Pattern = find_re
        End If
    End With
End Function

This enables optionally to set flags of the object in the search string after the trailing slash e.g. “/test/mi” will search for “test” ignoring case and multi-line. This helper function is not meant to be used by the client scripts but helps implement the actual search&replace functions.

Next the implementation of preg_match, preg_replace and preg_split becomes very simple

Function preg_match(find_re, text)
    preg_match = preg_init(find_re).Test(text)
End Function

Function preg_replace(find_re, replace_arg, text)
    preg_replace = preg_init(find_re).Replace(text, replace_arg)
End Function

Function preg_split(find_re, text)
    Dim esc: esc = ChrW(&HE1B6) '-- U+E000 to U+F8FF - Private Use Area (PUA)
    preg_split = Split(preg_init(find_re).Replace(text, esc), esc)
End Function

These are fairly straight forward and are about enough to complete 99% of any search&replace task at hand. For preg_replace one can use replace placeholders in VBScript notation ($1, $2, etc.) to match search groups.

The troublesome 1% with this implementation of preg_replace is that it cannot handle callback functions for replace_arg. So here is a manual implementation of preg_replace that can handles both strings and a callback objects for replace_arg.

Function preg_replace_callback(find_re, replace_arg, text)
    Dim matches, match, count, offset, retval
    
    Set matches = preg_init(find_re).Execute(text)
    If matches.Count = 0 Then
        preg_replace_callback = text
        Exit Function
    End If
    ReDim retval(matches.Count * (1 - IsObject(replace_arg)))
    For Each match In matches
        With match
            retval(count) = Mid(text, 1 + offset, .FirstIndex - offset)
            count = count + 1
            If IsObject(replace_arg) Then
                retval(count) = replace_arg(match)
                count = count + 1
            End If
            offset = .FirstIndex + .Length
        End With
    Next
    retval(count) = Mid(text, 1 + offset)
    If IsObject(replace_arg) Then
        preg_replace_callback = Join(retval, vbNullString)
    Else
        preg_replace_callback = Join(retval, replace_arg)
    End If
End Function

For a callback object one has to pass an instance of a class with a default method. Here is sample class that implements PHP notation for replace placeholders (\1, \2, etc. or \{1}, \{2}, etc.)

Function preg_substitute(replace_arg)
    Set preg_substitute = New preg_substitute_class.init(replace_arg)
End Function

Class preg_substitute_class
    private m_esc
    Private m_replace
    
    Public Function init(replace_arg)
        m_esc = ChrW(&HE1B6) '-- U+E000 to U+F8FF - Private Use Area (PUA)
        m_replace = Replace(replace_arg, "\", m_esc)
        Set init = Me
    End Function
    
    Public Default Function callback(match)
        Dim idx, replace_str
        
        replace_str = match.Value
        callback = Replace(Replace(m_replace, m_esc & "{0}", replace_str), m_esc & "0", replace_str)
        With match.SubMatches
            For idx = .Count To 1 Step -1
                replace_str = .Item(idx - 1)
                callback = Replace(Replace(callback, m_esc & "{" & idx & "}", replace_str), m_esc & idx, replace_str)
            Next
        End With
        callback = Replace(callback, m_esc, "\")
    End Function
End Class

This can be used like preg_replace_callback("/(test)\s+(this)/mi", preg_substitute("\{2} \{1}")).

The most interesting method of the callback class is the default one — here it’s named callback but the actual name can be arbitrary. The default method receives a match argument which is the entry in the RegExp‘s matches collection and returns a string to be used as a replace string.

Match object exposes currently matched substring in Value property, its position in FirstIndex property and all the matched subgroups in SubMatches collection. This allows a much more sophisticated replacement implementation, for instance lower/upper casing entries in the SubMatches collection, etc.

The performance of preg_replace_callback is about 20% worse than RegExp‘s build-in Replace method used directly by the simple preg_replace even for thousands of occurrences of the search regular expression.

Full source code of these functions including sample usage is available in our pg_conv.vbs converting script that we used to convert a Microsoft SQL Server database table definitions script to PostgreSQL dialect.

Posted in Articles | Tagged , , , | Leave a comment

[VB6] Using WinInet to post binary file

Here are a couple of VB6 functions that can be used to upload a zip file through an http post request.

Using ready-made Microsoft.XMLHTTP or WinHttp.WinHttpRequest.5.1

First the easy one, using XMLHTTP to do the actual work

Private Function pvPostFile(sUrl As String, sFileName As String, Optional ByVal bAsync As Boolean) As String
    Const STR_BOUNDARY  As String = "3fbd04f5-b1ed-4060-99b9-fca7ff59c113"
    Dim nFile           As Integer
    Dim baBuffer()      As Byte
    Dim sPostData       As String

    '--- read file
    nFile = FreeFile
    Open sFileName For Binary Access Read As nFile
    If LOF(nFile) > 0 Then
        ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
        Get nFile, , baBuffer
        sPostData = StrConv(baBuffer, vbUnicode)
    End If
    Close nFile
    '--- prepare body
    sPostData = "--" & STR_BOUNDARY & vbCrLf & _
        "Content-Disposition: form-data; name=""uploadfile""; filename=""" & Mid$(sFileName, InStrRev(sFileName, "\") + 1) & """" & vbCrLf & _
        "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & _
        sPostData & vbCrLf & _
        "--" & STR_BOUNDARY & "--"
    '--- post
    With CreateObject("Microsoft.XMLHTTP")
        .Open "POST", sUrl, bAsync
        .SetRequestHeader "Content-Type", "multipart/form-data; boundary=" & STR_BOUNDARY
        .Send pvToByteArray(sPostData)
        If Not bAsync Then
            pvPostFile = .ResponseText
        End If
    End With
End Sub

Private Function pvToByteArray(sText As String) As Byte()
    pvToByteArray = StrConv(sText, vbFromUnicode)
End Function

The biggest benefit of using XMLHTTP is the async option. MSXML spawns a worker thread that sends the request even if the last reference to the object is set to nothing.

Instead of Microsoft.XMLHTTP one can use WinHttp.WinHttpRequest.5.1 but setting the last object reference to nothing cancels the async request. Edit: Not true! It turned out Microsoft.XMLHTTP seems to get corrupted more often in the wild due to bad installers. We are now using WinHttp.WinHttpRequest.5.1 exclusively for our uploads.

Another caveat is the pvToByteArray function. It turns out send method can not handle “byref” byte arrays, so for instance passing baBuffer will fail, as VB6 sets up VT_BYREF bit of the type of the variant parameter.

Using wininet.dll API

Here is the hard-core API version. Biggest drawback is that it’s syncronous by nature.

Private Const INTERNET_AUTODIAL_FORCE_ONLINE As Long = 1
Private Const INTERNET_OPEN_TYPE_PRECONFIG  As Long = 0
Private Const INTERNET_DEFAULT_HTTP_PORT    As Long = 80
Private Const INTERNET_SERVICE_HTTP         As Long = 3
Private Const INTERNET_FLAG_RELOAD          As Long = &H80000000
Private Const HTTP_ADDREQ_FLAG_REPLACE      As Long = &H80000000
Private Const HTTP_ADDREQ_FLAG_ADD          As Long = &H20000000

Private Declare Function InternetAutodial Lib "wininet.dll" (ByVal dwFlags As Long, ByVal dwReserved As Long) As Long
Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long
Private Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" (ByVal hInternetSession As Long, ByVal sServerName As String, ByVal nServerPort As Integer, ByVal sUsername As String, ByVal sPassword As String, ByVal lService As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Private Declare Function HttpOpenRequest Lib "wininet.dll" Alias "HttpOpenRequestA" (ByVal hHttpSession As Long, ByVal sVerb As String, ByVal sObjectName As String, ByVal sVersion As String, ByVal sReferer As String, ByVal something As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Private Declare Function HttpAddRequestHeaders Lib "wininet.dll" Alias "HttpAddRequestHeadersA" (ByVal hHttpRequest As Long, ByVal sHeaders As String, ByVal lHeadersLength As Long, ByVal lModifiers As Long) As Long
Private Declare Function HttpSendRequest Lib "wininet.dll" Alias "HttpSendRequestA" (ByVal hHttpRequest As Long, ByVal sHeaders As String, ByVal lHeadersLength As Long, ByVal sOptional As String, ByVal lOptionalLength As Long) As Long
Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Long

Private Function pvPostFile(sUrl As String, sFileName As String) As Boolean
    Const STR_APP_NAME  As String = "Uploader"
    Dim hOpen           As Long
    Dim hConnection     As Long
    Dim hRequest        As Long
    Dim sHeader         As String
    Dim sBoundary       As String
    Dim nFile           As Integer
    Dim baData()        As Byte
    Dim sPostData       As String
    Dim sHttpServer     As String
    Dim lHttpPort       As Long
    Dim sUploadPage     As String

    '--- read file
    nFile = FreeFile
    Open sFileName For Binary Access Read As nFile
    If LOF(nFile) > 0 Then
        ReDim baData(0 To LOF(nFile) - 1) As Byte
        Get nFile, , baData
        sPostData = StrConv(baData, vbUnicode)
    End If
    Close nFile
    '--- parse url
    sHttpServer = sUrl
    If InStr(sHttpServer, "://") > 0 Then
        sHttpServer = Mid$(sHttpServer, InStr(sHttpServer, "://") + 3)
    End If
    If InStr(sHttpServer, "/") > 0 Then
        sUploadPage = Mid$(sHttpServer, InStr(sHttpServer, "/"))
        sHttpServer = Left$(sHttpServer, InStr(sHttpServer, "/") - 1)
    End If
    If InStr(sHttpServer, ":") > 0 Then
        On Error Resume Next
        lHttpPort = CLng(Mid$(sHttpServer, InStr(sHttpServer, ":") + 1))
        On Error GoTo 0
        sHttpServer = Left$(sHttpServer, InStr(sHttpServer, ":") - 1)
    End If
    '--- prepare request
    If InternetAutodial(INTERNET_AUTODIAL_FORCE_ONLINE, 0) = 0 Then
        GoTo QH
    End If
    hOpen = InternetOpen(STR_APP_NAME, INTERNET_OPEN_TYPE_PRECONFIG, vbNullString, vbNullString, 0)
    If hOpen = 0 Then
        GoTo QH
    End If
    hConnection = InternetConnect(hOpen, sHttpServer, IIf(lHttpPort <> 0, lHttpPort, INTERNET_DEFAULT_HTTP_PORT), vbNullString, vbNullString, INTERNET_SERVICE_HTTP, 0, 0)
    If hConnection = 0 Then
        GoTo QH
    End If
    hRequest = HttpOpenRequest(hConnection, "POST", sUploadPage, "HTTP/1.0", vbNullString, 0, INTERNET_FLAG_RELOAD, 0)
    If hRequest = 0 Then
        GoTo QH
    End If
    '--- prepare headers
    sBoundary = "3fbd04f5-b1ed-4060-99b9-fca7ff59c113"
    sHeader = "Content-Type: multipart/form-data; boundary=" & sBoundary & vbCrLf
    If HttpAddRequestHeaders(hRequest, sHeader, Len(sHeader), HTTP_ADDREQ_FLAG_REPLACE Or HTTP_ADDREQ_FLAG_ADD) = 0 Then
        GoTo QH
    End If
    '--- post data
    sPostData = "--" & sBoundary & vbCrLf & _
        "Content-Disposition: multipart/form-data; name=""uploadfile""; filename=""" & Mid$(sFileName, InStrRev(sFileName, "\") + 1) & """" & vbCrLf & _
        "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & _
        sPostData & vbCrLf & _
        "--" & sBoundary & "--"
    If HttpSendRequest(hRequest, vbNullString, 0, sPostData, Len(sPostData)) = 0 Then
        GoTo QH
    End If
    '--- success
    pvPostFile = True
QH:
    If hRequest <> 0 Then
        Call InternetCloseHandle(hRequest)
    End If
    If hConnection <> 0 Then
        Call InternetCloseHandle(hConnection)
    End If
    If hOpen <> 0 Then
        Call InternetCloseHandle(hOpen)
    End If
End Function

It’s not hard adding authentication to send request but I don’t need it so never implemented it. The binary nature ot the upload is set by Content-Type being application/octet-stream.

Note that you cannot use Content-Transfer-Encoding with http, it’s reserved for e-mail only, i.e. no base64 encoding is necessary. Binary file is sent directly in the http stream. Note: feel free to change the boundary (use uuidgen.exe to generate a guid or anything unique).

The server-side script

Here is a simple upload_errors.php script that can be used as a target of the post request

<?php
$base_dir = dirname( __FILE__ ) . '/../ErrorsUpload/' . $_GET["id"];
if(!is_dir($base_dir))
    mkdir($base_dir, 0777);
move_uploaded_file($_FILES["uploadfile"]["tmp_name"], $base_dir . '/' . $_FILES["uploadfile"]["name"]);
?>

Basicly it expects an id param in the url and an uploadfile param in the body. Id is used to create a sub-directory in an off-site (publicly not visible) directory ErrorsUpload where the uploaded zip file is stored.

Here as the caller code I’m using that accesses the above upload_errors.php

Private Sub Command1_Click()
    pvPostFile "http://{{your_server_here}}/upload_errors.php?id={A0AD2346-9849-4EF0-9A93-ACFE17910734}", "C:\TEMP\Errors_2011_07_11.zip"
End Sub

I’m preparing a zip file with the errors that are logged at the client site, then I’m using the client id in the url, posting the zip file.

Edit: JScript implementation

Turns out byte-array to string handling is not so straight-forward in JScript. Here is a sample implementation of postFile function extensively using ADODB.Stream to handle conversions:

function readBinaryFile(fileName) {
    var stream = WScript.CreateObject("ADODB.Stream");
    stream.Type = 1;
    stream.Open();
    stream.LoadFromFile(fileName);
    return stream.Read();
}

function toArray(str) {
    var stream = WScript.CreateObject("ADODB.Stream");
    stream.Type = 2;
    stream.Charset = "_autodetect";
    stream.Open()
    stream.WriteText(str);
    stream.Position = 0;
    stream.Type = 1;
    return stream.Read();
}

function postFile(url, fileName, async) {
    var STR_BOUNDARY = "3fbd04f5-b1ed-4060-99b9-fca7ff59c113";

    // prepare post data
    var stream = WScript.CreateObject("ADODB.Stream");
    stream.Type = 1;
    stream.Open()
    stream.Write(toArray("--" + STR_BOUNDARY + "\n" +
        "Content-Disposition: form-data; name=\"uploadfile\"; filename=\"" + fileName.substr(fileName.lastIndexOf("\\") + 1, fileName.length) + "\"\n" +
        "Content-Type: application/octet-stream\n\n"));
    stream.Write(readBinaryFile(fileName));
    stream.Write(toArray("\n--" + STR_BOUNDARY + "--"));
    stream.Position = 0;
    // post request
    var xhr = WScript.CreateObject("Microsoft.XMLHTTP");
    xhr.Open("POST", url, async);
    xhr.SetRequestHeader("Content-Type", "multipart/form-data; boundary=" + STR_BOUNDARY);
    xhr.Send(stream.Read());
    if (async) WScript.Sleep(1);
}
Posted in Articles | Tagged , , , , | 20 Comments

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

Posted in Articles | Tagged , , | Leave a comment