I have a 11g database on a machine who is linked to the MS SQL database using a DB-link. This works fine.
Now I wanted to create another db-link from my XE-database on my own machine to the same MS SQL database. But every time I get the login failed.
I made the System DSN in the ODBC as I did on the other machine and it showed that I was able to get connected. I can connect to the MS SQL database with the user name and password provided. But whenever I try to use the database link to connect I get the following error:
*********************************************************************
Link : "CORE_LINK"
Error : ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'CaesarCOREReader'. (SQL State: 28000; SQL Code: 18456)
ORA-02063: preceding 2 lines from CORE_LINK
*********************************************************************
According to the MS SQL its a state 8 error which means its the wrong password, but I am 100% sure I create the databaselink with the correct password ( and yes the username and password are with quotes ).
here is the info how I created this database link:
--databse link
CREATE DATABASE LINK CORE_LINK
CONNECT TO "CaesarCOREReader"
IDENTIFIED BY "<password>" USING 'SQLDB1CORE';
--listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = L3NHVE4.caesar.caesargroep.nl)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
LISTENERSQLDB1CORE =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT = 1522))
(ADDRESS=(PROTOCOL=IPC)(KEY=PNPKEY)))
SID_LIST_LISTENERSQLDB1CORE=
(SID_LIST=
(SID_DESC=
(SID_NAME=SQLDB1CORE)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM=HSODBC)
)
)
--tnsnames.ora
XE=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=L3NHVE4.caesar.caesargroep.nl)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=XE)
)
)
EXTPROC_CONNECTION_DATA=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=IPC)
(KEY=EXTPROC_FOR_XE)
)
(CONNECT_DATA=
(PRESENTATION=RO)
(SID=PLSExtProc)
)
)
ORACLR_CONNECTION_DATA=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=IPC)
(KEY=EXTPROC_FOR_XE)
)
(CONNECT_DATA=
(PRESENTATION=RO)
(SID=CLRExtProc)
)
)
SQLDB1CORE =
(DESCRIPTION=
(ADDRESS=(PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(CONNECT_DATA=
(SID=SQLDB1CORE)
)
(HS=OK)
)