none
mssql + sssd Ubuntu cannot login via AD group RRS feed

  • Question

  • I've got mssql  14.0.3192.2-2 setup unter Ubuntu; I have joined my machine to AD as described in https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-2017

    The Windows login via SSMS and sqlcmd works if I add invididual AD users, but not if I add groups.

    Also, when I try to get the "Effective Permissions" for the group under Server Properties / Permissions, I get an error "Could not obtain information about Windows NT group/user 'UserName', error code 0x80090304."

    I can do 'kinit' without problems, I can query users via 'id' (returns all assigned groups), I can query group members via 'members' which successfully returns all group members.

    If you have any idea what could be wrong I'd really appreciate your help! 

    Basic config:

    Domain: ad.xxxx.com
    Hostname: linux.ad.xxxx.com
    NetBIOS name: LINUX
    DC1, DC2: heart.ad.xxxx.com, file.ad.xxxx.com
    AD DNS resolves linux.ad.xxxx.com to 10.0.10.13
    Reverse DNS resolves 10.0.10.13 to linux.ad.xxxx.com

    The SQL server host is located in a DMZ, with ports 88, 3268, 389, 1433 open.

    My mssql.keytab:

    slot KVNO Principal
    ---- ---- ---------------------------------------------------------------------
       1    2 MSSQLSvc/linux.ad.xxxx.com:1433@AD.XXXX.COM
       2    2 MSSQLSvc/linux.ad.xxxx.com:1433@AD.XXXX.COM
       3    2           MSSQLSvc/LINUX:1433@AD.XXXX.COM
       4    2           MSSQLSvc/LINUX:1433@AD.XXXX.COM
       5    3                        LINUX$@AD.XXXX.COM
       6    3                        LINUX$@AD.XXXX.COM
       7    3                        LINUX$@AD.XXXX.COM
       8    3                        LINUX$@AD.XXXX.COM
       9    3                        LINUX$@AD.XXXX.COM

    My /etc/krb5.keytab:

    slot KVNO Principal
    ---- ---- ---------------------------------------------------------------------
       1    3                        LINUX$@AD.XXXX.COM
       2    3                        LINUX$@AD.XXXX.COM
       3    3                        LINUX$@AD.XXXX.COM
       4    3                        LINUX$@AD.XXXX.COM
       5    3                        LINUX$@AD.XXXX.COM
       6    3                    host/LINUX@AD.XXXX.COM
       7    3                    host/LINUX@AD.XXXX.COM
       8    3                    host/LINUX@AD.XXXX.COM
       9    3                    host/LINUX@AD.XXXX.COM
      10    3                    host/LINUX@AD.XXXX.COM
      11    3         host/linux.ad.xxxx.com@AD.XXXX.COM
      12    3         host/linux.ad.xxxx.com@AD.XXXX.COM
      13    3         host/linux.ad.xxxx.com@AD.XXXX.COM
      14    3         host/linux.ad.xxxx.com@AD.XXXX.COM
      15    3         host/linux.ad.xxxx.com@AD.XXXX.COM
      16    3       RestrictedKrbHost/LINUX@AD.XXXX.COM
      17    3       RestrictedKrbHost/LINUX@AD.XXXX.COM
      18    3       RestrictedKrbHost/LINUX@AD.XXXX.COM
      19    3       RestrictedKrbHost/LINUX@AD.XXXX.COM
      20    3       RestrictedKrbHost/LINUX@AD.XXXX.COM
      21    3 RestrictedKrbHost/linux.ad.xxxx.com@AD.XXXX.COM
      22    3 RestrictedKrbHost/linux.ad.xxxx.com@AD.XXXX.COM
      23    3 RestrictedKrbHost/linux.ad.xxxx.com@AD.XXXX.COM
      24    3 RestrictedKrbHost/linux.ad.xxxx.com@AD.XXXX.COM
      25    3 RestrictedKrbHost/linux.ad.xxxx.com@AD.XXXX.COM

    In AD I have a user linux.sql which has the following SPNs assigned:

    Registered ServicePrincipalNames for CN=linux.sql,CN=Users,DC=ad,DC=xxxx,DC=com:
            MSSQLSvc/LINUX:1433
            MSSQLSvc/linux.ad.xxxx.com:1433

    Also, the SQL server has a machine account LINUX.

    My mssql.conf:

    [sqlagent]
    
    enabled = false
    
    [EULA]
    
    accepteula = Y
    
    [network]
    
    kerberoskeytabfile = /var/opt/mssql/secrets/mssql.keytab 
    tlscert = /var/opt/mssql/secrets/ssl/cert.pem
    tlskey = /var/opt/mssql/secrets/ssl/privkey.pem
    tlsprotocols = 1.2
    forceencryption = 1

    Wednesday, August 14, 2019 3:12 PM

All replies