This week, I helped a customer install SAP BusinessObjects XI 3.1 SP3 on Microsoft Windows Server 2008 64-bit using Microsoft SQL Server 2005 for the system (CMS) and audit databases. And once again, I was tricked by Microsoft’s ODBC Data Sources panel into creating 64-bit ODBC connections that were rejected by the XI 3.1 installation program. XI 3.1 is fully supported on 64-bit operating systems, but it’s still a 32-bit application that requires 32-bit database connectivity. The whole experience felt like deja vu, and sure enough, I blogged about this topic over two years ago when it burned me the first time. So let’s review (from Microsoft Support article 942976):
The 64-bit version of the
Odbcad32.exe file is located in the
C:WindowsSystem32 folder. This 64-bit version appears on the Windows Start menu.
The 32-bit version of the
Odbcad32.exe file is located in the
C:WindowsSysWoW64 folder. This version does not appear on the Windows Start menu.
Got that? 64-bit code is stored in a folder named “System32” and 32-bit code is stored in a folder named “SysWoW64”. And both ODBC panels are identical in appearance – there’s no real clue to which one you’re using. All I can say is “SysWoW64! That’s really intuitive, Mr. Ballmer!”
During the XI 3.1 installation, the dialog box for establishing the BusinessObjects system and audit databases for Microsoft SQL Server will have the “Consume DSN created under WOW64” box checked by default. You should see your 32-bit DSNs on the list of available DSN’s. If your DSNs refuse to apper until you uncheck the “Consume DSN created under WOW64” box, that’s your clue that you goofed up and created 64-bit DSNs. Attempting to proceed further will cause the installation program to generate a humiliating STW00225 (Audit connection) and/or STW00226 (system/CMS connection) error message.
SAP tries to warn us with the following note in the supported platforms document:
BusinessObjects products use the 32-bit ODBC registry on all versions of Windows. To administer 32-bit ODBC DSNs on 64-bit versions of Windows, run the 32-bit ODBC Administrator, located here: C:WindowsSysWOW64odbcad32.exe
Thankfully, the upcoming release of SAP BusinessObjects Business Intelligence 4.0 is fully 64-bit, allowing the use of 64-bit DSN’s to Microsoft SQL Server. So it won’t be long before we can all put this ODBC controversy behind us. Well, all of us except for Mr. Ballmer.
UPDATE: Although the SAP BusinessObjects Business Intelligence 4.0 platform is 64-bit, it is not fully 64-bit (see related article, SAP BusinessObjects BI 4 is (Mostly) 64-bit). In particular, Crystal Reports 2011 still requires 32-bit database connectivity (see related article, Still Having Fun with 64-bit Windows and ODBC).
Best Practices for SAP BusinessObjects and ODBC
To minimize some of the confusion, create clearly labeled desktop shortcuts to the 32-bit ODBC panel (
C:WindowsSysWoW64 folder) and the 64-bit ODBC panel (
C:WindowsSystem32 folder) before even attempting your SAP BusinessObjects installation. Then create your DSNs via the appropriate shortcut (32-bit for XI 3.1 and lower, 64-bit for BI 4.0 and higher). On Microsoft Windows 2008 Server, I move these shortcuts to the hidden folder
C:UsersPublicDesktop so the rest of the administrative team can use them.
I used to recommend adding
_32 as a suffix to your DSN names to remind everyone that they are 32-bit connections. But then SAP BusinessObjects Business Intelligence 4.0 arrived. Crystal Reports 2011 is still 32-bit on the server, so I now prefer that the 32-bit DSN and 64-bit DSN share the same name.
Thank you, Mr. Ballmer.