[Matthieu]

Storing server, domains, users and groups in a Database via ODBC

This requires at least v3.1.0 build 70.

How-to

1. First, you'll have to create the tables, here are the tables structures, download the sql files :  odbc_settings.zip (4.06K)
Number of downloads: 2080 for Mysql, Postgresql, MS SQL (provided by ADutch1), Oracle (provided by Oaxa)

For instance, you can import these tables for Mysql with PhpMyAdmin.

2. Once the tables are created (you should have 8 tables), you'll have to create a file named account.ini in your installation directory and copy this to tell the server to use ODBC :

;-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->

[Settings]
Storage=ODBC

[ODBC]
;ConnectionString=Provider=MSDASQL.1;Persist Security Info=False;Data Source=mysql-odbc
ConnectionString=DSN=mysql-odbc
UserID=
Password=
; Set to 0 if your sql server doesn't support foreign keys (e.g. mysql), set
; to -1 otherwise
ForeignKeys=0
; This setting tells the server how to quote values in SQL queries
; 0 = backslashes characters that need to be quoted in queries (e.g. 'O\'reilly')
; 1 = double quotes in queries (e.g. 'O''reilly')
EscapeType=0

;-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->

For MS SQL (provided by ADutch1) :

;-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->

[Settings]
Storage=ODBC

[ODBC]
ConnectionString=DSN={whatever you named the SystemDSN in the ODBC control panel}
UserID={user account to connect to the DB in SQL}
Password={password if the user account above}
; Set to 0 if your sql server doesn't support foreign keys (e.g. mysql), set
; to -1 otherwise
ForeignKeys=-1
; This setting tells the server how to quote values in SQL queries
; 0 = backslashes characters that need to be quoted in queries (e.g. 'O\'reilly')
; 1 = double quotes in queries (e.g. 'O''reilly')
EscapeType=1

;-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->

For Oracle (provided by Oaxa) :

;-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->

[Settings]
Storage=ODBC

[ODBC]
;ConnectionString=Provider=MSDASQL.1;Persist Security Info=False;Data Source=mysql-odbc
ConnectionString=DSN=ORADC_G6
UserID={user account to connect to the DB in SQL}
Password={password if the user account above}

; Set to 0 if your sql server doesn't support foreign keys (e.g. mysql), set
; to -1 otherwise
ForeignKeys=-1
; This setting tells the server how to quote values in SQL queries
; 0 = backslashes characters that need to be quoted in queries (e.g. 'O\'reilly')
; 1 = double quotes in queries (e.g. 'O''reilly')
EscapeType=1

;-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->

3. Then you can create a system ODBC link with the name "mysql-odbc" linking to your mySQL database or you can change the ConnectionString with the parameters to connect to your SQL server.

4. Start the server and in "FTP Server/Logs & Reports/Main log" you should see the line "Accounts storage : 'ODBC v0.1'." instead of "Accounts storage : 'Inifiles v2.8'.".

If the ODBC source is not available the server will report a startup error in <installation path>\g6ftpserver-error.log and will use default inifile storage.

Uninstall

Stop the service and rename (or delete) the inifile, then restart the service.

Problems

Deleting users does not work :
http://www.g6ftpserver.com/forum/index.php?showtopic=1535

Duplicate entry error logged in G6FTPServer-error.log :
http://www.g6ftpserver.com/forum/index.php...ost&p=10014

[Fire-Fox]

Hey M.Roger....

I have tried the script but when i have loaded all to the mysql database.
it comes with and error that i can't add a domain to the server or something like that...

I have follow the guide step by step. And it says the account and that are storage in the database....???

[Matthieu]

There was a similar error with a mysqlodbc version : http://www.g6ftpserver.com/forum/index.php...findpost&p=6628

[ejzhang]

Can i storing server, domains, users and groups in a SqlServer Database via ODBC?

This post has been edited by ejzhang: 17 March 2005 - 04:21 PM

[ADutch1]

I also would like to know how to set this up for a SQl database. I also wonder what information all can be stored in the database and how often does Gene6 read the database.

[Matthieu]

SQL server can be used but there is currently no database structure available.
I have no sql server to test at the moment, so I suggest you base your structure on mysql one.

[ADutch1]

M.Roger,

Thanks for the reply. I was just wondering what columns are all needed/possible in the tables? I would like to control pretty much all user settings from the DB in SQL if possible.

Thanks,

[Matthieu]

You can see which are needed by looking at the structures : http://www.g6ftpserver.com/files/odbc-sql-tables.sql

[ADutch1]

Ok, I hate bugging about this but refering me to the link is not really helping (i found the link already by myself).

I am looking into buying this product (a lot of them) and I don't seem to get a decend answer here...

Are you telling me that the FTP server and DB do NOT synchronize? Meaning, I can't see in the FTP server program the accounts I create in the DB?

Also, it seems that the FTP program uses one (or more) MANUALLY created accounts and compares them to the users in the DB to provide rights, account settings etc.??? Seems a little weird.. why not read straight out of the DB and use tables in the DB to populate the fields in the FTP program?

I need to be able to go to the server and look at the FTP accounts (coming from the DB) and have one central DB feeding multiple FTP servers... (like Serv-U and WSFTP do).

Thanks,

V

[Matthieu]

Are you telling me that the FTP server and DB do NOT synchronize? Meaning, I can't see in the FTP server program the accounts I create in the DB?

No, this is transparent for the server, whether you store in ini or DB it will show you all accounts and settings (it will read/save them from/to ini when using ini, and from/to DB when using DB). Using the administration client and DB, you will see everything as when you are using ini files.

Also, it seems that the FTP program uses one (or more) MANUALLY created accounts and compares them to the users in the DB to provide rights, account settings etc.??? Seems a little weird.. why not read straight out of the DB and use tables in the DB to populate the fields in the FTP program?

It looks that you are talking about authentication against DB, not complete storage in a DB which is different from this topic.

[ADutch1]

Matthieu,

I think we are having a little misunderstanding here about what we are exactly talking about...

What I am looking for is a method to store/enter all user information/settings in a DB and having the FTP program use this information to populate it's fields. When I open up the administration program of the FTP server, I want to see the information in the FTP server (under the Users) that I entered in the DB.

If this program is capable of doing this then it is the fit I am looking for and I would like to get more detailed information regarding this. I don't see that anywhere in your documentation though. I DO find the information where you can create a account in the FTP program and use that to authenthicate against from a DB but I can't seem to find anywhere how to create a table in the DB with all the fields you see in the user settings. I also don't see anywhere where it would show these accounts (created in the DB) back into the FTP program.

So, all the fields that you see in the FTP program, I would like to know what columns I would need to create in a DB so that the FTP program would use them. If you look at (for example) Serv-U, they have a section in their program where you can reference all the user and group settings to a specific table/column in a DB. The DB and the FTP program synchronize both ways every 30 sec to synchronize the users.

Thanks,

Victor

[Matthieu]

What I am looking for is a method to store/enter all user information/settings in a DB and having the FTP program use this information to populate it's fields. When I open up the administration program of the FTP server, I want to see the information in the FTP server (under the Users) that I entered in the DB.

Yes, its does that, it is the main interest and what I'm talking about since the beginning

If you use odbc storage then you can view settings you added from another application directly to the DB.

I can't seem to find anywhere how to create a table in the DB with all the fields you see in the user settings. I also don't see anywhere where it would show these accounts (created in the DB) back into the FTP program.

You don't need to create columns, you need the settings name (which are the same as when using ini), you have 2 general fields in the tables : ConfigName, ConfigValue which store the information.

The ConfigName are taken from III Properties : http://www.g6ftpserver.com/manuals/devguide_en/

Since there can be new options additions, using this method allows us to not have to create or update all the column names (there is a lot of settings ...).

For instance, the property "Enabled" for a user would be seen as :

ConfigName=Enabled
ConfigValue=1/0

In the table User_settings :

UserID | ConfigName | ConfigValue
0 | Enabled | 1

If you have installed the evaluation version, then you can test this and see how the server uses them directly but as I said : this is transparent to you when using the administration client; instead of reading/saving to ini file it will read/save information from the DB even when added from another applications which writes to the database.

Only logged users that you are editing the settings externally (not via the server admin) will have their settings cached (up to 2 minutes), new created accounts (admin or externally added to the DB) will be immediately available.

I hope this answers to your questions

[ADutch1]

OK, a light just came on in my head

I totally didn't get the .ini part where you tell the server to use ODBC! My fault!
I was looking in the program itself under the authenthication part...

I will try this as soon as possible and let you know if I can find anything else I can be a total idiot about

[ADutch1]

Almost got it working... I will upload the MS SQL script to create the tables and the account INI file for other users. Also a little user step by step instructions...

[ADutch1]

Matthieu,

One more problem (yeah, yeah I know)...

It seems that the Servers are not being assigned an ID. The problem with this is that I have one database with all the accounts, feeding multiple FTP servers...

Meaning... I can't create multiple tables (databases) for all the seperate FTP servers. Is there a chance this is added in the new version?

V

[ADutch1]

Ok, attached is the account.ini file. This file needs to go into the install directory.
Make sure you edit the file and replace the {values} with the proper information...

Attached File(s)

•  account.ini (546bytes)
  Number of downloads: 514

[ADutch1]

Then create a Database in SQL (if you haven't done already) and add the SQL user to it that you mentioned in the SystemDSN. Make sure you give him DBO rights to the DB. Run the attached SQL script against the DB to create the tables.

Attached File(s)

•  g6mssql.sql (13.69K)
  Number of downloads: 736

This post has been edited by ADutch1: 21 March 2005 - 10:31 PM

[ADutch1]

Stop and Start the FTP server and that should do the trick. Add a user or change any settings, check the log and/or watch the tables for new entries...

Let me know if this works and if you run into any problems.

[Matthieu]

Great I guess this will help some other users here.

It seems that the Servers are not being assigned an ID. The problem with this is that I have one database with all the accounts, feeding multiple FTP servers...

I'm not sure this can be done. Only main server config will be affected (=> they will all have same main setup so take care of ip binding), I don't think you will host same domain name on different server.

[ADutch1]

You might have to change some code in the FTP program but it looks like you just need to add a table in the DB called Servers with the fields ID and Servername. Link the ID to the other tables including all the user and groups tables (since you will have different users and groups for different servers). Maybe something in a new version?

This post has been edited by ADutch1: 22 March 2005 - 03:47 PM