[LINUX] Pleasanter .NetCore version installation (CentOS7 .NetCore2.2 SQL Server 2019)

What is written in this article

2020/6 postscript: Support for .NetCore3.1 / PostgreSQL has been announced. Official Announcement: PostgreSQL compatible version has been released.

Click here for the installation procedure for .Net Core 3.1 compatible version Try installing Pleasanter with PostgreSQL on CentOS7 -Qiita Try installing Pleasanter With PostgreSQL on CentOS8. -Qiita

I want to run Plesanter in the newest possible environment. so, ** As of January 5, 2020, Pleasanter does not support .NetCore 3.1 ** Put up with .NetCore2.2 **. NetCore3.1 / SQL Server 2019 does not support CentOS8 ** Put up with CentOS7 ** SQL Server 2019 seems to work with CentOS7 **

This is the installation procedure using the .Net Core version distributed by the official Pleasanter. (Do not build) Pleasanter official installation procedure is a little organized for Linux and installed + It is a work log with the following procedure added.

• Example of configuration file for controlling startup with systemctl Example setting to start Pleasanter at the same time as system startup
• Reverse proxy settings using NginX Setting example for external disclosure on Port 80
• Permission settings on Firewalld Settings to allow access to Pleasanter from the outside

Operating environment

When using Ubuntu, Ubuntu 16.04 seems to be a good choice to meet all the conditions. (If you devise 18.04 seems to work as well) RedHat Enterprise Linux (RHEL) seems to be able to choose various reasonable versions, but RHEL8 is not yet supported. Also, although CentOS is not included in the SQL Server operating environment, [FAQ](https://docs.microsoft.com/ja-jp/sql/linux/sql-server-linux-faq?view=sql-server -Would work with linux-ver15). It is commented. And it's actually working. We decided to use CentOS7 because of a reasonable version of the OS and low installation hurdles.

Introduction flow

• OS introduction: This time, we will proceed assuming that CentOS 7 series is installed.
• Introduced .NetCORE 2.2
• Introduced SQL Server 2019
• Introducing MS SQL Server Command Line Tools
• Introduced Microsoft ODBC Driver 17 for SQL Server
• Introduced GDI +
• SQL Server 2019 configuration
• Run Pleasanter
• Operation check
• Reverse proxy settings
• Firewalld settings
• Outgoing mail server settings
• Additional settings (Reminder function / Backup script)

Introducing .NetCORE 2.2

$ sudo rpm -Uvh https://packages.microsoft.com/config/rhel/7/packages-microsoft-prod.rpm
$ sudo yum -y update
$ sudo yum -y install dotnet-sdk-2.2

Introducing SQL Server 2019

Introducing MS SQL Server

$ sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo
$ sudo yum -y install mssql-server

MS SQL initial setup

$ sudo /opt/mssql/bin/mssql-conf setup
usermod:There is no change
Select the edition of SQL Server:
  1) Evaluation (Free, no product rights, 180 days deadline)
  2) Developer (Free, no product usage 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 have a product key to purchase and enter a license through a retail sales channel.

For more information on editions, see
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x411

To use the paid edition of this software, you need to obtain a separate license from:
Microsoft Volume Licensing Program.
Choosing a paid edition is
You have ensured that you have the correct number of licenses to install and run this software.

Please enter the edition(1-8): 3
The license terms for this product are
/usr/share/doc/mssql-You can browse it on the server or download it from the following location::
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x411

A privacy statement can be found at::
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x411


SQL Server language selection:
(1) English
(2) Deutsch
(3) Espanol
(4) Francais
(5) Italiano
(6)Japanese
(7) ???
(8) Portugues
(9) Русский
(10)Chinese? ?body
(11)Traditional Chinese
Option 1-Enter 11: 6
Enter the SQL Server system administrator password: <Set administrator password>
Confirm and enter the SQL Server system administrator password:
Configuring SQL Server...

ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Setup completed successfully. Starting SQL Server.
$
$ systemctl status mssql-server

Specify a strong character string for the system administrator password (SA password).

Introducing MS SQL Server Command Line Tools

UnixODBC update and mssql-tools introduction Supports SQL Server 2019 since 17.3. Pay attention to the version installed.

$ sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
$ sudo yum remove unixODBC-utf16 unixODBC-utf16-devel
There may be no deletion target. Please proceed to the next without worrying about it.
$ sudo yum install -y mssql-tools unixODBC-devel

I have the following question twice"YES"Please respond
Do you accept the license terms? (Enter YES or NO)
YES

Introduced Microsoft ODBC Driver 17 for SQL Server

Since msodbcsql has already been installed at the same time as MS SQL Server Command Line Tools, it is omitted.

GDI installation

Install GDI + compatible API

$ sudo yum install -y epel-release
$ sudo yum install -y libgdiplus

Introduced Pleasanter

Executable file placement

Obtain it from the Official Download Site. There is a simple questionnaire, so let's fill it out. After extracting the obtained file to a suitable location, replace SetSaPWD in the SaConnectionString line of Rds.json with the SQL Server administrator password.

$ sudo mkdir /Pleasanter
$ unzip -q Pleasanter.NetCore.1.0.0.0.zip
$ sudo mv Pleasanter/* /Pleasanter/
$ sudo vi /Pleasanter/Pleasanter.NetCore/Implem.Pleasanter/App_Data/Parameters/Rds.json
{
    "Provider": "Local",
    "TimeZoneInfo": "Tokyo Standard Time",
    "SaConnectionString": "Server=(local);Database=master;UID=sa;PWD=SetSaPWD;Connection Timeout=30;",
    "OwnerConnectionString": "Server=(local);Database=#ServiceName#;UID=#ServiceName#_Owner;PWD=SetAdminsPWD;Connection Timeout=30;",
    "UserConnectionString": "Server=(local);Database=#ServiceName#;UID=#ServiceName#_User;PWD=SetUsersPWD;Connection Timeout=30;",
    "SqlCommandTimeOut": 600,
    "MinimumTime": 3,
    "DeadlockRetryCount": 4,
    "DeadlockRetryInterval": 1000
}
$

DB configuration for Pleasanter

$ cd /Pleasanter/Pleasanter.NetCore/Implem.CodeDefiner/
$ dotnet Implem.CodeDefiner.NetCore.dll _rds

Pleasanter startup confirmation

If you can confirm the startup, press Ctrl + C to exit.

$ cd ../Implem.Pleasanter/
$ dotnet Implem.Pleasanter.NetCore.dll
warn: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[35]
      No XML encryptor configured. Key {a11dba1c-f2da-4782-ae6d-ace09095eeac} may be persisted to storage in unencrypted form.
Hosting environment: Production
Content root path: /Pleasanter/Pleasanter.NetCore/Implem.Pleasanter
Now listening on: http://localhost:5000
Now listening on: https://localhost:5001
Application started. Press Ctrl+C to shut down.

Service script creation for Pleasanter

Create a service script to automatically start Pleasanter when the OS starts.

$ sudo vi /etc/systemd/system/pleasanter.service
[Unit]
Description = Pleasanter
Documentation =

Wants=network.target
After=network.target

[Service]
ExecStart = /usr/bin/dotnet Implem.Pleasanter.NetCore.dll --urls=http://localhost:5000
WorkingDirectory = /Pleasanter/Pleasanter.NetCore/Implem.Pleasanter
Restart = always
RestartSec = 10
User = root
Group = root
Environment=ASPNETCORE_ENVIRONMENT=Production

[Install]
WantedBy = multi-user.target

$ sudo systemctl daemon-reload
$ sudo systemctl start pleasanter
$ sudo systemctl enable pleasanter

Reverse proxy (NginX) settings

Set the reverse proxy so that a normal web server can be accessed with the same Port80. I won't introduce it here, but I think it's easy to use a reverse proxy when converting to https.

• In the server_name line of the setting example, specify the host name when actually accessing.

$ sudo vi /etc/nginx/conf.d/pleasanter.conf

server {
        listen  80;
    server_name   pleasanter.example.com;
    location / {
        proxy_pass         http://localhost:5000;
        proxy_http_version 1.1;
        proxy_set_header   Upgrade $http_upgrade;
        proxy_set_header   Connection keep-alive;
        proxy_set_header   Host $host;
        proxy_cache_bypass $http_upgrade;
        proxy_set_header   X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header   X-Forwarded-Proto $scheme;
    }
}

$ sudo sysetmctl restart nginx

Firewalld settings

Add a permission setting to 80 / tcp (http) to allow the client to access the web service.

Check valid zone
$ sudo firewall-cmd --get-active-zone
public
  interfaces: enp1s0

Check Firewall rules
$ sudo firewall-cmd --list-all

Port 80/Unprotect tcp
$ sudo firewall-cmd --permanent --zone=public --add-port=80/tcp
success
$ sudo firewall-cmd --reload
$ sudo firewall-cmd --list-all

Outgoing mail server settings

• Specify the mail server used for sending to SmtpHost. Set other items as needed. After changing the settings, you need to restart Pleasanter.

[Pleasanter Mail related settings](https://github.com/Implem/Implem.Pleasanter/wiki/%E3%83%91%E3%83%A9%E3%83%A1%E3%83%BC%E3% 82% BF% E8% A8% AD% E5% AE% 9A% EF% BC% 9A Mail.json)

$ sudo vi /Pleasanter/publish/Implem.Pleasanter/App_Data/Parameters/Mail.json
{
    "SmtpHost": "localhost",
    "SmtpPort": 25,
    "SmtpUserName": null,
    "SmtpPassword": null,
    "SmtpEnableSsl": false,
    "FixedFrom": null,
    "AllowedFrom": null,
    "SupportFrom": "\"Pleasanter.org\" <[email protected]>",
    "InternalDomains": "",
    "AddressValidation": "\\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]+\\b"
}

Reminder settings

Introduction of Python36, pyodbc / Reminder settings

• Since the EPEL repository is registered, it can be installed with yum. python36 is used by Reminder and pyodbc is used by backup.

$ sudo yum -y install python36
$ sudo mkdir /Pleasanter/tools
$ sudo cp -P /Pleasanter/Implem.Pleasanter.NetCore/Tools/Reminder.py /Pleasanter/tools/
$ sudo vi /Pleasanter/tools/Reminder.py

Change the localhost part to a host name that can be accessed by the client
urllib.request.Request("http://localhost/pleasanter/reminderschedules/remind?NoLog=1")

cron settings
$ sudo crontab -e
@reboot python3 /opt/pleasanter-tools/Reminder.py

$ python3 /opt/pleasanter-tools/Reminder.py

MS SQL backup script installation

When I run it on python3, I get angry when I don't have pyodbc. When I ran it with python, it worked fine whether pyodbc introduced from epel can be used.

$ sudo yum -y install pyodbc
$ sudo vi /Pleasanter/Implem.Pleasanter.NetCore/Tools/DbBackup.py

Set the sa password on the following line
pwd = "SetSaPWD"

$ python /Pleasanter/Implem.Pleasanter.NetCore/Tools/DbBackup.py
Backedup: /var/opt/mssql/data/backup/Implem.Pleasanter/20190815_121020.bak
Shrinked: Implem.Pleasanter
$

Initial account information

Note

sa password reset method

I was in trouble during work, so make a note

$ sudo systemctl stop mssql-server
$ sudo /opt/mssql/bin/mssql-conf set-sa-password
Enter the SQL Server system administrator password:　<New administrator password>
Confirm and enter the SQL Server system administrator password:
Configuring SQL Server...

ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
The system administrator password has changed.
'sudo systemctl start mssql-server'To start SQL Server.
$