Solved

Bypassing Oracle passwords

Posted on 2000-05-10
7
342 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
ID: 2798033
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
ID: 2799054
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
ID: 2800104
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:ckjim
ID: 2801467
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
ID: 2801504
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
ID: 2801922
Thanks for all the help.
0
 
LVL 10

Expert Comment

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

Best regards,
Paasky
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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 how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

776 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