TADOConnection in Delphi: the Correct Way to Use LoginPrompt := True

Code example

For a long time I did not really understand what LoginPrompt := True actually does. The correct way to use it is this:

procedure TfrmMain.FormShow(Sender: TObject);
var
  i: integer;
begin
  con1.Provider := 'SQLOLEDB.1';
  con1.Properties['Application Name'].Value := Application.Title;
  with TIniFile.Create(ExtractFileDir(ParamStr(0)) + '\setup.ini') do
  begin
    con1.Properties['Initial Catalog'].Value := ReadString('database', 'Initial Catalog', '');
    con1.Properties['Data Source'].Value := ReadString('database', 'Data Source', '');
    if ReadBool('database', 'Integrated Security', false ) then
    begin
      con1.Properties['Integrated Security'].Value := 'SSPI';
      con1.Properties['Persist Security Info'].Value := 'False';
      con1.LoginPrompt := False;
    end
    else
    begin
      con1.Properties['Persist Security Info'].Value := 'True';
      con1.LoginPrompt := true;
    end;
  end;
end;

procedure TfrmMain.con1Login(Sender: TObject; Username, Password: string);
begin
  con1.Properties['User ID'].Value := Username;
  con1.Properties['Password'].Value := Password;
end;

  

How does it work?

1. In the FormShow event the provider is set, and the basic connection properties (Data Source and Initial Catalog) are read from the setup.ini file next to the program.
2. If Windows authentication (Integrated Security) is enabled in the file, the connection uses SSPI and LoginPrompt is turned off, because no user name or password is needed.
3. Otherwise SQL Server authentication is used and LoginPrompt is turned on. When the program opens the connection (Open or Connected := True), the user gets a dialog for entering a user name and password.
4. After that the OnLogin event is called (con1Login in the example), which writes the received values into the User ID and Password properties.

The values should be set through the Properties collection, not by changing the connection text (ConnectionString), for example with StringReplace. Changing the connection text itself does not work in this case.

INI file example

The code reads the [database] section of the setup.ini file:

[database]
Data Source=SERVER\SQLEXPRESS
Initial Catalog=MyDatabase
Integrated Security=0

ReadBool treats the value 1 as True and 0 as False. For Windows authentication, set Integrated Security=1.

Things to watch out for

• The standard login dialog comes from the DBLogDlg unit (Vcl.DBLogDlg in newer Delphi versions), so it has to be included in the uses list.
• The SQLOLEDB provider is deprecated. For new programs Microsoft recommends the OLE DB Driver for SQL Server (provider MSOLEDBSQL); in this code only the first line changes.
• Persist Security Info = True keeps the password in the connection text after connecting. This only makes sense if the program later reuses the same connection text; otherwise False is safer.

Setting up the database connection is part of almost every desktop program that works with data. What else goes into building such programs is described in the article on Windows programming.