From: Flavio on
Hello Scott,

I ran into the same issue: after discovering that my IIS on 2003SBS didn't have any installed default certificate (which seemed *very* strange to me), I managed to install a certificate with SelfSSL. Actually, I did it three times, so I had three different certificates installed.
After rebooting the server, people were telling me that SQL wasn't working anymore. While doing troubleshooting, I ended up with your same situation.
Removing the certificates solved the SQL-issue, but now the clients accessing OWA or ActiveSync *can't connect* anymore because of the missing SSL-Certificate.

What can I do now? If I'd be installing a new certificate, manually, I think I would break up again the SQL...

Any help will be appreciated.
Thanks!



Scott wrote:

error occurred during the pre-login handshake - microsoft SQL server, Error: 10054
10-Aug-09

Hi,

RD sql server having a problem after i attempted to take a DB offline then
restarted the MS SQL service when no response.

Cannot get SQL agent to start, get this error is the application event log:

"SQLServerAgent could not be started (reason: Unable to connect to server
'(local)'; SQLServerAgent cannot start)."

So tried to connect via managemnet studio:

"a connection was successfully established with the server, but then an
error occurred during the pre-login handshake. (provider: TCP Provider: 0 -
an existing connection was forcibly closed by the remote host.) (microsoft
SQL server, Error: 10054)"

I can telnet to:

99.99.99.99 1200 (connects ok)

Checking the obvious - the IP im using is correct as is the port, user +
pass is correct too.

SQL service started with no error, if something else was using port 1200 im
sure the service would create an exception.

Thanks for any help
Scott

Previous Posts In This Thread:

On Monday, August 10, 2009 10:55 AM
Scott wrote:

error occurred during the pre-login handshake - microsoft SQL server, Error: 10054
Hi,

RD sql server having a problem after i attempted to take a DB offline then
restarted the MS SQL service when no response.

Cannot get SQL agent to start, get this error is the application event log:

"SQLServerAgent could not be started (reason: Unable to connect to server
'(local)'; SQLServerAgent cannot start)."

So tried to connect via managemnet studio:

"a connection was successfully established with the server, but then an
error occurred during the pre-login handshake. (provider: TCP Provider: 0 -
an existing connection was forcibly closed by the remote host.) (microsoft
SQL server, Error: 10054)"

I can telnet to:

99.99.99.99 1200 (connects ok)

Checking the obvious - the IP im using is correct as is the port, user +
pass is correct too.

SQL service started with no error, if something else was using port 1200 im
sure the service would create an exception.

Thanks for any help
Scott

On Monday, August 10, 2009 11:28 AM
Scott wrote:

ps.
ps. have tried changing the SQL publishing port but again i get the error
above instantly.

On Monday, August 10, 2009 11:31 AM
Scott wrote:

server has multi IP addresses, have enabled another IP.
server has multi IP addresses, have enabled another IP. Save error message
instatly.

I think i can rule our IP/PORt problems, seems its actually SQL server thats
having the issiue. happened after a service restart.

Worryingly this article calls for an windows and SQL re install to sort the
issue !

http://dbaspot.com/forums/ms-sqlserver/232143-sql-server-2005-connection-errors-after-changing-enforce-encryption-yes.html

On Monday, August 10, 2009 11:32 AM
Scott wrote:

Please note " force encryption" options is set to NO ...
Please note " force encryption" options is set to NO ... has not been
amended but SelfSSL has been used on a couple of websites recetely.

On Monday, August 10, 2009 11:34 AM
Scott wrote:

when checking SQL server configuration manager i do notice that "protocols for
when checking SQL server configuration manager i do notice that "protocols
for MSSQLserver" "certificate" tab has 4 certificates from SelfSSL.

Will remove them.

Scott

On Monday, August 10, 2009 12:08 PM
Scott wrote:

when i browse to- configuration manager- protocols for mssql- ricght click
when i browse to
- configuration manager
- protocols for mssql
- ricght click properties
- certificate

i see 4 certicates in the list with my server name.

How can i remove these from this list ?



sorry for all the posts.
Scott

On Tuesday, August 11, 2009 7:23 AM
Scott wrote:

after 2 hours or ripping keys out of the registry (thanks to MS telephone
after 2 hours or ripping keys out of the registry (thanks to MS telephone
support) this problem is resolved.

Had 4 selfSSL certificate installed.
SQL somehow wanted to use them even altough encyption was set to NO (hand on
heart nobody had touched this in relation to SQL server).
Delete certificates from MMC console
(certificates/persoanl/service/personal)
Didnt work so remove from the regitsry.

Would recomend avoiding SelfSSL when using:
- 2003 server standard 64bit (IIS6)
- SQL 2005 standard 32bit

when all are installed on the same box.

On Tuesday, August 11, 2009 8:10 AM
Scott wrote:

Troubleshooting tips below.
Troubleshooting tips below. Hope this helps solve a problem for someone one
of these days. Thanks for all the help from the guys and gals on
microsoft.public.sqlserver.server.



Scott

scott_lotus(a)yahoo.co.uk











[Action]

Trying to connect to the SQL server using SQL server management studio.



SQL server 2005 9.00.4035 32 bit

Windows Server 2003 Standard Edition 64bit



[Result]

The SQL server connection fails with the below error.



A connection was successfully established with the server, but then an error
occurred during the pre-login handshake. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.) (Microsoft
SQL Server, Error: 10054)





[Resolution]



n We are trying to connect to the SQL Server 2005 using the IP address and
the port number.



n Checked the protocols. All the protocols are fine and SQL server is
listening on the specified protocols.



n Checked if Force Encryption is on. It is off.



n We could see 4 certificates listed in the Configuration manager.



n Performed the UDL test. Obtained the error that SSL configuration failed.



n Obtained the thumbprints of all the certificates.



n Opened mmc.exe, added the certificates snap in for computer account and
local computer store.



n Deleted the four certificates in the personal store.



n Now when we tried to restart the SQL server. It failed with the below
errors in the error log.





Error: 26014, Severity: 16, State: 1.

Unable to load user-specified certificate. The server will not accept a
connection. You should verify that the certificate is correctly installed.
See "Configuring Certificate for Use by SSL" in Books Online.

Error: 17182, Severity: 16, State: 1.

On Tuesday, May 04, 2010 6:58 AM
Vinothkumar Selvaraj wrote:

Worked
After deleting certificates in personal stores. I am able to login to sql server.

Thanks
Vinoth


Submitted via EggHeadCafe - Software Developer Portal of Choice
Get Silverlight 4 Installed: Tips and Tricks
http://www.eggheadcafe.com/tutorials/aspnet/05910e41-3846-4db9-8e1b-f54c56a64ed9/get-silverlight-4-install.aspx