Access denied to virtual tables from SQL Server ODBC

[adiez]

I want to connect to SQL Server database (2016 version) via ODBC.
Running Windows 10 x64, I've created a system DSN using Microsoft ODBC 32bit driver (version 11).
Connection established correctly.

Then I create a database in Lianja App Builder, and choose "create Virtual Tables from ODBC..."
I fill "ODBC DSN", "Username" and "Password", clic "Tables" button, and tables list is shown correctly.


Then clic "Import", and Console Workspace Output shows progress, ending with

Create Virtual Table 'vt_tomaspautasics' from 'tomasPautasICS'
Create Virtual Table 'trace_xe_action_map' skipped
Create Virtual Table 'trace_xe_event_map' skipped
Operation complete, 52 Virtual Tables created.
Refreshing tables panel.
Completed.

Attachment 1584

But in "Data workspace", I cannot browse names or attributes of tables (message "Access denied"):

Attachment 1583

¿What am I doing wrong?

[yvonne.milne]

Hi Adiez,

If the username and password are not included in the DSN, you need to include them in the virtual table definition. Please see here (Oracle example showing uid and pwd):
https://www.lianja.com/doc/index.php...roubleshooting.

Yvonne

[josipradnik]

Hi,

I am puzzled if ODBC version 11 can even handle SQL 2016

Version 11, https://www.microsoft.com/en-us/down....aspx?id=36434 :

Microsoft ODBC Driver 11 for SQL Server is a single dynamic-link library (DLL) containing run-time support for applications using native-code APIs to connect to Microsoft SQL Server 2005, 2008, 2008 R2, SQL Server 2012, SQL Server 2014 and Windows Azure SQL Database

But version 13:

The Microsoft ODBC Driver for SQL Server allows native C and C++ applications to leverage the standard ODBC API and connect to Microsoft SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016 (Preview), Analytics Platform System, Azure SQL Database and Azure SQL Data Warehouse.

[josipradnik]

Yvonne's solution works.
Right click on a vt in "Table Names" and "modify virtual table" with adding uid and pwd in ODBC Connection

myodbc;uid=xxxx;pwd=yyyyyy

No more Access denied.

[HankFay]

Hi Josip,

according to this https://docs.microsoft.com/en-us/sql...ver-on-windows 11 does support Server 2016.

When they introduced 11, the first in the series, I remember reading that this series of drivers would be a) compatible for all past versions and b) compatible with forthcoming versions except for new features that might be supported by forthcoming versions. But that's just my memory: it is solid on a), and somewhat solid on b). The ability to handle "always encrypted" (i.e., decrypted by the ODBC driver, not before) in 13.1 should be of great interest for anyone handling "personal information" data which is subject to legal and/or industry requirements most everywhere.

Hank

[adiez]

Yvonne,

your solution solved the problem. Thanks for point me to that part of the documentation. I've also so many tables named with underscore ...



Josip, Hank,

yes, version 11 can handle SQL Server 2016. Well, only tested for reading. When possible, I will test for writing (create, modify, delete). Another option is installing version 13 or even 13.1, as both support from SQL Server 2005.

Thanks to all for your help.