• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

First connection with Database

Hi,
I`m writting an app which is connecting with MSSQL database - I want to keep all my configuration values in database. The problem occure when I want to login first time - becouse I don`t want to put a server address, login and pass as static I need to configure it during the first start.
What is the best way to solve this problem - I don`t want to use ini files.
I want to be able to install my app and to change the server address login and pass under my application control. It will allow me to compile my app only one time and to install it for every customer which will buy it.
I`m using UNIDAC components.
BR
Vaalar
0
Vaalar
Asked:
Vaalar
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Geert GruwezOracle dbaCommented:
Consider that you may not be the only provider of software which works on a mssql database.
Some customers can have 1 database server.
When a vendor comes in with software he/she is provided with a specific shema to put his tables/procedures in.

This is for license issues.

A other thing, even if you say that it works on the express edition of mssql,
some customers say no, and ask you to put it on their main database server.

This approach is easier for them for backup solutions.

0
 
VaalarAuthor Commented:
Hi Geert,
I don`t understand where is the problem with my solution, the thing is that i want to avoid compilation with static connection string - as i said i use UNIDAC components so i want to set up the server, username, pass,and port properities before the connection will be established. I don`t know how to change the connection properities on runntime and how to store the values.
Should I compile the software everytime for specific machine - i don`t think so

0
 
8080_DiverCommented:
Vaalar,
If I understand your question correctly, you have the following situation and issues that you want to resolve:
  • Situation:
    You want to have your database set up in such a manner that any connection strings, user names, passwords, etc., will be contained in the database;
  • Issues:
    How do you make the initial connection to the database?
I have dealt with a similar situation just recently and I'll provide the approach I took.
The first thing I had to do was to provide a means for the user (or some "admin") to create the initial connection.  I did this by means of a modified version of an application I use for testing connection strings.  Essentially, it provides a user interface to the same connection string editor tool that we use during the design time access to the TADOConnection component (the one that lets you wlak through the process of selecting the server and the database, etc.).
Once the user/admin has created the basic connection string, my app then parses the connection string to extract the information that identifies the database and the initial catalogue (although, I force that last if it is not what I want ;-) and then makes sure that the connection string is set to use SQL Server Username/password access.  I then provide a "gatekeeper" username and password that has only one single access, which is to a stored procedure that is used for validation user names and passwords (i.e. logins).  That connection string is the initial one that the app uses and the stored procedure returns the username and password that is used after that.
Now, the trick is, how do you store the basic connection string so that your user doesn't have to figure it out every time.  There are, as far as I can tell, two options available to you:
  1. INI files (and there is no shame in using an INI file for a minimal amount of data ;-);
  2. Registry.
The down side of INI files is that they are readily accessible.  However, unless one knows a valid user name and password, the connection just tells you where the data is.  (This is the option I chose.)
The downside to using Registry is that a) it clutters the Registry, b) you may not have access to update the Registry, and c) you need to clean it up on uninstall and that may not work all that well.  As far as security, it is only slightly better than an INI file.
By storing only the minimal connection string information (i.e. without the username and password) in the INI file, you do not have a serious security issue . . . after all, if someone has access to the box, they can probably find the database anyway. ;-)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Geert GruwezOracle dbaCommented:
nope, you should store it in a file, the registry, or a other such dynamic location
i allways use the registry

for my oracle programs i have a registry setting starting with cdb_
it's just simply a entry like this: key = db_wms
Driver=ORACLE,UserId=WMSREADER,DataSource=WMSPRD,ConnectionName=WMS,Type=ODAC,Enabled=TRUE

i read all the name entries from the registry (specific key for the company, for example: HKCU\Software\CompanyName\Program\Parameters)
when i encounter a db_ i create a connection using a TStringList to extract the parameters

this is a copy of my code, it may give you a general idea
of how i read from the registry within a component i made
procedure TDacConnection.DoConnect(aConnectionName, aConnectionString: string; aConnectPrompt: boolean);
var
  I: Integer;
  Reg: TRegistry;
  List: TStrings;
  AppName, ConnectionString, LogonUser: string;
  AutoConnectDB, ConnectionFound: boolean;
begin
  FConnectionName := aConnectionName;
  AutoConnectDB := False;

  AppName := ChangeFileExt(ExtractFileName(ParamStr(0)), '');

  Reg := TRegistry.Create;
  try
    Reg.RootKey := HKEY_CURRENT_USER;
    if Reg.OpenKey(Format('%s\%s\parameters',[CompanyKey, AppName]), True) then
    try
      if not Reg.ValueExists('auto_connect_db') then
        Reg.WriteBool('auto_connect_db', True);
      if Reg.ValueExists('auto_connect_db') and Reg.ReadBool('auto_connect_db') then
        AutoConnectDB := True;
    finally
      Reg.CloseKey;
    end;
  finally
    Reg.Free;
  end;

  if aConnectionString <> '' then
  begin
    List := TStringList.Create;
    try
      List.CommaText := aConnectionString;
      List.Values['ConnectionName'] := FConnectionName;
      aConnectionString := List.CommaText;
    finally
      FreeAndNil(List);
    end;
  end;
  ConnectionString := aConnectionString;
  if aConnectionString = '' then
  begin
    Reg := TRegistry.Create;
    try
      Reg.RootKey := HKEY_CURRENT_USER;
      if Reg.OpenKey(Format('%s\%s\parameters',[CompanyKey, AppName]), False) then
      try
        List := TStringList.Create;
        try
          Reg.GetValueNames(List);
          ConnectionFound := False;
          for I := 0 to List.Count -1 do
            if SameText(List[I], 'CDB_' + FConnectionName) and
              (Reg.GetDataType(List[I]) = rdString) then
            begin
              ConnectionString := '"' + StringReplace(Reg.ReadString(List[I]), ',', '","', [rfReplaceAll]) + '"';
              ConnectionFound := True;
              Break;
            end;
          if not ConnectionFound and SameText(FConnectionName, DB_Company) then
          begin
            if SameText(FConnectionName, DB_Company) then
              ConnectionString := Format('Driver=ORACLE,UserId=%s,DataSource=%s,ConnectionName=%s,Type=ODAC,Enabled=TRUE',
                ['SHOP', 'OracleDB', DB_Company]);
            if ConnectionString <> '' then
              Reg.WriteString('cdb_' + LowerCase(FConnectionName), ConnectionString);
            ConnectionString := '"' + StringReplace(ConnectionString, ',', '","', [rfReplaceAll]) + '"';
          end;
        finally
          List.Free;
        end;
      finally
        Reg.CloseKey;
      end;
    finally
      Reg.Free;
    end;
  end;
  FConnectionString := ConnectionString;

  if ConnectionString <> '' then
  begin
    List := TStringList.Create;
    try
      List.CommaText := ConnectionString;
      FIsHidden := List.Values['NOREGISTRY'] = '1';
      FCanTimeOut := List.Values['CANTIMEOUT'] = '1';
      FAskPassword := List.Values['ASKPASSWORD'] = '1';
      if FAskPassword then
        FCanTimeOut := True;
      fLogonType := ltNone;
      if (List.Values['USEWINLOGON'] = '1') or SameText(List.Values['LOGONTYPE'], 'WINDOWS') then
        fLogonType := ltWindows;
      if SameText(List.Values['LOGONTYPE'], 'CUSTOM') then
        fLogonType := ltCustom;
      if fLogonType in [ltWindows, ltCustom] then
      begin
        FCanTimeOut := True;
        FAskPassword := True;
      end;
      if FCanTimeOut then FTimedOut := True;
      case fLogonType of
        ltWindows: LogonUser := MyUserName;
        ltCustom: LogonUser := Dictionary.Read('logon_user', '');
      else
        LogonUser := List.Values['UserId'];
      end;
      if List.Values['TYPE'] = 'BDE' then
        List.Values['TYPE'] := 'ODAC';
      if List.Values['TYPE'] = 'ODAC' then
        fDacType := dacOracle;
      // Remove SDAC
      //else if List.Values['TYPE'] = 'SDAC' then
      //  fDacType := dacMSSQL;
      if (List.Values['NOREGISTRY'] = '') and (
        not SameText(List.Values['NOREGISTRY'], '1') or SameText(List.Values['NOREGISTRY'], 'TRUE')) then
        Dictionary.Write('cdb_' + ConnectionName, List.CommaText);
      case fDacType of
        dacOracle:
        begin
          if (FConnection <> nil) and not (FConnection is TOraSession) then
            FreeAndNil(FConnection);
          if FConnection = nil then
            FConnection := TOraSession.Create(nil)
          else if FConnection.Connected then
            FConnection.Disconnect;
          FConnection.Username := LogonUser;
          if List.IndexOfName('Password') = -1 then
            FPassword := GetPassword(FConnection.UserName + '@' + List.Values['DataSource'])
          else
            FPassword := List.Values['Password'];
          FConnection.Password := FPassword;
          FConnection.Server := List.Values['DataSource'];
          FConnection.OnError := ConnectionErrorOra;
          TOraSession(FConnection).ThreadSafety := True;
          TOraSession(FConnection).AutoCommit := True;
          FConnection.LoginPrompt := aConnectPrompt;
          FEnabled := SameText(List.Values['Enabled'], 'TRUE');
        end;
        // Removed SDAC
        {
        dacMSSQL:
        begin
          OLEDBAccess.ParamsInfoOldBehavior := True;
          if (FConnection <> nil) and not (FConnection is TMSConnection) then
            FreeAndNil(FConnection);
          if FConnection = nil then
            FConnection := TMSConnection.Create(nil);
          FConnection.Username := LogonUser;
          if List.IndexOfName('Password') = -1 then
            FPassword := GetPassword(FConnection.UserName + '@' + List.Values['DataSource'])
          else
            FPassword := List.Values['Password'];
          FConnection.Password := FPassword;
          FConnection.Server := List.Values['DataSource'];
          FConnection.OnError := ConnectionErrorMS;
          TMSConnection(FConnection).Authentication := auServer;
          TMSConnection(FConnection).Database := List.Values['DatabaseName'];
          FConnection.LoginPrompt := aConnectPrompt;
          FEnabled := SameText(List.Values['Enabled'], 'TRUE');
        end;
        }
      else
        if FConnection <> nil then
          FreeAndNil(FConnection);
      end;
    finally
      List.Free;
    end;
  end;
  ConnectStep := csInitConnect;
  if AutoConnectDB then
    MakeConnection;
  if (FConnection <> nil) and FConnection.Connected then
    ConnectStep := csConnected;
end;

Open in new window

0
 
Mahdi78Commented:
Try this

procedure TMainForm_F.Button1Click(Sender: TObject);
begin
UniConnection1.Server := 'yourserver';
UniConnection1.Port := 0; // your MSSQl port
UniConnection1.Username := 'username';
UniConnection1.Password := 'passwoed';
UniConnection1.ProviderName := 'SQL Server';
UniConnection1.Database := 'yourdatabase';
UniConnection1.connect;

end;
0
 
Mahdi78Commented:
do not forget to SQLServerUniProvider to uses clause
0
 
8080_DiverCommented:
@Mahdi78,
Doesn't having the username and password embedded in the code sort of compromise the security of the access?  I have at least 3 different tools that will let me view the hex and text versions of files (including executables) side by side.  I also have a couple of tools that will scan files (executables included) for text strings, either specific text strings or any text string.  Between those tools, I can view the text embedded in executables and it is not that hard to guess which strings are usernames and passwords.
@Vaalar,
If you are going to do something like what Mahdi78 suggested, at least add some sort of encryption for the username and password.  If nothing else, do a series of exclusive ORs either with a passphrase/value, e.g. some GUID that is stored somewhere or a very large numeric value that you convert to a string (either by IntToStr or a conversion to a Hex String) and use to XOR with the Username/Password, or with itself, e.g. XOR 'X' with the first character, then XOR the result with the next character, and so on.
0
 
VaalarAuthor Commented:
Hi Guys,
Thx for your suggestions - I`ll probably combine the solution of Geert and 8080_Diver. I have no idea that this problem is so complicated and it takes me so much time, becouse now I`m not able to spare more time on this i have made a compromise - I have saved uniconnectio.server and port properities in ini file and i have put the login and password during design - i think it`s safer than the solution of Mahdi - it`s not exactly what i was looking for but it very close.
Thank you Guys for your time and solutions.
0
 
Mahdi78Commented:
@8080_Diver

Thank for your reply, i just write my code to display necessary data to connect to database with UniDac component
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now