Can I use TIBServerProperties to test connection to server?

[RedOctober]

Platform:  Laz 1.8.1  Fpc 3.0.5, IBX 2.1.0  on Windows Server Essentials 2016

I am building a small database management utility, and I need to test a combination of Host/Port settings to ensure they will work (I want to do this before allowing database registrations and connections to that server in my utility).

I am trying to use the TIBServerProperties component for this purpose.  Even though the TIBServerProperties component has a Params property, where I programmatically put the USER_NAME=SYSDBA, and PASSWORD=masterkey parameters (with Login Prompt := False).

When I set .Active := True, the TIBServerProperties component will connect (or, at least will not throw an error).  I thought I was making progress, however, the .Active := True will succeed even with the wrong password.  This is not quite what I want.

If I omit the parameters completely, OR if I go Login Prompt := True and enter the wrong password, I get the following raw error message:


[Window Title]
Connection Problem

[Content]
Unable to connect.  Raw error message from DB: Cannot attach to services manager
service 3050:service_mgr is not defined

[OK]

Should I be using a different component just to test basic host/port connectivity?  If indeed TIBServerProperties can be used for this purpose, how do I get it to fail when the user enters the wrong SYSDBA password?

Thanks in advance for any help you can provide.

[dsiders]

I am building a small database management utility, and I need to test a combination of Host/Port settings to ensure they will work (I want to do this before allowing database registrations and connections to that server in my utility).

I am trying to use the TIBServerProperties component for this purpose.  Even though the TIBServerProperties component has a Params property, where I programmatically put the USER_NAME=SYSDBA, and PASSWORD=masterkey parameters (with Login Prompt := False).

When I set .Active := True, the TIBServerProperties component will connect (or, at least will not throw an error).  I thought I was making progress, however, the .Active := True will succeed even with the wrong password.  This is not quite what I want.

Are you running Firebird Embedded Server? It ignores security for server connections.

If I omit the parameters completely, OR if I go Login Prompt := True and enter the wrong password, I get the following raw error message:

[Window Title]
Connection Problem

[Content]
Unable to connect.  Raw error message from DB: Cannot attach to services manager
service 3050:service_mgr is not defined

[OK]

Did you modify your firebird.conf file to use a different value for the RemoteServicePort for the server instance?

Also assuming you have to set the ServerName and  Protocol properties in TIBServerProperties to appropriate values.

Should I be using a different component just to test basic host/port connectivity?  If indeed TIBServerProperties can be used for this purpose, how do I get it to fail when the user enters the wrong SYSDBA password?

If this is a DBA-level utility, I suppose it could be used for this purpose. If it's an end-user tool, needing to know the dba login is not a good idea. Nor is embedding login information in an executable.

If the service fails when activated, it should raise an exception. The services example program demonstrates its usage. Basically:

Code: [Select]

  with IBServerProperties1 do
  begin
    repeat
      try
        Active := true;
      except
       on E:EIBClientError do
        begin
          Close;
          Exit
        end;
       On E:Exception do
         MessageDlg(E.Message,mtError,[mbOK],0);
      end;
    until Active; {Loop until logged in or user cancels}   
  end;


[RedOctober]

Hi dsiders.  Here are the answers to your questions.  The problem is not yet resolved.

>> Are you running Firebird Embedded Server?

Answer:  No

>>Did you modify your firebird.conf file to use a different value for the RemoteServicePort for the server instance?

Answer:  No

>> Also assuming you have to set the ServerName and  Protocol properties in TIBServerProperties to appropriate values.

Answer:  Yes. 
Example 1:   ServerName = SRV1    Protocol = TCPIP 
Example 2:   ServerName = 127.0.0.1    Protocol = Local

>> If this is a DBA-level utility, I suppose it could be used for this purpose.

This is a SYSDBA level utility, intended for use by people who know the SYSDBA password

>> If it's an end-user tool, needing to know the dba login is not a good idea. Nor is embedding login information in an executable.

No passwords will be embedded in the executable.

>> If the service fails when activated, it should raise an exception.

It does not.  That is the problem.  It connects (.Active is True), even with the wrong password in the .Params list.

At the moment I am trapping for all exceptions, but I will add the EIBClientError section shown in the example.

[RedOctober]

Just looking through the examples, there is no place I can find that shows how or where to put the port number.  Which is the correct method?

Code: Pascal  [Select][+][-]

1. ...
2.     fb_sp: TIBServerProperties;
3. ...
4.  
5.   // This?
6.   fb_sp.ServerName := dbeHostNm.Field.AsString + ':' + dbePort.Field.AsString;
7.  
8.   // or this?
9.   fb_sp.ServerName := dbeHostNm.Field.AsString + '/' + dbePort.Field.AsString;
10.  
11.   // or this?
12.   fb_sp.ServerName := dbeHostNm.Field.AsString + '\' + dbePort.Field.AsString;
13.  
14.   // or all of this?
15.   if SameText(dbeHostNm.Field.AsString, 'localhost') or SameText(dbeHostNm.Field.AsString, '127.0.0.1') then
16.     fb_sp.Protocol := Local
17.   else
18.     fb_sp.Protocol := TCP;
19.  
20.   usr_nm := dbeUserNm.Field.AsString;
21.   pwd := edtPwd.Text;
22.  
23.   fb_sp.Params.Clear;
24.   if (Length(usr_nm) > 0) and (Length(pwd) > 0) then
25.     begin
26.       fb_sp.LoginPrompt := False;
27.       fb_sp.Params.Add('USER_NAME=' + usr_nm);
28.       fb_sp.Params.Add('PASSWORD=' + pwd);
29.       fb_sp.Params.Add('PORT=' + dbePort.Field.AsString);
30.     end
31. ...      
32.  

[dsiders]

Just looking through the examples, there is no place I can find that shows how or where to put the port number.  Which is the correct method?

Code: Pascal  [Select][+][-]

1. ...
2.     fb_sp: TIBServerProperties;
3. ...
4.  
5.   // This?
6.   fb_sp.ServerName := dbeHostNm.Field.AsString + ':' + dbePort.Field.AsString;
7.  
8.   // or this?
9.   fb_sp.ServerName := dbeHostNm.Field.AsString + '/' + dbePort.Field.AsString;
10.  
11.   // or this?
12.   fb_sp.ServerName := dbeHostNm.Field.AsString + '\' + dbePort.Field.AsString;
13.  
14.   // or all of this?
15.   if SameText(dbeHostNm.Field.AsString, 'localhost') or SameText(dbeHostNm.Field.AsString, '127.0.0.1') then
16.     fb_sp.Protocol := Local
17.   else
18.     fb_sp.Protocol := TCP;
19.  
20.   usr_nm := dbeUserNm.Field.AsString;
21.   pwd := edtPwd.Text;
22.  
23.   fb_sp.Params.Clear;
24.   if (Length(usr_nm) > 0) and (Length(pwd) > 0) then
25.     begin
26.       fb_sp.LoginPrompt := False;
27.       fb_sp.Params.Add('USER_NAME=' + usr_nm);
28.       fb_sp.Params.Add('PASSWORD=' + pwd);
29.       fb_sp.Params.Add('PORT=' + dbePort.Field.AsString);
30.     end
31. ...      
32.  

The firebird docs should have all the info for connection strings in the doc\README.connection_strings.txt file.  Let's just say it's "flexible". <g>

Code: [Select]

------------------
Connection strings
------------------

Connection string is a local or remote path to the database being attached.

The legacy syntax (supported by all Firebird versions) is the following:

For TCP (aka INET) protocol:

  <host> [ / <port>] : <file path to database or alias>

For named pipes (aka NetBEUI, aka WNET) protocol:

  \\ <host> [ @ <port>] \ <file path to database or alias>

For local connections as simple as:

  <file path to database or alias>

If host name is omitted, local connection is implied. Depending on settings,
platform and Firebird version, it could be performed via either the embedded
engine or XNET (shared memory) protocol or TCP localhost loopback.

Examples:

  Connect via TCP using database name:

    192.168.0.11:/db/mydb.fdb
    192.168.0.11:C:\db\mydb.fdb
    myserver:C:\db\mydb.fdb
    localhost:/db/mydb.fdb

  Connect via TCP using database alias:

    192.168.0.11:mydb
    myserver:mydb
    localhost:mydb

  Connect via TCP using non-default port 3051:

    192.168.0.11/3051:C:\db\mydb.fdb
    192.168.0.11/3051:mydb
    myserver/3051:/db/mydb.fdb
    localhost/3051:/db/mydb.fdb
    myserver/3051:mydb
    localhost/3051:mydb

  Connect via TCP using non-default service name:

    192.168.0.11/fb_db:C:\db\mydb.fdb
    192.168.0.11/fb_db:mydb
    localhost/fb_db:/db/mydb.fdb
    myserver/fb_db:/db/mydb.fdb
    myserver/fb_db:mydb
    localhost/fb_db:mydb

  Connect via named pipes:

    \\myserver\C:\db\mydb.fdb
    \\myserver@fb_db\C:\db\mydb.fdb

  Local connection:

    /db/mydb.fdb
    C:\db\mydb.fdb
    mydb

Additionally, Firebird 3.0 introduces generalized URL-like syntax for
connection strings:

  [ <protocol> : // [ <host> [ : <port> ] ] ] / <file path to database or alias>

Where protocol is one of: INET (means TCP), WNET (means named pipes) or XNET
(means shared memory).

Examples:

  Connect via TCP using database name:

    inet://192.168.0.11//db/mydb.fdb
    inet://192.168.0.11/C:\db\mydb.fdb
    inet://myserver/C:\db\mydb.fdb
    inet://localhost//db/mydb.fdb

  Connect via TCP using database alias:

    inet://192.168.0.11/mydb
    inet://myserver/mydb
    inet://localhost/mydb

  Connect via TCP using non-default port 3051:

    inet://192.168.0.11:3051/C:\db\mydb.fdb
    inet://192.168.0.11:3051/mydb
    inet://myserver:3051//db/mydb.fdb
    inet://localhost:3051//db/mydb.fdb
    inet://myserver:3051/mydb
    inet://localhost:3051/mydb

  Connect via TCP using non-default service name:

    inet://192.168.0.11:fb_db/C:\db\mydb.fdb
    inet://192.168.0.11:fb_db/mydb
    inet://localhost:fb_db//db/mydb.fdb
    inet://myserver:fb_db//db/mydb.fdb
    inet://myserver:fb_db/mydb
    inet://localhost:fb_db/mydb

  The "inet" protocol can be replaced by "inet4" or "inet6" to restrict client
  to IPv4 or IPv6 addresses corresponding to supplied name ("inet" protocol
  tries all addresses in the order determined by OS):

    inet4://myserver/mydb
    inet6://myserver/mydb

  Connect via named pipes:

    wnet://myserver/C:\db\mydb.fdb
    wnet://myserver:fb_db/C:\db\mydb.fdb

  Loopback connection via TCP:

    inet:///db/mydb.fdb
    inet://C:\db\mydb.fdb
    inet://mydb

  Loopback connection via named pipes:

    wnet://C:\db\mydb.fdb
    wnet://mydb

  Local connection via shared memory:

    xnet://C:\db\mydb.fdb
    xnet://mydb

  Local (embedded by default) connection:

    /db/mydb.fdb
    C:\db\mydb.fdb
    mydb

If protocol and host name are omitted, local connection is implied. Depending on settings,
it could be performed via either the embedded engine or XNET (shared memory) protocol
or TCP localhost loopback.

On the server side, default provider configuration is:

  Providers = Remote, Engine12, Loopback

It means that if the remote provider fails to match the connection string (because of missing
protocol / host parts), then the embedded engine handles the hostless connection. If you need
to connect locally using a specific transport protocol, please specify:

  inet://<file path to database or alias>
  or
  wnet://<file path to database or alias>
  or
  xnet://<file path to database or alias>

Note: WNET (named pipes) and XNET (shared memory) protocols are available on Windows only.

I know you're not interested in the database name parts of these examples. But the other parts related to server/service name and port numbers should apply.

localhost:3050
127.0.0.1:3050

Either of these should work for local connections. And since you're using the default port number, it should not be required. I haven't used any of the URL-style identifiers yet... so that's just an unknown at this point.

Hope that helps.

[RedOctober]

Upon further testing, I have determined that the .Protocol is affecting how TIBServerProperties utilizes the username and password.   If the .Protocol is set to "Local", then the name and password are ignored and the TIBServerProperties component will allow a successful connection, even without the correct username and password combination.  If the .Protocol is set to "TCP", then the username and password are used and if incorrect, it will show the expected behavior and not allow a connection.  In my code below, I set the .Protocol to "TCP" for local as well as remote, just to force the TIBServerProperties component to reject an incorrect username and password.

Code: Pascal  [Select][+][-]

1.   if SameText(dbeHostNm.Field.AsString, 'localhost') or SameText(dbeHostNm.Field.AsString, '127.0.0.1') then
2.     begin
3.       fb_sp.Protocol := TCP; //Local;
4.       fb_sp.ServerName := dbeHostNm.Field.AsString + '/' + dbePort.Field.AsString;
5.     end
6.   else
7.     begin
8.       fb_sp.Protocol := TCP;
9.       fb_sp.ServerName := dbeHostNm.Field.AsString + '/' + dbePort.Field.AsString;
10.     end;
11.  

The above code works now, in both situations.

[tonyw]

Upon further testing, I have determined that the .Protocol is affecting how TIBServerProperties utilizes the username and password.   If the .Protocol is set to "Local", then the name and password are ignored and the TIBServerProperties component will allow a successful connection, even without the correct username and password combination.  If the .Protocol is set to "TCP", then the username and password are used and if incorrect, it will show the expected behavior and not allow a connection.  In my code below, I set the .Protocol to "TCP" for local as well as remote, just to force the TIBServerProperties component to reject an incorrect username and password.

<snip>

The above code works now, in both situations.

My guess is that you are running Firebird 3 on Windows.

With earlier versions of Firebird, you only got the embedded server if you explicitly installed it. On Windows that usually meant putting it in the same folder as your executeable. However, wtih Firebird 3, the embedded server is always available as part of the standard installation - thanks to the new provider architecture. Embedded mode relies upon file system permissions and so if you have read/write permissions on the database file, then it will happily open the database for you and ignore any password you provide.

The problem has been around for longer on Linux as the embedded server was part of the classic server install and would be loaded by IBX if available. However, it is also more usual under Linux to set the file system permissions to limit database access to firebird only and hence the problem was less obvious.

Firebird 3 also has another gotcha waiting for you with the WinSspi user authentication plugin. On Windows systems this uses your Windows login credentials to log you into a database. If this is configured as available for use in the firebird.conf file then, even when using TCP/IP to connect you may still find yourself logged in even when providing the wrong password - as the password is again ignored as your Windows identity is used when granting access to the FIrebird database.

[tonyw]

One extra thought, if you want to know which protocol and authentication method Firebird used to log you in then the MON$ATTACHMENTS table can be queried to tell you e.g.

SELECT a.MON$USER, a.MON$ROLE, a.MON$REMOTE_PROTOCOL, a.MON$REMOTE_HOST,
    a.MON$AUTH_METHOD
FROM MON$ATTACHMENTS a  Where MON$ATTACHMENT_ID = CURRENT_CONNECTION

[RedOctober]

Thanks tonyw.  You are correct.  I have FB 2.5x on my remote server and 3.x on my local server.  It is behaving exactly as you describe.  Mystery solved.