SSL Certificate

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sullyds - 11 Apr 2006 22:35 GMT

We have been using SSL between SQL Server 2000 (SP4) and misc ODBC clients
(mix of Access 2002 and some custom apps) for the past year. All has been
working well until it is time to renew our ssl certificate via XRamp:

We get our new SSL certificate installed, keeping the old one in the Local
Computer/Personal store since there is a little overlap in time. We changed
the registry key (binary:Certificate) to match the thumbprint of the new
certificate.
Yesterday, I reloaded SQL Server so the new certificate is in effect. All
ODBC clients are displaying some derivative of the error "SSL Security error

:ConnectionOpen (SECDoClientHandshake())". I can connect via a non-secure

connection, however (bad for CC numbers. :()

I have since removed the old certificate on both clients and server,
reinstalled the new certificate on the clients, rebooted the server, reloaded
the MSSQL service. Nothing yields results. The only avenue I have not taken
is reinstalling the new certificate on the server, but this means my IIS
services need to be shutdown aswell since it is being used for my websites
aswell.

Any ideas on this?

Reply to this Message

Sullyds - 11 Apr 2006 22:41 GMT

Sorry about the generic name... I just realized I didn't fill that part out
completely.

One thing to squelch obvious gotchas:
The certificate is indeed designated to our FQDN.

There is an item I am curious about... the "Subject" line in the certificate
has each of the LDAP parameters in a different order. In the old certificate,
"CN" is first and "OU" is second. The new certificate has it reversed. I'm
pretty sure the order doesn't matter in most apps, but maybe in MSSQL...???

> We have been using SSL between SQL Server 2000 (SP4) and misc ODBC clients
> (mix of Access 2002 and some custom apps) for the past year. All has been
[quoted text clipped - 17 lines]
>
> Any ideas on this?

Reply to this Message

Roger Wolter[MSFT] - 11 Apr 2006 23:33 GMT

I would check the SQL Server error log and the Windows event log to see if
there is a more detailed error there. Certificates have a start and end
date. Are you sure it's past the start date?

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

> Sorry about the generic name... I just realized I didn't fill that part
> out
[quoted text clipped - 39 lines]
>>
>> Any ideas on this?

Reply to this Message

Sullyds - 12 Apr 2006 02:33 GMT

There are absolutely no errors (or even mention) about SSL, certificates or
even failed SQL authentications in the windows system, security, application
logs nor the SQL error logs. I had this sort of problem (SSL not working on a
different server) about a year ago too and there were no errors then either.

The certificate is valid 4/7/06-4/6/09. If it weren't valid, I would get
lots of complaints from customer when they purchase our books (If I haven't
already mentioned, this is also used for our shopping cart and other secure
items on our website.)

Just to add even more, I went so far as to sniff the packets going to the
server and verified that the serial number of my certificate matched the one
on the server.

Thanks for the reply, though.

> I would check the SQL Server error log and the Windows event log to see if
> there is a more detailed error there. Certificates have a start and end
[quoted text clipped - 43 lines]
> >>
> >> Any ideas on this?

Reply to this Message

Sullyds - 25 Apr 2006 21:01 GMT

In case anyone reads through this and is wondering about the resolution, I
dropped trying to use the XRamp cert and created my own using selfssl.
Just created a cert good for 5 years, attached to a fake website (selfssl
requires this). Exported the cert and and copied it to my clients in the
"Trusted Root Certification" store. Removed the fake website.
Then had copy the thumbprint hex to the registry on the server. Restarted
SQL server. Problem solved.

> There are absolutely no errors (or even mention) about SSL, certificates or
> even failed SQL authentications in the windows system, security, application
[quoted text clipped - 59 lines]
> > >>
> > >> Any ideas on this?

Reply to this Message