A couple of years ago we had a task to introduce a Single Sign on to our websites with Sharepoint being one of them. We decided to use ADFS which was at version 1.
Sharepoint was never actually used in a intranet environment so it was decided that we wanted all the existing windows users and groups to be converted to the new ADFS users. We scoured the web and found nothing, microsoft said it should not be done and that we should re-add all our users by hand! So because we had 9000 user records created in sharepoint we decided this was not an option and looked into the database. It turned out to be a simple task to alter the user records to be ADFS users.
There is a table in sharepoint called user_info. On this table the key fields are
- tp_login – Textual representation of user e.g. dom\user
- tp_systemid – Hex login of user
All we did was to change the data in these 2 fields to a format for ADFS and amazingly it worked. We changed tp_login from dom\user to firstname.lastname@example.org, and change the tp_systemid to represent SingleSignOnMembershiptProvider2:email@example.com in hex. Unfortunately we could not get the groups to convert as easily. We could change the record in userinfo, but there was another record that needed to change in a table that escapes me at this time with some sort of hashing involved we we could not determine. Maybe if I remember later to find out the table again i shall leave some further info. Anyway as we only had 16 references to groups setup we were OK to manually convert these. I have included the script “findGroups.sql” in the zip file with the code. We only used groups “Domain Users” and “NT Authenticated Users”, this script finds all the these references and tells you which groups and sites they are setup in. If you have used other windows groups then alter the script accordingly.
Once we mashed the users and added the new ADFS groups we thought all was well. A week or 2 later we realised that alerts had broken. We found that we should have updated field nvarchar3 in userdata to hold SingleSignOnMembershiptProvider2:firstname.lastname@example.org in textual form. We created a script “Sharepointvarchar3fix.sql” to fix this. Since then we ran sharepoint and ADFS with no further problems. Then we upgraded to ADFS2, but thats another story, one i shall hopefully write up someday!
I have attached our code written in Visual Studio 2008. Its all a bit knocked to together because it was just for a one off convert and didnt really need to be robust etc. I also added the 2 sql scripts mentioned in this article to the zip file. Please Please do not run this code on any live database, you shall need to change the domain and email address stuff in the code. Make sure you test it fully in a test environment. It is really easy to backup and restore sharepoint to a different environment. You really do want to be happy it works fine before you let it loose on any live server. Also note that we are only using the free Sharepoint services, we are not using the full Sharepoint paid for server, there will probably be a whole load more data to change for the full version.
Download zipped code