DynamicConnection worked in v10 not working in v11

[lvasic]

Dynamic Connection string doesn't seem to be working.

In the grid it works. When I load it from an A5w page it tells me it can't log in.

This used to work in v10

DynamicConnection_AgencyConnection

session.__protected__AgencyConnection = "{A5API='SQLServer',A5DBVersion=2008,Server='sqlserver',A5ANSINullPadWarn=Default,UserName='Tenant11',Password=<removed>',Database='AgencyData'}"

[Lenny Forziati]

Re: DynamicConnection worked in v10 not working in v11

Embedding obscured passwords in A5W pages is not something that is supported in version 11. This is fundamentally because the obscuring (not encryption) we do for connection strings corrupts the characters and makes it impossible to use them in a much of the new code implemented for version 11 (which is written in C# and depends on Unicode support and character translation).

That said, the best practice is NOT to embed passwords in an A5W page, but rather to read them from a secure database or other encrypted store.

In order to get your application working, below is an example of how you might actually encrypt the passwords and use them to explicitly connect to a database.

* Note that the password is encrypted using a5_encrypt_string(value, key).

?a5_encrypt_string("alpha5", "test")
= "uchag/DzMc4="

?a5_decrypt_string("uchag/DzMc4=", "test")
= "alpha5"

* The encrypted password is a text value and can be stored in the script.


* Passing the user name and the decrypted password to the SQL::Connection::Open() function minimizes the time the value is stored in decrypted form.

cn.open(Session.__protected__ConnectionString, Session.__protected__UserName, a5_decrypt_string(Session.__protected__Password, Session.__protected__EncryptionKey))

[Steve Wood]

Re: DynamicConnection worked in v10 not working in v11

How does this work in grids? Dynamic connection strings that is.

[lvasic]

Re: DynamicConnection worked in v10 not working in v11

The Named connection string for the grid is this:
DynamicConnection_AgencyConnection
With what I’m calling my test login Tenant11
I have a login in SQL for each of my Tenants. I use views that are based on the login for each web component. So I handle the tenant filter at the SQL level.
This allows me to test and build using the Tenant11 info.

When I load
session.__protected__AgencyConnection it replaces my grid defaults with whatever Tenant has logged in.

[Sparticuz]

Re: DynamicConnection worked in v10 not working in v11

I had this problem too, I changed the password to plaintext and it's working now. I'll have to implement what Lenny said eventually, but my password is in the xbasic of a dialog, so I don't think that is viewable from the web side of things.

[Steve Wood]

Re: DynamicConnection worked in v10 not working in v11

When I load
session.__protected__AgencyConnection it replaces my grid defaults with whatever Tenant has logged in.

Yes but exactly how in the grid. Existence of that session var by itself is not going to set the connection string for the grid.

[Lenny Forziati]

Re: DynamicConnection worked in v10 not working in v11

Yes but exactly how in the grid. Existence of that session var by itself is not going to set the connection string for the grid.

Building Multi-Tenant Applications Dynamic Connection Strings V10.5

[Sparticuz]

Re: DynamicConnection worked in v10 not working in v11

There is a 'default' connection string when you build the grid. This is how the grid gets it's structure, in my case DynamicConnection_jkapp. Then I have five schemas with all exactly the same tables in them, but different data based on the region. When a user logs in, I have a dialog that asks which region they would like to use. This dialog then sets Session.__protected__jkapp connection string. When the user pulls up a grid the WAS looks at this session variable then uses that connection string to populate the grid.

EDIT: Also, whenever you need to connect to my dynamic connection database in xbasic, you must use a5SQLConnectionOpen() instead of cn.open()

[Steve Wood]

Re: DynamicConnection worked in v10 not working in v11

Lenny - I don't see an 'egg on my face' emoticon here, but it would be handy right now. I was just having a discussion with a half-dozen developers about Help Resources.