Ms Sql Server – My Projects & Live

Install Microsoft SQL Server 2022 On Ubuntu 22.04

Ref: https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-ver16&tabs=ubuntu2204

1. Update and upgrade the packages on ubuntu 22.04
[codesyntax lang=”bash”]

sudo apt update
sudo apt upgrade -y

1 2	sudo apt update sudo apt upgrade -y

[/codesyntax]

2. Download the public key, convert from ASCII to GPG format, and write it to the required location:
[codesyntax lang=”bash”]

curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg

1	curl -fsSL https://packages.microsoft.com/keys/microsoft.asc \| sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg

[/codesyntax]

3. Manually download and register the SQL Server Ubuntu repository:
[codesyntax lang=”bash”]

curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list | sudo tee /etc/apt/sources.list.d/mssql-server-2022.list

1	curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list \| sudo tee /etc/apt/sources.list.d/mssql-server-2022.list

[/codesyntax]

4. Run the following commands to install SQL Server:
[codesyntax lang=”bash”]

sudo apt-get update
sudo apt-get install -y mssql-server
...
Setting up libc++abi1-14:amd64 (1:14.0.0-1ubuntu1.1) ...
Setting up libc++1-14:amd64 (1:14.0.0-1ubuntu1.1) ...
Setting up libc++1:amd64 (1:14.0-55~exp2) ...
Setting up mssql-server (16.0.4175.1-3) ...

+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

Processing triggers for man-db (2.10.2-1) ...
Processing triggers for libc-bin (2.35-0ubuntu3.8) ...

sudo apt-get update

sudo apt-get install -y mssql-server

...

Setting up libc++abi1-14:amd64 (1:14.0.0-1ubuntu1.1) ...

Setting up libc++1-14:amd64 (1:14.0.0-1ubuntu1.1) ...

Setting up libc++1:amd64 (1:14.0-55~exp2) ...

Setting up mssql-server (16.0.4175.1-3) ...

+--------------------------------------------------------------+

Please run 'sudo /opt/mssql/bin/mssql-conf setup'

to complete the setup of Microsoft SQL Server

+--------------------------------------------------------------+

Processing triggers for man-db (2.10.2-1) ...

Processing triggers for libc-bin (2.35-0ubuntu3.8) ...

[/codesyntax]

5. After the package installation finishes, run mssql-conf setup and follow the prompts to set the sa password and choose your edition.
Just choose ‘Developer’ version. type 2. My local sa password is {my-nick-name-first-uppercase}@123
[codesyntax lang=”bash”]

satria@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) - CPU core utilization restricted to 20 physical/40 hyperthreaded
  7) Enterprise Core (PAID) - CPU core utilization up to Operating System Maximum
  8) I bought a license through a retail sales channel and have a product key to enter.
  9) Standard (Billed through Azure) - Use pay-as-you-go billing through Azure.
 10) Enterprise Core (Billed through Azure) - Use pay-as-you-go billing through Azure.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=2109348&amp;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.
By choosing an edition billed Pay-As-You-Go through Azure, you are verifying 
that the server and SQL Server will be connected to Azure by installing the 
management agent and Azure extension for SQL Server.

Enter your edition(1-10): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from: https://aka.ms/useterms

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&amp;clcid=0x409

Do you accept the license terms? [Yes/No]:yes

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 /etc/systemd/system/multi-user.target.wants/mssql-server.service → /lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

satria@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) - CPU core utilization restricted to 20 physical/40 hyperthreaded

7) Enterprise Core (PAID) - CPU core utilization up to Operating System Maximum

8) I bought a license through a retail sales channel and have a product key to enter.

9) Standard (Billed through Azure) - Use pay-as-you-go billing through Azure.

10) Enterprise Core (Billed through Azure) - Use pay-as-you-go billing through Azure.

Details about editions can be found at

https://go.microsoft.com/fwlink/?LinkId=2109348&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.

By choosing an edition billed Pay-As-You-Go through Azure, you are verifying

that the server and SQL Server will be connected to Azure by installing the

management agent and Azure extension for SQL Server.

Enter your edition(1-10): 2

The license terms for this product can be found in

/usr/share/doc/mssql-server or downloaded from: https://aka.ms/useterms

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:

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 /etc/systemd/system/multi-user.target.wants/mssql-server.service → /lib/systemd/system/mssql-server.service.

Setup has completed successfully. SQL Server is now starting.

[/codesyntax]

6. Once the configuration is done, verify that the service is running:
[codesyntax lang=”bash”]

systemctl status mssql-server --no-pager

1	systemctl status mssql-server --no-pager

[/codesyntax]

Install the SQL Server command-line tools (sqlcmd)

1. Import the public repository GPG keys.
[codesyntax lang=”bash”]

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

1	curl https://packages.microsoft.com/keys/microsoft.asc \| sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

[/codesyntax]

2. Register the Microsoft Ubuntu repository. For Ubuntu 22.04, use the following command:
[codesyntax lang=”bash”]

curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

1	curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list \| sudo tee /etc/apt/sources.list.d/mssql-release.list

[/codesyntax]

3. Update the sources list and run the installation command with the unixODBC developer package.
[codesyntax lang=”bash”]

sudo apt-get update
sudo apt-get install mssql-tools18 unixodbc-dev

1 2	sudo apt-get update sudo apt-get install mssql-tools18 unixodbc-dev

[/codesyntax]

To update to the latest version of mssql-tools, run the following commands:
[codesyntax lang=”bash”]

sudo apt-get update  
sudo apt-get install mssql-tools18

1 2	sudo apt-get update sudo apt-get install mssql-tools18

[/codesyntax]

4. Add /opt/mssql-tools18/bin/ to your PATH environment variable in a bash shell.

To make sqlcmd and bcp accessible from the bash shell for login sessions, modify your PATH in the ~/.bash_profile file with the following command:
[codesyntax lang=”bash”]

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' &gt;&gt; ~/.bash_profile
source ~/.bash_profile

1 2	echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile source ~/.bash_profile

[/codesyntax]

5. Test sqlcmd with ‘sqlcmd -?’
[codesyntax lang=”bash”]

satria@teddy:~$ sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 18.4.0001.1 Linux
Copyright (C) 2017 Microsoft Corporation. All rights reserved.

usage: sqlcmd            [-U login id]          [-P password]
  [-S server or Dsn if -D is provided] 
  [-H hostname]          [-E trusted connection]
  ...
  [-G use Microsoft Entra ID for authentication]
  [-? show syntax summary]

satria@teddy:~$ sqlcmd -?

Microsoft (R) SQL Server Command Line Tool

Version 18.4.0001.1 Linux

usage: sqlcmd [-U login id] [-P password]

[-S server or Dsn if -D is provided]

[-H hostname] [-E trusted connection]

...

[-G use Microsoft Entra ID for authentication]

[-? show syntax summary]

[/codesyntax]

Connect locally. I tried to use : sqlcmd -S localhost -U sa -P your_password
but it showed up this error
[codesyntax lang=”bash”]

satria@teddy:~$ sqlcmd -S localhost -U sa -P your_password
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate].
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722.

satria@teddy:~$ sqlcmd -S localhost -U sa -P your_password

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate].

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722.

[/codesyntax]

it turned out I need to add -C parameter at the end like this
[codesyntax lang=”bash”]

satria@teddy:~$ sqlcmd -S localhost -U sa -P Teddy@123 -C
1&gt; SELECT Name from sys.databases;
2&gt; GO
Name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            

(4 rows affected)
1&gt; quit

satria@teddy:~$ sqlcmd -S localhost -U sa -P Teddy@123 -C

1> SELECT Name from sys.databases;

2> GO

Name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

(4 rows affected)

1> quit

[/codesyntax]

Create a new database

For example create a new database TestDB from sqlcmd
[codesyntax lang=”bash”]

1&gt; CREATE DATABASE TestDB;
2&gt; GO

1 2	1> CREATE DATABASE TestDB; 2> GO

[/codesyntax]

Create a new table dbo.Inventory and Insert data
[codesyntax lang=”bash”]

1&gt; USE TestDB;
2&gt; GO
1&gt; CREATE TABLE dbo.Inventory
2&gt; (
3&gt; id INT,
4&gt; name NVARCHAR (50),
5&gt; quantity INT,
6&gt; PRIMARY KEY (id)
7&gt; );
8&gt; GO
1&gt; INSERT INTO dbo.Inventory VALUES (1, 'banana', 150);
2&gt; GO

(1 rows affected)
1&gt; INSERT INTO dbo.Inventory VALUES (2, 'orange', 154);
2&gt; GO

(1 rows affected)
1&gt; SELECT * FROM dbo.Inventory;
2&gt; GO
id          name                                               quantity   
----------- -------------------------------------------------- -----------
          1 banana                                                     150
          2 orange                                                     154

(2 rows affected)
1&gt; SELECT * FROM dbo.Inventory;
2&gt; GO
id          name                                               quantity   
----------- -------------------------------------------------- -----------
          1 banana                                                     150
          2 orange                                                     154

(2 rows affected)
1&gt; quit

1> USE TestDB;

2> GO

1> CREATE TABLE dbo.Inventory

2> (

3> id INT,

4> name NVARCHAR (50),

5> quantity INT,

6> PRIMARY KEY (id)

7> );

8> GO

1> INSERT INTO dbo.Inventory VALUES (1, 'banana', 150);

2> GO

(1 rows affected)

1> INSERT INTO dbo.Inventory VALUES (2, 'orange', 154);

2> GO

(1 rows affected)

1> SELECT * FROM dbo.Inventory;

2> GO

id name quantity

----------- -------------------------------------------------- -----------

1 banana 150

2 orange 154

(2 rows affected)

1> SELECT * FROM dbo.Inventory;

2> GO

id name quantity

----------- -------------------------------------------------- -----------

1 banana 150

2 orange 154

(2 rows affected)

1> quit

[/codesyntax]

Install DBeaver (https://dbeaver.io/)
This is Database GUI for many databases including MS SQL Server

Download it https://dbeaver.io/download/
I used Ubuntu PPA
[codesyntax lang=”bash”]

sudo add-apt-repository ppa:serge-rider/dbeaver-ce
sudo apt-get update
sudo apt-get install dbeaver-ce

sudo add-apt-repository ppa:serge-rider/dbeaver-ce

sudo apt-get update

sudo apt-get install dbeaver-ce

[/codesyntax]

Open DBeaver CE and select to the MS SQL Server

Connect to the MS SQL Server but it needs to use JDBC driver. Install it directly from the DBeaver

If everything is okay, it’ll look like this

on the DBeaver GUI you can add a new row. for example add a new row
ID: 3
Name: Apple
Quantity: 158
Then save

on the sqlcmd should reflect the changes
[codesyntax lang=”bash”]

1&gt; USE TestDB;
2&gt; GO
Changed database context to 'TestDB'.
1&gt; SELECT * FROM dbo.Inventory;
2&gt; GO
id          name                                               quantity   
----------- -------------------------------------------------- -----------
          1 banana                                                     150
          2 orange                                                     154
          3 Apple                                                      158

(3 rows affected)

1> USE TestDB;

2> GO

Changed database context to 'TestDB'.

1> SELECT * FROM dbo.Inventory;

2> GO

id name quantity

----------- -------------------------------------------------- -----------

1 banana 150

2 orange 154

3 Apple 158

(3 rows affected)

[/codesyntax]

Install Microsoft Sql Server 2017 And The PHP Extension On Ubuntu 16.04

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!

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

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

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

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) ...

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.

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.

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

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]

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)

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

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

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

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

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

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

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

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???)

sudo gedit /etc/php/7.2/fpm/php.ini

1	sudo gedit /etc/php/7.2/fpm/php.ini

THEN INSERT THESE TWO LINES:

extension=sqlsrv.so
extension=pdo_sqlsrv.so

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’

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/

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

sudo service php7.2-fpm restart

1	sudo service php7.2-fpm restart

CHECK THEM IN phpinfo: http://localhost:8080/phpinfo.php

Create a database ‘SampleDB’ for your application

sqlcmd -S localhost -U sa -P Teddy@234 -Q "CREATE DATABASE SampleDB;"

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

teddy@teddy:~/Documents/works$ mkdir SqlServerSample
teddy@teddy:~/Documents/works$ cd SqlServerSample/

1 2	teddy@teddy:~/Documents/works$ mkdir SqlServerSample teddy@teddy:~/Documents/works$ cd SqlServerSample/

Create a new file ‘connect.php’

<?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";
?>

<?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:

teddy@teddy:~/Documents/works/SqlServerSample$ php connect.php 
OKAY, Connected again!

1 2	teddy@teddy:~/Documents/works/SqlServerSample$ php connect.php OKAY, Connected again!

NOTE: ON CLI I GOT THIS WARNING:

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!

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

Uninstalling SQL Server on Ubuntu

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}

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}