Does anyone have experience with this? We are re-developing our intranet with DotNetNuke and therefore need to connect the DNN user database with our HR SQL database, at least for the most important fields. This should be possible automatically, as our HR SQL database stores the same email address for our employees as he has in DNN (because DNN is authenticated against our ActiveDirectory).
I am looking for a KISS solution and do not mind programming a little. Something out-of-the-box of course would be more sweet, but then it should be some free module/script.

Any suggestions/experiences?

Do you need everything synchronized all or most of the time? Is the synchronization one-way or two-way?

For one-way synchronization, you might consider creating a scheduled task that runs periodically (the frequency depends on the number of users, etc.) to get data from the external database and bring it into DotNetNuke. If you decide to go that route, there is a module from iowacomputergurus.com that might simplify the task.

If you only need to synchronize a user when he/she logs in, you might want to take a look at Dynamic Login from Datasprings. That will allow you to perform special tasks when a user logs in. Synchronizing data could be such a special task.

Thank you for your answer.
The priority is on one-way from DNN user to our external database. So everytime a user updates his profile in DNN, the same changes should be applied to our external database. Anyways, if two-way is possible, too, this would be more sweet, of course.

I heard that schneduled tasks in DNN only run when the web application is active in IIS, so when it was used within the last 20 minutes. Furthermore as some of our HR applications are now implemented as DNN modules (e.g. request a business card) it could happen that a user updated his profile in DNN, then using one of our HR applications and wondering why his data there is still not updated, as our HR application modules are using the data from our external HR database.

Is there a way to achieve it without purchasing an additional module?

What about SQL triggers? Can they easily be implemented in the DNN database?
Or is there something like an UserProfileUpdated/UserLoggedIn event which we can hook into?

Oh, I guessed the direction of the dataflow to be the other way ...

Can you create something that periodically updates your HR database from the DotNetNuke database?  That might be easier, and also more controllable.

I once spent a very frustrating six weeks working on a system that was to keep two databases in sync and it was based partly on database triggers.  As I said ... frustrating and I'm not sure that it ever did what it did.

If you do anything with triggers, I would recommend that you do nothing more than send a message to some other task that the database has changed and the UserID.  Then, have some external task look into the database and get the information for that user.

Be aware that there are multiple tables - User, User Profile, and a couple of ASP.NET tables - that might change when a user makes a change.  You'll probably want to filter those to limit the number of updates that you make, and you will probably want to try to get all of them before you start. 

(Oh, and Joe Brinkman's blog has an entry about the SQL queryies that you'll need to get all of the data about a user.  So look for that one.)

Ultimately, the best solution will be to modify your HR database so that it gets it's data from the DotNetNuke database, and eliminate the duplication.    Will the HR database sometimes be the source of other updates?  If so, you'll need to handle that, too.

..

Do you have any suggestions what that could be? The only thing which comes to my mind is a DNN scheduled task which runs once a week or however, but then the above mentioned about DNN scheduled tasks affect.

I have to add that our HR database is really simple: All user information which need to be updated from DNN are stored in one flat table, so in my imagination I thought it might be a simple task to do something like (pseudo code):

OnDNNUserProfileUpdated() {
      dnn_email = user.getEmai();

      UPDATE employee_table
      SET address = user.getAdress(), cellphone= user.getCellPhone() ...
      WHERE email = dnn_email
}

So when a user updates his profile in DNN, the user profile fields from DNN are simply written to the corresponding employee row in the external table which can be found by a matching email address.

Actually I thought this way would be the more difficult one, as then we would have to deal with the (probably) more complex DNN table structure, maybe DNN tables will change in future (while our HR system will stay the same) etc.

I was thinking of creating a program that would run periodically (use scheduled task) on some computer, and independent of DotNetNuke.

You could maybe use the iWeb module to create a web service.  Send it a UserID and it will send you back full user details.  Inside of the service, the access to user information could use the business objects, i.e. UserInfo, and that would keep you out of the database specifics.  If you ever needed to do this in two directions, you could create a service that updates the DotNetNuke database, too.