ejabberd - Comments for "Using LDAP for auth and MSSQL for roster storage"

Thanks -o)

dray — Mon, 22 Dec 2008 12:36:08 +0000

Thanks -o)

That was only in my case.

mfoss — Mon, 22 Dec 2008 11:43:03 +0000

dray wrote:

But what kind of interest using MSSQL if the only updated database table is dbo.rosterusers ?

I mean: if using the default modules, and the only *_odbc module is mod_roster_odbc, then all data is stored in Mnesia except rosters. This is the test that I made.

In your case, as you use several *_odbc modules, most of the information in your server will be stored in the SQL database.

Thanks a lot : I've just

dray — Mon, 22 Dec 2008 08:26:27 +0000

Thanks a lot : I've just added the server IP address ans it's working fine :

{odbc_server, "Server=192.168.0.254;DSN=ejabberd;UID=ejabberd;PWD=*********"}.

But what kind of interest using MSSQL if the only updated database table is dbo.rosterusers ?

Check logs. Simple test to know where is data stored

mfoss — Sun, 21 Dec 2008 22:09:46 +0000

dray wrote:

I'm having quite the same problems : I'd like ldap authentication for users and ODBC MS SQL Server for logging.
LDAP authentication = OK but nothing is logged in my SQL "EJABBERD" database.

My DSN = OK (tested, no error)

My ejabberd.cfg file (part of) :

------------------------------

{auth_method, ldap}.

{ldap_servers, ["10.6.169.88"]}.
{ldap_port, 389}.
{ldap_uids, [{"sAMAccountName","%u"}]}.
{ldap_filter, "(&(objectClass=user)(objectCategory=person))"}.
{ldap_base, "OU=Tsung_Users,DC=COMMUNDEV-EVO,DC=FR"}.
{ldap_rootdn, "CN=EJABBERD,CN=Users,DC=COMMUNDEV-EVO,DC=FR"}.
{ldap_password, "*********"}.

{odbc_server, "DSN=ejabberd;UID=ejabberd;PWD=********"}.

{modules,
[
{mod_roster_odbc, []},
...
]}.

I tried a similar setup with ejabberd trunk SVN with MySQL; everything worked correctly.

You should check ejabberd.log and sasl.log when ejabberd starts and you add a contact, maybe they show an error message about trouble connecting to the SQL database.

If you use mod_roster, the contacts should be stored in the table 'roster' of the internal database. If you use mod_roster_odbc, the contacts should be stored in the table 'rosterusers' in the database you specified.

You can try this:

Check the size of roster table of the internal database. You can see the size in the ejabberd Web Admin -> Nodes -> your node -> Database
Also check the size of the rosterusers table in your SQL database.
Now login to a jabber account, and add any contact, for example testcontact@localhost or similar
Finally, check again the size of the tables in internal and SQL. This may give you an idea of where is the roster contacts stored.

LDAP + SQL Server

dray — Tue, 16 Dec 2008 15:45:25 +0000

I'm having quite the same problems : I'd like ldap authentication for users and ODBC MS SQL Server for logging.
LDAP authentication = OK but nothing is logged in my SQL "EJABBERD" database.

My DSN = OK (tested, no error)

My ejabberd.cfg file (part of) :

------------------------------

%%
%% Authentication using LDAP
%%
{auth_method, ldap}.
%%
%% List of LDAP servers:
{ldap_servers, ["10.6.169.88"]}.
%% LDAP Server port to use
{ldap_port, 389}.
%% LDAP attribute that holds user ID:
{ldap_uids, [{"sAMAccountName","%u"}]}.
{ldap_filter, "(&(objectClass=user)(objectCategory=person))"}.
%%
%% Search base of LDAP directory:
{ldap_base, "OU=Tsung_Users,DC=COMMUNDEV-EVO,DC=FR"}.
%%
%% LDAP manager:
{ldap_rootdn, "CN=EJABBERD,CN=Users,DC=COMMUNDEV-EVO,DC=FR"}.
%%
%% Password to LDAP manager:
{ldap_password, "*********"}.

%%
%% ODBC compatible or MSSQL server:
%%
{odbc_server, "DSN=ejabberd;UID=ejabberd;PWD=********"}.

%%
%% Modules enabled in all ejabberd virtual hosts.
%%
{modules,
[
{mod_adhoc, []},
{mod_announce, [{access, announce}]}, % requires mod_adhoc
{mod_caps, []},
{mod_configure,[]}, % requires mod_adhoc
{mod_disco, []},
%%{mod_echo, [{host, "echo.SRV-EJABBERD"}]},
{mod_http_bind,[]},
%%{mod_http_fileserver, [{docroot, "C:\\Program Files\\ejabberd-2.0.2_2\\www"}]},
{mod_irc, []},
{mod_last_odbc, []},
{mod_muc, [
%%{host, "conference.@HOST@"},
{access, muc},
{access_create, muc},
{access_persistent, muc},
{access_admin, muc_admin}
]},
%%{mod_muc_log,[]},
{mod_offline_odbc, []},
{mod_privacy, []},
{mod_private, []},
%%{mod_proxy65,[]},
{mod_pubsub, [ % requires mod_caps
{access_createnode, pubsub_createnode},
{plugins, ["default", "pep"]}
]},
{mod_register, [
%%
%% After successful registration, the user receives
%% a message with this subject and body.
%%
{welcome_message, {"Bienvenue !",
"Bienvenue sur le serveur JABBER SRV-EJABBERD."}},

%%
%% When a user registers, send a notification to
%% these Jabber accounts.
%%
%%{registration_watchers, ["admin1@example.org"]},

{access, register}
]},
{mod_roster_odbc, []},
%%{mod_service_log,[]},
{mod_shared_roster,[]},
%%{mod_stats, []},
{mod_time, []},
{mod_vcard, []},
{mod_vcard_ldap,
[{ldap_vcard_map,
[{"NICKNAME", "%u", []},
{"GIVEN", "%s", ["givenName"]},
{"MIDDLE", "%s", ["initials"]},
{"FAMILY", "%s", ["sn"]},
{"FN", "%s", ["displayName"]},
{"EMAIL", "%s", ["mail"]},
{"ORGNAME", "%s", ["company"]},
{"ORGUNIT", "%s", ["department"]},
{"CTRY", "%s", ["c"]},
{"LOCALITY", "%s", ["l"]},
{"STREET", "%s", ["streetAddress"]},
{"REGION", "%s", ["st"]},
{"PCODE", "%s", ["postalCode"]},
{"TITLE", "%s", ["title"]},
{"URL", "%s", ["wWWHomePage"]},
{"DESC", "%s", ["description"]},
{"TEL", "%s", ["telephoneNumber"]}]},
{ldap_search_fields,
[{"Utilisateur", "%u"},
{"Prénom", "givenName"},
{"Nom", "sn"},
{"Email", "mail"},
{"Société", "company"},
{"Service", "department"},
{"Mission principale", "title"},
{"Description", "description"},
{"Téléphone", "telephoneNumber"}]},
{ldap_search_reported,
[{"Nom complet", "FN"},
{"Utilisateur", "NICKNAME"},
{"Email", "EMAIL"}]}
]},
{mod_version, []}
]}.

odbc

cselph — Wed, 30 Jul 2008 09:22:38 +0000

I had ODBC all setup wrong. I can now connect to the sql server using iodbctest put not from erl with the same connection string.