Trouble with SSL

Hello,

I'm struggling a bit with getting SSL client-forced-encryption to work
on a stand-alone SQL Server. Any help fixing this issue would be much
appreciated.

The server in this case is a stand-alone W2K3 server running SQL2005.
Everything is service packed and up to date.

We need to make certain databases available to people across the web.
They'll be using Access (for now) to run their queries. It is very
important is that we give these people the ability to encrypt all SQL
Server traffic between their PC and the server. We want to do this by
using 'Use strong encryption for data' in the ODBC connection
properties on the client machine.

This is our main web server and hosts dozens of unrelated databases,
so I don't want to 'force encryption' on the whole server and cause
all connections to be encrypted. The possible performance hit on our
web sites is not something I can risk.

I know that to make this work I need a certificate issued to the fully
qualified server name. Since this server is stand-alone, and doesn't
have a domain suffix, that is effectively the server name (web01).

On our local network (which is not available to the web server) I used
another computer to submit a request to an install of Certificate
Services that's on one of our servers. I got the resulting cer file
and uploaded it to the web server. I have installed it (both as
administrator and as the service account), but no matter what I do I
can't see the certificate in the drop down list when I open SQL Server
Configuration Manager.

The frustrating thing is, before trying to put this live I went
through the same steps on a local stand-alone server and got it
working without too much trouble at all (well, a bit, but not much).

Here's where I think I might be going wrong:

* SQL runs as a service account with limited privileges
* I used a different computer to submit the certificate request

I would ideally install certificate services on the stand-alone web
server and then log in as the service account before requesting the
certificate, but that's not easy because somebody deleted the default
web site, so CertSrv won't be created and that's kind of crucial.

All ideas gratefully received...

Many thanks,
Adrian
.

Relevant Pages