Forum Stats

  • 3,838,625 Users
  • 2,262,385 Discussions
  • 7,900,710 Comments

Discussions

Categories

Oracle to sql server configuration issue - SQLNET.AUTHENTICATION_SERVICES = (NTS) or (NONE)

M.bro
M.bro Member Posts: 128 Blue Ribbon
edited Jun 7, 2022 11:13AM in Heterogeneous Connectivity

Hi All,

Oracle database version : 12.2.0.1.0

I was connecting the Oracle database to SQL Server using ODBC Connective and HS services and created the datalink also.

I did with the configuration but I am facing one issue.

 SQLNET.AUTHENTICATION_SERVICES = (NTS) or SQLNET.AUTHENTICATION_SERVICES = (NONE)

if I configurated NTS and restarted the database it's working fine there is no issue but I can't able connect to the SQL server.

ORA - 28454:error diagnosed by Net8 when connecting to an agent unable to retrieve the text of Network/NCR message 65535

ORA - 02063:Preceding 2 Lines from DBLINKNAME

if I configurated NONE and restarted the database but the error occurred database did not start

How it's working now :(Work Around)

First I configured NTS IN SQLNET file and then restarted the oracle database, Oracle DB connected successfully but I changed the SQLNET file NTS TO NONE then without restarting the DB services try to access the oracle database and SQL server both working fine.

After some time I restarted the oracle database services and the listener not working.

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Hpaiss
    Hpaiss Member Posts: 45 Bronze Badge

    Hi,

    SQLNET.AUTHENTICATION_SERVICES = (NTS) is used for database authentication in general. I don't think it should relate to HSODBC.

    what is your ordbc and hsodbc.ini content?

    what username is the owner of the database and listenr services ?


    Regards,

    Hadar

    · Share on TwitterShare on Facebook
  • M.bro
    M.bro Member Posts: 128 Blue Ribbon

    @Hpaiss

    Thanks for your reply.

    As I told you earlier I need to connect to the SQL Server I changed the SQLNET = (NONE) but whenever I restart the oracle DB listener services are not started.

    --------------------------------------------------------------------

    what are your odbc and hsodbc.ini content?

    HS_FDS_CONNECT_INFO = SQLSERVER

    #HS_FDS_TRACE_LEVEL = off

    #HS_FDS_SUPPORT_STATISTICS = FALSE

    File name : initapexpdb (HS--->Admin---->initapexpdb.ora)

    ODCB Name = SQLSERVER (configured in ODBC)

    ---------------------------------------------------------------------------

    ------------------------------------------------------------------------

    what username is the owner of the database and listener services?

    Oracle DB Username = apexpdb

    TNS Name =

    SQL_Connection_new =

    (description=(address_list=(address=(protocol = TCP)

    (HOST = IPADDRESS/HOSTNAME)(PORT=1521))

    (CONNECT_DATA = (SID=apexpdb))

    (HS=OK)

    )

    --------------------------------------------------------------------------------

    I hope I answered your question. if anything I missed let me know.


    thanks for your support.

    · Share on TwitterShare on Facebook
  • Hpaiss
    Hpaiss Member Posts: 45 Bronze Badge

    Hi,

    1.5 more questions :)

    0.5 In 'what username is the owner of the database and listener services' I meant - in the 'Services' - what is the login user

    1. Please check the odbc configuration for two things: 1. Make sure it is a SYSTEM DSN; 2. Make sure you selected username and password and NOT windows authentication.

    Regards,

    Hadar

    · Share on TwitterShare on Facebook
  • M.bro
    M.bro Member Posts: 128 Blue Ribbon
    edited Jun 10, 2022 5:11AM

    @Hpaiss

    Thanks for your time.

    0.5 In 'what username is the owner of the database and listener services' I meant - in the 'Services' - what is the login user

    My schema login is AAA_SQL and my database name is APEXDB and I have other database APEXPDB (Pluggable database)

    Example: My SQL server username is: att and my Password is att_123

    Services details in the listener: SQL_Connection_NEW (I mentioned in the previous thread full connection string).


    1. Please check the ODBC configuration for two things: 1. Make sure it is a SYSTEM DSN; 2. Make sure you selected username and password and NOT windows authentication. - I checked the system DSN = SQLSERVER, I configured the same thing in HS File also. I mentioned the previous thread. SQL Server, No it's not windows authentication. I am connecting from another machine Like example : my oracle system: 192.1.10.1 and my SQL server 192.1.11.2

    Thanks in Advance.

    · Share on TwitterShare on Facebook