Security Framework - SQL Tables

[kiwibruce]

Has anyone migrated their security tables to SQL as per http://downloads.alphasoftware.com/A...easenotes.html
Ctrl + F then search for "Web Applications - Security Framework - SQL Databases"


Maybe it is us... but we can't get the new genie to configure the Security Framework to use SQL tables to work for us.
Well that is not true the Genie seems to work fine it is the outcome that is killing us :)

We have tried this on 3 different projects
We are using MS SQL Server 2008 and the current build of AA 2.0 (2128-4292)

The genie runs through fine

Via SQL Server Management Studio we can see the tables have been created and the data in there that has been converted from the DBF security tables..
All good right? We not so fast...
Here is the kink

In the Web Security (in Alpha Developer not any UXs at this point) If you go there and change a password for a user (or create a user/ Group, anything really) you can go look at the SQL table and see the password data has changed (it is encrypted but you can see it has changed)
Cool,
We re-publish the projects WITH Security But not security data as well it is on SQL now right?)

Now go to the web app and login with the new password and ... no go.. Hmm Login with the old password and you are in.. Hmm that is weird
So to prove something here, I go to A5Web root on the server and delete the DBF security tables
Try again to log in and get an error that the security tables are not there.
WHAT???? Why is the project still pointing to the DBF tables????

Oh By The way I HAVE done a complete re-publish of the project

So the Genie works , creates the SQL Tables - Tick
Web Security is now updating data in the SQL Tables - Tick
But the App is still pointing to the OLD DBF tables - Cross

So is there a step I am missing ??
I have to be doing something dumb her but two of us have stepped through this on 3 different projects and same result each time

What are we missing to get the project up on the server to work with the SQL tables and NOT the DBF tables?
These have to be something so obvious that I am steering right past it.

Hopefully I have explained this OK

Thanks in advance

Bruce

[WindForce]

Re: Security Framework - SQL Tables

In the Web Security for your project, go to "Web Security Configuration". In the first tab ( Security Policy ) do you have "SQL Database" selected in the "Security Table Type" ?

[peteconway]

Re: Security Framework - SQL Tables

You need Active link tables to the SQL tables that reside in the folder in the webroot you are using. It won't work without them as the security looks st the SQl via the active linked tables. They are put in the webroot so you don't need to publish them.

[kiwibruce]

Re: Security Framework - SQL Tables

n the Web Security for your project, go to "Web Security Configuration". In the first tab ( Security Policy ) do you have "SQL Database" selected in the "Security Table Type" ?

Yep we do

[JerryBrightbill]

Re: Security Framework - SQL Tables

When you convert from DBF to SQL, the genie will build the tables and all of the active link tables needed. Depending on the Alpha Anywhere build, it will also tell you to publish the Web Security tables as the system is now using different tables and the originals must be replaces with the new files. So you must publish the "data" files after the conversion. The new files don't actually contain any data as that is in the SQL database, so you can publish them without worrying about impacting any data.

If you publish to a remote server, make sure the database used by the remote server is updated to include the new SQL tables, and the connection string used by security points to the correct database.

[Garry Flanigan]

Re: Security Framework - SQL Tables

Jerry,
I am a little curious about this. I have an app that I created before the Genie was made available and it uses SQL Server data tables based on the doc you made available years ago.
I look now at "Web Security" in my project and see it says the table types are SQL and it reports the correct table names.
I know from bad experience that years ago any change to web security would recreate the security dbf tables and overwrite my active-link tables, so I then had to create the active-link tables again. Is this now the case? Let's say I change to encrypted passwords or enable password recovery or some other change - is the SQL data updated by the Genie? I guess it could be treated like moving existing sql data to another sql database. In the help for making this change there is reference to "expected security fields". Can you please provide a detailed list of the "expected security fields" (name, data type, field size) and the "expected" table names.
Any general info would be most appreciated, as I will at some stage make security changes due to the changed scope of the application.

[kiwibruce]

Re: Security Framework - SQL Tables

When you convert from DBF to SQL, the genie will build the tables and all of the active link tables needed. Depending on the Alpha Anywhere build, it will also tell you to publish the Web Security tables as the system is now using different tables and the originals must be replaces with the new files. So you must publish the "data" files after the conversion. The new files don't actually contain any data as that is in the SQL database, so you can publish them without worrying about impacting any data.

If you publish to a remote server, make sure the database used by the remote server is updated to include the new SQL tables, and the connection string used by security points to the correct database.

Thanks for the reply Jerry,
I guessed that is what the genie should have done but in my case it has not.
Maybe this has failed so many times that I am now in some sort of messed up security hell were it is not DBF but it is not SQL now.
I have turned off security started from scratch and started again (3 times)
I have run the genie DBF to SQL and then SQL to DBF at least 5 times each way.
Nothing is working no matter what I try.

I am not seeing active link tables in the tables/sets window of the old control panel, Am I suppose to see them there? or are they hidden?

Not sure what to try I have been at his on and off for days and getting nowhere.
My next step is to start a whole new project and see if I can get it to work on a blank canvas.

I will let you know how that works.
Bruce

[GGandhi]

Re: Security Framework - SQL Tables

going from dbf to sql security is not easy by any means. after several attempts finally I succeeded by doing these steps. if you want to try please try only on a dummy database workspace and if you are conviced it is working well then only try on a real database workspace.

turn on security, that will automatically create all the necessary dbf files, or you may have been with dbf all along then there will be dbf files for security.
now create the sql tables as required and map the fields. then create the active link tables. append all the data from your dbf files to the active link tables.
before you append you may want to import data from the server so it is current. once the data is appended remove all websecurity files on the server and the development machine except the active link tables. now republish the security tables including the data files to the server.

you should have a components already created to
create a security user - a dialog component
edit the security user - grid with a button to call the security dialog to edit or delete.
now publish the whole stuff if the components are not published already.
test and see if it works.
it did for me, just took over three months to conquer.

[Garry Flanigan]

Re: Security Framework - SQL Tables

Gandhi,
As I hinted, the other problem is when you need to make changes to the configuration of web security. I spent (wasted?) many hours until I realised that security changes re-create the security tables and over-write the old ones, which in my case were active-link tables and hence the question in my earlier post. My current deadlines do not allow the luxury of spending days with a dummy database, which without better information is the only safe way to proceed, as in my case I am developing in a live application.
I want to build a full set of sql security tables to cater for all options and then be able to change security configuration with a minimum of fuss. I imagine that only changing to or from encrypted passwords should result in data changes and knowing how to manage this will then be quite important. (Obviously changing login type from say a name to an email address will also result in needing to change data).
As a developer, I know that clients frequently change their minds and build once and never modify is a fantasy. So developers need to know not only how to build but also how to modify with clear step by step instructions and/or genies.

[JerryBrightbill]

Re: Security Framework - SQL Tables

Thanks for the reply Jerry,
I guessed that is what the genie should have done but in my case it has not.
Maybe this has failed so many times that I am now in some sort of messed up security hell were it is not DBF but it is not SQL now.
I have turned off security started from scratch and started again (3 times)
I have run the genie DBF to SQL and then SQL to DBF at least 5 times each way.
Nothing is working no matter what I try.

I am not seeing active link tables in the tables/sets window of the old control panel, Am I suppose to see them there? or are they hidden?

Not sure what to try I have been at his on and off for days and getting nowhere.
My next step is to start a whole new project and see if I can get it to work on a blank canvas.

I will let you know how that works.
Bruce

The genie will create the proper files and table, but you much republish the web security data tables after the process is complete.

What Alpha Anywhere build are you using?

[kiwibruce]

Re: Security Framework - SQL Tables

The genie will create the proper files and table, but you much republish the web security data tables after the process is complete.

Hi Jerry,

What Alpha Anywhere build are you using?

We are using AA 2.0 (2128-4292)
I have republished so many times I think the server is going to shut down in protest soon :)

One question: When you say the genie will create the Active link tables, are they visible in the tables/sets window of the old control panel or are they hidden?

I am going to test this on a whole new project, but not today as I am too frustrated so I need to move onto something else for my sanity.

Thanks

Bruce

[JerryBrightbill]

Re: Security Framework - SQL Tables

The active link tables created by the genie are placed in the web project folder, not the workspace folder so they don't show in the tables/sets list. Open the web control panel and select "All files" from the left side list. You should see a series of files with the syntax websecurity_*.* in the web project folder. For each name, there should be files with .dbf and .dbp file extensions minimum, such as "websecurity_users.dbf" and "websecurity_users.dbp". The "dbp" files are the active link, They are only definition files and contain no data. After publish the webroot should have the same files.

Since you indicate the published project is still using the data in the original dbf, that indicates the publish did not overwrite the original files. When active link tables are created, the original dbf files are modified, and the data removed. When all of the security data files are published, the .dbf files in the published folder should be empty. If the dbf files in the webroot contain data, the system will use that data and not look in the SQL tables

The security only uses the .dbp files after publish, so if the .dbp files exist in the published webroot, you can delete all of the other websecurity_*.* files. To run, the security only needs these files in the webroot if using SQL data.

websecurity_users.dbP
websecurity_groups.dbP
websecurity_members.dbP
websecurity_expired.dbP

WARNING: DO NOT delete any of the websecurity_*.* files in the project folder in the workspace. All of these are needed to manage the security in the development system.

If you are using dbf data, do not delete any websecurity_*.* files in either location

[RichCPT]

Re: Security Framework - SQL Tables

Bruce (or anyone else), how was this issue resolved?

How did you get the security framework to work after publishing to an Application Server when using SQL tables?

Does "a5_application.a5i" require a separate connection string for the alpha security system?

I know the active link "Websecurity*.dbf" tables are pointing to the correct sql server. But it is like the UX Login does not know how to use them properly. If I delete the "Web*.*" files from the published folder, the UX Login complains about them missing. When I put them back in, all I get is "Login information incorrect" when I try to login. I'm using a freshly built UX Login and and a5w page. Is there some default setting in the UX Login that needs changing?

Build 2446

[CarlMPearson]

Re: Security Framework - SQL Tables

The DBF to SQL Genie will create the tables in the SQL Server 2008 database, but is FAILING TO CREATE PRIMARY KEYS.

Then moving the data generates key-errors and no security data is created.

AA 3.1 Version 12.3 Build 2614_4409

[kiwibruce]

Re: Security Framework - SQL Tables

Hi Carl,

yes this is exactly what we have found, the table create is fine but any data migrations fails, We have never found a solution, keep hoping Alpha will fix it.