I'm trying to get SSL encryption working on SQL Server 2005 Express edition Svc Pk 3. I've tried everything (worked the whole weekend), but I dont see TLS or SSL packets when I look at traffic using wireshark.
I have followed instructions on this KB http://technet.microsoft.com/en-us/library/ms189067.aspx and few other KB articles. I cant post all links since stackoverflow limits me to 1 link.
Environment
-SQl Svr 2005 Express Svc Pk 3.
-Windows XP Desktop
Problem Description
-I run the SQL Svr svc with "local system" as logon (instead of local service, network service)
-I've bought an SSL certificate from godaddy.com and imported it into Certificates/personal/certificates folder using MMC.
-I've checked the certificate for all required components and it looks good - like private key, server name etc
-Enabled "force encryption" and selected the imported certificate using SQL Server configuration manager.
-I've also enabled FIPS 140-2 from the local security policy (system cryptography), because I want to ultimately be compliant with FIPS 140-2.
-I use the Mgmt Studio with "Encrypt Connection" checked.
-I get connected and the "connection properties" indicate that the connection is encrypted
BUT then when I look at the packet traffic using Wireshark, I DONT SEE any TLS or SSL protocol packets !!
What is missing??
Here is my log from the sql server ERRORLOG file...I've removed the timestamps from the rows for convenience....
Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
This instance of SQL Server last reported using a process ID of 2860 at 11/8/2010 1:00:27 AM (local) 11/8/2010 9:00:27 AM (UTC). This is an informational message only; no user action is required. Registry startup parameters: -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
Detected 2 CPUs. This is an informational message; no user action is required.
Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
Database mirroring has been enabled on this instance of SQL Server.
Starting up database 'master'.
Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
SQL Trace ID 1 was started by login "sa".
Starting up database 'mssqlsystemresource'.
The resource database build version is 9.00.4912. This is an informational message only. No user action is required.
Starting up database 'model'.
Server name is 'AMITLAPTOP\SQLEXPRESS'. This is an informational message only. No user action is required.
Starting up database 'msdb'.
The certificate was successfully loaded for encryption.
Server is listening on [ 'any' 1772].
Server local connection provider is ready to accept connection on [ \.\pipe\SQLLocal\SQLEXPRESS ].
Server local connection provider is ready to accept connection on [ \.\pipe\MSSQL$SQLEXPRESS\sql\query ].
Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
Clearing tempdb database.
Starting up database 'tempdb'.
Recovery is complete. This is an informational message only. No user action is required.
The Service Broker protocol transport is disabled or not configured.
The Database Mirroring protocol transport is disabled or not configured.
Service Broker manager has started.
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
SQL Server is now ready for client connections. This is an informational message; no user action is required.
