Can't figure out how to create database user groups using AD with database groups

I am doing it successfully now in a first app.

You set up Active Directory. On that page, Database Options should be set.
Under Permissions, Dynamic Permissions should be set. You said you added yourself as an Admin.

When I go to the Admin area, I am a little confused, too. But here's what you do:

Go into the uggroups table and add the database groups yourself.
Then go into the ugmembers table and add the users to each group.
If you look at those tables, I believe it will be obvious to you what is needed there.

Then just use the Admin area to assign privileges to the groups.

If you need a hand with this: I can't connect during the biz day today, but if you wanted to connect this weekend via Zoom, I'd be glad to help.

Thanks for the reply, Frank. I was hoping to be able to manage groups and group membership in the admin area, as we can when using database authentication. But I can live with creating those CRUD tables myself. I happen to have a sweet RAD tool called ASPRunner.NET doing for that sort of thing in literally seconds. LOL.

—Pete

:)

It is really hard to understand what cause the trouble for both of you. Simply logon to the Admin Area and either search for AD groups there or add them manually by typing a group name. No need to go directly to the database.

It also worked the same in version in 10.5 and older version. What has changed is that now you can use both database-based and AD-based logins together.

Then please tell me what I'm doing wrong. If I only check "Domain users in the database" and leave "Read groups from the domain" blank, then I am unable to get to the Admin Area even though I added my domain account in ASPR:

So when I complie and run, this is what I see on my login menu:

If I also check the "Read groups from the domain" box, then the compiled app does recognize me as an admin and I am able to get to the Admin Area. However, I can only seem to add groups from AD:

If I type in a name that does not exist in AD, it won't allow me to proceed.

Pete,

thank you, makes sense. You are very close.

"Domain users in the database" - this option means that after a successful AD login a new record will be created in the login table which ext_id field having a value like "ad234623786573486". Once user's record is created you can assign this user to a group, any group that you create.

The problem is that getting a list of users from the AD is not possible for us, meaning that we can only work with users that were logged in at least once. If this is the kind of scenario that is too slow, you can try exporting a list of users from the AD and adding them to the login table. Just make sure to populate the External ID field properly. The easiest option is enable "Domain users in the database" option, logon as AD user and see what kind of record was created in the database.

Let me know if this makes sense.

I understand that, but I think where we are misunderstanding each other is how we manage groups and group membership when using AD authentication and database users and groups. It sounds like in this scenario I need to manage groups and group memebership myslef, either directly in the database or by generating pages to allow my end-user admins to manage that. Is that correct?

I don't think I understand the question. When you are saying "I need to manage groups and group memebership myslef" - is this your objective or something you want to avoid? In your ideal scenario, who will be managing these groups?

I'm sorry for the clumsy language. What I mean is in this scenario (AD authentication, only database groups and group membership) we cannot manage groups and membership though the built-in Admin Area. In that case we would have to have ASPR generate list/add/edit pages to manage those. Is that correct?

No, it is not correct at all.

Did you actually try to follow my advice? I feel like you are trying to solve it as a theoretical problem without actually doing it. One more try, start with the text in bold.

"Domain users in the database" - this option means that after a successful AD login a new record will be created in the login table which ext_id field having a value like "ad234623786573486". Once user's record is created you can assign this user to a group, any group that you create. The problem is that getting a list of users from the AD is not possible for us, meaning that we can only work with users that were logged in at least once. If this is the kind of scenario that is too slow, you can try exporting a list of users from the AD and adding them to the login table. Just make sure to populate the External ID field properly. The easiest option is enable "Domain users in the database" option, logon as AD user and see what kind of record was created in the database.

No sir, I am not speaking from theory. I am speaking from several days of frustration and three differernt projects using various combinations of settings and not getting anything to work. Just now I tried again with a brand new database and a brand new project. Here are the exact stpes I followed:

• Created a new project, ponited at an SQL Server database with one data table and one users table
• Selected AD Authentication
• Entered the LDAP server information
• Checked "domain users in the database"
• Selected "Database options" and matched up the fields
• Ran the app and logged in. It did find me in AD because it used my display name from AD
• However, it did not insert anything into the users table

Going further, if I subsequently select permissions, enable dynamic permissions (the only option), allow it to create the ug groups, and add my AD identity as Admin, then I am no longer able to access any tables in the generated app. I can open the admin area and select permissions on the <Admin> group, but that still doesn't give me those permissions. If I select Add group, the only option is to search AD for existing groups. There is no option to create local database groups. If I then go back to ASPR and deselect "Read groups for the domain" and rebuild, I can still logon, but now I can no longer access the Admin Area.

Thank you.

Lets proceed step by step. The most important issue is that users data is not being saved in the database and without this part working nothing else will work.

1. Make sure that records are in fact not eing added to the database. I see the screenshot of the users table in the application but you need to check that in the database as well. Maybe you have some sort of filter applied on that table and you do not see new records.
2. Make sure that records can be easily added to that login table. For instance, if you have some NOT NULL fields there that ASPRunner.NET doesn't know how to populate, then new records will not be added.
3. We would also need to see the structure of the login table.

Thanks for that reply. Here is my response:

1. Yes, of course I checked the database directly. I made several attempts, but my user record never got created.
2. Initially, I did have an integer PK set to auto-increment. To eliminate this as a possibility, I deleted that field and tried again. Same result. No record was added to the table when I logged in. I was able to manually insert a record using a standard SQL insert command.
3. Here is the structure of the Login table:
   CREATE TABLE [dbo].[_users](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [LoginID] varchar NULL,
   [Name] varchar NULL,
   [Email] varchar NULL,
   CONSTRAINT [PK__users] PRIMARY KEY CLUSTERED
   (
   [ID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   ) ON [PRIMARY]

As I said, I also tried it with the ID field removed, but that did not help. Any other ideas?

It looks like fields like Name and LoginID have no length specified. What's the length of those fields? This can probably be a reason why user record is not created.

Sorry, that text I pasted got messed up by the rich text contol. This is what it actually is (note that I have deleted the PK field -- I did try it both ways). Those are all varchar(200) NULL.

Your setup seems to be fine, the problem lies somewhere else.

Open a ticket with support, we'll send you a code snippet or an updated file that would print some additional troubleshooting info on the screen.

Thanks, will do.

Okay, thanks to your support staff I have now gotten past the issue of users not being created in the users table upon initial login. It seems to be a bug that will be fixed in teh next version. But the suggested workaround is working, as far as that goes.

So, I have logged in with a couple of different user accounts, one of which is my own admin acccount.

But the other problems still exist. I still don't see any way to create groups in the database or manage group membership. I want to use AD for authentication only and use my database to store groups and group membership. What is the proper setup to allow that?

Thanks for your patient help.

—Pete

All right, we made some progress.

Need more info again. What happens when you proceed to the Admin Area and create groups?

I see this:

If I click "Add group" all I see is the option to search AD groups. I see no way to add a database group or assign users:

This is with both "Domain users in the database" and "Read groups from the domain" checked. If I uncheck the latter, I am unable to get to the Admin Area.

If you select both "Domain users in the database" and "Read groups from the domain" you should see two buttons on that screen, that would allow to add groups manually.

If you do not see those buttons it is the time to contact support again.

That is the case. I will submit another ticket. Thank you.

As I suspected all along, Alexey has confirmed that this part is broken. A fix is coming in the next update.

—Pete