Sql To Active Directory Challenge

[Jm]

Hi All

Im not sure of which way to go about this so ill start by explaining what im
trying to do and the options ive come up with. ok..

I have a client program which talks to an sql server database whenever a
machine is turned on. It reports the client machine status to the server and
accepts incoming tcp connections for various functions. During logon the
client software will contact active directory services to retrieve the users
full name and report to the database. The sql database has stored procedures
that take your username and machinename and return a set of applications
that a user can run. This whole system runs mickey mouse. The issue i have
is a client has requested that this system include the ability to tie
applications to active directory group names. At the moment the software
uses the logged on user name only and the sql database has a table of
usernames and tied application names where required. Now im not sure of
which way to go about this. Should i be:

1. Reading all the groups a user is a member of and feeding them into a sql
stored procedure so it can check if the apps are associated to the group
names supplied (similar to how it checks for a username to be associated to
apps) ? I would think this way could work but may be slow if a user is a
member of many groups.

2. Is there a way for sql to check group memberships directly from active
directory services through a stored procedure by supplying a username and
getting it to do the rest ? The sql servers are always on machines that are
domain controllers and run in mixed mode authentications.

Well im not sure of any other ways to do this, Any help anyone can give me
is greatly appreciated.

Thanks in advance

3.

 

[Andy O'Neill]

Hi All

Im not sure of which way to go about this so ill start by explaining what
im
trying to do and the options ive come up with. ok..

I have a client program which talks to an sql server database whenever a
machine is turned on. It reports the client machine status to the server
and
accepts incoming tcp connections for various functions. During logon the
client software will contact active directory services to retrieve the
users
full name and report to the database. The sql database has stored
procedures
that take your username and machinename and return a set of applications
that a user can run. This whole system runs mickey mouse. The issue i have
is a client has requested that this system include the ability to tie
applications to active directory group names. At the moment the software
uses the logged on user name only and the sql database has a table of
usernames and tied application names where required. Now im not sure of
which way to go about this. Should i be:

1. Reading all the groups a user is a member of and feeding them into a
sql
stored procedure so it can check if the apps are associated to the group
names supplied (similar to how it checks for a username to be associated
to
apps) ? I would think this way could work but may be slow if a user is a
member of many groups.

2. Is there a way for sql to check group memberships directly from active
directory services through a stored procedure by supplying a username and
getting it to do the rest ? The sql servers are always on machines that
are
domain controllers and run in mixed mode authentications.

Well im not sure of any other ways to do this, Any help anyone can give me
is greatly appreciated.

Thanks in advance

Not sure I fully understand what you're doing.
Anyhow.
I've got an app uses the windows group to decide what users get to see in
the UI.
Maybe if you use sql server to return the relevant groups as a dataset you
could loop through and check for membership of each in turn.

Dim objWindowsPrincipal As New WindowsPrincipal(WindowsIdentity.GetCurrent)
If objWindowsPrincipal.IsInRole("domain/group") = True Then

Me.btnData_Maintenance.Visible = True

End If

 

[Nikolay Petrov]

My opinion is that (if possible, I don't know, but 9/10 it has this
capability) it is much easier for SQL server to check if a user is a member
of Active Directory or Local Windows groups.
This way you can check the group membership in SQL and only return the
allowed apps list, you'll not need to check the membership in your app.
You should look at SQL Documentation on MSDN about how to check the
membership.
If not possible, you can get the group membership from your app, send it to
SQL stored procedures and get the apps for this group. But you should store
group names instead of usernames in SQL.

 

[Jm]

Hi Nikolay

Do you know if sql has the ability to see which groups a user is a member of
? So i could basically supply a username, it checks what groups a user is a
member of with ADS and then checks what apps can be used ?