Yesterday Microsoft announced that they will now support SQL Server on Linux.  They are targeting mid-2017, but you can download a preview now.  I am on a technical list where I saw someone inquire about the support for Windows authentication or if SQL authentication would be the only choice.  Those of you that know me remember that Active Directory is my second strongest skill set next to SQL Server.  What you might not know is that I went through a Linux phase and up until a couple years ago I always had a Linux machine sitting next to my Windows machine.  It appears it may be time for me to get back into this world.

Back to our question at hand.  Since the preview just got released I have not had a chance to test this out with SQL Server.  However, you can use Active Directory accounts with Linux and Unix by using Samba and PAM.  I see no reason why this implementation would not also work with SQL Server on Linux, but again I have not yet had a chance to test this out.  Since I already have some written installation instructions, that’s what I am providing here.

SQL Server on Linux Authentication with Samba

    • Make sure to remove any installed binary Samba packages to start clean
      • rpm -qa | grep samba
      • rpm -e SambaPackageName
      • Do the above command for each samba package found.  If it complains about dependencies then run the command like the following
        • rpm -e – -nodeps SambaPackageName
    • Download latest Samba source code
    • Extract the source code
      • tar xvfz samba-latest.tar.gz
    • Run the configure script from the source directory to create a make file
      • ./configure –with-msdfs –with-smbwrapper –with-smbmount –with-ldap –with-ads –with-cifsmount –with-libsmbclient –with-winbind –with-krb5 –with-pam –with-acl-support 2>&1 | tee config.my.log
    • Run make from the source directory
      • make 2>&1 | tee make.my.log
    • Run make install from the source directory
      • make install 2>&1 | tee makeinstall.my.log
    • Copy the following files to your documents directory for future reference
      • config.my.log
      • make.my.log
      • makeinstall.my.log
      • config.status
        • This file contains the options that you ran the configure script with
        • Search for “with options” to find the line with your configure options
    • Add Samba executable directory to path for root and yourself
      • Add the following to the end of the PATH statement in /root/.bash_profile and /home/myuser/.bash_profile
        • :/usr/local/samba/bin:/usr/local/samba/sbin
    • Add Samba MAN pages directory to the MAN path
      • Edit /etc/man.config and add the following two lines to the end
        • MANPATH /usr/local/samba/man
        • MANPATH_MAP /usr/local/samba/bin /usr/local/samba/man
    • Copy /usr/local/samba/sbin/mount.cifs to /sbin so that you can mount with CIFS
    • Make sure to create a smb.conf file in /usr/local/samba/lib BEFORE you start Samba
      • A sample can be copied from the examples folder in the downloaded source code
      • run testparm on the smb.conf to verify correct syntax
    • To automatically start Samba upon boot
      • create a file named startsmb in /usr/local/samba/sbin with the following contents
        • #!/bin/sh
        • /usr/local/samba/sbin/smbd -D
        • /usr/local/samba/sbin/nmbd -D
        • /usr/local/samba/sbin/winbindd
      • Make it executable with chmod +x startsmb
      • Add the following line to /etc/rc.local to execute the above script upon boot
        • /usr/local/samba/sbin/startsmb
    • Edit /etc/resolv.conf and make sure it has appropriate entries for “search” and “nameserver”
      • search mydomain.com
      • nameserver 192.168.0.xxx
      • nameserver 192.168.0.xxx
    • Configure Kerberos for ADS realm and KDC
      • Edit /etc/krb5.conf and add the following to the [libdefaults] section
        • default_tgs_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • default_tkt_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • preferred_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • default_realm = MYDOMAIN.COM
        • dns_lookup_kdc = true
    • Join Samba server to the domain as a domain member
      • net ads join -U username
    • Look for and delete the following files if they exist
      • /lib/libnss_winbind.so
      • /lib/libnss_winbind.so.2
      • /lib/libnss_wins.so
      • /lib/libnss_wins.so.2
      • /lib/security/pam_winbind.so
      • /lib/security/pam_winbind.so.2
    • Copy your newly compiled winbind, wins, and pam libraries from the source
      • cp sourcecodepath/source/nsswitch/libnss_winbind.so /lib
        • chown root /lib/libnss_winbind.so
        • chgrp root /lib/libnss_winbind.so
        • chmod 755 /lib/libnss_winbind.so
      • cp sourcecodepath/source/nsswitch/libnss_wins.so /lib
        • chown root /lib/libnss_wins.so
        • chgrp root /lib/libnss_wins.so
        • chmod 755 /lib/libnss_wins.so
      • cp sourcecodepath/source/bin/pam_winbind.so /lib/security
        • chown root /lib/security/pam_winbind.so
        • chgrp root /lib/security/pam_winbind.so
        • chmod 755 /lib/security/pam_winbind.so
    • Create a symbolic link for your libnss_winbind and libnss_wins libraries
      • execute /sbin/ldconfig
      • verify that libnss_winbind.so.2 and libnss_wins.so.2 exist in the /lib directory
    • Modify /etc/nsswitch.conf to make the lines for passwd and group look like this
      • passwd: files winbind
      • group:  files winbind
    • Add the following lines to your smb.conf file in the global section
      • idmap uid = 10000-20000
      • idmap gid = 10000-20000
      • winbind enum users = no
      • winbind enum groups = no
      • winbind use default domain = yes
      • winbind nested groups = yes
      • allow trusted domains = no
      • template homedir = /home/%D/%U
      • template shell = /bin/bash
    • Stop and restart the SMBD NMDB and WINBIND processes.  You can restart them and ensure the starting order is correct by executing the /usr/local/samba/sbin/startsmb file you created earlier.

Configure PAM

  • Configure PAM to use Winbind for authentication on the local system if desired
    • Edit /etc/pam.d/system-auth and add the following lines
      • auth             sufficient    pam_winbind.so use_first_pass
      • account        sufficient    pam_winbind.so use_first_pass
      • password     sufficient    pam_winbind.so use_first_pass
      • session         required    pam_winbind.so use_first_pass
  • The finished file should look like the following
    • #%PAM-1.0
    • # This file is auto-generated.
    • # User changes will be destroyed the next time authconfig is run.
    • auth        required      pam_env.so
    • auth        sufficient    pam_unix.so nullok try_first_pass
    • auth        sufficient    pam_winbind.so use_first_pass
    • auth        requisite     pam_succeed_if.so uid >= 500 quiet
    • auth        required      pam_deny.so
    • account     required      pam_unix.so
    • account     sufficient    pam_succeed_if.so uid < 500 quiet
    • account     sufficient    pam_winbind.so use_first_pass
    • account     required      pam_permit.so
    • password    requisite     pam_cracklib.so try_first_pass retry=3
    • password    sufficient    pam_unix.so md5 shadow nullok try_first_pass use_authtok
    • password    sufficient    pam_winbind.so use_first_pass
    • password    required      pam_deny.so
    • session     optional      pam_keyinit.so revoke
    • session     required      pam_limits.so
    • session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
    • session     required      pam_unix.so
    • session     required      pam_winbind.so use_first_pass