Ref: https://www.microsoft.com/en-us/sql-server/developer-get-started/php/ubuntu
1. Register the Microsoft Linux repositories and add their keys.
Note: it’d be better to use ‘sudo su’ first!
|
1 2 3 4 5 6 7 8 9 10 11 |
teddy@teddy:~$ curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 983 100 983 0 0 2323 0 --:--:-- --:--:-- --:--:-- 2329 OK teddy@teddy:~$ curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list | sudo tee /etc/apt/sources.list.d/mssql-server-2017.list % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 91 100 91 0 0 102 0 --:--:-- --:--:-- --:--:-- 102 deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial main |
2. Install SQL Server.
NOTE: I USE ‘sudo apt-get install mssql-server=14.0.3192.2-2’ INSTEAD OF ‘sudo apt-get install mssql-server’ BECAUSE I GOT AN ERROR FOR SQLCMD ‘Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746.’
REF: https://stackoverflow.com/questions/57265913/error-tcp-provider-error-code-0x2746-during-the-sql-setup-in-linux-through-te
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
sudo apt-get update sudo apt-get install mssql-server=14.0.3192.2-2 ... Setting up mssql-server (14.0.3223.3-15) ... +--------------------------------------------------------------+ Please run 'sudo /opt/mssql/bin/mssql-conf setup' to complete the setup of Microsoft SQL Server +--------------------------------------------------------------+ SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. Processing triggers for libc-bin (2.23-0ubuntu10) ... |
3. Setup your SQL Server.
Note: I choose ‘Developer’ edition.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
teddy@teddy:~$ sudo /opt/mssql/bin/mssql-conf setup Choose an edition of SQL Server: 1) Evaluation (free, no production use rights, 180-day limit) 2) Developer (free, no production use rights) 3) Express (free) 4) Web (PAID) 5) Standard (PAID) 6) Enterprise (PAID) 7) Enterprise Core (PAID) 8) I bought a license through a retail sales channel and have a product key to enter. Details about editions can be found at https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409 Use of PAID editions of this software requires separate licensing through a Microsoft Volume Licensing program. By choosing a PAID edition, you are verifying that you have the appropriate number of licenses in place to install and run this software. Enter your edition(1-8): 2 The license terms for this product can be found in /usr/share/doc/mssql-server or downloaded from: https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409 The privacy statement can be viewed at: https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409 Do you accept the license terms? [Yes/No]:yes Enter the SQL Server system administrator password: The specified password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters Enter the SQL Server system administrator password: Confirm the SQL Server system administrator password: Configuring SQL Server... ForceFlush is enabled for this instance. ForceFlush feature is enabled for log durability. Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /lib/systemd/system/mssql-server.service. Setup has completed successfully. SQL Server is now starting. |
SORRY, ONLY ADMIN CAN SHOW THIS!
Check the mssql-server service status
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
teddy@teddy:~$ sudo service mssql-server status ● mssql-server.service - Microsoft SQL Server Database Engine Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor pre Active: active (running) since Kam 2019-09-05 10:37:58 WIB; 2min 40s ago Docs: https://docs.microsoft.com/en-us/sql/linux Main PID: 21988 (sqlservr) Tasks: 142 Memory: 665.3M CPU: 5.910s CGroup: /system.slice/mssql-server.service ├─21988 /opt/mssql/bin/sqlservr └─22018 /opt/mssql/bin/sqlservr Sep 05 10:38:00 teddy sqlservr[21988]: [78B blob data] Sep 05 10:38:00 teddy sqlservr[21988]: [84B blob data] Sep 05 10:38:00 teddy sqlservr[21988]: [122B blob data] Sep 05 10:38:00 teddy sqlservr[21988]: [145B blob data] Sep 05 10:38:01 teddy sqlservr[21988]: [66B blob data] Sep 05 10:38:01 teddy sqlservr[21988]: [75B blob data] Sep 05 10:38:01 teddy sqlservr[21988]: [96B blob data] Sep 05 10:38:01 teddy sqlservr[21988]: [100B blob data] Sep 05 10:38:01 teddy sqlservr[21988]: [71B blob data] Sep 05 10:38:01 teddy sqlservr[21988]: [124B blob data] |
NOTE: ERROR LOG IN /var/opt/mssql/log/error
MUST OPEN THE DIR WITH ‘sudo su’
4. Install the ODBC Driver and SQL Command Line Utility for SQL Server (SQLCMD)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
teddy@teddy:~$ sudo su root@teddy:/home/teddy# curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 79 100 79 0 0 89 0 --:--:-- --:--:-- --:--:-- 89 root@teddy:/home/teddy# exit exit sudo apt-get update sudo ACCEPT_EULA=Y apt-get install msodbcsql17 mssql-tools echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc sudo apt-get install unixodbc-dev |
After installing SQLCMD, you can connect to SQL Server using the following command: sqlcmd -S localhost -U sa -P Yourpassword
SORRY, ONLY ADMIN CAN SHOW THIS!
Install the PHP Driver for SQL Server
|
1 2 3 4 5 6 7 |
teddy@teddy:~$ sudo pecl install sqlsrv ... Build process completed successfully Installing '/usr/lib/php/20170718/sqlsrv.so' install ok: channel://pecl.php.net/sqlsrv-5.6.1 configuration option "php_ini" is not set to php.ini location You should add "extension=sqlsrv.so" to php.ini |
THEN
|
1 2 3 4 5 6 7 |
teddy@teddy:~$ sudo pecl install pdo_sqlsrv ... Build process completed successfully Installing '/usr/lib/php/20170718/pdo_sqlsrv.so' install ok: channel://pecl.php.net/pdo_sqlsrv-5.6.1 configuration option "php_ini" is not set to php.ini location You should add "extension=pdo_sqlsrv.so" to php.ini |
THEN
|
1 2 3 4 5 |
teddy@teddy:~$ sudo su root@teddy:/home/teddy# echo extension=pdo_sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/30-pdo_sqlsrv.ini root@teddy:/home/teddy# echo extension=sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/20-sqlsrv.ini root@teddy:/home/teddy# exit exit |
NOTE: IT’D COPY THE FILES ’20-sqlsrv.ini’ AND ’30-pdo_sqlsrv.ini’ TO DIR /etc/php/7.2/cli/conf.d/
THEN
MODIFY ‘php.ini’ file for php7.2 fpm and (cli NO NEED???)
|
1 |
sudo gedit /etc/php/7.2/fpm/php.ini |
THEN INSERT THESE TWO LINES:
|
1 2 |
extension=sqlsrv.so extension=pdo_sqlsrv.so |
NO NEED –>PLS DO THE SAME FOR ‘cli’: sudo gedit /etc/php/7.2/cli/php.ini
THEN COPY THE TWO INI FILES FROM ‘cli’ TO ‘fpm’
|
1 2 |
sudo cp /etc/php/7.2/cli/conf.d/20-sqlsrv.ini /etc/php/7.2/fpm/conf.d/ sudo cp /etc/php/7.2/cli/conf.d/30-pdo_sqlsrv.ini /etc/php/7.2/fpm/conf.d/ |
THEN RESTART PHP FPM
|
1 |
sudo service php7.2-fpm restart |
CHECK THEM IN phpinfo: http://localhost:8080/phpinfo.php
Create a database ‘SampleDB’ for your application
|
1 |
sqlcmd -S localhost -U sa -P Teddy@234 -Q "CREATE DATABASE SampleDB;" |
The DB will be exist in /var/opt/mssql/data/SampleDB.mdf and the log file.
Create a PHP app that connects to SQL Server and executes queries
|
1 2 |
teddy@teddy:~/Documents/works$ mkdir SqlServerSample teddy@teddy:~/Documents/works$ cd SqlServerSample/ |
Create a new file ‘connect.php’
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?php ini_set('display_errors', 1); error_reporting(E_ALL); $serverName = "localhost"; $connectionOptions = array( "Database" => "SampleDB", "Uid" => "sa", "PWD" => "Teddy@234" ); //Establishes the connection $conn = sqlsrv_connect($serverName, $connectionOptions); if($conn) echo "OKAY, Connected again!\n"; ?> |
RUN ON CLI:
|
1 2 |
teddy@teddy:~/Documents/works/SqlServerSample$ php connect.php OKAY, Connected again! |
NOTE: ON CLI I GOT THIS WARNING:
|
1 2 3 |
PHP Warning: PHP Startup: Unable to load dynamic library 'pdo_sqlsrv' (tried: /usr/lib/php/20170718/pdo_sqlsrv (/usr/lib/php/20170718/pdo_sqlsrv: cannot open shared object file: No such file or directory), /usr/lib/php/20170718/pdo_sqlsrv.so (/usr/lib/php/20170718/pdo_sqlsrv.so: undefined symbol: php_pdo_unregister_driver)) in Unknown on line 0 PHP Warning: Module 'sqlsrv' already loaded in Unknown on line 0 OKAY, Connected! |
SOLUTION: REMOVE/COMMENT THE EXTENSIONS ‘sqlsvr’ AND ‘pdo_sqlsvr’ IN /etc/php/7.2/cli/php.ini FILES!
CHECK THE LOADED MODULE WITH: php -m
IT SHOULD BE ALREADY LOADED FOR CLI SO NO NEED TO INCLUDE THEM IN CLI php.ini BUT IT NEED TO INCLUDE FOR FPM php.ini !
ALSO RUN VIA WEB: http://localhost:8080/works/SqlServerSample/connect.php
UNINSTALL MS SQL SERVER
REF: https://dba.stackexchange.com/questions/174175/how-do-i-totally-remove-sql-server-2017-on-ubuntu
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
sudo service mssql-server stop sudo add-apt-repository --remove "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)" # Remove those packages from the system. sudo apt-get --purge remove mssql-server mssql-server-fts mssql-tools msodbcsql unixodbc-dev sudo apt-get remove mssql-server msodbcsql unixodbc-dev # Delete the SQL Server repositories sudo rm /etc/apt/sources.list.d/mssql-* /etc/apt/sources.list.d/msprod* # Remove the Microsoft keys. sudo apt-key del "BC52 8686 B50D 79E3 39D3 721C EB3E 94AD BE12 29CF" # Delete the Microsoft created directories sudo rm -rf /var/opt/mssql/ /opt/{microsoft,mssql-tools,mssql}/ /usr/share/doc/{msodbcsql,mssql-server,mssql-tools} |