Error connecting to all of my SQL servers

views:

213

answers:

Error connecting to all of my SQL servers

I suddenly started getting this error when trying to connect to any of my sql servers (25+) from SSMS on Windows XP. When I left work yesterday everything was working fine, came in this morning, and I started getting this. Tried rebooting my pc but that obviously didn't fix it. My co-workers can all connect just fine. Searched for a solution but everything I found was regarding encryption in regards to .NET applications. Not sure how to apply that to SSMS. alt text

For some reason the image won't work so the error is this:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server)

From this link:

Disable client-side Force Encryption on the server. On the machine that runs the SQL Server instance, open up the SQL Server Configuration Manager, right-click SQL Native Client Configuration, and set Force Protocol Encryption to No. Then try connecting locally.

http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

Nissan Fan 2010-02-09 18:41:23

On all 25 servers? It seems that the problem is on my machine. My coworkers can still connect just fine so I don't think it's something on the servers.

Mike Thien 2010-02-09 18:44:39

I don't disagree, but if you look at the issue it appears you don't have the self-signed cert in the Domain registered used to authenticate you with the servers. That's also mentioned in this link.

Nissan Fan 2010-02-09 18:56:55

I think you were on the right track here. I sort of discredited your workaround at first because you suggested changing them at the server. Ended up I'm not using encryption and inadvertently checked the force protocol encryption box. Thanks for your help!

Mike Thien 2010-02-09 21:29:27

Glad to see you got it working.

Nissan Fan 2010-02-09 21:43:38

+1 A:

You connect to your SQL Servers requesting encrypted connections and you don't trust the certificate(s) used by those servers. Why that happens depends on a myriad or reasons.

Do your servers use self-signed certificates or PKI issued certificates?
Who is the PKI authorithy that issued your certificates? Is it a corporate certificate service?
Does your computer trust the PKI root authority?

If you don't know the answers to this, you must contact your network and security administrators. Simply disabling protocl enforcing requirement from your client may be against corporate policy, or the servers may enforce SSL anyway disregarding your local setting.

These are all questions you should ask your own environment admins, not public forums. You should try to solve the issue, not hack your way arround it and end up with a non-compliant machine.

Remus Rusanu 2010-02-09 18:42:42

"These are all questions you should ask your own environment admins"Yes exactly.

HLGEM 2010-02-09 19:12:54

I'm a little confused by your comment that I shouldn't be asking these questions in a public forum. I do completely agree that the issue should be fixed and not a hack for a workaround.

Mike Thien 2010-02-09 19:48:16

The comment refers to the fact that the problem is caused by your environment and the correct solution depends primarily on your exiting *policies*. If for example your company requires you to have encrypted connections to SQL Server (eg. the servers store medical data) and you disabled your client encryption requirement, you have exposed your employer to a lawsuit. Or maybe not. How would we know?

Remus Rusanu 2010-02-09 19:51:53

Good point, but not necessarily relevant to my original post. My question was asked to understand more about what might be going on and why I couldn't connect. That, you (or the collective "we") might actually know. I wasn't asking if I should bypass my companies security policies.In my case we aren't using encryption and I had inadvertently checked to use encryption to connect. So I could see where you might have thought that I'm hacking my way around our security by unchecking it. No worries, just too many clicks with the mouse. Thanks for your help!

Mike Thien 2010-02-09 21:25:19

Try this...

Its gotta be a client issue if you lost connection to all your remote servers and your coworkers are fine. You probably got "clicky" and changed some settings inadvertantly.

Open your client network utility (mine is here: C:\WINDOWS\system32\cliconfg.exe). Under the General Tab, check out the disabled protocols. They should all have "force protocol encryption" unchecked. If this is checked for any of those values, your local SSMS is probably trying to force an encrypted connection and failing.

Report back if this doesn't work, and I'll poke around a bit more.

ScottLenart 2010-02-09 19:10:01

ansaurus

tags:

views:

answers:

Error connecting to all of my SQL servers

related questions