5 Replies Latest reply: Feb 9, 2010 3:25 PM by 674481 RSS

    ODBC connection error: ORA-12154

    674481
      I have an Oracle Database and SQL Database on the same server and wants to use odbc to connect to the SQL 2005 database and query tables of my choice.

      I did configured the ODBC corrently and called the DSN TSH_ARCHIVE. I have tested it and it works great. But each time I run the following query I get error:

      SQL> select count(*) from BALANCES_HS@tse_archive;
      select count(*) from BALANCES_HS@tse_archive
      *
      ERROR at line 1:
      ORA-12154: TNS:could not resolve the connect identifier specified

      Any suggestion and help will be greatly appreciated.
        • 1. Re: ODBC connection error: ORA-12154
          674481
          I just realized one of my problems and I think it is about sql server authetication. I need to use a SQL Server username and password to create the database link in order to make it possible to connect to SQL Server database, but I am only connected via Windows authetication - no sql server username and password. My question now is - is it possible to create a database link to connect to sql server without a username and password or by using only Windows authetication?
          • 2. Re: ODBC connection error: ORA-12154
            gdarling - oracle
            Hi,

            You'd want to post this in one of the database forums, as this forum deals with questions regarding Oracle's ODBC driver, and your question is regarding Heterogeneous Services which is a database product.

            However, to throw in a random 2 cents that may or may not help... I'd think 12154 would indicate you dont have HSODBC configured properly on the Oracle database and you're not even making it to the SQLServer database to be able to run into an authentication problem. If you made it to the SQLServer database but then had a problem with uid/pwd, you'd get some kind of SQLServer error indicating such.

            With respect to your question regarding authentication, I'd ASSUME it would probably work but you'd have to change the Oracle database and or listener service to run as the OS account you're trying to authenticate to SQLServer as. Thats just a WAG though, I dont know much about configuring HSODBC or what limitations it may have.

            Hope it helps,
            Greg
            • 3. Re: ODBC connection error: ORA-12154
              618477
              Hi

              Looks like your HSODBC layer is not configured/initialized correctly, please refer to the following document for details on configuration:

              http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96544.pdf

              OpenLink Software have a document on HSODBC configuration for our suite of ODBC Driver you may also want to consider trying:

              http://wikis.openlinksw.com/dataspace/owiki/wiki/UdaWikiWeb/ConfigHSODBCr9

              The OpenLink ODBC Drivers can be downloaded from:

              http://download.openlinksw.com

              Best Regards
              Hugh Williams
              Professional Services
              OpenLink Software
              Web: http://www.openlinksw.com
              Support: http://support.openlinksw.com
              Forums: http://boards.openlinksw.com/support
              Twitter: http://twitter.com/OpenLink
              • 4. Re: ODBC connection error: ORA-12154
                752641
                Hi,

                The Connect for ODBC driver for SQL Server from Progress DataDirect not only supports Windows Authentication on UNIX and Linux platforms, but it also is the driver of choice for many customers who seek to use Oracle Heterogeneous Services (Generic Connectivity) and the Oracle Database Gateway for ODBC. Below are some helpful resources to help you out:

                Here is an overview of the driver and its features:

                [http://www.datadirect.com/products/odbc/odbc-sqlserver/index.ssp]

                Here are links to help you configure the driver for Oracle Heterogeneous Services or the Database Gateway for ODBC:

                [http://www.datadirect.com/developer/odbc/oracle_heterogeneous/index.ssp]
                [http://www.datadirect.com/developer/odbc/oracle-database-gateway/index.ssp]

                Here is a link to download a free 15-day evaluation of the driver so that you can try it for yourself:

                [http://www.datadirect.com/downloads/index.ssp]

                Good luck!
                Mike Frost
                Progress DataDirect
                • 5. Re: ODBC connection error: ORA-12154
                  674481
                  Your article has almost nothing to do with the problem I mentioned earlier in this post.