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!"
Create SQL Server Service Account in Active Directory
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:
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.
And as you can see below, we have successfully connected with our AD account on SQL Server instance!
Comments