Can't connect to SQL Sever 2005 after upgrading from SQL Server 2000

Question

I recently upgraded from SQL Server 2000 to SQL Server 2005.  I have Oracle databases I need to share data with.  I was using the Heterogeneous Services (hsodbc) from Oracle and connected to SS 2000 fine.  Since I've upgraded, I can't use hsodbc to connect to my SS 2005 database.  I get the following error:

ORACLE GENERIC GATEWAY Log File Started at 2006-09-06T09:40:07

hoainit (3): ; hoainit Exited with retcode = 0.

hoalgon (7): ; hoalgon Entered. name = edw.

DB_ODBC_INTERFACE (2085): ; [Microsoft][SQL Native Client][SQL Server]Login

failed for user 'edw'. (SQL State: 28000; SQL Code: 18456)

DRV_InitTdp: DB_ODBC_INTERFACE (2085): ; [Microsoft][SQL Native Client][SQL

Server]Login failed for user 'edw'. (SQL State: 28000; SQL Code: 18456)

nvRETURN (F:\Work\Connect_4_6_3\src\drv\DRV_BIND.C 356): -2220

nvRETURN (F:\Work\Connect_4_6_3\src\nav\NAV_BIND.C 293): -2220

hoalgon (7): ; hoalgon Exited with retcode = 28500.

 

Answer 1

Try this to possibly eliminate problems:

1. Create a new textfile on your desktop.
2. Rename the document and change the extension from .txt to .udl and accept the warning message.
   
3. Double click on the new .udl document to open it up.
   
4. Using the same connection/data source information try to do a test connect.
5. If it fails try a different method of connecting to see if you can still access it...
   

Success or failure may give you a clue to the origination of the problem.

Answer 2

I did the "test" and it works fine.  I can connect just fine using the System DSN (via "test ODBC Connection" or selecting data through MS Excel).  The only time it fails is if I try to connect using the hsodbc from Oracle.  It uses an Oracle database link to do the connection.  Oracle "insists" the probelm is with the Microsoft ODBC, but since I can connect using other applications, I believe the problem exists in the way the hsodbc program sends the connect string.

Answer 3

Hi,

I have the same problem. We are using Oracle 8 and connect to SQL 2005. Connecting to SQL 2000 works fine but to SQL 2005 gives a failed login.

Have you solved this yet? Can you give me the solution?

Thanks,

Alex

Answer 4

Hi Alex,

This thread has already has an answer marked as the solution. If the answer does not pertain to you, post a new forum question with your specifics and do not tag onto existing items.

Thanks,

Answer 5

In SQL server 2005 User ID and Password are case sensitive while in 2000 they were not. While creating the database link from Oracle make sure to put the ID and password in correct case and put them under double quotation like

 

CREATE DATABASE LINK ABCD_DBLINK
     CONNECT TO "user1" IDENTIFIED BY "pwd1"
        USING 'MyDSN'