Troubleshooting example webvirtualtables
Return to Virtual Tables - Lianja Cloud Server
Contents
Overview
Troubleshooter for problems accessing data from the MS SQL Server AdventureWorks database via the vt_customers Virtual Table and 'Lianja Web Virtual Tables Demo' (example_webvirtualtables) App.
Test DSN
Check the following for the ODBC DSN:
Has it been created in the 32 bit ODBC Data Source Administrator?
Note that on 64 bit Windows, this is %windir%\SysWOW64\odbcad32.exe
Is it a 'System DSN'?
Can you successfully connect to the DSN from the ODBC Microsoft SQL Server Setup?
Test Virtual Table
Check that you can open the cloudlibtest database and the vt_customers Virtual Table.
You can check or modify the Virtual Table configuration from the right-click context menu or from the Additional Commands cog icon.
If you have given your DSN a different name, make sure you update the Virtual Table configuration.
If you have specified SQL Server authentication in your DSN, see SQL Server authentication below.
If you make any changes to the Virtual Table configuration, remember to Deploy the database before trying to access it from the Lianja Web Client or the Lianja App Center Desktop Client.
Test Desktop App
If you want to run the example_webvirtualtables App in the Lianja App Center Desktop Client, first check that the Published App Setting is True.
Then Deploy the App.
You should now be able to open the App in the Lianja App Center. If not, check that you have correctly deployed the example_webvirtualtables App and the cloudlibtest database.
Test Web App
Open the example_webvirtualtables App in the App Builder and click Preview live in browser.
After login you should be able to open the App in your browser.
If not, check that you have correctly deployed the example_webvirtualtables App and the cloudlibtest database and that your Lianja Cloud Server is licensed and running and has the Enable OData Settings checked.
Server connection lost
If the App loads in the browser, but you see no data displayed and the message Server connection lost and you have checked that the cloudlibtest database has been correctly deployed (you can confirm this by running the App in the Lianja App Center Desktop Client), check the settings for the MS SQL Server login as detailed below.
Integrated Windows authentication
The following sections apply to ODBC DSNs with Integrated Windows authentication.
If you have specified SQL Server authentication in your DSN see SQL Server authentication below.
With Integrated Windows authentication, when you access the Virtual Table (and its underlying DSN) from the Lianja App Builder or the Lianja App Center your current Windows login is used.
When you access the Virtual Table via the Lianja Web Client and Lianja Cloud Server, the NT AUTHORITY\SYSTEM MS SQL Server login is used.
Default Database
The NT AUTHORITY\SYSTEM login will not have AdventureWorks as its Default database.
If you do not want to change this in the Login Properties, you should specify it in the DSN as shown below.
Select Change the default database and specify AdventureWorks in the ODBC DSN Configuration in the 32 bit ODBC Data Source Administrator.
Try the App again in your browser. If you are still seeing the Server connection lost error message, continue with the Permissions section below.
Permissions
Prior to MS SQL Server 2012, the NT AUTHORITY\SYSTEM login had the sysadmin Server Role by default and therefore system wide access to databases.
If you do not want to enable this Server Role, configure Database role membership for the AdventureWorks database as below.
If the sysadmin Server Role is not enabled for the NT AUTHORITY\SYSTEM login, enable User Mapping for the AdventureWorks database and select the appropriate Database role membership.
The db datareader role will be the minimum required, additional roles will give additional permissions.
Try the App again in your browser. If you are still seeing the Server connection lost error message, check your MS SQL Server logs for any error information and Submit a Ticket for Lianja Support if you require assistance.
Alternatively, specify SQL Server Authentication in your ODBC DSN to use a named SQL Server login.
SQL Server authentication
If you have specified SQL Server authentication in your DSN, the login information should be included in the Virtual Table configuration as shown below.
The UID and PWD parameters are included after the name of the DSN in the ODBC Connection string.
Don't forget to Deploy the cloudlibtest database after making changes to the Virtual Table.
