Category Archives: Ubuntu

Configure Active Directory authentication with SQL Server on Linux

Posted by cviorel on December 13, 2020 No comments | This post has 3,176 views.

Microsoft just released the adutil in public preview which is a CLI based utility developed to ease the AD authentication configuration for both SQL Server on Linux and SQL Server Linux containers.

We don’t need to switch to a Windows machine to create the AD user for SQL Server and setting SPNs.

In the following steps I will try to install a SQL Server instance on Linux using just the Linux CLI tool adutil.

We will need 2 VMs:

tf-wincore01.lab.local – Domain Controller (DC) running on Windows Server 2019 Core (will
host the lab.local domain)
tf-ubuntu01.lab.local – Ubuntu 18.04 LTS – SQL Server Instance on port 20001 will be
installed here

I will be creating a brand new environment for this test and I am using Terraform to provision the VMs .

Prepare the Domain Controller

Once the VMs are created we need to configure the domain controller:

# Configure Networking on the DC
$InterfaceIndex = $(Get-NetIPAddress -AddressFamily IPv4 | Where-Object { $_.InterfaceAlias -like "Ethernet*" }).InterfaceIndex
Set-DnsClientServerAddress -InterfaceIndex $InterfaceIndex -ServerAddresses 192.168.1.105, 192.168.1.1

# Configure the Domain Controller
$domainName = 'lab.local'
$domainAdminPassword = "SecretPa$w0rd"
$secureDomainAdminPassword = $domainAdminPassword | ConvertTo-SecureString -AsPlainTExt -Force

$domainName.Split('.')[0]
Install-WindowsFeature -Name AD-Domain-Services -IncludeManagementTools

# Create Active Directory Forest
Install-ADDSForest `
    -DomainName "$domainName" `
    -CreateDnsDelegation:$false `
    -DatabasePath "C:\Windows\NTDS" `
    -DomainMode "7" `
    -DomainNetbiosName $domainName.Split('.')[0] `
    -ForestMode "7" `
    -InstallDns:$true `
    -LogPath "C:\Windows\NTDS" `
    -NoRebootOnCompletion:$True `
    -SysvolPath "C:\Windows\SYSVOL" `
    -Force:$true `
    -SafeModeAdministratorPassword $secureDomainAdminPassword

# Configure Networking on the DC

$InterfaceIndex = $(Get-NetIPAddress -AddressFamily IPv4 | Where-Object { $_.InterfaceAlias -like "Ethernet*" }).InterfaceIndex

Set-DnsClientServerAddress -InterfaceIndex $InterfaceIndex -ServerAddresses 192.168.1.105, 192.168.1.1

# Configure the Domain Controller

$domainName = 'lab.local'

$domainAdminPassword = "SecretPa$w0rd"

$secureDomainAdminPassword = $domainAdminPassword | ConvertTo-SecureString -AsPlainTExt -Force

$domainName.Split('.')[0]

Install-WindowsFeature -Name AD-Domain-Services -IncludeManagementTools

# Create Active Directory Forest

Install-ADDSForest `

-DomainName "$domainName" `

-CreateDnsDelegation:$false `

-DatabasePath "C:\Windows\NTDS" `

-DomainMode "7" `

-DomainNetbiosName $domainName.Split('.')[0] `

-ForestMode "7" `

-InstallDns:$true `

-LogPath "C:\Windows\NTDS" `

-NoRebootOnCompletion:$True `

-SysvolPath "C:\Windows\SYSVOL" `

-Force:$true `

-SafeModeAdministratorPassword $secureDomainAdminPassword

Let’s setup our zones:

# Check the current zones
Get-DnsServerZone -ComputerName tf-wincore01

# Add a reverse lookup zone
Add-DnsServerPrimaryZone -NetworkId "192.168.1.0/24" -ReplicationScope Domain

# Display the list of records in the new DNS zone (it is empty)
Get-DnsServerResourceRecord -ComputerName tf-wincore01 -ZoneName lab.local

# Create an entry in Active Directory for the Linux host
Add-DnsServerResourceRecordA -Name tf-ubuntu01 -ZoneName lab.local -IPv4Address 192.168.1.85
Get-DnsServerResourceRecord -ZoneName lab.local -RRType A

# Add a PTR record to the Reverse Lookup Zone for the linux host
Add-DNSServerResourceRecordPTR -ZoneName 1.168.192.in-addr.arpa -Name 85 -PTRDomainName tf-ubuntu01.lab.local

# Check the current zones

Get-DnsServerZone -ComputerName tf-wincore01

# Add a reverse lookup zone

Add-DnsServerPrimaryZone -NetworkId "192.168.1.0/24" -ReplicationScope Domain

# Display the list of records in the new DNS zone (it is empty)

Get-DnsServerResourceRecord -ComputerName tf-wincore01 -ZoneName lab.local

# Create an entry in Active Directory for the Linux host

Add-DnsServerResourceRecordA -Name tf-ubuntu01 -ZoneName lab.local -IPv4Address 192.168.1.85

Get-DnsServerResourceRecord -ZoneName lab.local -RRType A

# Add a PTR record to the Reverse Lookup Zone for the linux host

Add-DNSServerResourceRecordPTR -ZoneName 1.168.192.in-addr.arpa -Name 85 -PTRDomainName tf-ubuntu01.lab.local

Note that this AD configuration is just the bare minimum for our lab and it’s not fit for a Production environment!

Join the Linux host to the domain

It’s now time to join the Linux box to our new domain. The yaml file used by netplan needs to point to the domain:

/etc/netplan/******.yaml
network:
  ethernets:
    eth0:
            dhcp4: true

            dhcp6: true
            nameservers:
                    addresses: [ **<AD domain controller IP address>**]
                    search: [**<AD domain name>**]
  version: 2

/etc/netplan/******.yaml

network:

ethernets:

eth0:

dhcp4: true

dhcp6: true

nameservers:

addresses: [ **<AD domain controller IP address>**]

search: [**<AD domain name>**]

version: 2

Confirm the configuration and apply it.

sudo netplan apply

1	sudo netplan apply

In my case, the file looks like this:

/etc/resolv.conf file should also point to the domain:

Next, we install the packages that will allow us to join the machine to the domain:

sudo apt-get install -y realmd krb5-user software-properties-common python3-software-properties packagekit
sudo apt-get install -y adcli libpam-sss libnss-sss sssd sssd-tools

1 2	sudo apt-get install -y realmd krb5-user software-properties-common python3-software-properties packagekit sudo apt-get install -y adcli libpam-sss libnss-sss sssd sssd-tools

Let’s also set the hostname:

short_hostname=$(hostname)
domain='lab.local'
fqdn=${short_hostname}.${domain}
sudo hostname $fqdn

short_hostname=$(hostname)

domain='lab.local'

fqdn=${short_hostname}.${domain}

sudo hostname $fqdn

We are now ready to join the machine to the domain:

sudo realm join lab.local -U 'administrator@lab.local' -v

1	sudo realm join lab.local -U 'administrator@lab.local' -v

This command:

creates a new computer account in AD
creates the /etc/krb5.keytab host keytab file
configures the domain in /etc/sssd/sssd.conf
updates /etc/krb5.conf

Let’s verify that we can now gather information about a user from the domain, and that we can acquire a Kerberos ticket as that user. The following example uses id, kinit, and klist commands for this.

id CHudson@lab.local
kinit CHudson@LAB.LOCAL
klist

id CHudson@lab.local

kinit CHudson@LAB.LOCAL

klist

Install adutil

We now need to install the adutil so we can interact with the Domain Controller directly from the Linux box.

# Register the Microsoft Ubuntu repository
sudo apt-get update && sudo apt-get install -y curl
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

# Install adutil-preview
sudo ACCEPT_EULA=Y apt-get install -y adutil-preview

# Register the Microsoft Ubuntu repository

sudo apt-get update && sudo apt-get install -y curl

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

sudo curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

# Install adutil-preview

sudo ACCEPT_EULA=Y apt-get install -y adutil-preview

Create a domain user using adutil

Let’s try to create a regular AD user:

kinit administrator@LAB.LOCAL # we need to use a privileged user
adutil user create --name cviorel -distname CN=cviorel,CN=Users,DC=lab,DC=local --password 'P@ssw0rd'

1 2	kinit administrator@LAB.LOCAL # we need to use a privileged user adutil user create --name cviorel -distname CN=cviorel,CN=Users,DC=lab,DC=local --password 'P@ssw0rd'

At this point adutil cannot list the users, but we can check if an account exists in the AD

adutil account exists cviorel

1	adutil account exists cviorel

Install SQL Server instance on the Linux host

From this point on, I can proceed at installing the SQL Server instance on the Linux host:

# Install SQL Server on Linux
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
sudo apt-get update
sudo apt-get install -y mssql-server

# Configure SQL Server
sudo /opt/mssql/bin/mssql-conf setup

# Enable the SQL Server Agent
sudo /opt/mssql/bin/mssql-conf <span class="hljs-built_in">set</span> sqlagent.enabled <span class="hljs-literal">true</span> 

# Set custom TCP port
sudo /opt/mssql/bin/mssql-conf set network.tcpport 20001
sudo systemctl restart mssql-server

# Check the configuration
systemctl status mssql-server --no-pager

# Install the SQL Server command-line tools
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update 
sudo apt-get install -y mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

# Install SQL Server on Linux

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"

sudo apt-get update

sudo apt-get install -y mssql-server

# Configure SQL Server

sudo /opt/mssql/bin/mssql-conf setup

# Enable the SQL Server Agent

sudo /opt/mssql/bin/mssql-conf <span class="hljs-built_in">set</span> sqlagent.enabled <span class="hljs-literal">true</span>

# Set custom TCP port

sudo /opt/mssql/bin/mssql-conf set network.tcpport 20001

sudo systemctl restart mssql-server

# Check the configuration

systemctl status mssql-server --no-pager

# Install the SQL Server command-line tools

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

sudo apt-get update

sudo apt-get install -y mssql-tools unixodbc-dev

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

source ~/.bashrc

Create an AD user for SQL Server and set the ServicePrincipalName (SPN) using adutil

SQL Server instance is running and let’s now create an AD user for SQL Server and set the ServicePrincipalName (SPN) using the adutil tool.

kinit administrator@LAB.LOCAL # we need to use a privileged user
adutil user create --name sqluser -distname CN=sqluser,CN=Users,DC=lab,DC=local --password 'P@ssw0rd'

# Register SPNs to the principal created above. We use port 20001/tcp for the SQL Server instance
adutil spn addauto -n sqluser -s MSSQLSvc -H tf-ubuntu01.lab.local -p 20001

adutil keytab createauto -k /var/opt/mssql/secrets/mssql.keytab -p 20001 -H tf-ubuntu01.lab.local --password 'P@ssw0rd' -s MSSQLSvc

# Add an entry in the keytab for the principal name and its password that will be used by SQL Server to connect to AD
adutil keytab create -k /var/opt/mssql/secrets/mssql.keytab -p sqluser --password 'P@ssw0rd!'

# Set proper permissions to the mssql.keytab file
chown mssql. /var/opt/mssql/secrets/mssql.keytab
chmod 440 /var/opt/mssql/secrets/mssql.keytab

# Configure SQL Server to use the keytab
/opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab
/opt/mssql/bin/mssql-conf set network.privilegedadaccount sqluser

kinit administrator@LAB.LOCAL # we need to use a privileged user

adutil user create --name sqluser -distname CN=sqluser,CN=Users,DC=lab,DC=local --password 'P@ssw0rd'

# Register SPNs to the principal created above. We use port 20001/tcp for the SQL Server instance

adutil spn addauto -n sqluser -s MSSQLSvc -H tf-ubuntu01.lab.local -p 20001

adutil keytab createauto -k /var/opt/mssql/secrets/mssql.keytab -p 20001 -H tf-ubuntu01.lab.local --password 'P@ssw0rd' -s MSSQLSvc

# Add an entry in the keytab for the principal name and its password that will be used by SQL Server to connect to AD

adutil keytab create -k /var/opt/mssql/secrets/mssql.keytab -p sqluser --password 'P@ssw0rd!'

# Set proper permissions to the mssql.keytab file

chown mssql. /var/opt/mssql/secrets/mssql.keytab

chmod 440 /var/opt/mssql/secrets/mssql.keytab

# Configure SQL Server to use the keytab

/opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab

/opt/mssql/bin/mssql-conf set network.privilegedadaccount sqluser

Test the connections and the authentication scheme

Let’s create an AD-based SQL Server login:

CREATE LOGIN [LAB\cviorel] FROM WINDOWS;
SELECT name FROM sys.server_principals;

1 2	CREATE LOGIN [LAB\cviorel] FROM WINDOWS; SELECT name FROM sys.server_principals;

Connecting as a domain user from the Linux box:

Let’s verify the authentication scheme:

sqlcmd -S localhost,20001 -U SA -h -1 -s, -W -Q '
SELECT DES.session_id, DES.login_name, DES.program_name, DES.host_name, DEC.auth_scheme, DEC.client_tcp_port 
FROM sys.dm_exec_sessions AS DES JOIN sys.dm_exec_connections AS DEC ON DEC.session_id = DES.session_id 
WHERE DES.session_id <> @@SPID;'

sqlcmd -S localhost,20001 -U SA -h -1 -s, -W -Q '

SELECT DES.session_id, DES.login_name, DES.program_name, DES.host_name, DEC.auth_scheme, DEC.client_tcp_port

FROM sys.dm_exec_sessions AS DES JOIN sys.dm_exec_connections AS DEC ON DEC.session_id = DES.session_id

WHERE DES.session_id <> @@SPID;'

Conclusion

Our setup is now complete and we managed to perform all the required operations from a Linux machine. The same can be applied to provision SQL Server running on Linux containers. This also should apply if you’re running in the cloud.

Enable transmission-daemon logging to file

Posted by cviorel on October 26, 2018 3 comments | This post has 36,739 views.

If you use transmission-daemon on your Raspberry Pi and want to have logs to a file, you can follow a few simple steps to accomplish this:

Edit the service file:

sudo vim /lib/systemd/system/transmission-daemon.service

1	sudo vim /lib/systemd/system/transmission-daemon.service

Change the ExecStart line to look like this:

ExecStart=/usr/bin/transmission-daemon -f --log-debug --logfile /var/log/transmission.log

1	ExecStart=/usr/bin/transmission-daemon -f --log-debug --logfile /var/log/transmission.log

You can choose between these options:

--log-error
Show error messages

--log-info
Show error and info messages

--log-debug
Show error, info, and debug messages

--log-error

Show error messages

--log-info

Show error and info messages

--log-debug

Show error, info, and debug messages

systemctl will complain that the service file was changed. Run:

sudo systemctl daemon-reload

1	sudo systemctl daemon-reload

to reload units.

Create the log file and assign the correct ownership and permissions:

sudo touch /var/log/transmission.log
sudo chown debian-transmission /var/log/transmission.log
sudo chmod 644 /var/log/transmission.log

sudo touch /var/log/transmission.log

sudo chown debian-transmission /var/log/transmission.log

sudo chmod 644 /var/log/transmission.log

Restart the service:

sudo service transmission-daemon restart

1	sudo service transmission-daemon restart

Have fun!

Add syntax color to crontab editor

Posted by cviorel on February 24, 2016 No comments | This post has 1,516 views.

You might be wondering why you don’t have syntax coloring when you are editing crontab entries.
This can be solved very simply by specifying your editor before starting crontab:

export EDITOR=vim

1	export EDITOR=vim

The change can be made persistent across the whole system if you add that line on the /etc/bashrc file.
You you only need this change for certain users only, add the line to their local ~/.bashrc files.

Set default gnome terminal size in Lucid Lynx

Posted by cviorel on May 26, 2010 No comments | This post has 554 views.

Use these commands to change the default gnome terminal size:

gconftool-2 --set /apps/gnome-terminal/profiles/Default/default_size_columns \
--type integer 160
gconftool-2 --set /apps/gnome-terminal/profiles/Default/default_size_rows \
--type integer 50

gconftool-2 --set /apps/gnome-terminal/profiles/Default/default_size_columns \

--type integer 160

gconftool-2 --set /apps/gnome-terminal/profiles/Default/default_size_rows \

--type integer 50

XMLStarlet: command line XML toolkit

Posted by cviorel on February 5, 2010 No comments | This post has 10,295 views.

XMLStarlet is a set of command line utilities (tools) which can be used to transform, query, validate, and edit XML documents and files using simple set of shell commands in similar way it is done for plain text files using UNIX grep, sed, awk, diff, patch, join, etc commands.

This set of command line utilities can be used by those who deal with many XML documents on UNIX shell command prompt as well as for automated XML processing with shell scripts.
Ubuntu allready has it in the repos, so issue the following command in your terminal to install it:

sudo aptitude install xmlstarlet

1	sudo aptitude install xmlstarlet

Vacuum your Firefox 3

Posted by cviorel on February 3, 2010 No comments | This post has 1,752 views.

This tip will hopefully speed up your Firefox 3 by optimizing it’s database.
Firefox stores your browsing data using SQLite – self contained, small footprint database.
Having database store your browsing data has one drawback, you have to optimize the database from time to time to get rid of old, deleted data, re-index the columns etc.
Otherwise you keep all the junk data mixed in with your real data.
In SQLite is the statement to do this called VACUUM. So here’s how to vacuum your Firefox:

1. Download sqlite command line client for your platform. In Ubuntu, go to yout terminal and issue the following command:

sudo aptitude install sqlite3

1	sudo aptitude install sqlite3

2. Turn off your Firefox
3. Go to your profile folder
4. Issue the command:
* For Linux

for i in *.sqlite; do echo "VACUUM;" | sqlite3 $i ; done

1	for i in *.sqlite; do echo "VACUUM;" \| sqlite3 $i ; done

* For Windows

for %i in (*.sqlite) do @echo VACUUM; | sqlite3 %i

1	for %i in (*.sqlite) do @echo VACUUM; \| sqlite3 %i

Enjoy!

Turn off or limit the Recent Documents feature in Ubuntu

Posted by cviorel on January 27, 2010 No comments | This post has 652 views.

If you want to turn off the Recent Documents feature in Ubuntu, all you have to do is create a .gtkrc file in your home directory.

touch $HOME/.gtkrc-2.0

1	touch $HOME/.gtkrc-2.0

Then add gtk-recent-files-max-age=0 to this file.

echo 'gtk-recent-files-max-age=0' | tee -a $HOME/.gtkrc-2.0

1	echo 'gtk-recent-files-max-age=0' \| tee -a $HOME/.gtkrc-2.0

This is documented here.
This will also work in any other linux distribution running Gnome.
Enjoy!

Ubuntu One coming to Windows

Posted by cviorel on January 26, 2010 No comments | This post has 474 views.

At PyCon 2010, Ubuntu One development team will focus on helping Ubuntu users who also use a Windows box. This is great news!
Source.

Disable splash screen in Ubuntu 9.10 Karmic Koala

Posted by cviorel on January 18, 2010 No comments | This post has 484 views.

You can disable the splash screen and instead view details about your boot process by following the next steps:
1. Edit grub

sudo vi /etc/default/grub

1	sudo vi /etc/default/grub

2. Locate the following line

GRUB_CMDLINE_LINUX_DEFAULT="quiet splash"

1	GRUB_CMDLINE_LINUX_DEFAULT="quiet splash"

and remove quiet and splash.
After that, the line should look like this:

GRUB_CMDLINE_LINUX_DEFAULT=""

1	GRUB_CMDLINE_LINUX_DEFAULT=""

3. Save and exit.

:wq

:wq

4. Now update the grub:

sudo update-grub

1	sudo update-grub

That’s it!

Karmic Koala Beta on ThinkPad T61 (continued)

Posted by cviorel on October 20, 2009 No comments | This post has 501 views.

After the latest updates, enabling touchpad on/off key (fn-f8) is now working. There are some annoying gdm random restarts without a trace in the logs. I suspect it’s the nvidia proprietary driver and I hope it will be fixed soon.
Here is a better approach to enable the Active Protection System in your ThinkPad:

# Install HDAPS - IBM Active Protection System Linux Driver
sudo cp /etc/modules /etc/modules_backup
sudo aptitude install tp-smapi-source
sudo module-assistant prepare tp-smapi
sudo module-assistant auto-install tp-smapi
sudo modprobe tp-smapi
sudo aptitude install hdapsd
echo 'tp-smapi' | sudo tee -a /etc/modules

# Install HDAPS - IBM Active Protection System Linux Driver

sudo cp /etc/modules /etc/modules_backup

sudo aptitude install tp-smapi-source

sudo module-assistant prepare tp-smapi

sudo module-assistant auto-install tp-smapi

sudo modprobe tp-smapi

sudo aptitude install hdapsd

echo 'tp-smapi' | sudo tee -a /etc/modules

Page 1 of 6
1
2
3
4
5
6
>

cviorel.com

Category Archives: Ubuntu

Configure Active Directory authentication with SQL Server on Linux

Prepare the Domain Controller

Join the Linux host to the domain

Install adutil

Create a domain user using adutil

Install SQL Server instance on the Linux host

Create an AD user for SQL Server and set the ServicePrincipalName (SPN) using adutil

Test the connections and the authentication scheme

Conclusion

Enable transmission-daemon logging to file

Add syntax color to crontab editor

Set default gnome terminal size in Lucid Lynx

XMLStarlet: command line XML toolkit

Vacuum your Firefox 3

Turn off or limit the Recent Documents feature in Ubuntu

Ubuntu One coming to Windows

Disable splash screen in Ubuntu 9.10 Karmic Koala

Karmic Koala Beta on ThinkPad T61 (continued)

Categories

Category Archives: Ubuntu

Prepare the Domain Controller

Join the Linux host to the domain

Install adutil

Create a domain user using adutil

Install SQL Server instance on the Linux host

Create an AD user for SQL Server and set the ServicePrincipalName (SPN) using adutil

Test the connections and the authentication scheme

Conclusion

Social

Categories

Tags