ODBC Connection Issue

[Former Member]

Dear Experts,

My BO version is 4.1sp1 under default database ( SQL anywhere) and OS is windows

I an connect my BO server to legacy database ( SQL server 2008 R2) Using ODBC Connection .

but when I give user and password following error occure

Both BOBJ server and SQL server physically different machine

I am connect with same credential in SQL server management studio

Regards

[Former Member]

This message was moderated.

[Former Member]

Shahid,

Make sure you have drivers installed on the server and create a connection with generic users account which has SQL server authentication.

If you can use OLEDB  provider try to use it.

Thanks,
Raja

[Former Member]

Dear All,

I created both 32bit ODBC and 64 bit ODBC on bobj server with same name sqlDSN I test IDT from BOBJ server ( Also Client tools installed in BOBJ server ) It working OK but when I connect IDT from Client machine it says [Microsoft][ODBC Driver manager] Data source not found no default driver specified   that means its need Data source sqlDSN in client machine.

and customer say do not allow client machines to speak to backend servers directly (SQL server ). I don't know what to do .

I tryed to created OLEDB prvider  but when I test connection it give me error

" OLE DB Provider CLSID not specified or erroneous"

SQL Native client 11 also installed on BOBJ Server

Regards

[Former Member]

You need to have a ODBC connection on the client machine too.

[Former Member]

Please check below KB- Even though it is related to 3.1, you should find oeldb.sbo in 4.0 install.

1204488 - Error: "CS:COM Provider CLSID not specified or erroneous" trying to create SQL Server 2005...

[Former Member]

Hi Shahid,

Could you please double check with users on what they exactly mean by "do not allow client machines to speak to backend servers directly"?

In order to establish connectivity, client machine or server machine (where you are launching IDT) require Database middleware or Native Client to connect with Database Server.

So there should not be any firewalls between machine with middleware and Database Server.

Also the hosts file of client machine should have hostname and ip address of Database Server machine.

I went through the whole thread and can suggest following:

Create 32 bit DSN on BO server as it will act as a client for Database Server.

Launch IDT on BO Server (for testing) --> use this DSN in your connection and check if it is responding.

Regards,

Yuvraj

[Former Member]

Thankx Yuvraj,

actualy our BOBJ server and also client installed in same system  and we test Universe from IDT also from universe design tool it's working fine, but when I login IDT from Client machine it's say's can not find saource data name ( which is our ODBC name) and when I run univers tool from Client mahine it's show our univers but when I double click it's say's can not find universe we are connected in right server .

please explain more about your reply

Launch IDT on BO Server (for testing) --> use this DSN in your connection and check if it is responding.

I am unable to understand     "use this DSN in your connection and check if it is responding."

Regards

[Former Member]

Thankx Yuvraj,

actualy our BOBJ server and also client installed in same system  and we test Universe from IDT also from universe design tool it's working fine, but when I login IDT from Client machine it's say's can not find saource data name ( which is our ODBC name) and when I run univers tool from Client mahine it's show our univers but when I double click it's say's can not find universe we are connected in right server .

please explain more about your reply

Launch IDT on BO Server (for testing) --> use this DSN in your connection and check if it is responding.

I am unable to understand     "use this DSN in your connection and check if it is responding."

Regards

[Former Member]

try to re-install the client tools. Do check whether the client is able to ping / telnet the server.

Firewall and anti-virus can cause connection issues. Give an exception to the client software in firewall and see whether it works.

[Former Member]

Hi Shahid,

As far as I understood, when you check the issue on BO server, IDT and Universe connection is workig fine. This is what I suggested to check

But it seems like when you login from another Client machine, it is not working.

Is this issue coming with single client machine or multiple machines?

If single machine then you can definitely go for re-installation and check.

If multiple machines then check if correct middleware is installed and the DSNs are in place.

The error says:

SQL Server does not exist or access denied.

Check the user used in DSN and check if it has atleast SELECT previlige.

Also check if there is any firewall coming in between. Use ping as suggested by Arvind and check if all packets are received by Server.

Regards,

Yuvraj

[Former Member]

Come across this issue now only (but might help any one else) and this is because the firewall in BO blocking the sql server

[cbethune]

This error appears to be driver related.  Perhaps you are attempting to use the default MS-SQL driver in the OS rather than setting up a version specific DSN.  Try the following:

1.  Install the MS-SQL 2005 client software on your local system where you are attempting to use a client tool such as IDT.

2.  Make sure that the same client software is installed on your server and that the 32 bit  and 64 bit DSNs are consistently named and using the same MS-SQL driver version.

3.  Attempt using your software again.

Some tools are more forgiving than others about how you configure your ODBC DSN and what MS-SQL client version you are using.  Also if you are editing a universe or a connection via IDT on your local machine you are using the ODBC DSNs on your local machine rather than the server by default.

[Former Member]

Thanks all for given me guidance

Our developer created ODBC connection in client machine  when he run webi reports  from that client webi reports run, but when he run that report from server then following error occur

“ Database error.[Microsoft][ODBC Driver Manager] data source name not found and no default driver   specified.(IES 10901) (WIS 10901)."

I told him to create ODBC connection in BOBJ server .

What is the best way to create , if I create 64bit connection then client cannot connect with this 64bit ODBC connection .

My  query Is create 32 bit ODBC or 64bit  or create both 32bit and 64bit with same name in BO server ?

Regards

[former_member136842]

Hi,

on the Server you need a 64 Bit ODBC connection and on the Client you need a 32 Bit ODBC connection.

Regards

-Seb.

[Former Member]

I create universe and published in server  now I an using different clinet machine and wanto to change or run this universe

why this error occur can I create ODBC connection ( sqlDNS in every client machine)

[cbethune]

It looks like you are missing a 32 bit ODBC DSN or 32 bit MS-SQL drivers or a on your server if everything is working locally.  Make sure you have both 32 and 64 bit drivers are installed.  MS-SQL is usually pretty good about doing this by default.  I would suspect you need another ODBC DSN to handle the 32bit connectivity need.

DISCLAIMER HERE:  Client Tools should typically be used on a client machine and not on your BI Platform server.  However, it is understood there may be a need for testing and troubleshooting.

I would recommend creating both a 32 and 64 bit ODBC DSN to make sure all bases are covered. Consider that in some point in the future you may need to produce Crystal 2011/2013 reports or consume a UNV.  In those cases 32 bit connectivity is likely to come into play.

If you look at the install path on your server you will see two folders:

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86

(32-bit components as a rule of thumb)

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64

(64-bit components as a rule of thumb)

You will find the IDT executable under win32_x86 which means its a 32-bit app which makes sense because it is a client tool and many (not all) client machines are still running 32-bit operating systems.

SCN community, please help keep me honest here if I am off base on anything here.

[Former Member]

Please check if you have both 32 bit and 64 bit DSNs.

[Former Member]

Jawahar where to check DSNs 32 and 64 bit in BOBJ server ( where ODBC connection creating ) ?

in server list my system not show I manually write system in server field hostname\intancename or only hostname

in our dev environment same OS version same database version and BOBJ version same no issue in dev

Regards

[Former Member]

• To create 32 bit ODBC data sources, use the ODBCAD32.exe in C:\Windows\SysWOW64\odbcad32.exe
• To create 64 bit ODBC data sources, use the ODBCAD32.exe in C:\Windows\System32\odbcad32.exe

[former_member136842]

Hi,

are you using the Windows Authentication or integrated Authentication?

Regards

-Seb.

[Former Member]

I am able to create odbc connection but when  I create connection through information design tool and assign user password and datasource name and test it give me error

DBD: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

I am creating ODBC from BOBJ server and BOBJ server in 64 bit how can I create 32bit ODBC connection

Please guide me

Regards

[onkarvelhals]

Hi Shahid,

A 64-bit version of the Microsoft Windows operating system includes the following versions of the Microsoft Open Database Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):

• The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
• The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

The Odbcad32.exe file displays the following types of data source names (DSNs):

• System DSNs
• User DSNs

*source

Hope this helps.

Regards,

Onkar