• Filip Holub

How to Configure Windows Authentication for SQL Server on Linux?

In this post, I'll demonstrate how to configure windows authentication for SQL Server on Linux, especially for RHEL\CentOS distribution. In another world, we will able to use Active Directory accounts and groups. We have to fulfill a couple of prerequisites, so, let's get start.



Prerequisites

  • Set up Active Directory Domain Controller

  • Install Linux machine with SQL Server

  • Join Linux machine to the domain


In my case, I have done already with three steps above. Honestly, it's not big deal setup this environment. I'm using VirtualBox for my environment, my domain controller is running on Windows Server 2016, domain is LABS.LOCAL and Linux host where is running CentOS distribution with SQL Server 2017. Now, the environment is ready for our intents.




Set up Windows Authentication for SQL Server.


"First of all, you have to connect as root user on your Linux machine!"


  1. Create SQL Server Service Account in Active Directory


Create Service Account in Active Directory

Confirmation of creating new user

2 . Register Service Principal Name (SPN) on domain controller for our mssql account.


  • Run CMD as Administrator on Domain Controller and run following commands:

  • You can check if mssql account was successfully registered by following command:

Successful result looks:

Register SPN for mssql account

3. Now, we have to check on Linux machine (KVNO) for mssql account, in my case, it's number 2 as you can see below:

  • kinit mssql@LABS.LOCAL - If you logon to system with Kerberos support you have to require TGT(Ticket-Granting Ticket) list from domain controller by kinit program.

  • kvno MSSQLSvc/centos_node1.labs.local:1433 - The kvno (Key Version Number) represents version of key, the number of key is changing(increase) with each change of password.


4. Next step is creating Keytab File by using ktutil:

As you can see, paramater -k 2, this is number from previous ouptut kvno = 2!


Note: The ktutil command invokes a command interface from which an administrator can read, write, or edit entries in a keytab or Kerberos V4 srvtab file. - Source


  • wkt /var/opt/mssql/secrets/mssql.keytab - That means writes keylist to keytab file

5. Then, we have to set appropriate access on mssql.keytab for our mssql account by folowing commands:

  • Chmod 400 - Read by owner only - (OWNERSHIP), in this case, only mssql account can read!

Warning: If you didn't set up appropriate access on keytab file, anyone can misuse that and is able to impersonate you!!!


6. The last thing is set up our SQL Server 2017 to use new keytab file for Kerberos authentication and restart SQL Server for apply changes:

And that's all guys!


Right now, we can create mssql login on our SQL Server and try log on with mssql account.


Add AD login mssql on SQL Server

And as you can see below, we have successfully connected with our AD account on SQL Server instance!


Successfully connected with mssql account

Source: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-2017


See you later :)

0 zobrazení
SIGN UP AND STAY UPDATED!
  • Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon

© 2020 All rights reserved. MSSQLONLINUX.com | Filip Holub

  • Twitter Social Icon
  • Facebook Social Icon
  • LinkedIn Social Icon