Can I use TIBServerProperties to test connection to server? lazarus / 16-Databases ---------------------------------------------------------------------- RedOctober · 2018-02-06 03:19 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 · 2018-02-06 07:56 Quote from: RedOctober on February 06, 2018, 04:19:50 amI 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. Quote from: RedOctober on February 06, 2018, 04:19:50 amIf 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. Quote from: RedOctober on February 06, 2018, 04:19:50 amShould 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 · 2018-02-06 15:30 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 · 2018-02-06 15:47 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ...    fb_sp: TIBServerProperties; ...   // This?  fb_sp.ServerName := dbeHostNm.Field.AsString + ':' + dbePort.Field.AsString;   // or this?  fb_sp.ServerName := dbeHostNm.Field.AsString + '/' + dbePort.Field.AsString;   // or this?  fb_sp.ServerName := dbeHostNm.Field.AsString + '\' + dbePort.Field.AsString;   // or all of this?  if SameText(dbeHostNm.Field.AsString, 'localhost') or SameText(dbeHostNm.Field.AsString, '127.0.0.1') then    fb_sp.Protocol := Local  else    fb_sp.Protocol := TCP;   usr_nm := dbeUserNm.Field.AsString;  pwd := edtPwd.Text;   fb_sp.Params.Clear;  if (Length(usr_nm) > 0) and (Length(pwd) > 0) then    begin      fb_sp.LoginPrompt := False;      fb_sp.Params.Add('USER_NAME=' + usr_nm);      fb_sp.Params.Add('PASSWORD=' + pwd);      fb_sp.Params.Add('PORT=' + dbePort.Field.AsString);    end... ---------------------------------------------------------------------- dsiders · 2018-02-06 21:01 Quote from: RedOctober on February 06, 2018, 04:47:59 pmJust 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ...    fb_sp: TIBServerProperties; ...   // This?  fb_sp.ServerName := dbeHostNm.Field.AsString + ':' + dbePort.Field.AsString;   // or this?  fb_sp.ServerName := dbeHostNm.Field.AsString + '/' + dbePort.Field.AsString;   // or this?  fb_sp.ServerName := dbeHostNm.Field.AsString + '\' + dbePort.Field.AsString;   // or all of this?  if SameText(dbeHostNm.Field.AsString, 'localhost') or SameText(dbeHostNm.Field.AsString, '127.0.0.1') then    fb_sp.Protocol := Local  else    fb_sp.Protocol := TCP;   usr_nm := dbeUserNm.Field.AsString;  pwd := edtPwd.Text;   fb_sp.Params.Clear;  if (Length(usr_nm) > 0) and (Length(pwd) > 0) then    begin      fb_sp.LoginPrompt := False;      fb_sp.Params.Add('USER_NAME=' + usr_nm);      fb_sp.Params.Add('PASSWORD=' + pwd);      fb_sp.Params.Add('PORT=' + dbePort.Field.AsString);    end...     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". 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:   [ / ] : For named pipes (aka NetBEUI, aka WNET) protocol:   \\ [ @ ] \ For local connections as simple as:   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:   [ : // [ [ : ] ] ] / 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://   or   wnet://   or   xnet:// 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 · 2018-02-06 22:37 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   if SameText(dbeHostNm.Field.AsString, 'localhost') or SameText(dbeHostNm.Field.AsString, '127.0.0.1') then    begin      fb_sp.Protocol := TCP; //Local;      fb_sp.ServerName := dbeHostNm.Field.AsString + '/' + dbePort.Field.AsString;    end  else    begin      fb_sp.Protocol := TCP;      fb_sp.ServerName := dbeHostNm.Field.AsString + '/' + dbePort.Field.AsString;    end;  The above code works now, in both situations. ---------------------------------------------------------------------- tonyw · 2018-02-08 09:20 Quote from: RedOctober on February 06, 2018, 11:37:28 pmUpon 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. 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 · 2018-02-08 10:22 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 · 2018-02-08 15:43 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.