How to import Microsoft Access into SQL Server 2005

I have a client that has an old Microsoft(MS) Access 2000 database that is attached to ColdFusion 6.1.

As one of the developers working on this project, I recommended, as did my colleague, that the client upgrade their database to SQL Server 2005 for better maintainability, scalability and development efficiency.

I have been using SQL Server since version 7.0 when Data Transformation Services (DTS) was introduced. DTS is an ETL or Extract, Transform and Load process that allows users to import and export data of many database types rather easily.

I started to research DTS with SQL Server 2005 and I found that service has a new name. DTS is now called SQL Server Integration Services (SSIS).

SSIS sounds like a great product that extends the previous DTS modules to be compatible with 2005 databases, but the question remained, “How do I import a MS Access database into SQL Server 2005?”

I started reading tech notes, blogs and forums for the solution. I noticed that this question is quite prevalent. The suggested solutions ranged from linked tables, the Microsoft Upsizing Wizard and ODBC datasource exports. I found that the suggested solutions did not sit well with me; I wanted a better solution.

My intuition told me that Microsoft would not release a database product unless they had some sort of migration tool or wizard that would help assimilate databases into the new 2005 database engine.

I am sure people are going to disagree with me on this one.

Regardless of Microsoft’s past response time to new product offerings, Microsoft will create a tool or console that will make it easier for more organizations to be apart of the Microsoft paradigm.

I am not peddling Microsoft’s software, but many of my clients do have MS Access or SQL Server for reasons of budget , consulting costs and apprehension with open source software.

I found a solution that solved my problem perfectly: SQL Server Migration Assistant (SSMA) for Access

I was surprised how many suggested solutions did not allude to this tool. SSMA is a free software product from Microsoft that has three versions (MS Access, Oracle, and Sybase).

In my efforts to help others, I have created a Microsoft Word document that will show you how to import Access into SQL Server 2005. Now, I say “into SQL Server 2005” as the documentation for the product does not discern the various versions of SQL Server 2005, so my intuition would suggest that this will work for other versions of SQL Server 2005.

Download the walk-through by clicking the following link:

Migrate Access to SQL Server 2005

Changing authentication in SQL Server 2005 Express

I was assisting some friends with a database issue. For the life of us, we could not get the database registered.

I eventually went down the path of, “How was the authentication set up?” This was the right question, but the answer was not readily found. We really did not want to uninstall and re-install the database, so one of us searched MSDN and found that the authetication mode is stored in the registry.

The registry path: \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\

The registry key: LoginMode – REG_DWORD – 0×00000001

I found that you need REG_DWORD to be 0×00000002 for mixed mode authentication.

You can change the value of the REG_DWORD by double-clicking the registry key(LoginMode) and replacing the value from 1 to 2.

Note: Make sure the radio button is still on hexadecimal

After the DWORD was changed, the database authenticated and all was right in the world.

This was a default installation so if you created an instance name, your registry path will differ only slightly towards the end of the path.