TMSConnection.Options.Encrypt issue

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

TMSConnection.Options.Encrypt issue

Post by brace » Wed 05 Mar 2014 17:08

i tried to set
TMSConnection.Options.Encrypt := True
at design time.

In this way (according to help that says "Encryp: Specifies if data should be encrypted before sending it over the network. ") the app should create an encrypted connection.

But If i try this i have an error "SSL Provider:..."

It seems like i need a local certificate or something like this.

Could you help me undesrtand this feature. I thought that "Encrypt" was for forcing connections on DBs where the encryption is not forced server side.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Re: TMSConnection.Options.Encrypt issue

Post by brace » Wed 05 Mar 2014 17:22

Just to express myself better:
in SS Configuration MAnager i can force encryption on a speific instance.

My app (withotu using TMSConnection.Options.Encrypt) connects sucesfully in both cases (encryption forced or not).

My goal is to connect in an encrypted way without forcing encryption server side.

I tried to experiment with TMSConnection.Options.Encrypt because it sounded like the good way, but maybe i am wrong. Thanks.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: TMSConnection.Options.Encrypt issue

Post by AlexP » Thu 06 Mar 2014 07:40

hello,

To solve the problem, you should set the TrustServerCertificate option to True:

Code: Select all

  MSConnection1.Options.Encrypt := True;
  MSConnection1.Options.TrustServerCertificate := True;
  MSConnection1.Connect;

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Re: TMSConnection.Options.Encrypt issue

Post by brace » Thu 06 Mar 2014 08:10

Thank you, it seems working. I will do some more tests to sniff data to ensure all is fine.

I suggest you to improve the help (btw i have 6.1.6 may be you already did it).

"Encrypt: Specifies if data should be encrypted before sending it over the network. "
add also a reference to TrustServerCertificate .

Thanks a lot

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: TMSConnection.Options.Encrypt issue

Post by AlexP » Thu 06 Mar 2014 08:19

Thank you. We will describe these properties in more details in the help in the next product version.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Re: TMSConnection.Options.Encrypt issue

Post by brace » Fri 14 Mar 2014 08:23

Hi.

After some testing i found a problem in one machine only for which i have this error:

[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]Errore di sicurezza SSL. (italian error)

The same exe before setting

Code: Select all

MSConnection1.Options.Encrypt := True;
MSConnection1.Options.TrustServerCertificate := True;
was working fine in that machine. In all other machines it works fine. This specific machine is a VM in a workgroup and not in domain, anyway by refreshing server list i can see my main sql server (to which i attempt to connect).

Could you please help me in identifying the problem?

Is there another property that can be set to ignore SSL errors, something more "powerful" than TrustServerCertificate?

Thank you.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: TMSConnection.Options.Encrypt issue

Post by AlexP » Fri 14 Mar 2014 10:39

When using the MSConnection.Options.Encrypt and MSConnection.Options.TrustServerCertificate, we set the following OLEDB options: SSPROP_INIT_ENCRYPT and SSPROP_INIT_TRUST_SERVER_CERTIFICATE. This error is most probably due to the server settings. Please contact the Microsoft support over the error.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Re: TMSConnection.Options.Encrypt issue

Post by brace » Fri 14 Mar 2014 11:35

Thanks, I will add a command line parametr to allow the usr to force encription, so it is more managable. Thanks

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: TMSConnection.Options.Encrypt issue

Post by AlexP » Wed 19 Mar 2014 14:06

If you have any other questions, feel free to contact us.

parisa
Posts: 1
Joined: Fri 06 Jun 2014 19:38

Re: TMSConnection.Options.Encrypt issue

Post by parisa » Fri 06 Jun 2014 20:05

Hi there,

We have a problem which is discussed in this post. Using SDAC for Delphi 2007, we use MsConnection with following settings, in order to have an encrypted data transmission between our application and Sql server, regardless of sql server SSL settings.
MSConnection1.Options.Encrypt := True;
MSConnection1.Options.TrustServerCertificate := True;
MSConnection1.Connect;

Running on some computers, application can connect and in some it does not. When it fails to connect, error "SECDoClientHandshake()).]SSL Security error" is received.

For example, when application is run on two different machines (which both run windows 7) and attempts to connect to same Sql Server, one fails to connect with above error and the other connects successfully. Both computers have same dbnetlib version. Tried with different instances of Windows 2003 and none could connect to Sql Server.

Could you help us based your experience about the reason of this different behavior?

Thanks for your support,
Parisa

azyk
Devart Team
Posts: 1008
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: TMSConnection.Options.Encrypt issue

Post by azyk » Wed 11 Jun 2014 08:15

Hi,

This error can occur due to settings of OS, certificates, etc. Make sure that on both client workstations the SQL Native Client is used. For this, in connection settings set the TMSConnection.Options.Provider option to prNativeClient.

In addition, see the discussion of this issue on stackoverflow forum:
http://stackoverflow.com/questions/4922 ... rity-error

Post Reply