Appeon Mobile with SQL Login Impersonation

[Former Member]

Have started this as a new Thread - was originally posted in

(Thanks for the suggestion David Peace and Chris Pollach)

OK so I am going to have to abandon the Cached DB for a while or at least put it on the back burner for a few weeks/months.  Client has come back and decided they will have connectivity on their devices and that they want to use a live DB connection now!

I have managed to get a Dynamic SQL Server transaction object connected and as a user logs in using their PIN I am disconnecting and reconnecting with their SQL login details.  This is required as we have certain data views that have been setup to filter based on the connected DB user, i.e. they will only see Racehorses associated with their login account.

This however will require an overhead as it will require maintenance on SQL Server login accounts to ensure the login details are kept updated and stored along with the users PIN.

An alternative method we use in our desktop apps is to impersonate the user while still connected using a single SQL Server Login.  This does not require me to be aware of the users password as I can use the PIN to match for security.  When I try to execute the 'EXECUTE AS LOGIN' statement as follows, I am getting a sqlerrtext "Could not find stored procedure 'AS' "

ls_sql = "EXECUTE AS LOGIN = '" + lower(ls_id) + "';"

Execute Immediate :ls_sql Using SQLCA;

If SQLCA.SQLCode <> 0 Then

       MessageBox('Login Failure','Could not set the context of your windows login to your Equisoft login. ' + SQLCA.sqlerrtext)

       Return FALSE

End If

This should change the impersonation context of SQLCA to the selected user as opposed to the sa login.

The dbms connection I am using is 'ODB-MSS'.  Just wondering if this is an issue with the driver on the mobile devices that does not support this?  I have also tried 'OLE-MSS'.

Any ideas or have you tried this before?

This would be a real help if I can get it working as I mentioned, it will save a lot of overhead on setting and knowing passwords.

Thanks

Michael

[Former Member]

Hi Michael,

If you want to change the database source which map to transaction dynamic, please try Chris’ suggestion. For more details please refer to the Dynamic transaction object to data source mapping  chapter in the Appeon online help at:

http://www.appeon.com/support/documents/appeon_online_help/2015/server_configuration_guide_for_net/c...

If you want to change the DB user dynamically, please try the steps below to use the functionality Dynamic Database Connection.

1. Go to AEM > Server > Resources > Data Source > %your data source% and check the option 'Dynamic Database Connection'.

When the Dynamic Database Connection feature is turned on the LogID and LogPass of the Transaction object will be used to connect to the database; when it is turned off then the user name and password specified in the data source will be used to connect to the database.

2. Use the script like below to specify username/password to connect to the database.

SQLCA.LogId = %your id%

SQLCA.LogPass=%your pass%

Thanks,

Appeon Support Team

[Former Member]

Hi Michael;

  When you mentioned DBCache and native SS client ... did you try the test as follows ...

SQLCA.DBMS = "MSS"

SQLCA.DBParm="CacheName='<DataSource>'"

Regards ... Chris

[Former Member]

Hi Chriss,

Yes have tried all different MSS DBMS supported, based on APPEON documentation.

None work, as per reply to David's post.

Michael

[Former Member]

Have tried a suggestion from David Peace about creating SQL Server SP to perform the 'EXECUTE AS LOGIN'

I want to be able to set the Impersonation on the SQLCA transaction object for the whole application, but from what I can see using the SP only sets the impersonation for queries executed within the procedure itself  unless I am not understanding this.

I have found other posts on the web indicating that this is the case and to pass through the query to be executed into the SP.  This is not really feasible as I would have to pass through every SQL query in the app as they all relate to the filtered dataview!

[Former Member]

OK, I have been playing with SQl Server (that's what I have available here), and the following works.

ls_sql = "EXECUTE AS LOGIN = '" + ls_user + "'"

sqlca = guo_app.uf_getdbcomms(1).uf_gettrans()

Execute immediate :ls_sql using sqlca;

If SQLCA.SQLCode <> 0 Then

       MessageBox('Login Failure','Could not set the context of your windows login to your Equisoft login. ' + SQLCA.sqlerrtext)

       Return -1

End If

The only difference is I do not add the ; to the ls_sql as this is implied by the execute immediate.

I hope that works for you.

Cheers

David

[Former Member]

Hi David,

That is almost identical code to what we use in our Desktop App.  Have tried with and without the ; and it does not work on APPEON Mobile.

Get sqlerrtext - "Could not find stored procedure 'A'."

I wonder if this a problem with SQL driver used on the mobile device.  Almost as if it is assuming that there will be a Procedure Name after the 'EXECUTE' and before the 'AS'

Hope APPEON can confirm this.

Michael

[Former Member]

Hi Michael;

  Are you using SA "lite" DBMS right on the mobile device (vs SA from the Appeon Server)?

Regards ... Chris

[Former Member]

Hi Chris,

I have changed the app from using a SQLite DB on the device (Synching Issues).

It is now using a Live SQL Server DB on an inhouse server, accessed using the DSN on Appeon Server.

Michael

[Former Member]

You may need to tweak some DML then. I would try & run the application natively from the PB IDE against the same instance of SS and see if it runs OK that way. I would also suggest connecting to SS using the same client connectivity as you are using from the Appeon Server. If the application runs OK in its Win32 form - then it should run OK from Appeon as well.

A friend of mine converted the AirMan (www.airman.ca) system to SS from SA and it ran OK using basically the same the DML statements (with a few tweaks) in Appeon Web and in Appeon Mobile 2015 build 0372.