Allow read file client SQL Anywhere 17

[taverns]

Hello,

we have a web application that imports client files into a sybase 12 database.

We are migrating the database to version 17, but the application can no longer read client file.

What changed from 12 to 17 version?

Thanks,

Simona

[André_Schild]

I think you give use very little information to help you.

Some common things are: - Error log - Code snippet - Perhaps even what files can't be read any longer

[taverns]

I know I'm giving you little information but we are sure that the web application has no problems because it works with SQLAnywhere 12 database with the allow_read_file_client database property on and the 'Read client file' authority for the user. If we use a SQLAnywhere 17 database with the READ_CLIENT_FILE system privilege adds to the user, we get the application error 'Microsoft OLE DB Provider for ODBC Drivers: [Sybase][ODBC Driver][SQL Anywhere]Client application does not allow transfer of data'. So we think the problem is a parameter on the database or ODBC.

[VolkerBarth · Accepted solution]

Is the read_client_file() call issued by the client as a separate statement, or is it part of a stored procedure or other code block? I'm asking because even with version 12, the database server would require additional verification for client file access when the call appeared not directly, but within a stored procedure or the like. Cf. the SA_REGISTER_VALIDATE_FILE_TRANSFER_CALLBACK ODBC extended attribute.

AFAIK, the handling has not been changed in v17 comapred to v12 – except for the changes by the role-based security model added with v16 as mentioned by Mark.

---

A note to your error message: It seems you are using an older ODBC Driver because of the "Sybase" brand, I would suggest to use a SQL Anywhere 17 driver, as well.

With that, you might check whether different settings of the new connection parameter "ClientFileValidator(CFV)" could help.

[taverns · Accepted solution]

The read_file_client() is called from a stored procedure. We don't remember using the SA_REGISTER_VALIDATE_FILE_TRANSFER_CALLBACK ODBC extended attribute. How can I control if we used the SA_REGISTER_VALIDATE_FILE_TRANSFER_CALLBACK on the v12? It's quite strange, we are using the SQL Anywhere 17 driver, I don't know why the error shows the 'Sybase' brand. I looked at the v17 ODBC, but I didn't find the connection parameter 'ClientFielValidator': should it be included in the additional ones?

[VolkerBarth · Accepted solution]

How can I control if we used the SA_REGISTER_VALIDATE_FILE_TRANSFER_CALLBACK on the v12?

Well, the attribute SA_REGISTER_VALIDATE_FILE_TRANSFER_CALLBACK would have to be set in the (web) application code as part of a ODBC SQLSetConnectAttr() call.

Does it work to read a client file from within the web application directly, i.e. outside a stored procedure call? (Because if so, I guess it would really point to the SA_REGISTER_VALIDATE_FILE_TRANSFER_CALLBACK feature, if not, it would be a different issue.)

Yes, the "ClientFileValidator" connection parameter would be to be configured as an additional parameter on the "Advanced" tab, yes. But I don't expect it to really help as the possible values seem to further restrict the pre-configured connection specific permissions - there is no "Allow always" setting...

---

Client application does not allow transfer of data

That error message (with SQLCODE -1200) has the following description:

The client application reported a permissions error while attempting to access the object for a data transfer request from the server. The client application likely does not have the necessary operating system authorization required to access the object.

Just as a wild guess: Could it be the problem is a missing permission per OS instead of per database?

[taverns · Accepted solution]

The read_client_file() function is called from a stored procedure. If we run the stored procedure from ISQL appears a pop-up with the same message, but if we choose 'allow' it works. But we have only migrated the database from the v12 to v17.

[VolkerBarth · Accepted solution]

Is DBISL running on the same machine (and with the same OS user account) as the web server and using the same ODBC driver?

[taverns · Accepted solution]

The 2 databases are on the same server, the v12 runs with dbsrv12 and the v17 runs with dbsrv17. The database v17's structure is the same of v12, we migrated it. The ODBC drivers on the web server are differnt in version SQLAnywhere 12 and SQLAnywhere17 rispectively.

[VolkerBarth · Accepted solution]

My question was related to the machine on which you checked the read_client_file() call via DBISQL. Is that the same machine the web server runs on, and do you use the same OS account for DBISQL and the web server? – I'm just asking to find out whether both "tests" (DBISQL vs. web server) use the same DSNs, drivers and OS permissions...

[taverns · Accepted solution]

We also tried on the web server, it is the same. The result is the same if we run the stored procedure from web server, from DBISQL on the same machine as the web server, from DBISQL on the databases machine, from DBISQL on a client pc.

[VolkerBarth · Accepted solution]

Well, if the DBISQL client itself has to ask for persmission to read a client file, then IMHO this strongly suggests that the web server really would need to register the according callback mentioned in my answer - because the question within DBISQL is part of DBISQL's own according callback...

Another approach might be to modify the according stored procedure so the read_file_client() call is used directly against the database server, say by putting its return value in a connection-specific variable or in a local temporary table just before the procedure is called, and the procedure would then access that variable/table contents. That way the database engine won't ask for permission because the client file access is then treated as "direct". A similar question and solution has been discussed here.

---

Aside: Otherwise, I'm out of my wits, and you might hope for other folks to answer or ask for official SAP support...

[taverns · Accepted solution]

At the end we decided to call directly the read_file_client() from whe web application, and so it works. But we don't understand what is different in the version17. Thank you very much for the support

[MarkCulp]

IIRC in order to use read_client_file in v17, you must enable the system privilege READ CLIENT FILE on the current connection. See the Privileges section on this page in the SQLA reference section. I'm not sure when this was added but likely didn't exist in v12.

[taverns]

We have migrated the SQLAnywhere 12 database and the user we use for the connection has the SYS_AUTH_READFILECLIENT_ROLE and the database has the allow_read_file_client property on. We have added the READ_CLIENT_FILE system privilege to the user, but when we try to upload a file from a client we have the application error 'Microsoft OLE DB Provider for ODBC Drivers: [Sybase][ODBC Driver][SQL Anywhere]Client application does not allow transfer of data'. The same web application with a SQLAnywhere 12 database has no issues with the allow_read_file_client database property on and the 'Read client file' authority for the user.