In my previous blog post, I talked about SQL Server on Linux and high availability. During my test, I used a NFS server to share disk resources between my cluster nodes as described in the Microsoft documentation. A couple of days ago, I decided to add a fourth node (LINUX04) to my cluster infrastructure and I expected to do this work easily. But no chance, I faced a problem I never had before on this infrastructure.

blog 117 - 01 - sqlfcionlinux - archi

Switching over this last node led to a failed SQL Server FCI resource. After digging into the problem, I found out the root from the SQL Server error log as shown below:

[mikedavem@linux04 ~]$ sudo cat /var/opt/mssql/log/errorlog
2017-02-12 18:55:15.89 Server      Microsoft SQL Server vNext (CTP1.2) - 14.0.200.24 (X64)
        Jan 10 2017 19:15:28
        Copyright (C) 2016 Microsoft Corporation. All rights reserved.
        on Linux (CentOS Linux 7 (Core))
2017-02-12 18:55:15.89 Server      UTC adjustment: 0:00
2017-02-12 18:55:15.89 Server      (c) Microsoft Corporation.
2017-02-12 18:55:15.89 Server      All rights reserved.
2017-02-12 18:55:15.89 Server      Server process ID is 4116.
2017-02-12 18:55:15.89 Server      Logging SQL Server messages in file 'C:\var\opt\mssql\log\errorlog'.
2017-02-12 18:55:15.89 Server      Registry startup parameters:
         -d C:\var\opt\mssql\data\master.mdf
         -l C:\var\opt\mssql\data\mastlog.ldf
         -e C:\var\opt\mssql\log\errorlog
2017-02-12 18:55:15.91 Server      Error: 17113, Severity: 16, State: 1.
2017-02-12 18:55:15.91 Server      Error 2(The system cannot find the file specified.) occurred while opening file 'C:\var\opt\mssql\data\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
2017-02-12 18:55:15.91 Server      SQL Server shutdown has been initiated

Well, the error speaks for itself and it seems I’m concerned by a file access permission in my case. My first reflex was to check the corresponding permissions on the corresponding NFS folder.

[mikedavem@linux04 ~]$ sudo ls -lu /var/opt/mssql/data
To.al 53320
drwxr-----. 2 995 993     4096 Feb 14 23:12 lost+found
-rwxr-----. 1 995 993  4194304 Feb 14 23:19 master.mdf
-rwxr-----. 1 995 993  2097152 Feb 14 23:19 mastlog.ldf
-rwxr-----. 1 995 993  8388608 Feb 14 23:19 modellog.ldf
-rwxr-----. 1 995 993  8388608 Feb 14 23:19 model.mdf
-rwxr-----. 1 995 993 13959168 Feb 14 23:19 msdbdata.mdf
-rwxr-----. 1 995 993   786432 Feb 14 23:19 msdblog.ldf
drwxr-----. 2 995 993     4096 Feb 14 23:08 sqllinuxfci
-rwxr-----. 1 995 993  8388608 Feb 14 23:19 tempdb.mdf
-rwxr-----. 1 995 993  8388608 Feb 14 23:19 templog.ldf

According to the output above we may claim this is a mismatch issue between uids/guids of the mssql user across the cluster nodes. At this stage, I remembered performing some tests including creating some linux users before adding my fourth node leading to create a mismatch for the mssql user’s uids/gids. Just keep in mind that the SQL Server installation creates a mssql user by default with the next available uid/gid. In my case uid and guid.

Let’s compare mssql user uid/gid from other existing nodes LINUX01 / LINUX02 and LINUX03:

[mikedavem@linux04 ~]$ id mssql
uid=997(mssql) gid=995(mssql) groups=995(mssql)
[mikedavem@linux04 ~]$ ssh linux01 id mssql
…
[root@linux04 ~]# ssh linux01 id mssql
uid=995(mssql) gid=993(mssql) groups=993(mssql)
…
[root@linux04 ~]# ssh linux02 id mssql
uid=995(mssql) gid=993(mssql) groups=993(mssql)
…
[root@linux04 ~]# ssh linux03 id mssql
uid=995(mssql) gid=993(mssql) groups=993(mssql)

Ok this explains why I faced this permission issue. After investing some times to figure out how to get rid of this issue without changing the mssql user’s uid/guid, I read some discussions about using NFS4 which is intended to fix this uids/gids mapping issue. It seems to be perfect in my case! But firstly let’s just confirm I’m using the correct NFS version

[mikedavem@linux04 ~]$ mount -v | grep nfs
nfsd on /proc/fs/nfsd type nfsd (rw,relatime)
192.168.5.14:/mnt/sql_data_nfs on /var/opt/mssql/data type nfs (rw,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,hard,proto=tcp,timeo=600,retrans=2,sec=sys,mountaddr=192.168.5.14,mountvers=3,mountport=20048,mountproto=udp,local_lock=none,addr=192.168.5.14)

Well, my current configuration is not ready to leverage NFS4 yet and some configuration changes seem to be required to address it.

Firstly, let’s change fstype parameter of my FS resource to nfs4 to mount the NFS share with NFS4.

[mikedavem@linux04 ~]$ sudo pcs resource show FS
 Resource: FS (class=ocf provider=heartbeat type=Filesystem)
  Attributes: device=192.168.5.14:/mnt/sql_data_nfs directory=/var/opt/mssql/data fstype=nfs
  Operations: start interval=0s timeout=60 (FS-start-interval-0s)
              stop interval=0s timeout=60 (FS-stop-interval-0s)
              monitor interval=20 timeout=40 (FS-monitor-interval-20)

[mikedavem@linux04 ~]$ sudo pcs resource update FS fstype=nfs4
[mikedavem@linux04 ~]$ sudo pcs resource restart FS
FS successfully restarted

Then I had to perform some updates to my idmap configuration on both sides (NFS server and client as well) to make the mapping working correctly. The main steps were as follows:

  • Enabling idmap with NFS4 (disabled by default in my case)
  • Changing some parameters inside the /etc/idmap.conf
  • Verifying idmap is running correctly.
[root@nfs sql_data_nfs]# echo N >  /sys/module/nfsd/parameters/nfs4_disable_idmapping
…
[root@nfs sql_data_nfs]# grep ^[^#\;] /etc/idmapd.conf
[General]
Domain = dbi-services.test
[Mapping]
Nobody-User = nobody
Nobody-Group = nobody
[Translation]
Method = static

[Static]
[email protected] = mssql
[email protected] = testp
…
[root@nfs sql_data_nfs]# systemctl status nfs-idmap
. nfs-idmapd.service - NFSv4 ID-name mapping service
   Loaded: loaded (/usr/lib/systemd/system/nfs-idmapd.service; static; vendor preset: disabled)
   Active: active (running) since Wed 2017-02-15 20:29:57 CET; 1h 39min ago
  Process: 3362 ExecStart=/usr/sbin/rpc.idmapd $RPCIDMAPDARGS (code=exited, status=0/SUCCESS)
 Main PID: 3364 (rpc.idmapd)
   CGroup: /system.slice/nfs-idmapd.service
           └─3364 /usr/sbin/rpc.idmapd

At this point, listening user permissions shows nobody/nobody meaning translation is not performed yet.

[root@linux04 ~]# ls -lu /var/opt/mssql
total 16
drwxr-----. 2 nobody nobody 4096 Feb 15 19:00 data
…

I forgot to create a corresponding mssql user on the NFS service side. Let’s do it:

 [root@nfs sql_data_nfs]# groupadd mssql -g 993
[root@nfs sql_data_nfs]# useradd -u 995 -g 993 mssql –M

After remounting the NFS share, I finally got the expected output as shown below:

[root@linux04 ~]# mount -o remount -t nfs4 192.168.5.14:/mnt/sql_data_nfs/sqllinuxfci /mnt/testp/
…
[root@linux04 ~]# ls -lu /var/opt/mssql
total 16
drwxr-----. 2 mssql mssql 4096 Feb 15 19:00 data
…
[root@linux04 ~]# ls -lu /var/opt/mssql/data/*
-rwxr-----. 1 mssql mssql  4194304 Feb 15 19:53 /var/opt/mssql/data/master.mdf
-rwxr-----. 1 mssql mssql  2097152 Feb 15 19:53 /var/opt/mssql/data/mastlog.ldf
-rwxr-----. 1 mssql mssql  8388608 Feb 15 19:53 /var/opt/mssql/data/modellog.ldf
-rwxr-----. 1 mssql mssql  8388608 Feb 15 19:53 /var/opt/mssql/data/model.mdf
-rwxr-----. 1 mssql mssql 13959168 Feb 15 19:53 /var/opt/mssql/data/msdbdata.mdf
-rwxr-----. 1 mssql mssql   786432 Feb 15 19:53 /var/opt/mssql/data/msdblog.ldf
-rwxr-----. 1 mssql mssql  8388608 Feb 15 19:53 /var/opt/mssql/data/tempdb.mdf
-rwxr-----. 1 mssql mssql  8388608 Feb 15 19:53 /var/opt/mssql/data/templog.ldf

This time the translation is effective but let’s perform another test by running the previous command as the mssql user

[root@linux04 ~]# runuser -l mssql -c 'ls -lu /var/opt/mssql/data/*'
ls: cannot access /var/opt/mssql/data/*: Permission denied

The problem starts when I try to access the database files despite the correct mapping configuration. I spent some time to understand that some misconceptions about how NFSv4 and magic mismatch uids/gids fix subsist. I admit the main documentation is not clear about it but please, feel free to comment if it is not the case. After digging into further pointers, I was able to understand that NFS itself doesn’t achieve authentication but delegates it down to the RPC mechanism. If we take a look down at the RPC’s security, we may notice it hasn’t been updated to support such matching. Basically, it continues to use the historic authentication called AUTH_SYS meaning sending uids/gis over the network. Translation work comes later through the idmap service. The only way to get rid of this issue would be to prefer another protocol like RPCSEC_GSS which includes authentication based on LDAP or Kerberos for example.

 The bottom line here is that SQL Server on Linux is not an exception of course. If we want to continue using basic Unix authentication, keeping synchronizing uids and guids across my cluster nodes seems to be a good way to go. Using Kerberos authentication in this case? This is another story that I will try to tell in another blog post!

Happy clustering on Linux!

By David Barbarin