NethServer and Microsoft SQL Server integration

Feature
1

Hi all, I’m glad to announce that we have a beta relase of NethServer MsSQL package!
We need your help to test it. I write here some info and you can install the package on a testing machine and report your results here.

  1. Install the package
    yum install -y nethserver-mssql --enablerepo=nethforge-testing

  2. Go in Cockpit Application menu and open NethServer MsSQL settings. You’ll see that it’s necessary a configuration because mssql-server package is not installed yet. Click on “Install package”.

  3. You’ll be redirect to Settings page: choose the version (express is the default and is free) and click on Save. Download will starts (this phase can be so long, depending on your internet connection).

  4. Once the package is installed perform these tests:
    4.1. Go under Settings page and copy SA password. Try to connect from SQL Management Studio using the IP or hostname and this password for SA user.
    4.2. Change this password with the “Change password” button and then try to connect with the new password.
    4.3. Go under databases page and create a new database. Check is created fine. Try to recreate it, with the same name and check you cannot do it. Go under SQL Management Studio and check database is showed.
    4.4. Check logs are displayed properly.
    4.5. Check dashboard is showing correct informations.
    4.6. Reboot the server and check mssql-server service starts fine.
    4.7. Connect to SQL Management Studio and try to restore a .bak file.

Note: you can use also sqlcmd utility instead of SQL Management Studio windows application.
SQL Server requires 2GB of RAM, otherwise installation will fails.

Thanks to all!

Many thanks to @giacomo and @davidep for support: you are special guys!

8 Likes
3

Which version of mssql server is, the 2016, 2017?
I wonder if exists the equivalent of mssql desktop and if this support AD authentication.
Hopefully I’ll try this in an opportunity soon.

4

mssql server 2019 (15.0)

First of all thanks for your great work! :+1:

Installation of mssql went fine, server logs look ok and cockpit dashboard too.

The installation of Management studio forced a restart of my PC so I had to restart my test VM too.

I couldn’t copy the password from cockpit ui with Firefox 73.0.1. It works with Chrome.

Password change was not possible, change button does not work in both browsers.

I had to disable firewall with shorewall clear to make login from Management studio 18.4 work.

I accidentally tried to install/download mssql without internet connection, there’s no error and the cockpit message shows 0% forever. (just cosmetic)

More tests will follow…

5 Likes
5

Confirm!

You are right… maybe we should add a Copy button.

I will investigate on it… during development it was working fine.

Right. We could add signal-event nethserver-firewall-base-update at the end of download. Shorewall reload is not enough.

I’m going to look into it to find a workable solution for this. Edit: cannot reproduce. With no internet connection:

Schermata 2020-03-01 alle 16.05.07
Schermata 2020-03-01 alle 16.05.072348×504 77.1 KB

Thanks a lot @mrmarkuz!

2 Likes
6

Microsoft wrote a doc for this, but I never used it Tutorial: Use Active Directory authentication for SQL Server on Linux - SQL Server | Microsoft Learn

1 Like
7

Good idea!

signal-event firewall-adjust works too.

https://docs.nethserver.org/projects/nethserver-devel/en/latest/services.html#add-a-new-network-service

Database creation with cockpit works. DB creation with same name is forbidden.

Backup/restore of a database with Management studio works.

1 Like
8

First of all, congrats for your great work :clap: - Just one question, Is it possible to backup a db with cockpit?

1 Like
9

For now I changed the input disabled prop to readonly and you can copy also on FF. Maybe later we can implement a Copy button.

Yes, finally I used firewall-adjust.

From interface not. Backup of MsSQL is included in backup-data.
If you want to backup manually you can launch /etc/e-smith/events/actions/mssql-dump-tables or backup database directly from SQL Management Studio or similar.

I noticed there are some problem on restore-config, but I’m investigating on it.

2 Likes
10

Hi @mrmarkuz, I updated the package. Can you try if all bugs are solved?
Please try also on a clean machine :slight_smile:

Can you also try backup and restore? (package nethserver-mssql will not automatically restored for now because is in testing repo)

yum clean all && yum update -y nethserver-mssql --enablerepo=nethforge-testing

Thanks!

3 Likes
11

Tested on fresh VM:

  • Copy password works with Firefox
  • Password change works
  • Firewall port 1433 open
  • Cockpit shows error when there’s no internet connection
  • Backup/restore works. I tested with cifs/duplicity and deleted dbs were back up again after restore.

0 issues left, thanks for your great work!

3 Likes
12

Thank you very much @mrmarkuz! I will offer you a beer :beers:

3 Likes
13

On green only?

@fausp AFAIK Microsoft SSMS is freely (no subscription required) to download and install on any MS OS. Not sure if the latest version works on Windows 7.
But i don’t know if there’s any DBMS management tool on Linux that can relate to MSSQL.

14

Yes.

[root@testserver ~]# config show mssql-server
mssql-server=service
    ProductId=express
    ProductKey=
    TCPPort=1433
    access=green
    status=enabled
15

Thank you :slight_smile:

16

By default is opened on green network. You can change it in services page or in firewall configuration.

I found Azure Data Studio that seems to work fine.

1 Like
17

Good health.
Tell me how to change the version of MS SQLSERVER from Express to another?
Senks.

18

Have you installed the Cockpit package?

19

Yes.
Cockpit package installed

20

Ok. And do U have a key of sql server?