Using Sql Server AlwaysEncrypted back-end for VFP application 2

[nqramjets]

We're investigating the possibility of using Sql Server 2017's AlwaysEncrypted functionality in our VFP 9 based application.

Enabling this involves swapping out the connection string form [tt]Driver={Sql Server}[/tt] for something newer which supports Always Encrypted, like [tt]Sql Server Native Client 11.0[/tt] which is ultimately using an [tt]ODBC 17[/tt] connection.

This works fine, but we are of course running into the [tt]varchar(max)[/tt] issue described in thread184-1776880.

We've even tried the Devart adapter referenced in that thread, but it does not appear to support the [tt]Trusted_Connection=yes;[/tt] connection attribute, which is a non-starter due to how our application is structured. Even if it did, it doesn't appear to support the [tt]ColumnEncryption=Enabled;[/tt] connection attribute anyhow.

Thus, this post.

Has anyone had any success with a more modern driver which supports the following?
[ol 1]
[li]varchar(max)[/li]
[li]Trusted_Connection=Yes[/li]
[li]Column_Encryption=Enabled[/li]
[/ol]

Any help would be appreciated!

 

[atlopes]

Nqramjets,

VFP Advanced, a patched version of the VFP runtimes by Chuanbing Chen, claims to have fixed the Varchar(Max) interoperability problem with the Native Client drivers.

Into at

http://www.baiyujia.com/vfpadvanced/f_vfpa_about.asp

 

[Olaf Doschke]

According to this (client development / Using Always Encrypted with the ODBC Driver)

https://docs.microsoft.com/en-us/sq...ted-with-the-odbc-driver?view=sql-server-2017

You will need to use the most modern ODBC driver series Microsoft also made for PHP: ODBC Driver 13 or 17. I have SQL Server 2018 and can connect with the ODBC Driver 13 for SQL Server. The version 17 driver fails with an error message about SQLAllocHandle.

Haven't investigated further, but at least you get a connection with the version 13 driver and that's also capable to work with Always Encrypted.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[atlopes]

An alternative would be to call the ODBC API directly, in particular, if getting Varchar(MAX) columns could be isolated in a separate statement from the rest of columns.

This requires further development, but for now, it just tries to prove the base concept:

* complete / replace as appropriate
m.hODBC = SQLSTRINGCONNECT("Driver={ODBC Driver 13 for SQL Server};Server=####;Database=####;UID=####;PWD=####"))

* first, the standard behavior returning a C(0) result
USE IN SELECT("curTeste")

SQLEXEC(m.hODBC, "SELECT CAST('Hello, world' AS Varchar(MAX)) AS colVarCharMax;", "curTeste")

SELECT curTeste
BROWSE

MESSAGEBOX(LEN(curTeste.colVarCharMax))

* we are going to use the SQL API directly
SQLAPI()

USE IN SELECT("curTeste2")

? SQLGETVARMAX(m.hODBC, "SELECT CAST('Hello, world' AS Varchar(MAX)) AS colVarCharMax;", "curTeste2")

SELECT curTeste2
BROWSE

MESSAGEBOX(LEN(curTeste2.colVarCharMax))

SQLDISCONNECT(m.hODBC)


#define	SQL_SUCCESS					0
#define	SQL_SUCCESS_WITH_INFO	1
#define	SQL_NO_DATA					100
#define	SQL_ERROR					-1
#define	SQL_SUCCEEDED				(INLIST(m.ReturnCode, SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA))
#define	SQL_HANDLE_STMT			3
#define	SQL_C_DEFAULT				99					
#define	SQL_MAX_VARCHAR			(2^24-32)

FUNCTION SQLGETVARMAX (ConnectionHandle AS Integer, Statement AS String, CursorName AS String) AS Integer

	LOCAL ReturnCode AS Integer
	LOCAL hStmt AS Integer
	LOCAL ReturnVarchar AS String
	LOCAL ReturnLen AS Integer

	IF PCOUNT() < 2
		m.CursorName = "curResult"
	ENDIF

	m.ReturnVarchar = REPLICATE(CHR(0), SQL_MAX_VARCHAR)

	m.hStmt = 0
	m.ReturnCode = SQLAPI_AllocStmt(SQLGETPROP(m.ConnectionHandle, "ODBChdbc"), @m.hStmt)
	IF !SQL_SUCCEEDED
		RETURN SQL_ERROR
	ENDIF

	m.ReturnCode = SQLAPI_ExecDirect(m.hStmt, m.Statement, LEN(m.Statement))
	IF SQL_SUCCEEDED

		m.ReturnCode = SQLAPI_Fetch(m.hStmt)
		IF SQL_SUCCEEDED

			CREATE CURSOR (m.CursorName) (colVarcharMax Memo)

			IF m.ReturnCode = SQL_NO_DATA
				SQLAPI_FreeHandle(SQL_HANDLE_STMT, m.hStmt)
				RETURN SQL_SUCCESS
			ENDIF

			m.ReturnLen = 0
			m.ReturnCode = SQLAPI_GetData(m.hStmt, 1, SQL_C_DEFAULT, @m.ReturnVarchar, SQL_MAX_VARCHAR, @m.ReturnLen)

			IF SQL_SUCCEEDED
				INSERT INTO (m.CursorName) VALUES (LEFT(m.ReturnVarchar, m.ReturnLen))
			ENDIF
		ENDIF
	ENDIF

	SQLAPI_FreeHandle(SQL_HANDLE_STMT, m.hStmt)
	RETURN IIF(SQL_SUCCEEDED, SQL_SUCCESS, SQL_ERROR)

ENDFUNC

FUNCTION SQLAPI ()

	DECLARE SHORT SQLAllocStmt IN odbc32 AS SQLAPI_AllocStmt ;
		INTEGER ConnHandle, ;
		INTEGER @ StatementHandle

	DECLARE SHORT SQLFreeHandle IN odbc32 AS SQLAPI_FreeHandle ;
		SHORT HandleType, ;
		INTEGER Handle

	DECLARE SHORT SQLExecDirect IN odbc32 AS SQLAPI_ExecDirect ;
		INTEGER StatementHandle, ;
		STRING StatementText, ;
		INTEGER TextLength

	DECLARE SHORT SQLFetch IN odbc32 AS SQLAPI_Fetch ;
		INTEGER StatementHandle

	DECLARE SHORT SQLGetData IN odbc32 AS SQLAPI_GetData ;
		INTEGER StatementHandle, ;
		SHORT ColParamNum, ;
		SHORT TargetType, ; 
		STRING @ TargetValuePtr, ;
		INTEGER BufferLength, ;
		INTEGER @ LenIndPtr

ENDFUNC

 

[Olaf Doschke]

No, you just define your result structure with cursoradapter. I'm more worried why driver 17 doesn't work and how long driver 13 will be sufficient, not even knowing what you get from the encryption features. I also don't see how native client 11.0 is ultimately using an ODBC Version 17 connection. Native Client is its own driver series.

But the varchar max problem is solved with cursoradapters CursorSchema and UseCursorSchema, of course:

Local lnH
lnH = SQLStringConnect("Driver={ODBC Driver 13 for SQL Server};Server=(local);Trusted_Connection=yes;")
If lnH<0
   AError(laError)
   Set Step On 
EndIf 

Local loCUA as CursorAdapter 

loCUA = CreateObject("cursoradapter")
loCUA.DataSourceType = "ODBC" 
loCUA.DataSource = lnH
loCUA.CursorSchema = "col1 M"
loCUA.UseCursorSchema = .T.
loCUA.SelectCmd = "SELECT CAST('Hello, world' AS Varchar(MAX)) as col1;"
loCUA.Alias = "crsResult"
Use in Select(loCUA.Alias)
loCUA.cursorfill()
Browse nowait

SQLDisconnect(lnH)

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[atlopes]

Olaf, this is so great to know.

I tried with ODBC 17* and it's working fine. Can you post a snippet that can produce the error you're experiencing?~

* Update: but targetting an SQL 2012, not an SQL 2018...

 

[Olaf Doschke]

The Cursorschema solution is not new.

The error I get with Microsoft SQL Server 2017 (14.0.2027.2) and comes up connecting to that server when I just replace 13 with 17, no other code changes. AERROR() reports SQLSTATE IM004, SQLAllocHandle on SQL_HANDLE_ENV failed, as stated already (less detailed). There's nothing more to it.

The driver I installed is MSODBC17.DLL (32bit) Version 2917.174.01.01 from 07/24/2019, I just installed this a few days ago. And I'm on Win 10 (Home) build 1903 and use VFP 09.00.0000.7423 (SP2, latest hotfix).

Bye. Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[tomk3]

Hello,

For the varchar(max) problem I remember :
- Using cast(varcharfield as text)
- using SQLEXECex from vfp2c32

Best regards
tom