ORA-28500: connection from ORACLE to a non-Oracle system tips

For starts, see these complete directions for establishing a connection from Oracle to SQL Server using heterogeneous services.  A common error when connecting to SQL Server from Oracle through a  database link is the ORA-28500, suggesting that the Oracle database login is incorrect.

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Transparent gateway for ODBC]DRV_InitTdp: [DATADIRECT][ODBC SQL Server Driver][SQL Server] Login failed (SQL State: 28000; SQL Code: 4002)
ORA-02063: preceding 3 lines from HSTEST

Cause: The Oracle database link created for the foreign datasource has either no credentials or incorrect credentials.

Action: Recreate the Oracle database link with the proper username and password.

 When  resolving the ORA-28500 error it's important to note that the username and password must be in double quotes.

create database link ODBC connect to "sa" identified by "pencil" using 'hsodbc'.

create [public] database link link_name connect to "user_name" identified by "password" using 'cas30c';

If you encase the login and password in double quotes and the tnsnames reference in single quotes, then the connection between Oracle and MSSQL works perfectly.

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.