13 Replies Latest reply on Jul 5, 2010 8:37 PM by dutchbirdy

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

    dutchbirdy
      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)
      )
        • 1. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
          Fahd.Mirza
          check the case sensitivity of the password.

          regards
          1 person found this helpful
          • 2. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
            rodneyli
            Looks like you don't have the correct password based on the error: Login failed for user 'CaesarCOREReader'.
            • 3. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
              dutchbirdy
              I triple checked the password for the database link. It is correct.
              It is even in quotes. So its not that.
              • 4. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
                Fahd.Mirza
                Well while creating the database link you should also give machine name with the username separated by the slash.

                regards
                • 5. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
                  PrafullaNath
                  Creating a database link from Oracle DB to MS SQL is not similar as creating a DB from ORACLE to ORACLE database.

                  Here is the step...

                  CONNECTIVITY TO A SQL SERVER FROM
                  ORACLE DATABASE USING DATABASE LINK


                  Settings Required for the listener.ora file:

                  SID_LIST_LISTENER =
                  (SID_LIST =
                  (SID_DESC =
                  (SID_NAME = boostdm)
                  (ORACLE_HOME =/oracle/g01/software/rdbms/10.2.0.4)
                  (PROGRAM = hsodbc) ----- >#This will tell we are using a heterogeneous service to connect to the sql server #
                  (ENVS=LD_LIBRARY_PATH=/oracle/g01/software/rdbms/10.2.0.4/lib:/usr/lib:/usr/ccs/lib:/sasapp/SAS_8.2/SQL_ODBC/lib)
                  )

                  Note: We have to restart the listener after this settings.
                  ORACLE_HOME contains the home from which where we are trying to connect






                  Settings Required for the tnsnames.ora file:

                  boostdm =
                  (DESCRIPTION =
                  (ADDRESS = (PROTOCOL= TCP) (Host= atlsrch4.nextel.com) (Port= 1521))
                  (CONNECT_DATA =
                  (SID = boostdm) )
                  (hs=ok))

                  Note: the important entry is the (HS=OK) key word. This key word must
                  be added manually and opening the Net Configuration Assistants might
                  remove this entries from your tnsnames.ora file!The (HS=OK) parameter must be outside the SID section and specifies that this connector uses the Oracle Heterogeneous Service Option.

                  Hostname contains the hostname where odbc driver is installed rather the host name to which we want to connect.
                  init.ora of the gateway:


                  The SID is also relevant for the init.ora file of the gateway. The name of the
                  file is init<SID>.ora.

                  The file is located at $ORACLE_HOME/hs/admin.
                  It should contain the following entries:

                  # This is a sample agent init file that contains the HS parameters that are
                  # needed for an ODBC Agent.

                  #
                  # HS init parameters
                  #
                  HS_FDS_CONNECT_INFO = boostdm
                  HS_FDS_TRACE_LEVEL =debug
                  HS_FDS_TRACE_FILE_NAME = /oracle/g01/software/rdbms/10.2.0.4/hs/log/boostdm.trc
                  HS_FDS_SHAREABLE_NAME = /sasapp/SAS_8.2/SQL_ODBC/lib/libodbc.so

                  #
                  # ODBC specific environment variables
                  #
                  set ODBCINI=/sasapp/SAS_8.2/SQL_ODBC/
                  set ODBCINI=/sasapp/SAS_8.2/SQL_ODBC/odbc.ini
                  set LD_LIBRARY_PATH=/oracle/g01/software/rdbms/10.2.0.4/lib32:/usr/lib:/usr/ccs/lib:/sasapp/SAS_8.2/SQL_ODBC/lib

                  #
                  # Environment variables required for the non-Oracle system
                  #
                  #set <envvar>=<value>






                  Short explanation of the parameters:

                  HS_FDS_CONNECT_INFO points to the ODBC DSN configured

                  HS_FDS_SHAREABLE_NAME points to the ODBC Driver Manager library at
                  $ODBC_HOME/lib/<ODBC_Driver_MANAGER_LIB>

                  Configuring the odbc.ini file:

                  The odbc.ini file is similar to an address book for the odbc driver. It is located by default in the ODBC_HOME directory, but can be placed anywhere you like it.

                  A side note how odbc works: The odbc driver (nothing else than a library) gets a request to connect to a server described in the odbc.ini file. The alias for the description of the server is called:Data Source Name (=DSN).Then the driver reads the information from the odbc.ini file according to the specified DSN and connects to the server.

                  The prerequisite for the odbc driver to connect to the the server is the
                  configured odbc.ini.
                  It is divided into 3 different sections:
                  [ODBC Data Sources]
                  [<DSN>]
                  [ODBC]

                  [ODBC Data Source] is the section that contains all the available DSNs.
                  [<DSN>] contains the different names of the DSNs and specifies the connect
                  details.
                  [ODBC] is the general section for the odbc driver

                  A very simple file of the odbc.ini file looks like:
                  ========================================================
                  [ODBC Data Sources]
                  ndw=MERANT SQLServer 2000 driver
                  junkyard=Junkyard SQLServer 2000 driver
                  procare=Procare SQLServer 2000 driver
                  sas_grp=SAS_Group SQLServer 2000 driver(available for write access)

                  [boostdm]
                  Driver=/sasapp/SAS_8.2/SQL_ODBC/lib/S0msss15.so
                  Description=MS SQL Server Version 7
                  Database=boostdm
                  ServerIPAddress=10.178.170.23
                  ServerPortNumber=1433
                  LogonID=sdis_ro
                  Password=XXXXXX
                  QuotedId=Yes
                  AnsiNPW=Yes

                  [ODBC]
                  Trace=1
                  TraceFile=/users2/sashxp/odbctrace.out
                  TraceDll=/usr/local/dbi/odbc/merant360/lib/odbctrac.so
                  InstallDir=/sasapp/SAS_8.2/SQL_ODBC

                  Note: It contains the address (and port) of the server to contact, the driver that should be used while connecting to the remote server and the user id and password of the remote server.
                  The [ODBC] section contains general paremeters like tracing (Trace=1 enables tracing, Trace=0 disables it).

                  Before calling the program demoodbc, you need to set two environment variables:
                  a) LD_LIBRARY_PATH must contain the odbc library path
                  export LD_LIBRARY_PATH=$ODBC_HOME/lib:$LD_LIBRARY_PATH
                  b) ODBCINI
                  export ODBCINI=$ODBC_HOME/odbc.ini
                  The ODBCINI parameter will guarantee, that the newly configured
                  odbc.ini file from above is used.


                  Configuring the environment:.

                  Normally there is nothing to configure anymore. But to test the odbc
                  connectivity for the Oracle user the following should be performed:
                  Set the ODBCINI and ODBC_HOME environment variable and add the
                  $ODBC_HOME/lib directory to the $LD_LIBRARY_PATH.

                  Sample LD_LIBRARY_PATH

                  LD_LIBRARY_PATH=/oracle/g01/software/rdbms/10.2.0.4/lib32:/usr/lib:/usr/ccs/lib:/sasapp/SAS_8.2/SQL_ODBC/lib:




                  Configuring the Oracle database:

                  The only thing that must be done here is to create a database link:
                  connect with the username/password that has sufficient rights to create a
                  database link (i.e. system).
                  The syntax is:
                  create [public] database link <name>
                  connect to <UID> identified by <pwd> using '<tnsalias>';


                  The db link name is sqlserver. Username and password must be in double quotes,
                  because the username and password are case sensitive. 'tnsalias' points to
                  the alias in the tnsnames.ora file that calls the HS subsystem.




                  Errors during the connection:

                  1.Check the listener , tns , init<SID>.ora settings

                  2.check the ip ,port user/password correctly set in the odbc.ini file.

                  3.Check the env variable LD_LIBRARY_PATH, ODBCINI is correctly set or not and it should be reflecting in the listener file.

                  4.Check whether the ODBC_HOME is added in the LD_LIBRARY_PATH

                  5. We can check the log file for the problem/error during the connection .The location of the log file is mentione in the init<SID>.ora under ORACLE_HOME/hs/admin

                  HS_FDS_TRACE_FILE_NAME = /oracle/g01/software/rdbms/10.2.0.4/hs/log/Aspect.trc
                  • 6. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
                    dutchbirdy
                    The database link I created on the other machine didn't need the machine name. That one worked correct. Why would it be required on my machine?
                    • 7. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
                      dutchbirdy
                      I used this url to make the SYSTEM DSN ODBC, it worked on the other machine:
                      http://www.databasejournal.com/features/oracle/article.php/3442661/Making-a-Connection-from-Oracle-to-SQL-Server.htm

                      Using the above information I adjusted the files on the other machine I created a database link from the 11g database to the SQL-database. This works.
                      I recreated all the steps on my own laptop with an XE database towards the same SQL-database and it fails.


                      I laccidently left out this document I created too in oraclehome/hs/admn:

                      --initSQLDB1CORE.ora
                      HS_FDS_CONNECT_INFO = SQLDB1CORE
                      HS_FDS_TRACE_LEVEL = 0
                      • 8. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
                        PrafullaNath
                        What ever the changes and configuration i mentioned , we have to do it in the server where the oracle database resides where we are creating the dblink and not in the server where MS SQL resides .Nothing to do any changes in client or MS SQL machine.
                        • 9. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
                          dutchbirdy
                          That is correct.. all the files adjustments ( tnsnames.ora, listener.ora, initSQLDB1CORE.ora ) are on my laptop and the adding of the SYSTEM DSN ( administrative tools > Data Sources ( ODBC ), tab SYstem DSN ) also.
                          No change is done to the MS SQL server.
                          • 10. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
                            PrafullaNath
                            ping and telnet the ip and port and check the connectivity

                            check the log files if you have mentioned inside initSQLDB1CORE.ora "HS_FDS_TRACE_FILE_NAME ="

                            Edited by: PrafullaNath on Jun 22, 2010 8:04 PM
                            • 11. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
                              dutchbirdy
                              Pinging from my machine the MS SQL database works.
                              I can use Microsoft SQL Server Management Studio Express to connect to the database with the CaesarCOREReader user and its password. I created a ODBC in System DNS and that too functions.
                              • 12. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
                                dutchbirdy
                                My colleage came with this:
                                http://download-uk.oracle.com/docs/cd/B25329_01/doc/license.102/b25456/toc.htm

                                Chapter 2.6: Transparent Gateways No

                                According to him this is a important part for a db-link to mssql. But I am not a DBA. Can anyone clarify please?
                                • 13. Re: DB LINK to sql-server, Login failed for user. What is going wrong?
                                  dutchbirdy
                                  Oracle XE doesn't have transparent gateway. so that was the problem