Drupal supports MySql & PostgreSQL database, while our recent client have some security problem so that they don’t want to store any registered members details into site’s database. They want to use their local network’s Windows Server to store membership registration details in MS SQL Server. Client’s main requirement was that user(member) needs to register on the live Drupal website and the Drupal Applicatio needs to validate for non-duplicates and store their details into their Windows Server(with unique IP) MS SQL Server Membership database.

Only after that, user can login to the site using their Membership ID and Password if their login credentials match in MS SQL Server data. Besides this process for new members, they also want to allow to acess their existing 64000+ members using their membership id which again should be vailidated against MS SQL Server database from the Drupal site. One option was mssql_connect() function in php, which we proposed for MS SQL Server connection establishment and querying agains the database. But client has ignored this solution because they had existing code-base that uses FreeTDS where other applications communicated with Membership database. Client wanted us to maintain consistency with this approach. Hence we did a proof of concept and then decided to use FreeTDS(freetds.org) library’s tsql command for communication with MS SQL Server from Drupal.

Using freeTDS library we can execute queries from command prompt. We have to write every query as a commnd. We created one function tsql() for the all command prompt requests. Our tsql() function does the same functionality like db_query() function of drupal, There is an added complexity in registration process which is multi-step process. In first step, all information is taken from user via Drupal site and in second step they have to do payment for their membership via beanstream. After successful payment confirmation, application passes data to MS SQL Server and then only deletes user data from Drupal side. If the transaction of transferring data/validating data fails due to remote MS SQL Server having issues or because of network issues, we temprarily retain data in Drupal in one queue table and then try to submit again on cron run at a set interval. User data stays in Drupal queue table until successful transfer is done. Every failed attempt will send email notification to Client’s web admin.

Only when registration process is fully complete and data transfererd to MS SQL Server membership database, user gets mail with their Membership ID and Password for login into Drupal site. We have altered Drupal login process for checking login credentials into MS SQL Server. We verify user’s Membership ID and Password against remote MS SQL database and if user is verified then only we load one generic drupal user with paid member role. This paid user post authentication against MS SQL Server can now access all the sections of the site that is allowed for paid member role. It is important to note that we are storing user’s Membership ID in session after successful login for further communication with MS SQL Server.

For the currently logged-in user, we also allow them to edit user’s profile, change password and this is also done directly against MS SQL Server database using tsql(). After this integration, 64000+ users from MS SQL Server Membership database are able to access this website with their Membership ID and password. Gloscon team also helped in requirements specifications, data-mapping and beanstream integration and have completely implemented this in Phase 1 of the project. There was another competing CMS vendor with different solution who also did a proof of concept for similar solution, but Gloscon’s solutions and overall approach was considered because Gloscon delivered the proof of concept in 2 weeks and guranteed delivery of a large and complex multi-lingual Drupal site in 2 months. We executed this project 1 week prior to the delivery date.