Page 1 of 1
TMSConnection.Options.Encrypt := True and NativeClient
Posted: Fri 31 May 2019 08:08
by Calkins
Hi.
If I want to use option TMSConnection.Options.Encrypt := True NativeClient must be instaled to work properly?
Re: TMSConnection.Options.Encrypt := True and NativeClient
Posted: Fri 31 May 2019 11:01
by Stellar
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;
Re: TMSConnection.Options.Encrypt := True and NativeClient
Posted: Fri 07 Jun 2019 08:46
by Calkins
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:
After set
Code: Select all
MSConnection1.Options.Provider := prDirect;
we get this:
.
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;
Re: TMSConnection.Options.Encrypt := True and NativeClient
Posted: Fri 07 Jun 2019 12:14
by Stellar
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
Re: TMSConnection.Options.Encrypt := True and NativeClient
Posted: Tue 11 Jun 2019 11:20
by Calkins
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.
Re: TMSConnection.Options.Encrypt := True and NativeClient
Posted: Wed 26 Jun 2019 15:29
by Stellar
Thank you for the information. We have fixed the issue, and the fix will be included in the next SDAC build.