Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
neonsun
Starting Member
4 Posts |
 Posted - 2008-04-21 : 11:23:31
|
| Hello,I have a SQL 2005 test system set up that I need to test with protocol encryption, and had in mind to peform this test using a self-signed SSL certificate. I have read the guides and requirements from MS as well as the few blogs I could find on the subject and I've *almost* gotten it to work, but I'm failing at what seems to be the last step - getting clients to connect.. Here is what I've done:-Created a self-signed certificate (I've tried using SSL Diagnostics from the IIS Diagnostics package, makecert.exe from .NET SDK, and OpenSSL) with the 'Server Authentication' purpose, with a CN that matches the server name, along with a private key. I've imported the cert in the local computer's personal store (also in LC's Trusted Root).-SQL Server Configuration Manager sees the certificate and allows selecting it in the configuration. I've done this and selected 'Force Encryption' in the protocol settings.-Restarted SQL Server, errorlog says 'The certificate was sucessfully loaded for encryption'.-Tried connecting locally using SSMS and setting encryption: No problem.-Exported the cert from the store and imported it into the LC's Trusted Root store on the client comp.Here's where the problems begin.1: Client is still apparently able to connect *unencrypted* to the SQL Server (I tested with SSMS on the client machine, it states under connection properties that the conn is not encrypted)2: When trying to connect encrypted, SSMS returns an error during the pre-login handshake saying "The certificate's CN name does not match the passed value." When using the same cert in IIS there are no problems whatsoever, and the CN in the cert does indeed match the server name so I'm thinking the real error is hiding beneath the one that is thrown.Also tried to configure the SQL Client Network Utility to force encryption from the client side, but this didn't seem to have any effect. I tried to use Network Monitor to capture network traffic, but since it appears to be encrypted (SQL encrypts login handshake anyway afaik) I couldn't get much out of it.. Does anyone have any tips? |
|
|
neonsun
Starting Member
4 Posts |
Posted - 2008-04-22 : 16:08:07
|
| I think I found the reason for this. The machines I used for testing are virtual server machines, all based off the same image. They have not been sysprepped, only the machine names have been changed. Looks like this might have some impact on how the server identifies itself (even though 'hostname' and all details looked correct) if the machine SID is the same for all the machines (which I suspect it might be). Tried on a different system that was not based off the same virtual system and experienced no problems whatsoever. Ended up creating the cert with the makecert.exe tool from the Windows Platform SDK. |
 |
|
|
Waldodj
Starting Member
1 Post |
Posted - 2010-05-07 : 14:52:49
|
| Hello neonsun, I´m trying to use Sql Server 2005 SSL encrypt.I make the certificate with makecert.exeI try CN = Server name, type OID = server, install the certificate in personal and trusted root.But the certificate never appear in the SQl Certificate configuration.Can you show the makecert syntax you use.Have an email for help me?ThanksWaldo |
|
|
|
|
|
|
|
|
Problem with encryption using self-signed SSL cert