Test connection failed because of an error in initializing provider

You may experience the following error. When a developer installed oracle client and tested the visual studio BIDS packages ask you to fix this. We know the MS only has a visual studio BIDS 32 bit.

Recently, It asked in the forum, I had managed Oracle sometimes and had same kinda issue couple more times–> https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6b6efab4-c26f-45cd-aca6-4ba3d53f13a4/the-oraoledboracle1-provider-is-not-registered-on-the-local-machine?forum=sqlintegrationservices

How can we find which bit of oracle installed on:

How to Tell if Oracle Client is 32 or 64 bit installed on Windows

Errors:

1) Test connection failed because of an error in initializing provider. Oracle client and networking components were not found.These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.Provider is unable to function until these components are installed.

2) TITLE: SQL Server Import and Export Wizard The operation could not be completed.

The ‘OraOLEDB.Oracle.1’ provider is not registered on the local machine.

When you do a ETL from SQL server to oracle database, you might get this error, especially on 64 bit windows.

3) The requested OLE DB provider MSDAORA.1 is not registered — perhaps no 64-bit provider is available.  Error code: 0x00000000.

An OLE DB record is available.  Source: “Microsoft OLE DB Service Components”  Hresult: 0x80040154  Description: “Class not registered”.

How to fix this:

1. Software needed https://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
2. Steps to troubleshoot

Download the both Oracle 32 and 64 bit 11G or latest S/W – full client do a full Administrator installation, you can also select whichever you want, if you know it.

Configure the “tnsnames.ora” to connect the oracle database, you can do this by NETCA using same installation or easy one get the file from oracle server and copy paste it to the right location OH Admin folder, you can get a oracle DBA help (OR) You can do it yourself go to RUN–> regedit –> HKEY_LOCAL_MACHINE –> SOFTWARE –> ORACLE or if you have already tnsping DBname or echo $ORACLE_HOME. Find the oracle home and paste it.

$ORACLE_HOMENetworkAdmin directory”

Ex: F:Oracel_32Bitproduct11.2.0client_1NetworkAdminTnsnames.ora”.

Sample Tnsnames.ora

DB_test =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)

(Host = DBname.abcd.com)

(Port = 1521)

)

(CONNECT_DATA =

(SID = DBname)

)

)

Set the environment variable by Right click My computer –> Properties –> Advanced system settings –> Click –>Advanced tab–>Environment Variables –>Click –> Navigate Path –>Click edit –> paste the 32 bit client installation first and “;” 64 bit installation path and leave the remaining.

Ex: F:Oracel_32Bitproduct11.2.0client_1bin;F:Oracle_64Bitproduct11.2.0client_1bin;C:Windowssystem32;C:Windows and rest you can leave it. Oracle_Home and Admin if you want you can set as well.

Reboot the server.

Testing steps:

1. Try to connect oracle database to make sure you have a connectivity.

In command line:

= = = = = = = = = =

C:Usersusername>tnsping DBname — Type this.

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 04-NOV-2

017 08:59:01

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

F:Oracel_32Bitproduct11.2.0client_1NetworkAdminsqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host = sukgch0

5) (Port = 1521)) (CONNECT_DATA = (SID = DBname)))

OK (50 msec)

Even you can try connect the oracle user and password by CMD. If you have an account given by oracle team. Type the following and enter the password.

= = = = = = = = = =

U:>sqlplus username@dbname

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 4 15:28:35 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 – 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 – Production

SQL> select * from user_tables;

no rows selected

You will get the response like the above, if it does not ,you have a problem with database connection you need to fix it before move to next step.

Once you have a connection with oracle database, test the provider connection by import and export wizard.

Testing BIDS/ SSIS package / OLEDB provider:

Connect SSMS –> Go to database –> Right click –> Tasks–>Import data –>Source Microsoft OLED provider for oracle (OR) Oracle provider for OLE DB –> enter the server and credential detail and test it.

If you have SQL agent job check the package run mode 32 or 64 bit in the agent job step and test it and make a change.

Fell free to drop me an comment, I have installed Oracle on my VM, I can help it.

1. In Excel, you choose to set up a data connection From Other Sources -> From Analysis Services. When you enter in the [ServerNameInstanceName], you get the following error:“Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the ‘[ServerName]’ server.”
2. In Excel, you choose to set up a data connection From Other Sources -> From Analysis Services. When you enter in the connection string [ServerName:SSASPort] (usually 2382/2383 by default), you get the following error:“The following system error occurred: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.”
3. In Excel, you choose to set up a data connection From Other Sources -> From Analysis Services. When you enter in the connection string [ServerName] (no Instance name or port), you get the following error:“The following system error occurred: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.”
4. In SSIS, you attempt to create a new connection to Analysis Services. In “Server or file name” textbox, you enter [ServerNameInstanceName] and get the following error:“Test connection failed because of an error in initializing provider. Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the ‘[ServerName]’ server. Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the ‘[ServerName]’ server.”
5. In SSIS, you attempt to create a new connection to Analysis Services. In “Server or file name” textbox, you enter [ServerName] (no Instance name) and get the following error:“Test connection failed because of an error in initializing provider. The following system error occurred: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.”

Solution:

First things first, since the error message may have mentioned the SQLBrowser service is not running – log on to your server and ensure it is running. Mine was. On to the next solution:

Try simply putting in the IP ADDRESS of the server instead of the server name. No Instance name or port needed. In both Excel AND SSIS, this solution worked for me! What this most likely means is that there is a name (DNS) resolution issue on the server that needs to be resolved. I was not involved in setting up the server so I had no idea that there was a resolution issue.

After spending two days trying to get this to work in Excel, I finally stumbled upon this MSDN article that covers connection problems and found a paragraph about name resolution that I had completely skipped over upon first consulting it. It has a pretty good explanation of what ended up being my issue:

If the client application cannot connect to Analysis Services by using the server (or serverinstance) name, there may be name resolution problems on the network. Try connecting to the Analysis Services instance by using the IP address of the computer that is hosting Analysis Services rather than using the server name itself (xxx.xxx.xxx.xxx or xxx.xxx.xxx.xxxinstance_name).

Tip If you are connecting to an Analysis Services instance on the same computer as the client application, try using the server name or IP address rather than using localhost or (local).

You can also use the Ping command-line utility to isolate the problem. Try to ping the computer that is hosting Analysis Services by using the computer’s hostname, fully qualified domain name, and IP address. You can use the Ping –a parameter to return the fully qualified domain name of a computer. 

If you can ping the computer only by its IP address, you have isolated the source of the connectivity problem. Resolve the name resolution issue and the connectivity issue will go away.

Tip To begin resolving a name resolution issue, verify that there are no incorrect entries in the hosts or lmhosts files on the local computer. These files are located in the ..system32driversetc folder on the client computer.

Please let me know in the comments if this solution worked for you!