Use a MSSQL database within a CallySquare project

Owned by Valerio Ciotta
Last updated: May 04, 2022 by XCALLY Knowledge (Unlicensed)
3 min read



On this Page:



Important

Please stop all the callcenter activities before starting the MSSQL odbc driver installation. It may cause server downtime.

1 Prerequisites and minimum requirements


Important

The minimum working and supported configuration is:

MSSQL Server 2008 R2 Express installed on Windows 2008 R2 SP1

2 Download the Microsoft SQL ODBC drivers

[root@localhost ~]# cd /usr/src
[root@localhost src]# curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
193   193  193   193    0     0    566      0 --:--:-- --:--:-- --:--:--  5216


3 Backup the current odbc configuration

[root@localhost src]# mv /etc/odbc.ini /etc/odbc.ini.bck


4 Install the MSSQL ODBC drivers and the dependencies 

Important

Please note the odbc driver release may change, check the current driver release here 

[root@localhost src]# yum remove unixODBC

[root@localhost src]# sudo ACCEPT_EULA=Y yum install msodbcsql mssql-tools --nogpgcheck

[root@localhost src]# sudo yum install unixODBC-devel freetds freetds-devel mysql-connector-odbc


5 Restore the original odbc configuration

[root@localhost src]# mv /etc/odbc.ini.bck /etc/odbc.ini
mv: overwrite `/etc/odbc.ini'? y

6 Configure the MSSQL ODBC source into the Motion GUI

Add a new ODBC source and insert the MSSQL connection string (check and copy the correct driver name into /etc/odbc.ini or /etc/odbcinst.ini eg. [ODBC Driver 13 for SQL Server]):


DRIVER=ODBC Driver 13 for SQL Server;SERVER=<SERVER IP>;UID=<USER>;PWD=<PASSWORD>;DATABASE=<DBNAME>

Example using standard port 1433 (port can be omitted):
DRIVER=ODBC Driver 13 for SQL Server;SERVER=192.168.2.148;UID=xcally;PWD=xcally;DATABASE=XCALLY

Example using non standard port (eg. 11433)
DRIVER=ODBC Driver 13 for SQL Server;SERVER=192.168.2.148,11433;UID=xcally;PWD=xcally;DATABASE=XCALLY

Important

Don't forget to write the port after IP address separated by comma if you are using a port different than default 1433


If everything is ok, the connection should be verified clicking TEST ODBC in the ODBC specific drop-down menu and as the result should be appear a popup on right top corner saying "ODBC properly tested! Connection Succeded"

If something wrong (popup with error message) than check Server IP, port, UID and password. If you are sure they are correct go on reading this wiki.


Important

ATTENTION:

Port used by default is 1433. Make sure in SQL Server Configuration Manager that network protocol TCP/IP is enabled and that you disabled "TCP dynamic ports" (leave field empty) and write port 1433 (or other port if using a different port) in "TCP port" field (IPAll section)

After modify don't forget to restart SQL service



7 Use the MSSQL database in the CallySquare project