TMSConnection.Options.Encrypt := True and NativeClient

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Calkins
Posts: 13
Joined: Thu 12 Sep 2013 10:40

TMSConnection.Options.Encrypt := True and NativeClient

Post by Calkins » Fri 31 May 2019 08:08

Hi.

If I want to use option TMSConnection.Options.Encrypt := True NativeClient must be instaled to work properly?

Stellar
Devart Team
Posts: 334
Joined: Tue 03 Oct 2017 11:00

Re: TMSConnection.Options.Encrypt := True and NativeClient

Post by Stellar » Fri 31 May 2019 11:01

You can use encryption when transferring data over the network for the following providers:
- prDirect - Connect to SQL Server directly via TCP/IP without client library.
- prNativeClient. SQL Server Native Client (SQLNCLI).
- prMSOLEDB. Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL).

To use the prNativeClient and prMSOLEDB providers, you must have the SQL Server Native Client and Microsoft OLE DB Driver for SQL Server drivers, respectively, installed on your machine.
For example:

Code: Select all

  MSConnection1.Options.Provider := prDirect;
  MSConnection1.Options.Encrypt := True;
  MSConnection1.Options.TrustServerCertificate := True;

Calkins
Posts: 13
Joined: Thu 12 Sep 2013 10:40

Re: TMSConnection.Options.Encrypt := True and NativeClient

Post by Calkins » Fri 07 Jun 2019 08:46

After set :

Code: Select all

MSConnection1.Options.Provider := prDirect;
we have got weird behavior - executing our procedure we get error: "Procedure or function Get_user has too many arguments specified".


If

Code: Select all

MSConnection1.Options.Provider := prAuto;
is set, everything is working fine:
Image

After set

Code: Select all

MSConnection1.Options.Provider := prDirect;
we get this:
Image.

SDAC 8.2.9
Builder 10.2
SQL Server 2012 SP4 ver. 11.0.7462.6

SQL procedure code:

Code: Select all

ALTER PROCEDURE [dbo].[Get_User]
   @User_ID BIGINT=NULL,
   @FirstName [nvarchar](20)=NULL,
   @LastName [nvarchar](50)=NULL,
   @NickName [nvarchar](20)=NULL,
   @HidePassword [bit]=NULL
AS
  DECLARE @Result INT

SET NOCOUNT ON
SET @Result=0

BEGIN
     BEGIN TRY
     /****** Sprawdzenie poprawności parametrów ******/
      IF @HidePassword IS NULL SET @HidePassword=0

	 /****** Jeżeli parametry poprawne *****/ 
	  IF @Result=0
	    BEGIN	     
	  	  SELECT [User_ID],
				 [FirstName],
				 [LastName],
				 [Login],
				 CASE WHEN @HidePassword=1 THEN '********'
					  ELSE [Password]
				 END 'Password',
				 [Encrypt],
				 [HighPriority],
				 [LicenseNr],
				 [Telephone],
				 [Description] 
		  FROM [dbo].[Users]
		  WHERE (@User_ID IS NULL OR [User_ID]=@User_ID) AND
				(@NickName IS NULL OR [Login]=@NickName) AND
				(@FirstName IS NULL OR FirstName=@FirstName) AND
				(@LastName IS NULL OR LastName=@LastName)
		END    
     END TRY
     BEGIN CATCH
          SET @Result=(SELECT ERROR_NUMBER());
          RETURN @Result;
     END CATCH
END

RETURN @Result;

Stellar
Devart Team
Posts: 334
Joined: Tue 03 Oct 2017 11:00

Re: TMSConnection.Options.Encrypt := True and NativeClient

Post by Stellar » Fri 07 Jun 2019 12:14

Unfortunately, we can't reproduce the issue. To investigate this behavior of SDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

Calkins
Posts: 13
Joined: Thu 12 Sep 2013 10:40

Re: TMSConnection.Options.Encrypt := True and NativeClient

Post by Calkins » Tue 11 Jun 2019 11:20

Hi.

I think we found the problem. If we set

Code: Select all

MSConnection1.Options.Provider := prDirect;
and try to execute procedure which exists with the same name in several schemas, for example:

Code: Select all

dbo.Get_User
arh.Get_User
reports.Get_User
we get ALL parameters from ALL instances of procedure Get_User from different schemas.

Code: Select all

ALTER PROCEDURE [arh].[Get_User]
   @Buffer [xml]
   
AS
    DECLARE @Result INT
		
SET NOCOUNT ON
SET @Result=0

BEGIN
     BEGIN TRY
     /****** Sprawdzenie poprawności parametrów ******/
      IF @Buffer IS NULL SET @Result=-1
      
	 /****** Jeżeli parametry poprawne *****/ 
	  IF @Result=0
	     BEGIN	
	     
		--SELECT

         END
     END TRY
     
     BEGIN CATCH
          SET @Result=(SELECT ERROR_NUMBER());

          RETURN @Result;
     END CATCH
END

RETURN @Result;
Is that explanation helpful?

P.S.
If we set

Code: Select all

MSConnection1.Options.Provider := prDirect;
the same behavior is in the query designer.
Image

Stellar
Devart Team
Posts: 334
Joined: Tue 03 Oct 2017 11:00

Re: TMSConnection.Options.Encrypt := True and NativeClient

Post by Stellar » Wed 26 Jun 2019 15:29

Thank you for the information. We have fixed the issue, and the fix will be included in the next SDAC build.

Post Reply