Synchronize users from AD to SQL-Server [password problem]

[Pictor]

I have an ASP portal where I authenticate the user via Active
Directory.
But I can disable this option too (our client asked it) and, in this
case, I should authenticate the users from the SQL-Server database.

So I need to synchronize the user that are in the User Table on the
SQL-Server database with the Active Directory users.

Obviously I can synchronize everything but the password, that is
crypted and isn't shown, even if I make a query on it (it returns a
blank or null value).

I sure don't want to steal the passwords (;-P), but I need, at least,
to get it in binary&crypted format and save it in the Password field on
the S-Server user table.
Then (I hope) I'll use a function to encrypt the password (in the same
way Windows does it) written by the user and compare with the copy in
the database.

Is there any way to do it?
How can I retrieve the password to store it in the database?

I doubt there is no way to synchronize the AD database with any SQL
database.

Can you please help me?

Thank you a lot !

 

[Mike Shepperd]

Given the information you have provided, the right way to authenticate these
users to the database is by setting it up with AD Integrated Authentication.
Using SQL Authentication is designed for maintaining separate user accounts
from what is in AD. If you want them to be the same, just utilize SQL
Server's built-in ability to sync with AD.

If the user/customer is adamant that it must use SQL Authentication, I would
explain that the right way to do this is using AD Integrated Authentication.

If you decide to give in and keep looking for a way to sync the passwords, I
believe you can programatically access the encrypted password but if it's
possible, it's very "behind the scenes" and probably undocumented. If
memory serves, the values are prevented from being accessed using typical
LDAP queries, even LDAP over SSL, but I'm thinking that the ADSI API may
have a way of accessing the info...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/using_adsi.asp
is a good starting point to dig into that idea.

Hope this info helps.

--
Mike Shepperd
Sunfire Solutions LLC
Seattle, Washington

=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Pictor]

Thank you for the information

My problem is I MUST have a users table in the Sql Server.
Infact we developed the portal and the user table is linked to many
other tables (with a numeric UserID as primary key).
The AD part of the portal as been developed later....

So, it's not only an authentication problem but, with this structure, I
NEED the user table in the SS database.

There is absolutely no tools/scripts/way to keep the two database
syncronized?

What could be a solution?

Can you even tell me more about this synchronization option with AD you
were talking about? I can't find nothing useful (until now ).
Even a name would be useful ^_^

Thank you!

 

[Mike Shepperd]

I'm sorry, I don't know of a tool that will do what you want, I only know
that you can query the Active Directory for usernames (and other information
of interest) but you would probably have to sync passwords manually.

Mike