dbagurus


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

I am running SQL Server 2005 Developer ed. Windows XP SP2

Trying to connect over the internet to a SQL Server 2005 Workgroup ed. SP1 on Windows Small Business Server 2003 SP1

I have had success doing this before.

I can terminal sevice in to the box and confirm my credentials work

"Force Enycrption" has not been enabled on either the server or the client

The Certificate tab is clear under "Protocols for MSSQLServer", but the server does have certs I can see them in the "Certificate" dropdown.

Any help would be great.

John





Re: I need assistance please


Re: I need assistance please

dbagurus

Madhu,

Thanks for the quick reply, but this is not even close. There is no encryption enabled and it is not even needed. It appears the error message I recieve though implies that it is enabled on the server. When I check the server through RDC under protocols, as stated before, "Force Encryption" is not enabled. I am stuck and not sure what I need to do from my client laptop which will allow me to register the server within my SQL Server Management Studio remotely. Like I stated before I have done this in the past already so I know it works.

Thanks,

John







Re: I need assistance please

dbagurus

Madhu,

Did you have any other ideas on this issue I am receiveing, I still cannot connect. I cannot even ping the box, but can terminal service into it.

Thanks,

John






Re: I need assistance please

Il-Sung Lee - MSFT

John,

The error indicates that you ar forcing encryption on the client side (either through the connection string or through the global setting) and the server is using the self-signed certificate, i.e., not proper cert has been provisioned. You can work around this by doing 1 of the following:

1 - Specifying the "Trust Server Certificate" parameter on the client, http://msdn2.microsoft.com/en-us/library/ms131691.aspx

or

2 - Do not force encryption from the client.

Il-Sung.






Re: I need assistance please

dbagurus

II-Sung,

Let me explain what I am doing and my settings.

Objective: I am trying to connect over the internet to a remote SQL Server in which I have been able to do in the past. I found out that the remote SQL Server has added a Verisign certificate and I do not seem to be able to connect through SQL Server Management Studio's. My objective is to setup replication between the 2 instances of SQL Server.

There is no application involved besides SQL Server Management Studios.

Client-side Settings:

When looking from the client-side (my laptop) in "SQL Server configuration manager | SQL Server 2005 Network configuration | and right-clicking on Protocols for SQL2005 | Properties" under the Flag tab, "Force encryption" is set to "NO", "hide instance" is set to "No" . When going under the Certificate tab there is no certificate selected. I have also tried it with the certificate in the dropdown as well, still no worky. I am not sure if I can go in the registry to see if it is set there and simply not updating the screen, not sure.

I am also not sure where to set the TrustServerCertificate setting within SQL Server Management Studios. I can tell you that in the "Connect to Server" dialogue "Encrypt connection" is not checked.

I have also tried to send my certificate from my laptop to the remote server and imported it into the "Trusted Root Certification Authority" and the "Trusted People". Still no worky.

Remote SQL Server:

When terminal servicing into the remote SQL Server and checking "SQL Server configuration manager | SQL Server 2005 Network configuration | and right-clicking on Protocols for MSSQLSERVER | Properties" under the Flag tab, "Force encryption" is set to "NO", "hide instance" is set to "No" . When going under the Certificate tab there is no certificate selected.

I am coming close to a deadline and not sure what to do, let me know where I am going wrong. I have taken all of your advise, not sure what else to do here, please advise.

John






Re: I need assistance please

Il-Sung Lee - MSFT

John,

Try taking a look at the client-side encryption settings in the SQL Server Configuration Manager. The places you described above are for the server side. In SQL Server Configuration Manager, right-click on "SQL Native Client Configuration" and make sure that "Force protocol encryption" is set to No. If it isn't, then this is your problem. I know that it's wierd to look at the native client settings since SSMS is a managed application but they both look at the same registry key for the encryption setting. Otherwise, please indicate what the value of the variable "Value" under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag1 in the registry.

Il-Sung.






Re: I need assistance please

dbagurus

II-Sung,

Thanks again for your assistance. After going into the client-side which you described (right-click SQL Native Client Config.) Force protocol encryption is set to "No" and Trusted Server Certificate is set to 'No". When going into the registry and looking at the Value for Flag1 it equals "0". Please advise...

John






Re: I need assistance please

Il-Sung Lee - MSFT

John,

My mistake. SSMS uses this registry vairable HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Encrypt. If this variable exists and is set to 1, then set try setting the variable to 0 and restart SSMS.

Il-Sung.






Re: I need assistance please

dbagurus

II-Sung,

Dude, you are the man!!! That was it. Weird how the setting never read or updated the registry correctly. I appreciate you staying with this one. This was a stumper for me.

John






Re: I need assistance please

Il-Sung Lee - MSFT

Glad to hear that we got through the problem. Incidentally, the proper way to change the default setting for this variable is through the Client Configuration Tool, cliconfg.exe.

Il-Sung.