We are a datacenter that hsots a SQL Server 2000 environment which provides database services for a product we sell that is loaded as a rich-client applicatin at each of our many clients and their workstations.

Currently today, the application uses straight ODBC connections from the client site to our datacenter.

We need to begin encrypting the credentials -- since everything is clear-text today and the authentication is weakly encrypted -- and I'm trying to determine the best way to implement SSL on the server with minimizing the impact of the client.

A few things, however:

1) We have our own Windows domain and all our servers are joined to our private domain. Our clietns no nothing of our domain.

2) Typically, our clients connect to our datacenter servers either by: a) Using TCP/IP address b) Using a DNS name that we publish via internet, zone transfers from our DNS servers to our customers, or the client can add static HOSTS entries.

3) From what I understand from enabling encryption is that I can go to the Network Utility and select the "encryption" option for the protocol that I wish to encrypt. Such as TCP/IP.

4) When the encryption option is selected, I have a choice of installing a third-party certificate or a self-signed. I have tested the self-signed, but do have potential issues. I'll explain in a bit. If I go with a third-party cert, such as Verisign, or Network solutions... what kind of certificate do I request? These aren't IIS certificates? When I go create a self-signed via Microsoft's certificate server, I have to select "Authentication certificate". What does this translate to in the third-party world?

5) If I create a self-signed certificate, I understand that the "issue to" name has to match the FQDN for the server that is running SQL. In my case, I have to use my private domain name. If I use this, what does this do for my clients when trying to connect to my SQL Server? Surely they cannot resolve my private DNS names on their network....

I've also verified that when the self-signed certificate is installed, it has to be in the local personal store for the user account that is running SQL Server. SQL Server will only start if the FQDN matches the "issue to" of the certificate and SQL is running under the account that has the certificate installed.

If I use a self-signed certificate, does this mean I have to have every one of my clients install it to verify?

6) If I used a third-party certificate, which sounds like the best option, do all my clients have to have internet access when accessing my private servers of their private WAN connection to use to verify the certificate?

What do I do about the FQDN? It sounds like they have to use my private domain name -- which is not published -- and can no longer use the one that I setup for them to use?

7) I plan on upgrading to SQL 2000 soon. Is setup of SSL any easier/better with SQL 2005 than SQL 2000?

Any help or guiadance would be appreciated


Honestly your best bet is to stop going directly to the database server. This is an inherently weak solution (security wise) as anyone can try logging into your SQL Server using the sa account.

A better solution would be to setup a Web Service on a web server, and have your application send requests over HTTPS to this Web Service, then have the Web Service forward the requests to the database, with the database disconnected (or firewalled) from the public internet.

This also gives you the advantage of being able to switch the database out without issue as the web servers just talk to the database. Also you can load balance the web tier so that as things grow you can spread the connect load across the web servers.

  • I agree, but I didn't write the application and I have a very short window to get this security hole tightened. I cannot drop the application. I cannot change the archectiture. The only thing I can do is try to make it more secure while minimizing any client impact or changes. – Angry_IT_Guru Jan 29 '10 at 2:47
  • If you use a self signed cert in order to get passed any cert errors you'll have to have the client computer install your CA root cert as a trusted cert root. They will also need to be able to access the CA root server to verify that the cert is valid. They will need to connect to the SQL Server under the name which is setup within the cert. The SSL setup for SQL 2005 hasn't really changed any as most people don't use it and instead handle the encryption via an IP-Sec offload engine in the NIC so that you aren't wasting a lot of CPU power encrypting and decrypting the SQL traffic. – mrdenny Feb 1 '10 at 7:42
  • I've now upgraded my SQL2000 server to SQL2005. What is nice feor me is that I can turn on server-side enryption and it will create a self-signed certificate. Now I don't have to worry about creating a CA or buying a real certificate. The cert will get recreated after every restart, but I don't think that's a problem and I'm not too concerned with clients not being able to validate the certificate/server. If I needed that extra security, I guess client-side certificate would be in order... – Angry_IT_Guru Feb 24 '10 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy