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.
Excellent reminder, Dallas. I’ve made the same mistake more times than I care to admit.
I was only at a client site yesterday when this came up. What’s going to be interesting is when BI4 is released, what troubles are we going to have getting 64bit app to talk to 32bit database drivers? 🙂
– Josh
I have trouble with BO XI 4.0 right now (trying to implement the Patch 6): the CMS server doesn’t start, and it could very well be linked to the ODBC version…..
I’ll play with it and keep you informed.
Thanks for your blog
Best regards
Eric
Awesome info.. Thanks a lot
Cheers,
Kiran
Dallas – just fell for this yet again. I was in gung-ho BI 4.0/64-bit mode from the past year and didn’t get all my ducks in a row before charging through an XI 3.1 SP3 install. Fun stuff.
Gotta run – the uninstall finished. Time to re-deploy! Yippee!
Turned out I copied over the correct ODBC connection. I assumed that was the issue when I got an STW00213 error. Turned out because 4.0 requires an admin password, I got in the habit of setting one up where I used to check the box in 3.1 to setup later. I entered a password with a unicode character (eg. @, !, #, $, etc) and it was rejected at the end of the installation process:
SAP Note 1576586
Also the McAfee antivirus on their server then continued to cause a problem by blocking dbcheck.exe during the install process.
BusinessObjects and any anti-virus is always a BAD combination.
Excellent reminder, Dallas.
We did a test migration of Universe based Crystal Reports to BI 4.0. Since these reports were created in XI 3.1 and Crystal 2008, 32-bit DSNs were used which were created on XI 3.1 64-bit servers using 32 -bit ODBC administrator under SysWow64. Everything worked fine in XI 3.1. After migration to XI 4.0, these reports would throw connection errors in InfoView as well as errors when trying to create a new WebI document against the same universe. We created the DSNs used by the universe using 32-bit odbc administrator. Since BI 4.0 is now a 64-bit application, I am assuming that it looks for 64-bit DSNs and sure enough, as soon as we created the DSNs using 64-bit ODBC administrator on BI 4.0 servers, everything started working. Questions:
1. Since Crystal Reports 2011 is still a 32-bit application, it will need to use only 32-bit drivers on developer machines. But once migrated to BI 4.0 platform, at runtime, BI 4.0 will look for the same DSN under 64-bit DSNs, correct?
2. No client tools will directly access the BI 4.0 servers so is there a need to retain the 32-bit DSN on the servers?
3. Any scenario where we would need to retain copies of the same DSN under both 32/64-bit connections on the servers? FYI, no client tools are installed on the server.
Thanks.
Farhan,
No, BI4.0 doesn’t need 32-bit DSN’s. Unless you choose to install CR on server to assist in troubleshooting.
We were looking at an Oracle problem today. No ODBC, but still wishing CR was on server to diagnose why it wasn’t running properly.
I think SAP made the right decision to keep clients 32-bit, but it does make side-by-side testing tricky. I’ll bet 5.0 will be exclusively 64-bit.
Is is possible to install XI 3.1 on a 64 bi processor using Windows Server 2008 R2 while referencing the SMS and Audit databases in SQL Server 2008 that is located on a seperate 64 bit server?
I am going through this installation and still getting a SMS error STW00226. Very frustrating.
Brian,
Always check the Platform Availability Matrix (PAM), but your Windows Server OS + SQL Server combination is supported. Just remember that SAP BusinessObjects Enterprise XI 3.1 is a 32-bit application, even when installed on a 64-bit OS. That means that you’ll need to create 32-bit rather than 64-bit ODBC connections to the database. Hence, the “fun” that Steve Ballmer and crew have given us. Good luck!
Thanks Dallas. So you don’t feel that using a second server for the location of the SQL Server database is a hurdle for a successful installation? And btw, as a former employee of Microsoft, I have absolutely no idea why that developement team would have use such a odd naming convention for the drivers.
Brian,
You’re doing the right thing. It’s actually best practice NOT to have the database running on the BOBJ server. Trust me, it needs all the performance it can get.
If you’re still scratching head, probably should open a SAP support note.
Dallas
Hi,
This is exactly the problem I am facing. I have installed Oracle 11g Server and Client and set My Oracle Home to the server. I am then installing BO XI3.1 and have no experience in knowing what to do when I see the STW00226 error. So any plain english help would be great.
Thanks
Sharon,
I’m not familiar with this problem and Oracle, as SAP BusinessObjects connects to Oracle directly, not through ODBC as it does for MS SQL Server. I recommend opening a case with support or posting your question on the BusinessObjects Board.
Dallas
Dallas,
Thanks for posting this! It seems like a simple thing but it definitely helped me out today.
Jim, glad it was helpful. Kind regards, Dallas
Very, very grateful for this explanation and steps to set this up correctly. Thank you!
Dallas,
How are you?
I remember the fun with this 32 and 64 bit DB drivers…. ha ha.
Now trying my hand on Linux VM. Wondering if we would experience the same fun there too 🙂
Any idea, will BI 4 SP 5 on 64 bit Linux VM require both 64 and 32 bit Oracle Client? Or will 64 bit Oracle Client (11.0.2.0) suffice?
Thanks
Aurobindo
Have not had a Linux 4.0 customer yet but used it for XI R2 and XI 3.1. My hunch is that only Crystal Reports will still need 32-bit DB, but if I were you I’d only install 64-bit first to see what specifically breaks.