Tag: ms sql

  • Enable Remote Connection in MS SQL Server

    Enable Remote Connection in MS SQL Server

    MS SQL Server does not allow remote connection by default. To enable MS SQL remote connection, you need to open port 1433 in the firewall. After the port is open, you need to enable TCP/IP protocol in MS SQL Server.

    Start SQL Server Management Studio, take the property of the server

    Enable SQL Server and Windows Authentication mode

    SQL server autentication

    Under Connections, allow Allow Remote Connections

    SQL Server allow remote connection

    Start SQL Configuration Manager.

    Start SQL Configuration Manager

    Go to

    SQL Configuration Manager > SQL Server Network Configuration > Protocols for MSSQL SERVER

    Enable TCP/IP

    MS SQL enable TCP/IP

    Now you need to restart MS SQL server, after that, you should be able to connect to MS SQL server remotely.

  • Install PHP drivers for Microsoft SQL Server on Ubuntu PHP 7.2

    Install PHP drivers for Microsoft SQL Server on Ubuntu PHP 7.2

    On Ubuntu 18.04 server running PHP 7.2, i want to install Microsoft SQL Server module for PHP. You can find PHP module for SQL server at

    https://github.com/microsoft/msphpsql

    At the time of writing this PHP module only support PHP 7.4 and newer. SO i need to find older version that supported PHP 7.2. On checking release page, i found version 5.8.0 supported PHP 7.2

    First install php7.2 dev package with

    apt install php7.2-dev

    Instal php modules with pcel

    pecl install sqlsrv-5.8.0

    During install, i got error

    configure: creating ./config.status
config.status: creating config.h
config.status: executing libtool commands
running: make
/bin/bash /tmp/pear/temp/pear-build-rootnuxjAy/sqlsrv-5.8.0/libtool --mode=compile g++ -std=c++11 -I. -I/tmp/pear/temp/sqlsrv -DPHP_ATOM_INC -I/tmp/pear/temp/pear-build-rootnuxjAy/sqlsrv-5.8.0/include -I/tmp/pear/temp/pear-build-rootnuxjAy/sqlsrv-5.8.0/main -I/tmp/pear/temp/sqlsrv -I/usr/include/php/20170718 -I/usr/include/php/20170718/main -I/usr/include/php/20170718/TSRM -I/usr/include/php/20170718/Zend -I/usr/include/php/20170718/ext -I/usr/include/php/20170718/ext/date/lib -I/tmp/pear/temp/sqlsrv/shared/  -DHAVE_CONFIG_H  -std=c++11 -D_FORTIFY_SOURCE=2 -O2 -fstack-protector   -c /tmp/pear/temp/sqlsrv/conn.cpp -o conn.lo
libtool: compile:  g++ -std=c++11 -I. -I/tmp/pear/temp/sqlsrv -DPHP_ATOM_INC -I/tmp/pear/temp/pear-build-rootnuxjAy/sqlsrv-5.8.0/include -I/tmp/pear/temp/pear-build-rootnuxjAy/sqlsrv-5.8.0/main -I/tmp/pear/temp/sqlsrv -I/usr/include/php/20170718 -I/usr/include/php/20170718/main -I/usr/include/php/20170718/TSRM -I/usr/include/php/20170718/Zend -I/usr/include/php/20170718/ext -I/usr/include/php/20170718/ext/date/lib -I/tmp/pear/temp/sqlsrv/shared/ -DHAVE_CONFIG_H -std=c++11 -D_FORTIFY_SOURCE=2 -O2 -fstack-protector -c /tmp/pear/temp/sqlsrv/conn.cpp  -fPIC -DPIC -o .libs/conn.o
In file included from /tmp/pear/temp/sqlsrv/shared/typedefs_for_linux.h:23:0,
                 from /tmp/pear/temp/sqlsrv/shared/xplat_winnls.h:24,
                 from /tmp/pear/temp/sqlsrv/shared/FormattedPrint.h:24,
                 from /tmp/pear/temp/sqlsrv/shared/core_sqlsrv.h:41,
                 from /tmp/pear/temp/sqlsrv/php_sqlsrv_int.h:25,
                 from /tmp/pear/temp/sqlsrv/conn.cpp:24:
/tmp/pear/temp/sqlsrv/shared/xplat.h:30:10: fatal error: sql.h: No such file or directory
 #include 
          ^~~~~~~
compilation terminated.
Makefile:194: recipe for target 'conn.lo' failed
make: *** [conn.lo] Error 1
ERROR: `make' failed
root@server:~#

    This is fixed with command

    apt-get install unixodbc-dev

    install pdo_sqlsrv with

    pecl install pdo_sqlsrv-5.8.0

    Run

    printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/7.2/mods-available/sqlsrv.ini
printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/7.2/mods-available/pdo_sqlsrv.ini

    Enabe PHP modules with

    phpenmod -v 7.2 sqlsrv pdo_sqlsrv

    Restart Apache, now phpinfo() shows pdo_sqlsrv

    But when accessing PHP script that connect to MS SQL server, i get error

    This extension requires the Microsoft ODBC Driver for SQL Server to communicate with SQL Server. Access the following URL to download the ODBC Driver for SQL Server for x64

    To fix this, do

    curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

    For Ubuntu 16.04

    curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

    For Ubuntu 18.04

    curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

    For Ubuntu 20.04

    curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

    Ubuntu 20.10

    curl https://packages.microsoft.com/config/ubuntu/20.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

    Update apt cahe

    apt-get update

    Install Microsoft ODBC

    apt-get install -y msodbcsql17

    Optional: for bcp and sqlcmd

    apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
apt-get install -y unixodbc-dev

    Now php MS SQL module will work. You can find sample PHP code at

    https://gist.github.com/serverok/456b3d1d7295463df42c9822e8db3e5b
    https://github.com/microsoft/msphpsql/blob/master/sample/pdo_sqlsrv_sample.php

    Here are microsoft documentation

    https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

    https://docs.microsoft.com/en-us/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-ver15

    See PHP