Multiprotocol Encryption Through a Firewall

Hi All,
I am now on my fourth day of trying to establish an 'encrypted' (net-library
encryption) multiprotocol connection from QueryAnalyzer to a remote MSDE 20
00 SP3, through a firewall. At this point, after about 10,000 KB articles,
none of which are exactly o
n point, but all enticingly related, I am to the point of offering up my 'fi
rst-born' to anyone who can help:-)
First SSL is not an option in this environment, so I am trying to enable 'Cl
ientSide' multiprotocol encryption to the remote server. I have enabled the
multiprotocol net library on the server and specified a single port for the
RPC traffic associated wit
h the multiprotocol traffic on the server. I chose 1433 (not to be confused
with the standard TCP/IP listening port) for the RPC traffic because it is
one of the few ports that the 'keepers of the server' have opened through th
eir firewall. I also speci
fied 1433 as the client side RPC port. I set this up per KB 164667.
As long as I don't enable encryption on the client side, I am able to connec
t to and query the remote server. A network packet analyzer shows that a mu
ltiprotocol over TPC/IP (ncacn_ip_tcp) has indeed been established.
When I make the registry entry to enable client side encryption at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\RPCNetlib
Value Name: Security
Value Type: REGS_SZ
Value: Encrypt
I am no longer able to connect to the remote server. After the timeout, I g
et the standard "SQL Server does not exist or access denied" message.
My best guesses as to the probable problem areas is either the need to open
additional ports through the firewall (though unencrypted traffic flows thro
ugh it just fine on 1433) or some sort of RPC authentication problem on the
server end. FWIW, the serv
er computer is a member of a Workgroup, not a domain.
The 'Keepers of the Server' will not allow opening ALL ports through the fir
ewall for troubleshooting, even with a promise that unnecessary ports can be
closed later.
I am most interested in advice from someone who has actually made this confi
guration work but any and all suggestions would be most welcome!
Thanks,
DougYou'll also need the RPC endpoint mapper ports open as well to get this
working.
This would require ports 135 open on the firewall.
The other problem is that the machine is in a workgroup, so the
authentication might be failing.
See:
239894 How to establish encrypted multiprotocol connections with SQL Server
7.0
http://support.microsoft.com/?id=239894
Make sure that you don't have the encrypt bit flipped on both the client
and the server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||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\MS
SQLSERVER\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\MS
SQLSERVER\Client\RpcNetlib,
create a REG_SZ value name of "Security" and for the value enter
"Encrypt".
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Doug,
Sorry for all the pain & for my delayed responses.
I filed the bug you referenced as well documnted in the kb;
814950 FIX: A computer might transmit nonencrypted data when clients use the
http://support.microsoft.com/?id=814950
The problem with Multiprotocol is that it was used more in SQL 7.x than
with SQL 2000. Since we moved to using certificates with SSL,
in SQL 2000 we see very few calls on it anymore. Most of them ended up in
my lap. <g> DBlib is another dead API that we rarely get issues on.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We are setting up something similar and I'm looking for advice/comments/idea
s. On the client side:
1. We can't run a client utility on the client computer, but our application
can set any necessary registry settings, register dlls, etc.
2. We must use SQL login, not Windows login.
3. The IP address of the SQL server is read dynamically by the client from a
web server.
Thanks,
Ralph