RE: Multiprotocol Encryption Through a Firewall

From: Doug Knudson (doug_at_advinfosolutions.com)
Date: 04/29/04 

Date: Thu, 29 Apr 2004 09:45:30 -0700

Establishing an Encrypted Multiprotocol Net Library Connection to a
Remote SQL Server 2000 (MSDE 2000) through a firewall with only a single
port open.

We have just emerged, scarred but successful from the very painful
process of trying to establish an encrypted multiprotocol net library
connection to a remote SQL Server (MSDE) and I wanted to share the
technique in hopes of sparing others the pain.

The Problem:

A Db-Library application must establish a secure connection, over the
internet, to a remote MSDE. The MSDE sits behind a firewall at a very
security conscious facility. The SQL Server is running on a Windows
2003 Server computer that is not part of domain, but instead in a
Workgroup. Opening a large number of ports through the firewall, and
especially the RPC end-point mapper port (TCP 135) is not an option. We
cannot use SSL (an easy solution using a CA certificate due to a
Microsoft bug in db-lib (Bug #94129 submitted by Kevin McDonnell (MSFT)
which prevents db-lib connections from establising a connection to a
server using a certifcate.

The Solution:

For over a week, we were unable to find any information on the web in
newsgroups, MSDN or Microsoft site that was exactly on point, but we did
compile a number of articles that led us to the solution. Some of these
articles pertained to SQL Server 6.5, some to 7.0, and some had to do
with setting up Replication (which in itself was of no interest to us).
A special thanks to Kevin McDonnel of Microsoft for some very helpful
responses to desperate postings on various newgroups.

Firstly, a closely guarded Microsoft HotFix (KB-814950 which references
Microsoft Bug # 363783) must be obtained by calling Microsoft. There is
a lot of possible confusion here because after about 30 minutes of
'grilling' by a Microsoft tech to make sure that you really needed the
HotFix, you will be directed to a download site for a HotFix (KB-815495
entitled Cumulative Security Patch For SQL Server and discusses Named
Pipes vulnerability, with no mention of fixing a problem with
multiprotocol encryption). Download and apply the patch anyway to the
MSDE 2000.

On the MSDE 2000 computer, using the Server Network Utility, enable the
Multiprotocol Net Library and the TCP/IP Net Library. Select
Multiprotocol and with the Properties button and check the Enable
Encryption checkbox. This will force encryption for all multiprotocol
net library connections. Now select the TCP/IP Net Library and with the
Properties button change the Port number to anything other than 1433
(the default) just to get it out of the way of Port 1433 that will be
used by RPC over TCP/IP. Press Apply, acknowledge the Restart message,
press Close and again acknowledge the same message.

Still on the MSDE 2000 computer, create the registry entry to cause SQL
Server to listen for RPC/Multiprotocol connections on Port 1433.
Reference KB-164667. At
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLSERVER\MSSQLSERVER, create a
Key, if it is not already present, entitled RpCNetlib. To this key
create a MultiString entry (REG_MULTI_SZ) entitled RPCprotocols and for
the value enter "ncacn_ip_tcp,1433".

>From the System tray, stop and then restart the SQL Server service.

While on the MSDE 2000 computer, make sure that the RPC service is
running and if not, start it and set it to start
automatically. The RPC Locator service is not required because we are
forcing the RPC over TCP/IP connection to take place on Port 1433 (not
to be confused with the default TCP/IP Port of 1433). As an aside, we
chose to use 1433 for the RPC over TCP/IP connection because the 3rd
party hosting the MSDE 2000 was willing to open Port 1433 through their
firewall. I suspect that any other port of your choosing would work
just as well.

Still on the MSDE 2000 computer, create a local login for the RPC
authentication. I chose to create the login with local administrator
permissions (remember that this computer is a member of a Workgroup, not
a Domain), but again, this level of permissions may not be required, but
at this point, I wanted to take no chances!

You are done with the MSDE 2000 Server computer.

Now for the Client computer, still generally referencing KB-164667,
start the Client Network Utility and create an Alias to the remote MSDE
2000. Specify Multiprotocol and for the Server enter <ip address of
remote MSDE>[1433]. Important* You may be tempted to enter the
"ncacn_ip_tpc:" prefix to the Server connection string as is referenced
in KB-164667, but don't do

it. With this prefix, QueryAnalyzer(which uses ODBC) will work fine and
can be used for verifying the connection and encryption BUT db-library
will not connect, presumably because it does not know how to handle the
prefix! In the Additional Parameters field, you must enter your
authentication credentials so the remote MSDE 2000 computer can validate
your RPC connection. Enter as "<remote computer user name>,<remote
computer password>".

That's it! I would recommend that you test the connection first with
QueryAnalyzer. We tested by first starting a Network Monitor to watch
network traffic and then connecting with QueryAnalyzer using the Alias
that was just created above. Run a query to return some reasonable
amount of data and verify that the data is indeed encrypted. If you
have successfully reached this point, there is every reason to believe
that the db-library application will establish and successfully use the
encrypted connection.

Some Final Thoughts:

This method has allowed us to establish an encrypted multiprotocol
connection which will be honored by db-library, as well as any other
connection method, without the use of SSL certificates on a single port
(1433 in our case) without the need to open a large number of
potentially vulnerable ports (especially 135) through the remote
firewall.

Note that the example above forces the encryption on the server, meaning
that all clients wishing to establish a multiprotocl connection will
have to be set up as described above. The technique works just as well,
however, if you want to establish encryption on a client by client
basis. For this, on the remote MSDE 2000 server computer, do NOT check
the Enable Encryption checkbox in Server Network Utilities but instead
on each client computer, make the following registry entry.

At HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLSERVER\Client\RpcNetlib,
create a REG_SZ value name of "Security" and for the value enter
"Encrypt".

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!