Here's how I did it. I found a module called User Sync Manager from DNN masters. http://www.dnnmasters.com After installing the module, I set up a database to export the user information to. I added a profile in the module that synced to a table called UserSync in that database. Initially, nothing was getting synced and I was getting really confused. I then found out on a thread in their forum that the module requires the users profile in DNN to be modified since the module was installed. I then told all my users that needed FTP access that they had to make a change in their profile to initiate the sync process. Now here's where the magic happens.. I only needed users from a certain group to have access. USM doesn't export role membership data, so I had to figure it out. I wrote a SQL script and put it in a scheduled job that drops the old FTP access table then joins the exported data with the data in the DNN users table. I then select out the users who are in the userroles table with a roleid that corresponds to the role that I need to give access to. It's a complex query, but it works beautifully.. Here's the query(edited for security):
select lower(replace(s.username, ' ', '')) as USERID, s.password as PASSWORD, (s.firstname + ' ' + s.lastname) as FULLNAME, NULL as FTPMAXSPACE, NULL as FTPMAXFILES, '100' as FTPFLAGS, u.userid as DNN_USERID INTO auth.dbo.ftp from usersync.dbo.usersync s inner join dotnetnuke.dbo.users U on s.username = u.username where u.userid in (select userid from dotnetnuke.dbo.userroles where roleid = 4) |
You'll notice that I do some text processing in there as well. The FTP server software doesn't support spaces in the usernames, so I strip out all the spaces and lowercase all the usernames for good measure. The password is left in tact. Next I combine the user's first name a space character and the last name in at FULLNAME column then set some required fields for the FTP server. I select all this information into the the FTP table.
To take it a step further, I needed to give a select number of members a higher disk quota on the FTP server because they paid for additional space, were moderators, etc. I wrote another sql query to update the FTPMAXSPACE column with the space needed based on thier role membership. In looking at it now, this script could be edited to make it a little more efficient. I'm still doing a join, when I don't need to since I copied out their DNN userid to the FTP database in the first step. I will problably make those changes soon.
Things have been running on this new sync solution for a couple weeks now and I haven't seen any issues and my users are once again happy. Some are still confused about having to edit their profile to get their access, but we're doing our best to steer folks toward the post in the forum that explains it all. This module has really turned out to be a lifesaver on this project. I was at a loss on how to keep these in sync. I hope that sharing how I made this solution will help someone else who is struggling to get this module working in their environment. I know that it would have helped me.
Posts
