Link to home
Start Free TrialLog in
Avatar of kkamm
kkammFlag for United States of America

asked on

Implementing SSL on SQL Server 2000

I need to encrypt LAN  traffic between 30-40 workstations and a local Win2k3 server running SQL 2000. I am looking at SSL-based encryption

I want to keep things relatively simple from an administration standpoint. Designing an entire PKI solution may be overkill for simply encrypting local network traffic.

I am leaning towards an Enterprise CA and client side encryption. I want to use a self-signed certificate as the encrypted network traffic will be internal and I don't believe the cost and trouble of getting a 3rd-party certificate is necessary.

What do I need to do to get this implemented?

 I also would be interested in best practices for certificate creation and management.
ASKER CERTIFIED SOLUTION
Avatar of Dave Howe
Dave Howe
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kkamm

ASKER

Dave,

Thanks for the info.

I have used makecert.exe before to generate code-signing certificates. My main consideration in generating a certificate for SSL is how the Certificate Service relates to the process. I currently do not have that installed.

If I install a self-signed SSL certificate (created with makecert.exe) on a client machine and force encryption from the client do I still need the Certificate Service running on the server to complete the process? In going over the process of installing the Certificate Service I became a little wary of the administration overhead that may go along with it.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kkamm

ASKER

Can the certificates be pushed via Group Policy? I have 30-40 workstations that are easily accessible but would prefer a push install if possible.

I am not directly familiar with XCA.  I looked at the SourceForge page and it looks to be primarily certificate
management but is there an active component that needs to be running in order to get SSL up and running?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kkamm

ASKER

Dave,

I have a certificate generated through xca. The 'Intended Purpose' is set to ALL, but I would like to constrain it to SSL usage. There are a couple of entries that seem related, but nothing directly referencing SSL.

Which intended purpose is most suitable for SSL?

Also-is it more of a common practice to generate another certificate for SSL and use the trusted root certificate to sign the new certificate or should I just use the root certificate?
Personally I would just do an all purpose (although not ca) self signed certificate; the fact that the certificate *could* be used for other purposes isn't going to matter as only the secret key holder can decode the transactions, so unless you let that out of your control, that isn't going to be an issue.
Avatar of kkamm

ASKER

Dave,

After reconsidering the primary impetus for doing this, which is to meet an FDA requirement for data encryption, I decided that I should look at the Certificate Services approach again. I would like to be able to demonstrate to any auditors that we have a valid certificate path for any certificates we issue in our practice.

I would like to generate a root certificate to issue an intermediate certificate that can in turn be used to issue the SSL or user certificates. I can safeguard the root so that if we have any issues with the intermediate certificates we can revoke them and generate new ones without changing the actual root certificate.

If I install Certificate Services, will it interfere with any pre-exisiting services or settings in Win2k3? I would not want to find that remote connections ,logins, etc. were suspended because I installed the new service.
it shouldn't interfere. you can of course use xca to act as a root if you wish - it has a template for a ca built-in.
Avatar of kkamm

ASKER

I think I managed to get  a client SSL connection to my SQL test box.

Steps:

1) I installed certificate services on our development server and generated a root certificate.   Intended use was set to "Client Authentication" and "Server Authentication".
2) I added a 'Certificate' key to the server registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib and entered the thumbprint of the new certificate.
3) Restarted SQL Server
4) Added the root certificate to the client machine.
5) Used SQL Client Network Utility (cliconfg.exe) on client machine to enable 'Force protocol encryption'.
6) Created a file DSN with 'Use strong encryption for data'  to set up a data source that supported encryption.
7) Used DSN  to initiate connection to SQL database.

I did various queries through various clients using the connection string and they returned the expected data. I also changed one part of the certificate thumbprint data in the server registry, restarted SQL, and tried the same queries. At that point I received an error so it LOOKS like the encryption is being used, but I want to be sure.

What is the best way to confirm the connection is actually encrypted?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kkamm

ASKER

Dave,

I grabbed WireShark (nice app-thx for the tip).  

After trying some queries and capturing the transactions I saw some TDS packets in the capture. This, I presume, is the traffic that contains the information I need to see if encryption is effect. As I have not analyzed this kind of traffic before, what should I look for to verify encryption is actually being used?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Here is the wireshark page on decoding ssl streams:

http://wiki.wireshark.org/SSL
Avatar of kkamm

ASKER

I applied the "decode as" to view any ssl traffic on port 1433. I set up a basic app that allowed me to turn encryption on and off for a given query through the connection string.  

When I captured data on an unencrypted query I got a couple of lines like this:
17      2008-07-14 13:14:00.510441      192.168.5.25      192.168.5.125      TLSv1      Ignored Unknown Record

 When I captured data for the encrypted query I got:
48      2008-07-14 13:14:02.762061      192.168.5.25      192.168.5.125      TLSv1      Application Data

It looks like I have evidence that encryption is taking place, as the SSL description for each of these encrypted query packets indicates TLS 1.0 was being used .

I turned off "Force Encryption" on the client and noticed that encryption seems to be controllable solely through the connection string. Is that true?
It usually is, yes.
Avatar of kkamm

ASKER

As this solution was derived from many different suggestions, I spread the points across the most helpful.

Thanks for the assist Dave.