[Release] gTeamSpeak - TS User Management for vBulletin

[SoloDrakban]

We've been running MySQL v5.0 with TS for awhile with vB integration and I took an entirely different approach. There is no need to sync, no need to run a crontab, any update to the client account is reflected automatically. Might as well give back to the community. This method can be used to quickly integrate authentication of any forum that uses MySQL without having to write custom plugins. You will need to have SQLfu however.

First, get TS running within MySQL as normal. Once everything works without vB integration you're ready to move on. Getting TS to talk to MySQL is beyond the scope of this. I am also going to assume that you know enough SQL to understand what's happening here. This isn't a 'plug and play' solution. Depending on your situation you'll probably want to disallow granting SA, user registration and other features on TS that could mess with your forum DB.

If you are running TS and your forum software in separate database (highly recommended) you will need to grant your TS user access to the forum database. The following command should do the trick for a vBulletin install

GRANT SELECT ON <vb_db>.user TO <ts_user>@<host> IDENTIFIED BY '<password>'
FLUSH PRIVILEGES;

If your TS and vB installs are running out of the same database with table prefixes then you probably don't need to do anything to allow TS to read the vB tables.

Alter your vB user table to add a 'dt_crapfield' entry as a varchar(20). This is a dummy column that gets around two fields that TS needs that I don't care about. I tried different ways of avoiding this field but it appears that the mysql driver that TS uses really doesn't like simple "SELECT 's' as ..." statements even though it allows 'SELECT 0 as ...' statements.

Modify the mysql_sql/read_client_login.sql (make a backup first) to read:

SELECT
 userid as i_client_id,
 if(username='superadmin',0,1) as i_client_server_id,
 (membergroupids REGEXP '(^|,)(11|22|33)(,|$)')*-1 AS b_client_privilege_serveradmin,
 username as s_client_name,
 password as s_client_password,
 dt_crapfield as dt_client_created,
 dt_crapfield as dt_client_lastonline
FROM
 vbulletin.user
WHERE
    membergroupids regexp '(^|,)(1|2|3)(,|$)' = 1
AND usergroupid NOT IN ( 1, 3, 4, 8 )
AND username = :sName
AND password= MD5(CONCAT(MD5(:sPassword),salt))

And a basic explanation:

if(username='superadmin',0,1) as i_client_server_id - Change this to a username on your forum that should have 'superadmin' login abilities. I created a dummy account unrelated to getting TS access and locked it down. You only need this if you need to access the server via the telnet superadmin. Otherwise you can just have 1 as i_client_server_id. Make sure to replace 1 with the appropriate server ID.

(membergroupids REGEXP '(^|,)(11|22|33)(,|$)')*-1 AS b_client_privilege_serveradmin - This is a list of the group IDs on the forum that get SA. These are SECONDARY groups, not PRIMARY groups. Just list them inside the ()'s separated by |'s

membergroupids regexp '(^|,)(1|2|3)(,|$)' = 1 - This is a list of the group IDs on the forum that are allowed to connect to TS. Again, these are SECONDARY groups not PRIMARY groups.

AND usergroupid NOT IN ( 1, 3, 4, 8 ) - These are a list of groups that are NOT to be allowed onto TS under any circumstances. The groups I have here include the Banned, COPPA, and Awaiting Authorization groups.

The last step I took was to 0 out the edit_client_lastonline.sql file. This is because I didn't want TS updating the DB when a client logs in.

As you can see this example is geared towards vB installations where TS membership and privileges is determined by the forum users membership in secondary groups but it does show that this can be accomplished without the need for forum plugins or hacks or waiting to sync up databases, etc. Anybody who is familiar with SQL and the way their forum works should be able to adapt this method to any forum software out there.

There is only one issue we have found when using this method and that is people who have non-ASCII characters in their password or passwords longer than 25 characters will fail. The same probably applies to usernames as well though I have no had a user on my forum report that they haven't been able to access TS due to this.

I hope this helps with some of the people asking how to integration your forums and TS.

And a request for the next version of TS: Support views! God damn this would have been a lot easier if I could create a view and have TS read from the view but I can't, your MySQL driver doesn't support it.

[Liability]

Any chance that someone here could take 20 minutes and convert SoloDrakban's method for use in SMF? No need for a fancy plugin!

[alexandervba]

Make a version for SMF please?!

[romenyrr]

Make a version for SMF please?!

well i must be getting really close, because im not seeing any errors in the server.log when i'm tailing the file. heres my modified login.sql, i'm still trying to figure it out. im not a coder, i'm just trying to make this work for my site. i have some basic knowledge of the way this stuff works.

SELECT
memberName as i_client_id,
memberName as s_client_name,
passwd as s_client_password,
dateRegistered as dt_client_created,
dateRegistered as dt_client_lastonline
FROM smf_members
WHERE memberName = :sName AND passwd= MD5(CONCAT(MD5(:sPassword),passwordSalt))

[romenyrr]

This method can be used to quickly integrate authentication of any forum that uses MySQL without having to write custom plugins. You will need to have SQLfu however.

Does anybody have an idea what SQLfu is? I can't seeem to find anything on this

[SoloDrakban]

Does anybody have an idea what SQLfu is? I can't seeem to find anything on this

SQLfu isn't "anything" but skill with SQL.

I haven't had much experience with SMF forums but I'll install it and see about adapting what I've done for vB to it.

[mutsop]

Hi,

Is it possible with this to actually let only registered users (after a certain rank or amount of posts) to be able to create channels?

As I have a traders forum and it would be easier for users to actually talk to each other if they want instead of swapping emails all day long.

Kind regards
Peter

[Gryphon]

Yes. Create a TeamSpeak usergroup, grant that usergroup access rights to the signup form, and then create a new promotion that grants access to the new usergroup based on postcount, registration date, etc.

[LincolnCoe]

Will this be converted for vB4 or no current plans?

Thanks!

[SoloDrakban]

Will this be converted for vB4 or no current plans?

Thanks!

No plans. We have abandoned the use of vB after their licensing changes and moved to IP.Board. I will post for IP.Board when I get it running (still converting).

[SoloDrakban]

The TS Forum administrators may want to turn 'display_errors = off' in their php.ini:

Warning: fsockopen() [function.fsockopen]: php_network_getaddresses: getaddrinfo failed: Temporary failure in name resolution in [path]/includes/class_mail.php on line 641

Warning: fsockopen() [function.fsockopen]: unable to connect to tcp://mail.teamspeak.com:25 (Unknown error) in [path]/includes/class_mail.php on line 641