Difference between revisions of "Troubleshooting example webvirtualtables"
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
| Line 170: | Line 170: | ||
| − | Select '''Change the default database''' and specify ''AdventureWorks'' in the DSN Configuration. | + | Select '''Change the default database''' and specify ''AdventureWorks'' in the ODBC DSN Configuration in the 32 bit ODBC Data Source Administrator. |
<br clear=all> | <br clear=all> | ||
| + | |||
| + | 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==== | ||
| + | |||
| + | [[{{ns:file}}:vtweb_sysadmin.png|450px|thumb|left|link={{filepath:vtweb_sysadmin.png}}|Sysadmin Server Role]] | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | 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. | ||
| + | |||
| + | <br clear=all> | ||
| + | |||
| + | [[{{ns:file}}:vtweb_databaserole.png|450px|thumb|left|link={{filepath:vtweb_databaserole.png}}|User Mapping and Database Role]] | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | 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. | ||
| + | |||
| + | <br clear=all> | ||
| + | |||
| + | 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 [http://www.lianja.com/support/newticket 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=== | ===SQL Server authentication=== | ||
Revision as of 10:53, 8 July 2015
Under Construction
Contents
See Also
Overview
Please check the following if you are having problems accessing the data from the MS SQL Server AdventureWorks database via the vt_customers Virtual Table.
Test DSN
Check the following for the DSN:
It has been created in the 32 bit ODBC Data Source Administrator On 64 bit Windows, this is %windir%\SysWOW64\odbcad32.exe
It is a 'System DSN'
You are able to successfully connect to the DSN
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, click Web App View, then click Deploy .
You should now be able to open the App in your browser. If not, firstly 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
If you have specified SQL Server authentication in your DSN and not Integrated Windows authentication, 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.
