ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)

Question

Hello,
I am having issue with connecting oracle from Sql Server using Linked Server.
I have installed Oracle client and Sql Server on both the same server.
I have tested TNSPING ORCL coming fine

Blockquote
C:\Windows\system32>TNSPING orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-OCT-2020 13:00:03
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle_user\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=orclXDB)))
OK (0 msec)

Blockquote

My Listener status also looks fine for the service

Blockquote
C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-OCT-2020 13:00:24

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER

Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 01-OCT-2020 23:30:11
Uptime 0 days 13 hr. 30 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\oracle_user\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
Listener Log File C:\app\oracle_user nslsnr\ORATEST\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "Oracle8" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Blockquote

When I run sqlplus OraUser@ORCL, it's throwing an error:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When I try to create the linked server and test it, getting following error:
ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)

My Linked Server script is:

Blockquote

SE master;

EXEC sys.sp_addlinkedserver @Testta = N'ORCL'
,@srvproduct = N'Oracle'
,@provider = N'OraOLEDB.Oracle'
,@datasrc = N'orcl';

-- Configure the server for remote procedure calls
EXEC dbo.sp_serveroption @Testta = N'ORCL' -- sysname
,@optname = 'rpc out' -- varchar(35)
,@optvalue = N'true' -- nvarchar(128)

EXEC sys.sp_addlinkedsrvlogin @rmtsrvname = N'ORCL'
,@useself = 'false'
,@locallogin = NULL
,@rmtuser = N'OraUser'
,@rmtpassword = N'******';

Blockquote

I have tried few things from the Google but didn't help, trying to follow the steps as other have provided.

Thanks for your help!

Answer

Please also see the following Sqlnet, tnsnames and listener.ora files

Sqlnet.Ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TNSNAMES.ORA

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=orclXDB)
)
)

LISTENER.ORA

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=orclXDB)
)
)

Answer

Also please see the result for C:\Windows\system32>lsnrctl services

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-OCT-2020 15:34:51

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
CLRExtProc
(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\NTN_B18_4AA6BBE0.ORA))
"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
CLRExtProc
(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\NTN_B18_4AA6BBDE.ORA))
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:137 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ORATEST)(PORT=58463))
The command completed successfully

Answer

Try bypassing the TNSNAMES.ORA file by using EazyConnect naming. eg

EXEC sys.sp_addlinkedserver @server = N'ORCL'
,@srvproduct = N'Oracle'
,@provider = N'OraOLEDB.Oracle'
,@datasrc = N'//localhost/orcl';

Answer

Hi @pdsqsql ,

Do you still have the error?
If yes,please troubleshoot as this: how-to-resolve-ora-12514-tns-listener-does-not-currently-know-of-service-requested-in-connect-descriptor
Follow steps as this : oracle-linked-server

2:

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer

Still having issue

ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)

6 answers