Architecture mismatch when using ODBC

Today I ran into a problem when I was trying to connect to a data source using ODBC.  Currently I’m working on a web application that uses ASP.NET running on .NET Framework 2.0 and that has one connection to a SQL Server database and a number of other connections to databases based on Pervasive SQL. The web application uses ODBC to connect to the Pervasive SQL databases.

Ok, and now for the problem…

When I was trying to start the web application I got an error message saying “ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”.

After a little investigation I found out that my Visual Studio project was compiled targeted towards the x64 architecture (64 bit) using ODBC drivers created for the x86 architecture (32 bit) which led to the error message. Apparently you need to have ODBC-drivers that is compiled for the same architecture as the application that uses them.

This is how I solved the problem and got my application up and running…

The first thing I did was to make sure that I had a valid ODBC connection, and since I’m running Windows 7 x64 (64 bit) I needed to open the old ODBC Data Source Administrator using the following path:

%WINDIR%\SYSWOW64\ODBCAD32.exe

image

The WOW part in SYSWOW64 basically means Windows 32 bit on Windows 64 bit and is sort of a copy of the system32 folder created on the x86/32 bit architecture

I checked my ODBC connection and made sure that it was properly created.

Next thing – Visual Studio …

Ok, the ODBC connection seems to work and the next thing I had to do was to change the platform for the projects in my Visual Studio solution. First a little background: the solution contains five C# projects that are class libraries and two projects that are ASP.NET web site projects. The web sites has references to the class libraries and everything is created with default settings in Visual Studio, i.e. the platform is set to “Any CPU”. The web sites are configured to run using IIS and not Cassini, the ASP.NET development server used by Visual Studio.

Changing the target platform for the class libraries was easy and I simply just opened the properties for each project and changed the Platform target found under the Build tab in the Properties dialog.

image

When I tried to do the same thing with the The ASP.NET web site projects I discovered that that type of Visual Studio projects doesn’t have any setting for this. In order to make the web sites run on the x86 architecture I found out that this has to be done in Internet Information Services Manager (IIS).

Next and final thing – IIS

So, almost there! I started the IIS Manager (INETMGR) and expanded the node for Application Pools. This is where we get the possibility to tell the application what platform to run on. I selected the application pool used by my web site and clicked Advanced Settings in the right panel. The next thing I did was to alter the Enable 32-Bit Applications setting to true which basically means that my web site application should run as an application using the x86 (64 bit) architecture.

image

 

Problem solved!

I solved the problem following the steps above and my web site application is up and running.

Advertisements

MembershipProvider – Importing old user-accounts

Recently i stumbled into a problem where I needed to import already existing user-accounts from an old website into a new EPiServer website that was using the ASP.NET Membership-system.

The question was:

  • How can you automatically create approximately 7,500 user-accounts in an easy way by using the ASP.NET Membership-system?
  • What are the requirements for doing this and is it possible to connect the newly created user-account with a certain role or several roles?
  • What about Profiles? Can you create the UserProfile as a part of the import-process?

Well, after a little research i came up with the following solution:

  • Create a Windows desktop-application (this is not a requirement, but it eases the development process) that uses the classes for the ASP.NET Mebership system.
  • Make sure the application can access the datasource for the old useraccounts; preferable a SQL Server database or some sort of datasource that is easy to extract data from.
  • Log every account that is imported even if the import failed in order to investigate the reason for the failure.

Requirements:

  • The passwords must be in clear-text in the datasource you are importing from. When the account is created in the ASP.NET Membership-system the password will be hashed/encrypted which means that you will not be able to read it in clear-text, so this is a one-way import process.

The solution is quite simple and straightforward. A couple of methods and some configuration makes it possible to create n user-accounts from the old membership-system.

Sourcecode for the client application.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Web;
using System.Web.Profile;
using System.Web.Security;
using System.IO;
 
namespace AccountConverter
{
    public partial class frmConvert : Form
    {
        public frmConvert()
        {
            InitializeComponent();
        }
 
        /// <summary>
        /// Start the conversion process.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btConvert_Click(object sender, EventArgs e)
        {
            // Get all active users 
            List<ConvertingCustomer> oldAccounts = GetOldAccounts();
 
            progressBar1.Minimum = 0;
            progressBar1.Maximum = oldAccounts.Count();
 
            // Iterate through all users that should be converted.
            int i = 0;
            foreach (ConvertingCustomer account in oldAccounts)
            {
                // Set current rownumber
                i += 1;
 
                try
                {
                    // Convert the current account
                    CreateAccount(account);
 
                    // Update the listbox with status for the current account
                    UpdateListBox(i, string.Format("SUCCESS: {0}\t{1}",
                        account.AccountNumber,
                        account.UserName));
 
                    // Log that the account was successfully converted
                    Log(account.AccountNumber, ConvertStatus.SUCCESS.ToString(), "Account was successfully converted.");
                }
                catch (Exception ex)
                {
                    // Update the listbox with status
                    UpdateListBox(i, string.Format("FAILED: {0}\t{1}\t{2}",
                        account.AccountNumber,
                        account.UserName,
                        ex.Message));
 
                    // Log that the account could not be converted
                    Log(account.AccountNumber, ConvertStatus.FAILED.ToString(), ex.Message);
                }
 
                // Update the progressbar
                progressBar1.Value = i;
                progressBar1.Update();
            }
        }
 
        /// <summary>
        /// Convert a single account. Create a new account with the ASP.NET Membership-system, connect it to a certain role and create an user-profile.
        /// </summary>
        /// <param name="convertingCustomer">An object with necessary data for the customer to be converted.</param>
        private void CreateAccount(ConvertingCustomer convertingCustomer)
        {
            // Create the User
            MembershipUser mu = Membership.CreateUser(
                string.Format("{0}{1}", "TEST_", convertingCustomer.UserName),
                convertingCustomer.Password,
                convertingCustomer.UserName);
            mu.IsApproved = true;
 
            // Add the user to the Customers-role
            Roles.AddUserToRoles(
                convertingCustomer.UserName,
                new string[] { "Customers" });
 
            // Update the User-Profile
            WebProfile webProfile = new WebProfile(WebProfile.Create(convertingCustomer.UserName));
            webProfile.Email = convertingCustomer.UserName;
            webProfile.CustomerNumber = convertingCustomer.AccountNumber;
            // ... add your own fields here
            webProfile.Save();
        }
        
        /// <summary>
        /// Connect to a datasource and get data for the old accounts that is to be converted.
        /// </summary>
        /// <returns></returns>
        private List<ConvertingCustomer> GetOldAccounts()
        {
            // Get old account-data.
            //var myOldAccounts = datasource.GetAccounts();
 
            // Get accounts from datasource
            //var accountDataList = from account in myOldAccounts
            //                      select new ConvertingCustomer
            //                      {
            //                          AccountNumber = account.CustomerId,
            //                          UserName = account.UserName,
            //                          Password = account.Password
            //                      };
            //return accountDataList.ToList<ConvertingCustomer>(); 
            
            return new List<ConvertingCustomer> 
            {
                AccountNumber = "123456",
                UserName = "TestUser@labs.com",
                Password = "Pa$$w0rd"
            }
        }
        
        /// <summary>
        /// Log the progress with the account conversion.
        /// </summary>
        /// <param name="accountNumber">The customers accountnumber in the backend.</param>
        /// <param name="status">Status of the progress.</param>
        /// <param name="message">An errormessage or a message with information about the conversion.</param>
        private void Log(string accountNumber, string status, string message)
        {
            using (StreamWriter sw = new StreamWriter(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "convert.log"), true, Encoding.UTF8))
            {
                sw.WriteLine("{0}\t{1}\t{2}", status, accountNumber, message);
            }
        }
 
        /// <summary>
        /// Update the ListBox in the GUI to display the progress.
        /// </summary>
        /// <param name="recordNumber">An integer with the rownumber of the account that was processed.</param>
        /// <param name="message">The resulting message from the conversion process.</param>
        private void UpdateListBox(int recordNumber, string message)
        {
            lbConvertProgress.Items.Insert(0, string.Format("#{0} - {1}", recordNumber, message));
            lbConvertProgress.Update();
        }
        
        /// <summary>
        /// An internal class used to handle customer-data. 
        /// </summary>
        private class ConvertingCustomer
        {
            public string UserName;
            public string Password;
            public string AccountNumber;
        }
        
        /// <summary>
        /// An enum with conversion-status.
        /// </summary>
        private enum ConvertStatus
        {
            SUCCESS,
            FAILED
        }
 
    }
}

 

The App.Config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="MyDataSourceConnectionString"
             connectionString="Data Source=myDatabaseServer;Database=myDatabase;User Id=myDBUser;password=P@ssw0rd;Connection Timeout=30"
             providerName="System.Data.SqlClient" />
    </connectionStrings>
    <system.web>
        <membership defaultProvider="SqlServerMembershipProvider">
            <providers>
                <clear/>
                <add name="SqlServerMembershipProvider"
                     type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
                     connectionStringName="MyDataSourceConnectionString"
                     requiresQuestionAndAnswer="false"
                     applicationName="MyWebApplication"
                     requiresUniqueEmail="true"
                     passwordFormat="Hashed"
                     maxInvalidPasswordAttempts="5"
                     minRequiredPasswordLength="6"
                     minRequiredNonalphanumericCharacters="0"
                     passwordAttemptWindow="10"
                     passwordStrengthRegularExpression=""/>
            </providers>
        </membership>
        <roleManager enabled="true"
                     defaultProvider="SqlServerRoleProvider">
            <providers>
                <clear/>
                <add name="SqlServerRoleProvider"
                     connectionStringName="MyDataSourceConnectionString"
                     applicationName="MyWebApplication"
                     type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
            </providers>
        </roleManager>
        <profile enabled="true"
                 defaultProvider="SqlProfile"
                 automaticSaveEnabled="true">
            <properties>
                <add name="CustomerNumber"
                     type="System.String"/>
                <add name="Email"
                     type="System.String"/>
                <add name="Name"
                     type="System.String"/>
                <add name="Phone"
                     type="System.String"/>
                <add name="CellPhone"
                     type="System.String"/>
            </properties>
            <providers>
                <clear/>
                <add name="SqlProfile"
                     type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
                     connectionStringName="MyDataSourceConnectionString"
                     applicationName="MyWebApplication"/>
            </providers>
        </profile>
    </system.web>
    <appSettings />
</configuration>
Technorati-taggar: ,,