powered by Jive Software

Openfire and Asterisk Authentication + Billing

Hi,

I have stumbled across openfire and find it to be a great server package and spark being equally good. However, i might be missing the concept here slightly but this is what i need and for some reason i just can’t get my head around it.

I have an Asterisk platform which works with a billing platform, users can connect to this using a username/password and then place calls through our network over SIP. Our billing platform calculates the rate of the call and sets the time limits accordingly.

Considering that each user has its own username/password to the platform is it not possible to set this information within the client? From what i can see you effectively setup multiple extensions in asterisk and then add this information on the openfire server side. Im just confused slightly, do the administrators add sip capabilites to each individual account as they register through open fire? Do we auto add the information directly into the MySQL datbase at the point of registration into asterisk?

This is what i would like to achieve.

Users open Spark and IM each other.

Users has the ability to place calls onto our VoIP network to each other or through the PSTN from which they are billed (controlled by asterisk via extensions)

Is this possible and if so, how do i setup openfire to handle this correctly without using the Asterisk Manager interface. Just simply each user supplying a username/password either the one they initially registered with or an addtional set of fields within the client to enter it.

Thanks

Ok, 8 minutes in and i think i have a solution, there maybe an easier way but i think this may work. Each user that registers to the billing platform has the same crudentials directly entered into the MySQL database used by openfire. Then at the same time create a phone mapping automatically within the SQL database. Solution: both platforms have the same username and password with phone mapping (all completed with one script at registration)

If you already have a database with the usernames and passwords you could also instruct Openfire to use this database instead of its own for authentication. That way you only have to store the credentials in one place and users can easily change their password.

Hi,

Actually i have read that somewhere on the documentation and did think about that, i even gave it a shot, the problem comes down to md5. I just cannot get the system to authenticate its driving me crazy. I did make a post with relation to this. http://www.igniterealtime.org/community/thread/30973?tstart=0

What is the correct way of setting up openfire to connect to a remote database using md5. I read here: http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/db-integ ration-guide.html

I tried many variations, and furthermore how do you connect to a remote SQL database?

Thanks

Hi,

Actually sorry, upon reviewing my post i do belive remote connections would be setup here:

<connectionString>jdbc:mysql://localhost/dbname?user=username&password=secret</connectionString>

Thanks

Hi,

Ok ive tried to setup remote authentication and this is the error in my log:

2008.01.09 07:47:25 org.jivesoftware.openfire.auth.JDBCAuthProvider.getPasswordValue(JDBCAuthProvide r.java:254) Exception in JDBCAuthProvider

java.sql.SQLException: The url cannot be null

at java.sql.DriverManager.getConnection(Unknown Source)

at java.sql.DriverManager.getConnection(Unknown Source)

at org.jivesoftware.openfire.auth.JDBCAuthProvider.getPasswordValue(JDBCAuthProvid er.java:240)

at org.jivesoftware.openfire.auth.JDBCAuthProvider.authenticate(JDBCAuthProvider.j ava:116)

This is my configration:

<provider>

<auth>

<className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className& gt;

</auth>

<user>

<className>org.jivesoftware.openfire.user.JDBCUserProvider</className& gt;

</user>

</provider>

<jdbcAuthProvider>

<passwordSQL>SELECT password FROM users WHERE username=?</passwordSQL>

<passwordType>md5</passwordType>

</jdbcAuthProvider>

<jdbcUserProvider>

<loadUserSQL>SELECT first_name,email_address FROM users WHERE username=?</loadUserSQL>

<userCountSQL>SELECT COUNT(*) FROM users</userCountSQL>

<allUsersSQL>SELECT username FROM users</allUsersSQL>

<searchSQL>SELECT username FROM users WHERE</searchSQL>

<usernameField>username</usernameField>

<nameField>first_name</nameField>

<emailField>email_address</emailField>

</jdbcUserProvider>

<locale>en</locale>

<!-- Network settings. By default, Openfire will bind to all network interfaces.

Alternatively, you can specify a specific network interfaces that the server

will listen on. For example, 127.0.0.1. This setting is generally only useful

on multi-homed servers. -->

<!–

<network>

<interface></interface>

</network>

–>

<connectionProvider>

<className>org.jivesoftware.database.DefaultConnectionProvider</classN ame>

</connectionProvider>

<database>

<defaultProvider>

<driver>com.mysql.jdbc.Driver</driver>

<serverURL>jdbc:mysql://hidden/datausers</serverURL>

<username>username</username>

<password>password</password>

<minConnections>5</minConnections>

<maxConnections>15</maxConnections>

<connectionTimeout>1.0</connectionTimeout>

</defaultProvider>

These settings were adjusted and appended in /opt/openfire/conf/openfire.xml

any ideas as to where i might be going wrong?

Ok, problem solved!!!

For those of you who are looking to connect to a remote MySQL database with different fields etc… this is how i done it. First of all i will show you the config file then explain where i was going wrong and the things that confused me

in /opt/openfire/conf/server.xml

<provider>

<auth>

<className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className&g t;

</auth>

<user>

<className>org.jivesoftware.openfire.user.JDBCUserProvider</className&g t;

</user>

</provider>

<jdbcProvider>

<driver>com.mysql.jdbc.Driver</driver>

<connectionString>jdbc:mysql://ip.ip.ip.ip/netusers?user=remoteadmin&passw ord=123456</connectionString>

</jdbcProvider>

<jdbcAuthProvider>

<passwordSQL>SELECT decrypted_password FROM users WHERE account=?</passwordSQL>

<passwordType>plain</passwordType>

</jdbcAuthProvider>

<jdbcUserProvider>

<loadUserSQL>SELECT first_name,email_address FROM users WHERE account=?</loadUserSQL>

<userCountSQL>SELECT COUNT(*) FROM users</userCountSQL>

<allUsersSQL>SELECT account FROM users</allUsersSQL>

<searchSQL>SELECT account FROM users WHERE</searchSQL>

<usernameField>account</usernameField>

<nameField>first_name</nameField>

<emailField>email_address</emailField>

</jdbcUserProvider>

Ok, the first section: <provider> you must have both <auth> and <user> in this section, its well documented and most likely you already have it.

The next and important section:

<jdbcProvider>

<driver>com.mysql.jdbc.Driver</driver>

<connectionString>jdbc:mysql://ip.ip.ip.ip/netusers?user=remoteadmin&passw ord=123456</connectionString>

</jdbcProvider>

Now this confused me a little bit, i didn;t find any hard reference to this being essential i was always changing the values further in the config file like this:

<database>

<defaultProvider>

<driver>com.mysql.jdbc.Driver</driver>

<serverURL>jdbc:mysql://ip.ip.ip.ip:3306/netusers</serverURL>

<username>remoteadmin</username>

<password>123456</password>

<minConnections>5</minConnections>

<maxConnections>15</maxConnections>

<connectionTimeout>1.0</connectionTimeout>

</defaultProvider>

</database>

But actually this seems to be only for the default database from what i can make out, although, intrestingly when i tried to connect through spark and the parameters were correct i was getting invalid username/password but when i made then i deliberatley changed details so they were wrong and then i was getting “unable to connect”, so clearly they were being used. So you must have:

<jdbcProvider>

<driver>com.mysql.jdbc.Driver</driver>

<connectionString>jdbc:mysql://ip.ip.ip.ip/netusers?user=remoteadmin&passw ord=123456</connectionString>

</jdbcProvider>

For your connection and don’t forget by default the section between the “user” and “password” only has “&” (what i mean by default is that is what seems to be shown on the support pages) and it must be “& a m p ;” ( i have but spaces in between the & and ; becuase the forum keeps removing it, just so you know there are no spaces! ) with the semicolon!!

Ok, moving on…

<jdbcAuthProvider>

<passwordSQL>SELECT decrypted_password FROM users WHERE account=?</passwordSQL>

<passwordType>plain</passwordType>

</jdbcAuthProvider>

This is the query openfire will run against the table to pick up the username or in my case the account number. Some pages suggest that it should be left at SELECT password FROM user_account WHERE username=? i might have read it wrong on some posts i was reading but nothing indicated that i should change this line although it made sense to me, so i did and presto !http://www.igniterealtime.org/community/images/emoticons/wink.gif!

Ok finally…

<jdbcUserProvider>

<loadUserSQL>SELECT first_name,email_address FROM users WHERE account=?</loadUserSQL>

<userCountSQL>SELECT COUNT(*) FROM users</userCountSQL>

<allUsersSQL>SELECT account FROM users</allUsersSQL>

<searchSQL>SELECT account FROM users WHERE</searchSQL>

<usernameField>account</usernameField>

<nameField>first_name</nameField>

<emailField>email_address</emailField>

</jdbcUserProvider>

Here is pretty self explanitory, once you have setup the above setting jdbcUserProvider is easy just enter the fileds accordingly. So “users” is the table that my users are in so after FROM it should be you table name ( in my case “users” ), and everything prior to FROM is the field names and in my case first_name and email_address.

Before you scream shouting your using plain password how insecure is that!! i just wanted to get the thing working, by default all passwords are stored in MD5 and i have another issue realting to that but my guess is this will solved very easily by changing:

<passwordType>plain</passwordType>

to

<passwordType>md5</passwordType>

and changing my query string from:

<passwordSQL>SELECT decrypted_password FROM users WHERE account=?</passwordSQL>

to

<passwordSQL>SELECT encrypted_password FROM users WHERE account=?</passwordSQL>

When trying to setup this up i had two errors:

org.jivesoftware.util.JiveGlobals.loadSetupProperties(JiveGlobals.java:774)

java.io.IOException: Error on line 49 of document : The reference to entity “password” must end with the ‘;’ delimiter. Nested exception: The reference to entity “password” must end with the ‘;’ delimiter.

and

org.jivesoftware.openfire.auth.JDBCAuthProvider.getPasswordValue(JDBCAuthProvide r.java:254) Exception in JDBCAuthProvider

java.sql.SQLException: The url cannot be null

by following the about this solved my problem. Ive yet to login as admin, but i assume that by transferring the admin account accross to the remote server i should be ok.

Good Luck - I hope you don’t mind the lengthy post admins