Kannel SQLBox Alternative: How to migrate SQL sms from Kannel to Ozeki

This guide explains how to migrate database-driven SMS sending from Kannel SQLBox to Ozeki SMS Gateway. It shows how to review the existing Kannel tables and workflow, configure an Ozeki SQL user, create the required Ozeki message tables, and update your application to submit messages through ozekimessageout while monitoring delivery status directly in the database.

How to configure Kannel SQLBox

Configure SQLBox with the bearerbox and smsbox connection details, then define the SQL insert and sent-message tables. Add the MySQL/MariaDB connection settings so SQLBox can read queued messages and record sent messages. Verify database connectivity and ensure the SQLBox service is running before starting the migration.

Confiugre Kannel SQLBox
Figure 1 - Confiugre Kannel SQLBox

Insert a test SMS record into Kannel’s send_sms table using the existing application or a direct SQL query. SQLBox detects the new row and submits the message through the configured Kannel SMS infrastructure. Use this step to document the current sender, recipient, message, and status-handling workflow.

Insert message to send SMS table
Figure 2 - Insert message to send SMS table

After Kannel processes the queued message, confirm that SQLBox moves or logs it in the sent_sms table. Check that the stored values provide the audit information your application requires after migration. This establishes the current database-driven sending behavior that Ozeki will replace.

Message moved to Sent SMS table
Figure 3 - Message moved to Sent SMS table

Confirm that the message submitted by SQLBox is delivered to the configured SMPP connection. Review Kannel and SMPP logs for acceptance, delivery-report, and error information before switching systems. Resolve any existing routing or connectivity issues so they are not carried into the Ozeki deployment.

Message sent in SMPP connection
Figure 4 - Message sent in SMPP connection

Kannel SQL Box config: 

group = sqlbox
id = sqlbox
smsbox-id = sqlbox
bearerbox-host = 127.0.0.1
bearerbox-port = 13001
smsbox-port = 13005
smsbox-port-ssl = false
sql-log-table = sent_sms
sql-insert-table = send_sms
log-file = "/root/kannel/kannel-snapshot/logs/sqlbox.log"
log-level = 0

# MySQL / MariaDB connection (verified reachable: MySQL 8.0.31)
group = mysql-connection
id = sqlbox
host = 192.168.95.14
port = 3306
username = kanneluser
password = kannelpass
database = kannel
max-connections = 5

How to configure Ozeki SMS Gateway SQL user

In Ozeki SMS Gateway, install an SQL user to enable database-based SMS submission and status updates. This user replaces the separate Kannel SQLBox component by handling database polling directly in the gateway. Assign a clear user name and configure it alongside the SMS service connection that will send the messages.

Install SQL user
Figure 5 - Install SQL user

Configure the SQL user with the database server address, port, database name, and dedicated credentials. Use an account with only the permissions required to read outgoing rows and update message statuses. Test the connection from Ozeki before creating tables or redirecting production application traffic.

Configure database connection
Figure 6 - Configure database connection

Create the Ozeki ozekimessageout and ozekimessagein tables in the target database using the published schema. The outgoing table stores SMS submission fields, delivery status, operator data, and error messages. Preserve the primary-key index and UTF-8 character set to support efficient processing and non-ASCII message text.

Create database tables
Figure 7 - Create database tables

Update the application, integration, or SQL job to insert outgoing SMS messages into ozekimessageout. Populate at least the sender, receiver, message text, and any required reference or message-type fields. Run initial tests with a small controlled batch before redirecting all traffic from Kannel.

Insert message to database
Figure 8 - Insert message to database

Verify that each submitted SMS appears correctly in ozekimessageout and that field values match the intended message. Confirm phone-number formatting, character encoding, sender identity, and application reference values. Ozeki reads these records and forwards them through its configured SMS connection.

Message in database table
Figure 9 - Message in database table

After Ozeki submits and receives delivery information for a message, check the updated status, receivedtime, operator, and errormsg fields. Use these fields to adapt reports or application logic that previously relied on Kannel’s sent_sms data. Keep Kannel available during validation, then retire SQLBox after Ozeki’s database sending and delivery-status flow has been confirmed.

Message status updatetd after deivered
Figure 10 - Message status updatetd after deivered

Ozeki SQL table layout: 

CREATE TABLE ozekimessagein (
    id int(11) NOT NULL auto_increment,
    sender varchar(255) default NULL,
    receiver varchar(255) default NULL,
    msg text default NULL,
    senttime varchar(100) default NULL,
    receivedtime varchar(100) default NULL,
    operator varchar(100) default NULL,
    msgtype varchar(160) default NULL,
    reference varchar(100) default NULL,
    PRIMARY KEY (id)
) charset=utf8; 
ALTER TABLE ozekimessagein ADD INDEX (id) ; 

CREATE TABLE ozekimessageout (
    id int(11) NOT NULL auto_increment,
    sender varchar(255) default NULL,
    receiver varchar(255) default NULL,
    msg text default NULL,
    senttime varchar(100) default NULL,
    receivedtime varchar(100) default NULL,
    reference varchar(100) default NULL,
    status varchar(20) default NULL,
    msgtype varchar(160) default NULL,
    operator varchar(100) default NULL,
    errormsg varchar(250) default NULL,
    PRIMARY KEY (id)
) charset=utf8;
ALTER TABLE ozekimessageout ADD INDEX (id);

More information