ODBC Connection Error and VMWare View Composer 4.0 installation

I was working on creating an installation of VMWare vSphere View Composer 4.0 to connect to MS SQL 2008 Enterprise database. This install requires an ODBC connection to exist on the server.

Since my SQL server resides on the same server that vSphere is installed on, I thought I would create an SQL native client connection via ODBC.

Creating this connection with the Native Client always threw an error when specifying the ODBC DSN. In order to troubleshoot, I had to remove and recreate the ODBC connection, which brought me to the first issue:

ODBC Connection cannot be deleted!

That’s right, anything I do to remove or reconfigure the ODBC connection would give me the following error:
The setup routines for the he login. The login failed. ODBC driver could not be found

Looking up some info regarding the error, I found that the ODBC drive may have gotten corrupted, and the MDAC drivers need to be reinstalled. To do that you go to c:\Windows\INF and find mdac.inf

Right Click –> Install –> mdac.inf (You need the windows 2003 CD for that to work)
Once the CD is provided, all the original files pertaining to the ODBC drivers will be reinstalled.

Unfortunately, doing this didn’t resolve my problem. The next step was try to find the source of this ODBC connection, and deleting it from the registry. There are two location under  HKLM/SOFTWARE/ODBC/ODBC.INI

From ODBC Data Sources: Delete the DSN entry in question
Also Delete the ODBC specific connection under ODBC.INI

Now, go back to the Data Sources (ODBC) under administrative tools, and you will see the stubborn System DSN is no longer there. Hooray!!

Now, re-create your ODBC connection

That was only the first problem.

Connect Composer to MS SQL 2008 via ODBC connection!

the second was related to the connection from Composer to the database. For a minute there, I was thinking View Composer does not support SQL 2008 yet, as it is not listed on the compatibility list.

now that I could mess with the ODBC connection, I decided to re-create it with a SQL Sever connection (instead of Native client), user the SQL Authentication, with a DB user that has db_owner privileges on the database. Woo hoo! that did it! the connection was successful.

In searching google, I couldn’t find anyone dealing with this sort of issue, and  especially not a whole lot dealing with VMWare composer 4.0 and SQL 2008. So hopefully this will help someone in their quest to install these 2 products.