In this exercise we will see the implementation of Windows Active Directory integration to Microsoft SQL Server on Linux environment. In this example Centos1 is the SQL Server Linux machine name and GALAXY.COM is the domain.

Step 1 – Create a windows domain account “sa_sqlsrv” in Windows AD Server

Step 2 – Set the Service Principal Name (SPN) for above account

  • setspn -A MSSQLSvc/Centos1.GALAXY.COM:20201 sa_sqlsrv
  • setspn -A MSSQLSvc/Centos1:20201 sa_sqlsrv

Step 3 – Start kinit on Check the Key Version Number (KVNO) for the AD account created

  • $ kinit sa_sqlsrv@GALAXY.COM

Step 4 – Find the KVNO Number

  • $ kvno MSSQLSvc/Centos1.GALAXY.COM:20201@GALAXY.COM

Step 5 – Add Keytab entry for aes256 and rc4 encryption for FQDN and NetBIOS names

  • $ ktutil: addent -password -p MSSQLSvc/Centos1.GALAXY.COM:20201@GALAXY.COM -k 2 -e aes256-cts-hmac-sha1-96
  • $ ktutil: addent -password -p MSSQLSvc/Centos1.GALAXY.COM:20201@GALAXY.COM -k 2 -e rc4-hmac
  • $ ktutil: addent -password -p MSSQLSvc/Centos1:20201@GALAXY.COM -k 2 -e aes256-cts-hmac-sha1-96
  • $ ktutil: addent -password -p MSSQLSvc/Centos1:20201@GALAXY.COM -k 2 -e rc4-hmac

Step 6 – Write keytab to file using wkt command

  • $ ktutil: wkt /var/opt/mssql/secrets/sa_sqlsrv.keytab

Step 7 – Review the keytab file content using rkt command

  • $ ktutil: rkt /etc/krb5.keytab
  • $ ktutil: list

Step 8 – Remove the non-simplified name entry in keytab (Those with host/ as prefix) using the delent 1 command

  • $ ktutil: list
  • $ ktutil: delent <slot number>

Step 9 – Append keytabs to the sa_sqlsrv keytab file list using wkt command

  • $ ktutil: wkt /var/mssql/secrets/sa_sqlsrv.keytab

Step 10 – Change keytab ownership & permission

  • $ chown mssql:mssql sa_sqlsrv.keytab
  • $ chmod 400 sa_sqlsrv.keytab

Step 11 – Specify the account to be used to access the keytab file

  • $ sudo /opt/mssql/bin/mssql-conf set network.privilegedadaccount sa_sqlsrv

Step 12 – Configure SQL Server to start using the keytab file for Kerberos authentication

  • $ sudo mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab

Step 13 – Login to SQL Server using local authentication to create AD account in database server

  • $ sqlcmd -S Centos1.GALAXY.COM,20201 -U SA -p
    • 1 > CREATE LOGIN [GALAXY\sa_sqlsrv] FROM WINDOWS;
    • 2 > GO;
    • 3 > EXEC SP_ADDSRVROLEMEMBER ‘sa_sqlsrv’, ‘SYSADMIN’
    • 4 > GO;

Step 13 – Login to SQL Management Studio/Azure Studio to verify AD account

Categories: RDBMSTags: , , , ,