Error when using encryption

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
rbrodner
Posts: 8
Joined: Fri 11 Mar 2011 21:34
Location: Portland, Oregon, USA

Error when using encryption

Post by rbrodner » Wed 18 Jan 2012 21:51

Version 6.0.2 for Delphi 2007.
Running against SQL Server 2008 R2

Code: Select all

object sqlConn: TMSConnection
  Options.NumericType = ntBCD
  Options.Encrypt = True
  Options.LocalFailover = True
  LoginPrompt = False
  OnConnectionLost = sqlConnConnectionLost
  Left = 32
  Top = 24
end

      sqlConn.Connected := False;
      sqlConn.ConnectString := 
       'Provider=SQLOLEDB.1;User ID=%s;Application Name=MyApp;Password=%s;Data Source=%s;Initial Catalog=%s';
      sqlConn.Options.Provider := prSQL;
      sqlConn.ConnectionTimeout := 5;
      sqlConn.Options.Encrypt := True;
      sqlConn.Connect;
Receive error:
Project MyProject.exe raised exception class EMSError with message '[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.'.

AndreyZ

Post by AndreyZ » Thu 19 Jan 2012 11:03

Hello,

Such error usually occurs if you have expired or invalid certificates on your SQL Server. You can find more information about it here:
http://blogs.msdn.com/b/sql_protocols/a ... 78596.aspx
http://msdn.microsoft.com/en-us/library/ms189067.aspx
You can try to avoid this problem by setting the sqlConn.Options.Provider property to prNativeClient and the sqlConn.Options.TrustServerCertificate property to True. The TrustServerCertificate property enables traffic encryption without validation. For more information, please read the SDAC documentation.

rbrodner
Posts: 8
Joined: Fri 11 Mar 2011 21:34
Location: Portland, Oregon, USA

Post by rbrodner » Thu 19 Jan 2012 23:08

Thank you for your prompt reply. Unfortunately, I cannot use prNativeClient since it does not handle re-connecting properly for us (an issue we have never resolved).

I don't understand why standard ADO components connect fine with Encrypt=True, but SDAC components don't? We haven't changed anything on the server.

AndreyZ

Post by AndreyZ » Fri 20 Jan 2012 13:08

To use encryption in ADO, you should add the "Use Encryption for Data=True" string to the connection string. When encryption is enabled, ADO cannot connect to SQL Server R2 without the correct certificate and returns the same error as SDAC does. You can check it using the following code:

Code: Select all

ADOConnection.ConnectionString := 'Provider=SQLOLEDB.1;User ID=%s;Data Source=%s;Initial Catalog=%s;Use Encryption for Data=True';
ADOConnection.LoginPrompt := False;
ADOConnection.Open;

Post Reply