Solved

Bypassing Oracle passwords

Posted on 2000-05-10
7
339 Views
Last Modified: 2008-03-03
I have an Access database with link tables through ODBC to an Oracle database. Whenever a user runs a query that uses the link tables, they are prompted for the Oracle password.

A problem that I believe to be related (since neither was happening when I began working with this database) is that the error message, "Invalid procedure call or argument" pops up when I try to open the Linked Table Manager.

Any idea what the causes and/or solutions to these problems might be?

0
Comment
Question by:ckjim
  • 4
  • 2
7 Comments
 
LVL 10

Expert Comment

by:paasky
Comment Utility
Hello ckjim,

Seems that linked tables have lost their connect username/password information. If you can't use linked table manager wizard, you could try refreshing links with code. Here's an example (this is air-code and I can't test this with Oracle DB right now, but the important thing is the idea) function. Copy it into module or form and execute with correct connect string. It will loop all linked ODBC tables and refresh connection.

Public Function RefreshODBCLinks(ConnectString As String)
Dim tdf As TableDef
   
    On Error GoTo LinkErr
   
    For Each tdf In CurrentDb.TableDefs
        If tdf.Attributes = dbAttachedODBC Then
            tdf.Connect = ConnectString
            tdf.RefreshLink
        End If
    Next
   
    Set tdf = Nothing
    RefreshODBCLinks = True
   
   
LinkExit:
    Exit Function
   
LinkErr:
    MsgBox Err.Description
    Resume LinkExit

End Function

Usage:
Success = RefreshODBCLinks("ODBC;DATABASE=database;UID=user;PWD=password;DSN=datasourcename")

Example:
Success = RefreshODBCLinks("ODBC;DATABASE=TEST;UID=scott;PWD=tiger;DSN=TEST")

Hope this helps,
Paasky
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
I made some enchangements to previous code and tested if with ODBC linked Oracle tables. Here's the 2nd version of function which will recreate the link and saves username and password information to linked table.

Public Function RefreshODBCLinks(ConnectString As String)
Dim tdf As TableDef
Dim newtdf As TableDef
Dim tablename As String
     
    On Error Resume Next
     
    For Each tdf In CurrentDb.TableDefs
        If tdf.Attributes And dbAttachedODBC And Right(tdf.Name, 4) <> "_tmp" Then
            tablename = tdf.Name
            tdf.Name = tdf.Name & "_tmp"
            Set newtdf = CurrentDb.CreateTableDef(tablename, dbAttachSavePWD, tablename, ConnectString)
            CurrentDb.TableDefs.Append newtdf
            If Err.Number = 0 Then
                Debug.Print "Table " & tablename & " relinked."

                ' delete old link
                CurrentDb.TableDefs.Delete tablename & "_tmp"
            Else
           
                ' errors encountered, restore original link
                tdf.Name = tablename
                Err.Number = 0
            End If
        End If
    Next
     
    Set tdf = Nothing
     
End Function

Regards,
paasky
0
 

Expert Comment

by:Sladey
Comment Utility
I would presume you checked the save password box when you attached the tables? - If not re-attach and save the password

One other solution I used on Windows 95 was when the system DSN was activated on the users PC was to edit the registry with the Oracle Pasword, so the were not asked for it.

Start Regedit

HKEY_LOCAL_MACHINE
SOFTWARE
ODBC
ODBC.INI
Select the DSN xxxxxx
Select the PWD field and add the password?

Close Regedit

I had a similar problem with the Linked Table Manager on one database, but no others are affected.



0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:ckjim
Comment Utility
paasky: Thanks for all the code, but I don't know where to put it. I haven't been able to find any startup sequence, although I know it has to be somewhere. Can you tell me where to look for that?

Sladey: You were right about the check box; I figured it out about 2 minutes after writing the question. As for editing the registry, there are too many machines accessing the database to go about it like that. Unless I'm misunderstanding (that's likely), it seems like I'd have to go to each machine and change its registry.

Still no luck figuring out why I can't use my Linked Table Manager for this database either.

0
 
LVL 10

Accepted Solution

by:
paasky earned 75 total points
Comment Utility
ckjim, copy the above code into clipboard, then select Modules page from the database window and press New button. Paste the function into Module window and save it using name such as "General functions".

To run it immediatly, select from menu View | Debug Window (or press Ctrl+G) and enter to appearing box:

? RefreshODBCLinks("ODBC;DATABASE=TEST;UID=scott;PWD=tiger;DSN=TEST")

(remember to change the DSN and other connect information to match your enviroment)

You can also add a macro which executes this function. Set the macro action to RunCode and enter the Function name field

RefreshODBCLinks("ODBC;DATABASE=TEST;UID=scott;PWD=tiger;DSN=TEST")

HTH,
Paasky
0
 

Author Comment

by:ckjim
Comment Utility
Thanks for all the help.
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
I hope my suggestion was useful. Good luck with your project!

Best regards,
Paasky
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now