This is an old question but Oracle’s latest installers are no improvement, so I recently found myself back in this swamp, thrashing around for several days …
My scenario was SQL Server 2016 RTM. 32-bit Oracle 12c Open Client + ODAC was eventually working fine for Visual Studio Report Designer and Integration Services designer, and also SSIS packages run through SQL Server Agent (with 32-bit option). 64-bit was working fine for Report Portal when defining and Testing an Data Source, but running the reports always gave the dreaded «ORA-12154» error.
My final solution was to switch to an EZCONNECT connection string — this avoids the TNSNAMES mess altogether. Here’s a link to a detailed description, but it’s basically just: host:port/sid
http://www.oracledistilled.com/oracle-database/oracle-net/using-easy-connect-ezconnect-naming-method-to-connect-to-oracle-databases/
In case it helps anyone in the future (or I get stuck on this again), here are my Oracle install steps (the full horror):
Install Oracle drivers: Oracle Client 12c (32-bit) plus ODAC.
a. Download and unzip the following files from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html and http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html ):
i. winnt_12102_client32.zip
ii. ODAC112040Xcopy_32bit.zip
b. Run winnt_12102_client32client32setup.exe. For the Installation Type, choose Admin. For the installation location enter C:OracleOracle12. Accept other defaults.
c. Start a Command Prompt “As Administrator” and change directory (cd) to your ODAC112040Xcopy_32bit folder.
d. Enter the command: install.bat all C:OracleOracle12 odac
e. Copy the tnsnames.ora file from another machine to these folders: *
i. C:OracleOracle12networkadmin *
ii. C:OracleOracle12product12.1.0client_1networkadmin *
Install Oracle Client 12c (x64) plus ODAC
a. Download and unzip the following files from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html and http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html ):
i. winx64_12102_client.zip
ii. ODAC121024Xcopy_x64.zip
b. Run winx64_12102_clientclientsetup.exe. For the Installation Type, choose Admin. For the installation location enter C:OracleOracle12_x64. Accept other defaults.
c. Start a Command Prompt “As Administrator” and change directory (cd) to the C:SoftwareOracle ClientODAC121024Xcopy_x64 folder.
d. Enter the command: install.bat all C:OracleOracle12_x64 odac
e. Copy the tnsnames.ora file from another machine to these folders: *
i. C:OracleOracle12_x64networkadmin *
ii. C:OracleOracle12_x64product12.1.0client_1networkadmin *
* If you are going with the EZCONNECT method, then these steps are not required.
The ODAC installs are tricky and obscure — thanks to Dan English who gave me the method (detailed above) for that.
First published on MSDN on Jun 30, 2010
This is one of the most common errors while creating linked server to Oracle database. Today I will discuss the reason for this error and possible resolutions.
Full error message:
OLE DB provider «MSDAORA» for linked server «LINKED_ORA» returned message «ORA-12154: TNS:could not resolve the connect identifier specified».
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider «MSDAORA» for linked server «LINKED_ORA».
First of all make sure you have reviewed the following Microsoft KB article that has a lot of good information on troubleshooting Oracle linked server issues.
How to set up and troubleshoot a linked server to an Oracle database in SQL Server
http://support.microsoft.com/kb/280106
Also make sure you have installed Oracle Client on the SQL server. If the SQL server is 64 bit then we need to install 64 bit Oracle provider. You can also create linked server using Oracle ODBC driver together with Microsoft OLE DB provider for ODBC. Once again on a 64 bit SQL server you need to install the
64-Bit OLEDB Provider for ODBC (MSDASQL)
and 64 bit Oracle ODBC drivers. However 64-Bit OLEDB Provider for ODBC (MSDASQL) is already there in Windows Vista/Windows Server 2008 and later OS.
This particular error message is a very general error message and can happen for quite a number of reasons. For general understanding of the error, you can review oracle documentation like this
http://ora-12154.ora-code.com/
In SQL Server Linked Server, it could indicate a few things (not limited to)–
1. SQL Server (and oracle net libraries) is not able to get the TNS alias from tnsnames.ora file.
2. Something is wrong with the way the alias is created in the tnsnames.ora file (incorrect syntax)
3. TNS alias could not be resolved into a connect descriptor
Below is a list of things that you can try to resolve this issue.
1. Verify that the tnsnames.ora file has the alias and the service name that the customer is using.
TNS entry for the Oracle database
===========================
OracleDB_Dev =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server01.mydomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleDB)
(SERVER = DEDICATED)
)
)
In the above tnsnames.ora file Alias = OracleDB_Dev
Service Name: OracleDB (Actual Oracle service name [instance name in SQL])
2. Check the sqlnet.ora file under ‘Admin’ folder in Oracle home [Dir:appproduct11.1.0client_1networkadmin] and ensure that we have TNSNames in NAMES.DIRECTORY_PATH
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
3. Verify if you can connect to Oracle from the SQL server machine using tools installed with Oracle Client [For example «SQL Developer» or “SQL Plus”] with the same user id/password or TNS alias.
5. Check if the environment variable ‘PATH’ has the path for tnsnames.ora file specified.
Sample Value of Environment Variable PATH:
E:appproduct11.1.0client_1bin
;C:Program FilesBusiness ObjectsCommon3.5binNOTES;C:Program FilesBusiness ObjectsCommon3.5binNOTESDATA;%Systemroot%Microsoft.NETFrameworkv1.1.4322;%SystemRoot%system32;%SystemRoot%;%SystemRoot%System32Wbem;C:Program FilesDellSysMgtomabin;C:Program FilesMicrosoft SQL Server80ToolsBINN;C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60TEMPLATEADMIN1033;C:Program FilesMicrosoft SQL Server80ToolsBinn;C:Program FilesMicrosoft SQL Server90DTSBinn;C:Program FilesMicrosoft SQL Server90Toolsbinn;C:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDE;C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies;C:Program FilesMicrosoft Network Monitor 3
Note: make sure that the path is a valid path and there is no space.
6. Check the value of the key ”Oracle_Home” in the registry under HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_OraClient11g_home1 and verify that it has the right path for the Oracle home.
7. Check for the registry key “TNS_ADMIN” at HKEY_LOCAL_MACHINESOFTWAREORACLE. If it exists then make sure it has the right value as “Dir:appproduct11.1.0client_1networkadmin”. If you don’t see the key then create the key and set appropriate value as below.
Regedit->HKEY_LOCAL_MACHINE->Software->Oracle->RightClick NEW->StringValue and name
it TNS_ADMIN and give the value “X:appproduct11.1.0client_1networkadmin”
Note: This is not a must but in some cases this is what fixed the issue.
8. Check if SQL server start up account has permission to the Oracle Home. Also collect Process monitor log and check for “access denied”. Process monitor log should show if we are able to find the tnsnames.ora file.
9. Make sure you don’t have multiple Oracle homes or multiple Oracle clients installed. Check the «HKEY_LOCAL_MACHINESOFTWAREORACLEALL_HOMESHOME_COUNTER» key value.
10. Check if Oracle OLE DB provider is running InProcess. If ‘yes’ then try to run out-of- process and see if that resolves the issue.
Note: You can check and verify if MS OLE DB Provider for Oracle is running InProcess from the registry key at HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerProvidersMSDAORA
11. You can try collecting simultaneous Network trace from both SQL and Oracle servers and check if there are any communications between the two servers.
12. Try to connect to Oracle from the SQL server using the UDL. Use the same TNS name. If you get the same error that means the issue is not specific to SSMS or linked server.
Creating and Configuring Universal Data Link (.udl) Files
http://msdn.microsoft.com/en-us/library/e38h511e(VS.71).aspx
13. Try to specify all the information in the data source instead of using the TNS alias to connect to the Oracle database (this is a way to bypass tnsnames.ora file when connecting to Oracle).
Sample Data Source:
Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= server01.mydomain.com)(PORT=1521)))(CONNECT_DATA=(SID=OracleDB)(SERVER=DEDICATED)));
Author : Mohammad(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Azim(MSFT), SQL Developer Technical Lead , Microsoft
I want use PL/SQL Developer
first my oracle directory
C:oracleproduct10.2.0client_1BIN
I use windows10 and environment variable setting
ORACLE_HOME C:oracleproduct10.2.0client_1
Path C:oracleproduct10.2.0client_1BIN
but execute Initialization error.
Initialization error
Could not load "C:oracleproduct10.2.0client_1binoci.dll"
OracleHomeKey: SOFTWAREORACLEKEY_OraClient10g_home1
OracleHomeDir: C:oracleproduct10.2.0client_1
Found: oci.dll
Using: C:oracleproduct10.2.0client_1binoci.dll
LoadLibrary(C:oracleproduct10.2.0client_1binoci.dll) return 0
asked Mar 10, 2017 at 4:42
1
- download oracle instant client (64 bits for latest version of PL/SQL; 32 bits for old version)
- extract to a folder. for example:
c:oracleinstantclient - open PL/SQL developer—> Configure—> Preferences —> Oracle Connection
- under OCI library, keyin
OCI.DLLfile path in the text box:c:oracleinstantclientoci.dll - click OK and restart PL/SQL
Ahmed Ashour
5,05710 gold badges35 silver badges55 bronze badges
answered Dec 28, 2017 at 3:40
honglin zhanghonglin zhang
1,4671 gold badge10 silver badges8 bronze badges
1
I meet the same error on my new windows 10 computer. At last i found that i missed the Microsoft Visual Studio 2013 Redistributable. After i install it, the error gone.
answered Jul 26, 2018 at 3:17
林少峰林少峰
931 gold badge1 silver badge6 bronze badges
I was getting below error while connecting to database from PL/SQL developer (in windows10 64 bit).
Initialization error
Could not initialize
«C:app<username>product12.2.0dbhome_1binoci.dll»
Make sure you have the 32 bits Oracle Client installed.
OracleHomeKey:
OracleHomeDir: C:app<username>product12.2.0dbhome_1
Found: oci.dll
Using: C:app<username>product12.2.0dbhome_1binoci.dll
LoadLibrary(C:app<username>product12.2.0dbhome_1binoci.dll)
return 0
To resolve this issue, refer to the below documentation provided by Oracle to install the Oracle Instant Client. If your windows is 64 bit, then download the 32 bit Oracle Instant Client from below link —
Oracle guide
Link to download the Oracle Instant Client — (Download the BASIC and SDK and SQL*Plus Package)
Take all the above three packages in one directory and extract the same and add the path of extracted directory in system environment variable PATH and OCI_LIB32.
If OCI_LIB32 is not present then create the same.
Try to connect to database from PLSQL developer, if you getting error as ‘ORACLE initialization or shutdown in progress’ then go through the below link
Good Luck 
Makdous
1,4491 gold badge11 silver badges24 bronze badges
answered Jul 7, 2020 at 13:40
Check Environment Variables %PATH%
answered Dec 10, 2018 at 5:33
I had the similar problem and these steps did helped me
steps:
- Download oracle instant client (64 bits for latest version of PL/SQL; 32 bits for old version)
- Extract to a folder. for example: c:oracleinstantclient
- Open PL/SQL developer—> Configure—>Preferences—> Oracle Connection
- Under OCI library, keyin OCI.DLL file path in the text box: c:oracleinstantclientoci.dll
- Click OK and restart PL/SQL
done
Taslim Oseni
6,06710 gold badges44 silver badges68 bronze badges
answered Oct 3, 2019 at 20:09
Most of the cases this happens
- If you dont have any oracle client
In this case, you can download instant client and put it in C drive, then copy the path of the oci.dll file path and put it in the OCI library text field in the PL SQL Dev config option
- If you have multiple oracle home
If you have multiple homes reflecting, then check the correct oracle home from the Oracle Home drop down in PLSQL Dev config option.
Attached the image of the config screen for easy reference.
answered Sep 29, 2021 at 12:56
BishnuBishnu
991 silver badge7 bronze badges
Download oracle client after that use PL/SQL to connect
answered May 12, 2022 at 17:13
KR93KR93
1,06811 silver badges10 bronze badges
User-153615472 posted
<?xml:namespace prefix = o ns = «urn:schemas-microsoft-com:office:office» /><o:p></o:p>
<o:p> </o:p>
I created webapp on one server to query an oracle database housed on another server
It works from within visual studio,”view in browser” but when the page is server from IIS the following
is displayed<o:p></o:p>
<o:p> </o:p>
System.Data.OracleClient.OracleException:
ORA-12154: TNS:could not resolve the connect identifier specified<o:p></o:p>
<o:p> </o:p>
Any help is much appreciated; [:'(]
full details of the setup are below, is this a permissions issue.
I have read many many posts and believe that I have everything configured correctly ?
<o:p> </o:p>
<o:p> </o:p>
SERVERone: <o:p></o:p>
Windows 2003 server running IIS 6.0 with .NET framework 2.057 and oracle InstantClient.<o:p></o:p>
Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210<o:p></o:p>
I have a tnsname.ora and sqlnet.ora saved to the same directory (C:instantClientinstantclient10_1) <o:p></o:p>
I have a corresponding enviroment variable to that folder TNS_ADMIN C:instantClientinstantclient10_1<o:p></o:p>
“Authenticated Users” has FULL CONTROL
permissions on this folder and subfolders<o:p></o:p>
Permission for read execute are also set for IWAM_SERVERone , IUSR_SERVERone, SERVERtwo/ASPNET<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
SERVEtwo: <o:p></o:p>
Windows 2003 sever which is running an instance of Oracle 9i database.in <o:p></o:p>
ORA home folder
“Authenticated Users” has FULL CONTROL permissions and sub folders
<o:p></o:p>
Permission for read execute are also set for IWAM_SERVERone, IUSR_SDMS-SERVERone, SERVERtwo ASPNET<o:p></o:p>
<o:p> </o:p>
I can successfully connect from SERVERone to SERVERtwo using
TOAD 9.0.1 and I can query the database.<o:p></o:p>
<o:p> </o:p>
<o:p>More Details…..</o:p>
I want to create a web app on SERVERone to queryan oracle database on SERVERtow
I created an ASP.NET application using visual web developer.<o:p></o:p>
I addded a sqldatasource and configured the sqldatasource to use “oracle database” as the
data source, and the data provider “.NET Framework Data Provider for Oracle”<o:p></o:p>
I set the
Server Name to the entry from the Tnanames.ora file an dentered the username and password<o:p></o:p>
When I click TEST CONNECTION it works , I then added a select * from xyz query<o:p></o:p>
<o:p> </o:p>
WHEN I RUN THE PAGE FROM WITHIN VWD it works perfectly ( “view in browser”)<o:p></o:p>
<o:p> </o:p>
However when I add the site to IIS and launch the page I get the ORA-12154 error
<o:p> </o:p>
<o:p>
TNSNAME.ORA entry<o:p></o:p>
<o:p> </o:p>
HOMER= <o:p></o:p>
(DESCRIPTION = <o:p></o:p>
(ADDRESS_LIST = <o:p></o:p>
(ADDRESS = <o:p></o:p>
(PROTOCOL = TCP)<o:p></o:p>
(HOST = 182.198.100.14)<o:p></o:p>
(PORT = 1521)<o:p></o:p>
)<o:p></o:p>
)<o:p></o:p>
(CONNECT_DATA =<o:p></o:p>
(SID = ERR)<o:p></o:p>
(GLOBAL_NAME = ERR.world)<o:p></o:p>
)<o:p></o:p>
)<o:p></o:p>
<o:p></o:p>
sqlnet.ora ( this is the only line in this file , I also tried NTFS to no avail)<o:p></o:p>
<o:p> </o:p>
SQLNET.AUTHENTICATION_SERVICES= (NONE)<o:p></o:p>
</o:p>
<o:p> </o:p>
DATA SOURCE<o:p></o:p>
<asp:SqlDataSource
ID=»SqlDataSource1″
runat=»server»
ConnectionString=»<%$ ConnectionStrings:ConnectionString
%>«<o:p></o:p>
ProviderName=»<%$ ConnectionStrings:ConnectionString.ProviderName
%>«
SelectCommand=’SELECT * FROM XYZ’><o:p></o:p>
<SelectParameters><o:p></o:p>
<o:p> </o:p>
<o:p> WEB CONFIG Entry</o:p>
<o:p>
<
</o:p>
ORA-12154: TNS:could not resolve the connect identifier specified<o:p></o:p>
Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:
System.Data.OracleClient.OracleException: ORA-12154: TNS:could not resolve the connect identifier specifiedSource Error: <o:p></o:p>
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified <o:p></o:p>
using the exception stack trace below.
Stack Trace: <o:p></o:p>
<o:p> </o:p>
[OracleException (0x80131938): ORA-12154: TNS:could not resolve the connect identifier specified<o:p></o:p>
]<o:p></o:p>
System.Data.OracleClient.OracleException.Check(OciErrorHandle errorHandle, Int32 rc) +84<o:p></o:p>
System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName) +688<o:p></o:p>
System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions) +135<o:p></o:p>
System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +37<o:p></o:p>
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28<o:p></o:p>
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +429<o:p></o:p>
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +70<o:p></o:p>
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +512<o:p></o:p>
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85<o:p></o:p>
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +102<o:p></o:p>
System.Data.OracleClient.OracleConnection.Open() +34<o:p></o:p>
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121<o:p></o:p>
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137<o:p></o:p>
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83<o:p></o:p>
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770<o:p></o:p>
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17<o:p></o:p>
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149<o:p></o:p>
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70<o:p></o:p>
System.Web.UI.WebControls.GridView.DataBind() +4<o:p></o:p>
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82<o:p></o:p>
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69<o:p></o:p>
System.Web.UI.Control.EnsureChildControls() +87<o:p></o:p>
System.Web.UI.Control.PreRenderRecursiveInternal() +41<o:p></o:p>
System.Web.UI.Control.PreRenderRecursiveInternal() +161<o:p></o:p>
System.Web.UI.Control.PreRenderRecursiveInternal() +161<o:p></o:p>
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360<o:p></o:p>
<o:p> </o:p>
Version Information: Microsoft .NET Framework Version:2.0.50727.42;
ASP.NET Version:2.0.50727.210<o:p></o:p>
█ 26.05.2017 17:23
Доброе время суток! )
При добавлении базы в Администраторе сервера приложений выскакивает такая ошибка. В алертлогах все в порядке. Переменная path проверена тоже все ок. БД 10.2.0.4. СМ версия 1033.3 СП4.
Дополнительно вот еще: «Значение не может быть неопределенным.
Имя параметра: sPath»
█ 26.05.2017 19:11
Решилось удалением софта оракла полностью (прибег к помощи RegOrganizer), была лишняя запись ORACLE_DEFAULT
█ 26.05.2017 19:45
Я так подозреваю, что и до этого к чьей-то помощи из этого вот прибегали… Покромсали реестр и как результат…
На будущее рекомендую сравнивать ключ вроде HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_OraDb10g_home1 с другой машиной…
Часовой пояс GMT +3, время: 03:17.
Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.