Disabling the login prompt without using the TDatabase bypass

I am currently trying to connect to a database using an ODBC Alias to SQL Server. The problem I'm having is that when I use my TQuery object to get the information it always requests login details (nevermind whether I've specified them in the ODBC creation). I don't mind manually setting them in the code, but I can't find how to do that.
The most common solution I've found is to use the database component and go through that. However that comes with its own issues. Due to my dataset being so large and the database component converting the dataset to a Paradox table I keep getting a BDE error of 'Temporary Table Resource Limit'. I don't get this error if I ignore the database component (which is fine) however this leaves me with the login prompt issue. Has anyone found a way to bypass this for TQuerys without swapping to other connection paths such as ADO?

I'm a bit rusty with the BDE but I don't think there's an easy way to avoid the login prompt if what you're saying is that you're not using a TDatabase component in your project.

The reason is that when you attempt to open your TQuery without a TDatabase (or TSession) component in your project, the default Session object in your app will call the routine below from within your TQuery's OpenCursor:

{ from DBTables.Pas }
function TSession.DoOpenDatabase(const DatabaseName: string; AOwner: TComponent): TDatabase;
var
  TempDatabase: TDatabase;
begin
  Result := nil;
  LockSession;
  try
    TempDatabase := nil;
    try
      Result := DoFindDatabase(DatabaseName, AOwner);
      if Result = nil then
      begin
        TempDatabase := TDatabase.Create(Self);
        TempDatabase.DatabaseName := DatabaseName;
        TempDatabase.KeepConnection := FKeepConnections;
        TempDatabase.Temporary := True;
        Result := TempDatabase;
      end;
      Result.Open;
      Inc(Result.FRefCount);
    except
      TempDatabase.Free;
      raise;
    end;
  finally
    UnLockSession;
  end;
end;

As you can see, if the session can't find an existing TDatabase component with the right name, it creates a temporary one, and it's the call to Result.Open that pops up the login prompt, without, so far as I can see, giving you any opportunity to supply the password + user name before the pop-up (the Session's OnPassword doesn't seem to get called in the course of this).

Obviously you need to check using the debugger that that's what's happening in your app, a temporary TDatabase being created, I mean.

If what I've suggested in the Update below didn't work and I were desperate to avoid using a TDatabase component, I would look into the possibility of maybe deriving a TQuery descendant, and trying to override its OpenCursor to see if I could jam in the user name/password.

Anyway, seeing as you say you're not using an explicit TDatabase, if I understand you correctly, because of the "Temporary Table ..." issue, and seeing as the Session will create a temporary one anyway, I suppose it might be worth your while investigating why the temporary one doesn't provoke the "Temporary Table" error, whereas using a TDatabase component in your app evidently does. Idapi32.Cfg configuration issue, maybe? At the moment, I can't help you with that because I can't reproduce your "Temporary Table" error, despite using my TQuery to do a SELECT on a SqlServer table to return 250,000+ rows.

Oh, that's a point: Does your table contain any BLOBs? I seem to recall there's an Idapi config parameter that lets you reduce the temporary storage space the BDE uses for BLOBs (to zero, maybe, but it's been a long time since I used the BDE "for real").

Update: The thought just occurred to me that since your query seems to work with Session dynamically creating a TDatabase object, maybe it would also work with a TDatabase which you dynamically create yourself. I just tried the following, and it works for me:

procedure TForm1.DatabaseLogin(Database: TDatabase;
  LoginParams: TStrings);
begin
  LoginParams.Add('user name=sa');
  LoginParams.Add('password=1234');  
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  ADatabase : TDatabase;
begin
  ADatabase := TDatabase.Create(Self);
  ADatabase.AliasName := 'MAT41032';
  ADatabase.DatabaseName := 'MAT41032';
  ADatabase.SessionName := 'Default';
  ADatabase.OnLogin := DatabaseLogin;
  Query1.Open;
end;

+1 for an interesting question, btw.