Solved

Implementing SSL on SQL Server 2000

Posted on 2008-06-26
17
790 Views
Last Modified: 2011-10-03
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.
0
Comment
Question by:kkamm
  • 9
  • 8
17 Comments
 
LVL 33

Accepted Solution

by:
Dave Howe earned 500 total points
Comment Utility
A lot of things about certificate creation/management depend on personal preference. I am fond of the standalone ca available here

http://sourceforge.net/projects/xca

similarly, openssl is very popular (given its long history) and for issuing client certificates, the integrated features of the microsoft CA are not to be sniffed at. You may wish to look at this in the context of issuing email signature/encryption certificates for your exchange users.

however, a full blown CA may be overkill for what you want, which is really just a single certificate for a single sql server - a self-signed from makecert would do just as well.

blog entry discussing creating a self-signed sql 2000 certificate here:

http://blogs.msdn.com/sql_protocols/archive/2007/06/27/install-a-self-signed-test-certificate-that-can-be-loaded-by-sql-server-automatically.aspx

0
 
LVL 1

Author Comment

by:kkamm
Comment Utility
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.
0
 
LVL 33

Assisted Solution

by:Dave Howe
Dave Howe earned 500 total points
Comment Utility
you can distribute the certificate manually to the workstations (either as a .cert file or as a registry key) without needing any certificate service components installed anywhere. the MS CA solution is only useful if you are handing out dozens of certificates - for one or two, self signed is fine, and for a dozen or so, xca is a better choice.
0
 
LVL 1

Author Comment

by:kkamm
Comment Utility
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?
0
 
LVL 33

Assisted Solution

by:Dave Howe
Dave Howe earned 500 total points
Comment Utility
xca is just a gui ca tool - there isn't a server component to it, I usually prefer to run it on my workstation (where I can keep things secure with truecrypt) rather than on a production server.

you can push certs with gpo, see here:

http://technet2.microsoft.com/windowsserver/en/library/acb24791-b78b-4a5b-9cd4-98a04314c3fb1033.mspx?mfr=true

or you can just import the certificate on any machine, then extract the registry key that comprises the certificate and push that out in a login script or gpo (although the regkey-to-gpo tool I used to use appears to be 404 these days)
0
 
LVL 1

Author Comment

by:kkamm
Comment Utility
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?
0
 
LVL 33

Expert Comment

by:Dave Howe
Comment Utility
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.
0
 
LVL 1

Author Comment

by:kkamm
Comment Utility
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.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 33

Expert Comment

by:Dave Howe
Comment Utility
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.
0
 
LVL 1

Author Comment

by:kkamm
Comment Utility
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?
0
 
LVL 33

Assisted Solution

by:Dave Howe
Dave Howe earned 500 total points
Comment Utility
well you could use a packet sniffer - wireshark is considered good, or if you prefer to use ms products, network monitor 3.1 is pretty good as a capture tool (even if it isn't a patch on wireshark for analysis)
0
 
LVL 1

Author Comment

by:kkamm
Comment Utility
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?
0
 
LVL 33

Assisted Solution

by:Dave Howe
Dave Howe earned 500 total points
Comment Utility
tbh I haven't tried that - however, in theory right clicking and selecting "decode as" on the conversation should allow you to designate it as ssl traffic and verify as such. That works for most other ssl encoded streams.
0
 
LVL 33

Expert Comment

by:Dave Howe
Comment Utility
Here is the wireshark page on decoding ssl streams:

http://wiki.wireshark.org/SSL
0
 
LVL 1

Author Comment

by:kkamm
Comment Utility
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?
0
 
LVL 33

Expert Comment

by:Dave Howe
Comment Utility
It usually is, yes.
0
 
LVL 1

Author Closing Comment

by:kkamm
Comment Utility
As this solution was derived from many different suggestions, I spread the points across the most helpful.

Thanks for the assist Dave.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
As a financial services provider, your business is impacted by two of the strictest federal regulations on record: the Sarbanes-Oxley Act and the Gramm-Leach-Bliley Act. Correctly implementing faxing into your organization to provide secure, real-ti…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now