OLE DB connection error for MS SQL Server 2008 in Information Design Tool

[Former Member]

Hi,

System Details:

Windows Server 2008 R2 (64 Bit)

SBOP BI Platform Server 4.0 SP02 (64 Bit)

SBOP BI Platform Clients 4.0 SP02 (32 Bit)

Problem:

In new information Design Tool (4.0 SP02), while creating 'Relational Connection' to MS SQL Server 2008 via OLE DB Providers middlebase driver, getting below error message as "Test Connection" is clicked :

OLE DB Provider CLSID not specified or erroneous

Any suggestions to resolve this? Thanks in advance.

Nirmit

[Former Member · Accepted solution]

Hello,

You need to install SQL Native Client in your machine.

Look for this KB Article:

1204488 - Error: "CS:COM Provider CLSID not specified or erroneous" trying to create SQL Server 2005 or 2008 OLEDB connection in BusinessObjects client tools

This will help you out.

Thanks,

Vivek

[Former Member · Accepted solution]

Thanks for your suggestion Vivek. However isn't it true that BO Server installation is supposed to install all the necessary database middleware driver for accessing the respective database from Universe layer?

If I'm right, then explicitly installting Native SQL Client would do nothing but install the OLE DB drivers necessary for accessing SQL Server, which, in this case, should be taken care of by BO BI 4.0 server installation itself.

Any comments please?

Nirmit

[Former Member · Accepted solution]

Well that will not be possible because there are plenty of database (Terdata, Sybase, DB2, Oracle, Microsoft etc.) and that too with different versions so which DB and version end user will be using will not be known to Business Objects.

Hence to install the native drivers is always users responsibility.

Another important thing if you are using client installation and server installation concept make sure you have proper connectivity on both palces.

Thanks

Vivek

[Former Member · Accepted solution]

Hi Vivek,

Thanks for your response. I agree by what you say.

However, while accessing a databse, in my case SQL Server 2008, through OLE DB providers, should it also require native client drivers installation. As much as I know, its required while we want to connect to database using ODBC/JDBC middleware driver connectivities. Not sure about OLE DB middleware. Can you elaborate bit more please.

Thanks,

Nirmit

[Former Member · Accepted solution]

Check that SAP note you will get your answer.

I am just picking a section of it: -

Cause

-

---

This is expected behavior if Microsoft SQL Native Client (SQLNCLI) is not installed

Per Microsoft Knowledge Base Article 910016, MS recommends using MS SQL Native Client which includes OLEDB and ODBC connectivity

http://support.microsoft.com/kb/910016

BusinessObjects Enterprise has followed Microsoft recommendation in setting up OLEDB connectivity for MS SQL Server 2005 and 2008 to use SQLNCLI instead of MDAC

Thanks,

Vivek

[Former Member · Accepted solution]

Thanks Vivek for all your help!

I seem to understood from here that Native SQL Client drivers are recommended and required to connect to SQL Server via any middleware options. Will work out on these options.

Thanks again,

Nirmit

[Former Member · Accepted solution]

I come back to that case, as we are using a MS OLAP cube (STSWarehouse, for old BOBJ demo users...), in the new BI4.0 SP2 (FP5) BODI VM, and we are confronted to a connectivity issue. The cube responds correctly in MS Studio. We can setup a unv universe on top of it with designer and query with Webi. On the other hand, impossible to define an OLAP connection with the IDL or through the CMC, it generates an error (CodeErr404, Description not found).

Never been confronted to that issue ?

Kind reg,

Fred.

[Former Member · Accepted solution]

Hi Fred,

Not sure of your error. We have tested wih MSAS OLAP cube with below results-

In our case, on SBOP BI 4.0 SP02 Client tools, we could successfully create an OLAP connection to MSAS 2008 through XMLA in our system from IDT 4.0 SP02 and could create DF and Business Layer & exported as Universe on top of it.

Consumed unx in WebI 4.0- report shows data.

Consumed unx in Webi 4.0 SP02- throws error-

ErrorCode: 0x80040154

Description: Class not registered

Source: Microsoft OLE DB Service Components

(WIS 00000)

We haven't yet started diagnosing this though.

Regards,

Nirmit

[Former Member · Accepted solution]

Hi Nirmit,

We have the exact same problem as you ... did you find a solution ?

cheers

Peter

[Former Member · Accepted solution]

Hi Peter,

We have not gone ahead testing and installing the SQL client on the IDT machine for the middlebase driver to be able to connect to MSSQL server. But I learnt from various sources that SQL client would definitely be required so that the drivers are made available for the connectivity to be established. Try installing SQL client. Let me know the results please.

Nirmit

[former_member380832]

I had the issue in BI 4.1 SP2.

The only thing that needed to be installed was the "Microsoft® SQL Server® 2008 R2 Native Client" from the link below. You have to choose the 64 bit version for a 64 bit OS, but it actually installs both the 32 and 64 bit OLE drivers. For 32 bit client machines you have to install the 32 bit version of the drivers.

Download Microsoft® SQL Server® 2008 R2 SP2 Feature Pack from Official Microsoft Download Center

Thanks,

Ralph

[Former Member]

Hi, I installed the OLE drivers, but now I am getting another error and can't find anything on this on the SCN:

[Microsoft SQL Server Native Client 10.0] : Login failed for user [usernname]

Regards,

Jacqui

[former_member380832]

That appears to be an authentication error. Did you put in the correct database username and password in the Universe connection? Also, do you have the username hard-coded into the connection or are you using pass-through authentication? If you are using pass-through, each user must have a DB account.

[Former Member]

I am using Information Design Tool. I can log in Launch Pad and CMC with the same username and password, so that is correct. I've tried hard-code connection and pass-through, but not success in either. The user does have a DB account. I am wondering if the Windows AD authentication is causing the issue or if something further needs to be done in the CMC or the Custom Parameters? I am not an administrator, but just trying to narrow if this is a SQL Server issue or a BOBJ issue.

Thanks for your help.

[Former Member]

I had the same Problem on BO4 SP2.12

Problem solved by additional install SQLServer2005_ASOLEDB9_x64.msi and sqlncli.msi .

If you wat to connect to an SQL2005 Analysis Server youll need both Version of MSSQL Native Client.

SQLSERVER2008_ASOLEDB10.msi

SQLServer2005_ASOLEDB9_x64.msi

Download from:

http://www.microsoft.com/download/en/details.aspx?id=15748

Regards

Sven