Web Wiz Forums - Non AD to AD upgrade strategy.

Print Page | Close Window

Non AD to AD upgrade strategy.

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=25659
Printed Date: 03 April 2026 at 4:05pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Non AD to AD upgrade strategy.

Posted By: p3ter
Subject: Non AD to AD upgrade strategy.
Date Posted: 28 April 2008 at 10:47am

Hi,

I'm planning to upgrade our Intranet forum from 7.7a to 9.08, and have installed a test version from a MS SQL database backup.

The site seems to be working fine with AD integration, but I have an issue with already existing users:

In v7 we have been very clear that we wanted users to sign up using the exact same name as their AD username, and most, but not all, users have followed this instruction.

Now, for every user that already exists and tries to login, they will get "an error has occurred while writing to the database" Server Error:

Server Error in Forum Application
An error has occurred while writing to the database.
Please contact the forum administrator.

Support Error Code:- err_SQLServer_windowsAuthentication()_add_new_user
File Name:- functions_windows_authentication.asp

Error details:-
Microsoft OLE DB Provider for SQL Server
Cannot insert duplicate key row in object 'dbo.tblAuthor' with unique index 'Username'.

So... is there a way to allow a 1:1 match on member name automatically to automatically log the user in as that member? (i.e. upgrade an existing member to an AD user automatically) Otherwise, I have a massive job ahead of me to manually try to link these people back to their 'old' non-AD users.

For the rest - maybe 100 or so users have created different usernames, either because they have a hard time following instructions, or because were forced to do so since the Username field was limited to 20 characters, and their names were longer.

Of these around 100, there are maybe 30 current & active users where I will need to manually convert their old username into a new AD user. Can anyone tell me how to do this?

Finally, I notice that the Email address field doesn't automatically get populated from AD - is there a way to rectify this?

Replies:

Posted By: WebWiz-Bruce
Date Posted: 28 April 2008 at 11:11am

To use the AD integration you would normally need to start with a clean install but as most of your members have used their AD username you may be OK.

For those that get errors what you would need to do is edit their account from the admin area and set their username to the exact same the as username they use for AD. If you can not edit their username from the admin area as it may be disabled when using AD integration it would be a case of editing the tblAuthor table in the database manually.

ASP is limited in the information it can get when AD integration is enabled, the only information available is the Windows login they use to browse the website, so email addresses are not available.

If you have a look though the old version 8 forums I do recall a few years back someone posting something which I beleive used a WSH script to be able to get the email address from AD accounts.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: p3ter
Date Posted: 28 April 2008 at 3:47pm

The problem (& the error message) I am getting is when "Existing name = Active Directory Name"

This does not link the old user to the new AD login as expected. It looks like it's the 'User_ Code' field that determines if the user is unique, and for all existing users, this appears to have a random number tagged onto the end of it,

i.e.

Existing v7.7a local users:

Username field = 'Firstname Lastname'

User_Code field = 'Firstname LastnameZ55EE637EA'

New v9.08 AD Users:

Username field = 'Firstname Lastname'

User_Code field = 'DOMAIN\Firstname Lastname'

So even though the usernames are identical, the difference in the User_Code field stops the AD user from ever being connected back to their 'old' user.

So... what are these numbers, and what is the impact of deleting them? Most are 10 characters, but some are up to 15...

It looks like it will be hard to do any kind of automatic search & replace on the User_Code field alone, since the number of 'random' characters is variable, so assuming I can export the table to Excel I could maybe replace the User_Code field with '=Concatenate("DOMAIN\",C1)' or similar, but I would like to know the risk first.

And another thing ;)...

When I try to update a new AD user's Email address, I get the error

Error

Only members with sufficient permission can access this page.

A security error has occurred with authentication.

Please ensure that all cookies are enabled on your web browser, you are not using a saved or cached copy of the page, and your Firewall/Proxy settings are not hiding or masking your IP address.

- is this another limitation of AD users?

Posted By: WebWiz-Bruce
Date Posted: 28 April 2008 at 4:05pm

I knew there was a reason that it says in the setup file for this it must be done on a clean install and that is it.

I'm afraid you would need to manually update every single user in the database or start again with a clean install.

There well maybe other issues you find as well why you can not switch over to using the AD integration. I think you may find that you would need to stick with your previous method or start again.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: p3ter
Date Posted: 28 April 2008 at 4:26pm

Thanks for the confirmation Bruce.

I would be glad to hear from anyone who has succeeded in doing this upgrade - from my perspective it doesn't look so risky to export the Author table, replace the text in the 'User_Code' field to be ("DOMAIN\" + "Username") and reimport.

I have done this successfully for one user, and all user related functionality tested so far sems to work OK.

Bruce, I did see Jono's mod, but since we will be registering this if we get it running, I tend towards not wanting to change anything. So what whould your position be on supporting a 'modded' registered forum?

Posted By: WebWiz-Bruce
Date Posted: 28 April 2008 at 5:03pm

It should work by making the change you mention but depending on the number of members it may take some time.

3rd Party Mods are not officially supported as without studying and testing each one it's hard to give support for them. However, if you are happy to go it alone with any issues with the mod and test it out yourself you should be fine. All the mod is doing is getting the email address anyway so shouldn't course to many issues.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: Jono
Date Posted: 04 May 2008 at 5:53pm

If you are feeling really brave, you could execute this script against the WebWizForums database (after making a backup, or better still on a test system):

UPDATE tblAuthor SET User_code='DOMAIN\'+Username WHERE Author_ID NOT IN (1,2)

Replace DOMAIN with your NetBIOS domain name. It will exclude records 1 and 2 as these are the Admin and Guest accounts. This assumes that your users have registered with there AD logon names.

You could then export the User_Code to check againist AD and identify if they do not match up - i.e. they haven't used their logon name, or they have left the company. You may be able to utilise some of the functions in the vbScript file included with my AD MOD for this.

Let us know how you get on.

Posted By: Jono
Date Posted: 05 May 2008 at 11:51am

And here's a vbScript to identify any records that are not in your domain after running the SQL statement above.

http://forums.webwiz.net/uploads/24799/CheckWebWizAgainstNT.zip - CheckWebWizAgainstNT.zip

Edit the vbScript and enter your database connection details at the top. Then type:

cscript //nologo CheckWebWizAgainstNT.vbs > Errors.txt

at a command prompt. It will give a tab delimited output (import in to Excel). Ignore the Administrator and guest accounts, the rest you'll have to fix manually.

Hope this helps,

Jono

Posted By: p3ter
Date Posted: 07 May 2008 at 11:03am

Thanks so much for the help Jono.

We came across a number of additional problems that made this even more complicated, so it's become a very manual task!

Converting the table from the old version created unicode conversion problems - all members with international characters in their usernames were replaced with the '⃞' symbol (e.g. Björn Fältskog becomes Bj⃞rn F⃞ltskog)
Our company standard is to 'anglify' international characters using the standard english alphabet, (e.g. Björn Fältskog becomes Bjorn Faltskog)

So... it's become simpler to manually check & amend all users before updating them to AD users.

We also realized that since it will be a lot of work, we should try purge all old users,and ignore all users that are no longer at the company - for historical purposes its good that all old posts have a 'real' owner (deleting the user would mean that all their posts becomes owned by the username 'Guests') but they don't need to become AD users, and this will save us some time in the manual editing.

So the goals are:

Remove ALL users who have never posted - they can update their member profile in their new automatically created AD user.
Leave all ex-employees with any posts as 'local' users
Leave current employees with very low post counts as 'local users'
Rename all current & active users using their AD name.
Update all current & active users to AD users. (change the user_code field to 'DOMAIN\User name') with the now correct user name.

Since we need the old forum to stay in production and this is taking a long time, this is the process we are following:

Back up the old 7.7 forum database
Install a test version of 9.x forum in a new folder
Create a new empty SQL database for 9.x
Import all old database tables into new database
Setup & Configure new 9.x forum
Run 'Batch Delete Members' to remove ALL users who have never posted, irrespective of signup date
Ask internal systems for an exported list that compares 'AD Name' to 'Real Name'
Decide a 'break point' for manual user checking, of e.g. 25 posts.
Go into Admin/Member Administration & Sort by # Posts (descenfing)
Manually check every username against their AD user - rename to AD user as necessary.
For renamed users with international characters, enter their 'plain english' AD name, and also enter their 'real name' in the real name field.

Remaining steps - not done yet.

Create the correct user_code field similar to in Jono's instructions above. (actually, we have done it with a query that takes into account employment, signup date & number of posts and excludes admin users, so we are using "where Author_ID" followed by a big lists of authors that are:

a) currently employed

b) have posted more than 'n' posts

c) not local admin users

I'm not sure of the value of Jono's 'CheckWebWizAgainstNT' vbscript, since we will have so many exceptions anyway, but i'll try it just to see.

Finally, merge the new amended users table with the other currently running 7.7 forum tables, import into the new forum, Pay Bruce some money (unbranded for our corporate intranet), and Go Live!

Posted By: Jono
Date Posted: 08 May 2008 at 6:47pm

Well, that quite a bit of work ahead of you. The Unicode issue would be my biggest problem, but this may be helpful: http://forums.webwiz.net/forum_posts.asp?TID=25696 - http://forums.webwiz.net/forum_posts.asp?TID=25696

It would probably be possible to write a script to query youe live database and return a list of execption (i.e. members who do not look like they have an AD account). You could then go through this list to see if they are valid or not (and update their Username).

The following may be useful if you want to extract a list of users from AD: http://www.rlmueller.net/DocumentUsers.htm - http://www.rlmueller.net/DocumentUsers.htm

you can add viritually any AD attribute, and this is an excellent site to find which attribute is which: http://www.wisesoft.co.uk/Scripts/activedirectoryschema.aspx - http://www.wisesoft.co.uk/Scripts/activedirectoryschema.aspx

Let us know how you get on.

Jono

Posted By: p3ter
Date Posted: 12 May 2008 at 12:35pm

Another issue we have come across is that we need to retain a number of 'special' users that are not linked to AD login - we have some non-individual Admin/Mod level users that are used to post offical notices from certain departments, so the login name is "Department Name" rather than "User Name".

Since you can't stop the forum from automatically logging in the current windows user, these users will need to manually login as a standard (non-AD) using login_user.asp, and then, when they have finished, manually log out using log_off_user.asp. I'm considering making a small modification to the default page footer to get around this, i.e. by modifying default.asp line 890:

<a href="mark_posts_as_read.asp<% = strQsSID1 %>" class="smLink"><% = strTxtMarkAllPostsAsRead %></a> :: <a href="remove_cookies.asp<% = strQsSID1 %>" class="smLink"><% = strTxtDeleteCookiesSetByThisForum %></a> :: <a href="login_user.asp" class="smLink">Manual Login</a> :: <a href="log_off_user.asp" class="smLink">Manual Logout</a> <% = strTxtCookies %>

Which gives:

Mark all posts as read :: Delete cookies set by this forum :: Manual Login :: Manual Logout

Cookies and JavaScript must be enabled on your web browser in order to use this forum

instead of the original:

Mark all posts as read :: Delete cookies set by this forum

Cookies and JavaScript must be enabled on your web browser in order to use this forum

I realize this breaks the style a little, but putting these links up the top where they 'should' be would most likely cause confusion for the majority of AD users.

Posted By: p3ter
Date Posted: 14 May 2008 at 12:52pm

Related to the post above, I now have a new problem with 'Special Users' - when I try to add a new non-AD user using the Admin control panel 'Add new member' feature, I get:

HTTP 500 Internal Server Error

Is it not possible to add new 'local' users once AD login is activated?

And, I cannot promote a new AD user to be an Administrator then log in, since it asks for a non-existent password.

So, it looks like I can only log in as Admin with 'Non-AD' users with admin rights, and only where the user was created before the upgrading the Forum and configuring AD login.

And, I try to rename an existing user that has admin rights, i lose the ability to log in as Admin with that user. (I still can log in manually as a local user, with a direct link to login_user.asp, but when logging in via admin.asp i just get an "insufficent permissions" error

This is rather chaotic, and is a showstopper for my implementation which is dependent on department/group level (non-AD) 'users' being able to log in manually in parallel to the AD login system - does anyone have any ideas?

Posted By: WebWiz-Bruce
Date Posted: 14 May 2008 at 4:08pm

When you use AD you should only use AD a not non-AD login for the forum.

The built in admin account which is not AD should be left 'as is' and used for logging into the admin area. As the admin area should only need to be entered infrequently once the forum is setup this should not be an issue and for security reasons it is recommend you only have one admin account, if other user accounts need more privileges then you should look at making them moderators.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: p3ter
Date Posted: 14 May 2008 at 4:32pm

The problem is twofold, the 'Admin' issue we can live with, however we do need to maintain a number of 'Team/Department/Role' usernames that are used for posting official messages in different parts of the forum. This is critical for us in seperating "unofficial" information, (the personal or professional opinions of the posters), from "official statements", (e.g. an official announcement or policy decision from a central department).

There are other reasons too - the R&D department for example may want the ability to directly comment on technical issues semi-anonymously (by posting from a username called 'Development' for example), without risking that they will become the 'go to guy' for all future questions on that product.

In a community forum I can understand that it's all about the individual member, but in a corporate environment it's important that the credibility of certain topics can be boosted above simply the level of 'who posted it', and in a company with average levels of staff turnover it is important that official statements can live longer than the employment contract of the person who posted them.

This is 'non-negotiable' for me, so If I can't get it to work, I won't be upgrading. Apart from the fact that the functionality is a bit flaky today, what is the real risk of allowing both Local and AD users? I have tested combining the two for normal forum use (posting, moderating etc) very sucessfully using the manual Login/Logout links above, the only limitation being that I cannot modify the local users in any way while AD user management is enabled.

Since this is so important, in the short term I would be prepared to accept heavy limitations in this functionality (even to the level of needing to diable posting, and temporarily reconfigure to local user management just to create or maintain a local user) but of course if there is little technical reason why i need to do this, I would prefer a smarter solution.

I would appreciate a quick risk analysis on working in this way if possible.

Posted By: WebWiz-Bruce
Date Posted: 14 May 2008 at 4:50pm

The problem is that Web Wiz Forums has not been developed for mixed login types, which means that you will find problems like the ones you mention.

If you are prepared for the odd error message and getting your hands dirty in the code you can get mixed login types to work, just that it's not something that has been developed or tested.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: p3ter
Date Posted: 16 May 2008 at 9:29am

We tested a brute-force workaround to this, which was deemed as 'just about' acceptable:

In functions_windows_authentication.asp set blnWindowsAuthentication = False
Log in to admin section of the forum and create a new user
In functions_windows_authentication.asp set blnWindowsAuthentication = True

Then use the mods I mentioned previoulsy (direct link to login_user.asp and log_off_user.asp in page footer) to allow temporary use of local users when necessary.

It's not pretty, and any changes, no matter how small, to these 'special' users will require taking the forum offline and temporarily disabling windows authentication, but it will do for now, so as of yesterday we are the proud owners of a Premium Edition Single Site Brand Free License

Thanks for the assistance Bruce & Jono!

Posted By: p3ter
Date Posted: 16 May 2008 at 11:59am

OK, one happy hacker here came up with a neater solution which allows adding & modifying local users while Windows Authentication is enabled, and is smart enough to not update the User_code field if the user is a local user.

Edit admin_register.asp at around line 367:

'If blnWindowsAuthentication = False Then .Fields("User_code") = strUserCode
     If strMode = "edit" Then
               If blnWindowsAuthentication = False Then .Fields("User_code") = strUserCode
     Else
          .Fields("User_code") = strUserCode
     End If

I think it's worth bug-reporting that without this hack, if Windows Authentication is active the db Fields "User_code" is not handled correctly, giving the 500 Error any time you try to modify a Local User. Irrespective of whether or not this is advised or supported, it is still undersirable and unhandled.

At a minimum this error situation should be handled, but in my view (because I need it!) it would be more flexible to allow mixed authentication, since as far as I can tell there are few risks (no risks?) as long as it's done right...

Posted By: WebWiz-Bruce
Date Posted: 16 May 2008 at 12:21pm

Will look at adding this to version 10.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting