Tag Archives: MSSQL

Configure Active Directory authentication with SQL Server on Linux

Posted by cviorel on December 13, 2020 No comments | This post has 2,549 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.

Always On Availability Groups using containers

Posted by cviorel on December 11, 2020 No comments | This post has 2,349 views.

The complete code can be found on my GitHub account

As a SQL Server person, I usually need to work with full blown Availability Groups for my various test scenarios.
I need to have a reliable and consistent way to rebuild the whole setup, multiple times a day.

For this purpose, docker containers are a perfect fit.
This approach will serve multiple scenarios (tsql development, performance tuning, infrastructure changes, etc.)

Target

Using the process I’ll explain below, I will deploy:

3 nodes running SQL Server 2019 Dev on top of Ubuntu 18.04
1 Clusterless Availability Group (also known as Read-Scale Availability Group)

Note that our Clusterless AG is not a high availability or disaster recovery solution.
It only provides a mechanism to synchronize databases across multiple servers (containers).
Only manual failover without data loss and forced failover with data loss is possible when using Read-Scale availability groups.

For production ready and true HA and DR one should look into traditional availability groups running on top of Windows Failover Cluster.
Another viable solution is to run SQL Server instance on Kubernetes in Azure Kubernetes Service (AKS), with persistent storage for high availability.

How

The workflow consist of the following steps:

prepare a custom docker image running Ubuntu 18.04 and SQL Server 2019
create a configuration file that will be used by docker-compose to spin up the 3 nodes

The actual build of the Availability Group will be performed by the entrypoint.sh script that will run on all the containers based on the image we just created.

The entrypoint.sh file is used to configure the container.
We just need to add a few .sql scripts that will get executed using sqlcmd utility.
In this case is the ag.sql file that contains the commands to create logins, certificates, endpoints and finally the Availability Group.

Remember, we’re using a Clusterless Availability Group, so the SQL Server service on Linux uses certificates to authenticate communication between the mirroring endpoints.

In a matter of minutes I have a fully working AG.

Credentials

During the build of the docker image and to create the AG I will need to specify various variables and credentials.

For production environments the recommended approach to manage secrets is to use a vault.

For my case I’m storing various variables and credentials in plain text files in the env folder.
Docker will parse those files and they will be available as environment variables.

sapassword.env – this contains the SA password and it’s needed when the custom image is built.

SA_PASSWORD=Str0ngPa$$w0rdForSA!

1	SA_PASSWORD=Str0ngPa$$w0rdForSA!

sqlserver.env – various variables are set here and are needed when the custom image is built.

ACCEPT_EULA=Y
MSSQL_DATA_DIR=/var/opt/sqlserver/data
MSSQL_LOG_DIR=/var/opt/sqlserver/log
MSSQL_BACKUP_DIR=/var/opt/sqlserver/backup
MSSQL_PID=Developer
MSSQL_AGENT_ENABLED=1

ACCEPT_EULA=Y

MSSQL_DATA_DIR=/var/opt/sqlserver/data

MSSQL_LOG_DIR=/var/opt/sqlserver/log

MSSQL_BACKUP_DIR=/var/opt/sqlserver/backup

MSSQL_PID=Developer

MSSQL_AGENT_ENABLED=1

miscpassword.env – will be needed to create the login and certificate needed by the Availability Group. This file is actually added to the container and it will be deleted after the Availability Group is created.

:SETVAR sa_password "Str0ngPa$$w0rdForSA!"
:SETVAR encryption_password "Str0ngPa$$w0rdForEnc!"
:SETVAR dbm_login_password "Str0ngPa$$w0rdForDBM!"

:SETVAR sa_password "Str0ngPa$$w0rdForSA!"

:SETVAR encryption_password "Str0ngPa$$w0rdForEnc!"

:SETVAR dbm_login_password "Str0ngPa$$w0rdForDBM!"

The advantage of this approach is that I have only one place where I store all these variables and credentials, but as I mentioned earlier, it’s not a proper solution from a security standpoint.

A few alternative approaches would be:
– use a tool to manage secrets, like Vault
– multi-stage builds
– use BuildKit

Conclusion

From a testing and development point of view, this solution works very well for me as I can rebuild the environment in a fast and consistent way.

It’s not by any means the best option out there, but it’s really simple to use and reproduce.

See it in action

Click on the image for the full gif

Wrong network location profile causes issues with Windows Failover Cluster

Posted by cviorel on July 25, 2020 2 comments | This post has 24,737 views.

Hi folks,

The other day I was pulling hair from my head trying to configure a Windows Failover Cluster intended for an SQL Server Availability Group setup.

During the cluster validation stage I always got this message:

The Windows Firewall on node node01.domain.local is not properly configured for failover clustering.
In particular, the ‘Public’ firewall profile is enabled on adapter ‘node01.domain.local – SLOT 1 PORT 2’.
The ‘Failover Clusters’ rule group is not enabled in firewall profile ‘Public’.
This may prevent some network communication between cluster nodes.

The OS install and networking part was already configured by a someone else and it was a pretty straightforward installation.

The issue turned out to be caused by the 2 NICs we have for iSCSI traffic which did not have a gateway configured.

Windows uses gateways to identify networks. If it doesn’t have a gateway configured, or if it can’t successfully ping it, it will not be able to identify the network it’s connected to and will assume it’s a public one.

Network cards in Windows can be connected to one of these type of networks:
– Public
– Private
– DomainAuthenticated

By default, the public network location type is assigned to any new networks when they are first connected.

A public network is considered to be shared with the world, with no protection between the local computer and any other computer. Therefore, the Windows Firewall rules associated with the public profile are the most restrictive.

As part of the Windows Failover Cluster validation/creation there are checks to verify connectivity (between cluster nodes, active directory, etc.).

These were the settings I had:
Before

All I needed to do was to move all non-domain network interfaces into the private profile:

Get-NetConnectionProfile | Where-Object { $_.NetworkCategory -ne 'DomainAuthenticated' } | Set-NetConnectionProfile -NetworkCategory Private

1	Get-NetConnectionProfile \| Where-Object { $_.NetworkCategory -ne 'DomainAuthenticated' } \| Set-NetConnectionProfile -NetworkCategory Private

After

After the change the cluster creation went without issue.

This small detail be easily missed and can cause a lot of headaches and lost time investigating failover clusters.

Cheers!

Beautify SQL code

Posted by cviorel on March 15, 2011 No comments | This post has 401 views.

Recently I had the oportunity to test SQL Pretty Printer (Add-In for SSMS). I don’t have to waste time formatting long sql queries. In no time, SQL Pretty Printer does the job
for me. It can also translate the sql code into C#, Java, Php and many other program languages so I can use it in my own programs.
SQL Pretty printer is designed to deal with SQL statement used by different Database Such as MSSQL, Oracle, DB2, Informix, Sybase, Postgres, MySQL and so on. The code conforms to most of the entry-level SQL99 Standard.
To use this add-in you need to have SQL Server Management Studio (SSMS), with .NET2.0 installed. In sql editor you can use shortcut key (ctrl+k,ctrl+j for all sqls, and ctrl+k, ctrl+h for selected sql). There is also a toolbar with two buttons to format sql or selected sql.
To see it in action, take a look here, where you have some sample code blocks before and after the formatting.

cviorel.com

Tag Archives: MSSQL

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

Always On Availability Groups using containers

Target

How

Credentials

Conclusion

See it in action

Wrong network location profile causes issues with Windows Failover Cluster

Beautify SQL code

Categories

cviorel.com

Tag Archives: MSSQL

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

Always On Availability Groups using containers

Target

How

Credentials

Conclusion

See it in action

Wrong network location profile causes issues with Windows Failover Cluster

Beautify SQL code

Social

Categories

Tags