Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DB LINK to sql-server, Login failed for user. What is going wrong?

dutchbirdyJun 22 2010 — edited Jul 5 2010
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)
)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 2 2010
Added on Jun 22 2010
13 comments
3,704 views