Fork me on GitHub
Teleport

Database Access with Microsoft SQL Server with Active Directory authentication (Preview)

Improve

Database Access for Microsoft SQL Server with Active Directory authentication is available starting from Teleport 9.0.

Preview

Database access for Microsoft SQL Server is currently in a Preview mode and does not include audit logging of database query activity.

This guide will help you to:

  • Install and configure Teleport.
  • Set up access to SQL Server using Active Directory authentication.
  • Connect to SQL Server through Teleport.

Teleport Database Access SQL Server Self-Hosted

This guide will focus on Amazon RDS for SQL Server using AWS-managed Active Directory authentication.

Prerequisites

  • A SQL Server database with Active Directory authentication enabled.
  • A Windows machine joined to the same Active Directory domain as the database.
  • A Linux node joined to the same Active Directory domain as the database. This guide will walk you through the joining steps if you don't have one.
  • The tsh client tool version >= 9.2.4.

    tsh version

    Teleport v9.2.4 go1.17

    See Installation for details.

  • A host where you will install the Teleport Auth Service and Proxy Service.

  • A registered domain name.

  • The tsh client tool version >= 9.2.4, which you can download by visiting the customer portal.

    tsh version

    Teleport v9.2.4 go1.17

  • A host where you will install the Teleport Auth Service and Proxy Service.

  • A registered domain name.

  • The tctl and tsh client tools version >= 9.2.4.

    You can download these from Teleport Cloud Downloads.

    tctl version

    Teleport v9.2.4 go1.17

    tsh version

    Teleport v9.2.4 go1.17

To connect to Teleport, log in to your cluster using tsh, then use tctl remotely:

tsh login --proxy=teleport.example.com [email protected]
tctl status

Cluster teleport.example.com

Version 9.2.4

CA pin sha256:abdc1245efgh5678abdc1245efgh5678abdc1245efgh5678abdc1245efgh5678

You can run subsequent tctl commands in this guide on your local machine.

For full privileges, you can also run tctl commands on your Auth Service host.

To connect to Teleport, log in to your cluster using tsh, then use tctl remotely:

tsh login --proxy=myinstance.teleport.sh [email protected]
tctl status

Cluster myinstance.teleport.sh

Version 9.2.4

CA pin sha256:sha-hash-here

You must run subsequent tctl commands in this guide on your local machine.

Step 1/7. Set up the Teleport Auth and Proxy

On the host where you will run the Auth Service and Proxy Service, download the latest version of Teleport for your platform from our downloads page and follow the installation instructions.

Teleport requires a valid TLS certificate to operate and can fetch one automatically using Let's Encrypt's ACME protocol. Before Let's Encrypt can issue a TLS certificate for the Teleport Proxy host's domain, the ACME protocol must verify that an HTTPS server is reachable on port 443 of the host.

You can configure the Teleport Proxy service to complete the Let's Encrypt verification process when it starts up.

Run the following teleport configure command, where tele.example.com is the domain name of your Teleport cluster and [email protected] is an email address used for notifications (you can use any domain):

teleport configure --acme [email protected] --cluster-name=tele.example.com > /etc/teleport.yaml

The --acme, --acme-email, and --cluster-name flags will add the following settings to your Teleport configuration file:

proxy_service:
  enabled: "yes"
  web_listen_addr: :443
  public_addr: tele.example.com:443
  acme:
    enabled: "yes"
    email: [email protected]

Port 443 on your Teleport Proxy Service host must allow traffic from all sources.

Next, start the Teleport Auth and Proxy Services:

sudo teleport start

You will run subsequent tctl commands on the host where you started the Auth and Proxy Services.

If you do not have a Teleport Cloud account, use our signup form to get started. Teleport Cloud manages instances of the Proxy Service and Auth Service, and automatically issues and renews the required TLS certificate.

You must log in to your cluster before you can run tctl commands.

tsh login --proxy=mytenant.teleport.sh
tctl status

Step 2/7. Create a Teleport user

Create a local Teleport user with the built-in access role:

tctl users add \ --roles=access \ --db-users=\* \ --db-names=\* \ alice
FlagDescription
--rolesList of roles to assign to the user. The builtin access role allows them to connect to any database server registered with Teleport.
--db-usersList of database usernames the user will be allowed to use when connecting to the databases. A wildcard allows any user.
--db-namesList of logical databases (aka schemas) the user will be allowed to connect to within a database server. A wildcard allows any database.
Warning

Database names are only enforced for PostgreSQL and MongoDB databases.

For more detailed information about database access controls and how to restrict access see RBAC documentation.

Step 3/7. Join the Linux node to Active Directory

Note

You can skip this step if you already have a Linux node joined to the same Active Directory domain as your SQL Server instance.

The Linux node where the Database Service will run must be joined to the same Active Directory domain as the SQL Server database.

Note that in order to be able to join, the Linux node must be able to resolve your Active Directory fully-qualified domain name. For example, for AWS-managed AD, use nameservers provided under "Networking details" on the directory's overview page.

Install necessary packages:

sudo apt-get update
sudo apt-get -y install sssd realmd krb5-user samba-common packagekit adcli
sudo yum -y update
sudo yum -y install sssd realmd krb5-workstation samba-common-tools

Edit /etc/krb5.conf to disable reverse DNS resolution and set the default realm. Make sure that the [realms] section contains your domain definition and has admin_server and kdc fields set pointing to the domain controllers:

[libdefaults]
default_realm = EXAMPLE.COM
rdns = false

[realms]
  EXAMPLE.COM = {
    kdc = example.com
    admin_server = example.com
  }

Join the realm:

sudo realm join -v -U [email protected] example.com

...

* Successfully enrolled machine in realm

Warning

Note that the realm name in [email protected] must be capital case, otherwise the node might not be able to join.

To confirm the node has joined the realm, use the realm list command:

sudo realm list

example.com

type: kerberos

realm-name: EXAMPLE.COM

domain-name: example.com

configured: kerberos-member

server-software: active-directory

client-software: sssd

...

Step 4/7. Create keytab file

Teleport requires a keytab file to obtain Kerberos service tickets from your Active Directory for authentication with SQL Server. The easiest way to generate it is to use the adutil Linux CLI utility.

Install adutil on the Linux node you have joined to your Active Directory domain:

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
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y adutil
sudo wget -qO /etc/apt/trusted.gpg.d/microsoft.asc https://packages.microsoft.com/keys/microsoft.asc
sudo curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y adutil
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo
sudo ACCEPT_EULA=Y yum install -y adutil

Log in to Active Directory using the kinit command:

Use the adutil keytab create command to generate keytab entries for each Active Directory user that will be connecting to the SQL Server database:

adutil keytab create teleport.keytab alice
adutil keytab create teleport.keytab bob

You will be prompted to enter each user's password. All keytab entries will be merged into the same teleport.keytab file.

Assign Service Principal Names

For the adutil keytab create command to work, each user account must be assigned a Service Principal Name, otherwise the command will not be able to determine its kvno (key version number).

To check if the user has any SPNs assigned, run the following command on the Windows machine joined to your Active Directory domain:

setspn -L alice

To assign an SPN to a user account, use the following command:

setspn -s user/alice alice

You can verify entries in the keytab file using klist command:

klist -ke teleport.keytab

Keytab name: FILE:teleport.keytab

KVNO Principal

---- --------------------------------------------------------------------------

5 [email protected] (aes256-cts-hmac-sha1-96)

2 [email protected] (aes256-cts-hmac-sha1-96)

Warning

You must update the keytab file after updating a user's password to avoid authentication failures.

Step 5/7. Set up the Teleport Database Service

The Database Service requires a valid auth token to connect to the cluster. Generate one by running the following command against your Teleport Auth Service and save it in /tmp/token on the node that will run the Database Service:

tctl tokens add --type=db

Install Teleport on the host where you will run the Teleport Database Service:

Download Teleport's PGP public key

sudo curl https://deb.releases.teleport.dev/teleport-pubkey.asc \ -o /usr/share/keyrings/teleport-archive-keyring.asc

Add the Teleport APT repository

echo "deb [signed-by=/usr/share/keyrings/teleport-archive-keyring.asc] https://deb.releases.teleport.dev/ stable main" \| sudo tee /etc/apt/sources.list.d/teleport.list > /dev/null
sudo apt-get update
sudo apt-get install teleport
sudo yum-config-manager --add-repo https://rpm.releases.teleport.dev/teleport.repo
sudo yum install teleport

Optional: Using DNF on newer distributions

$ sudo dnf config-manager --add-repo https://rpm.releases.teleport.dev/teleport.repo

$ sudo dnf install teleport

curl https://get.gravitational.com/teleport-v9.2.4-linux-amd64-bin.tar.gz.sha256

<checksum> <filename>

curl -O https://get.gravitational.com/teleport-v9.2.4-linux-amd64-bin.tar.gz
shasum -a 256 teleport-v9.2.4-linux-amd64-bin.tar.gz

Verify that the checksums match

tar -xzf teleport-v9.2.4-linux-amd64-bin.tar.gz
cd teleport
sudo ./install
curl https://get.gravitational.com/teleport-v9.2.4-linux-arm-bin.tar.gz.sha256

<checksum> <filename>

curl -O https://get.gravitational.com/teleport-v9.2.4-linux-arm-bin.tar.gz
shasum -a 256 teleport-v9.2.4-linux-arm-bin.tar.gz

Verify that the checksums match

tar -xzf teleport-v9.2.4-linux-arm-bin.tar.gz
cd teleport
sudo ./install
curl https://get.gravitational.com/teleport-v9.2.4-linux-arm64-bin.tar.gz.sha256

<checksum> <filename>

curl -O https://get.gravitational.com/teleport-v9.2.4-linux-arm64-bin.tar.gz
shasum -a 256 teleport-v9.2.4-linux-arm64-bin.tar.gz

Verify that the checksums match

tar -xzf teleport-v9.2.4-linux-arm64-bin.tar.gz
cd teleport
sudo ./install
Note

Teleport Database Service must run on a Linux server joined to the same Active Directory domain as the SQL Server.

Start the Teleport Database Service. Make sure to update --auth-server to point to your Teleport Proxy Service address and --uri to the SQL Server endpoint.

teleport db start \ --token=/tmp/token \ --auth-server=teleport.example.com:3080 \ --name=sqlserver \ --protocol=sqlserver \ --uri=sqlserver.example.com:1433 \ --ad-keytab-file=/path/to/teleport.keytab \ --ad-domain=EXAMPLE.COM \ --ad-spn=MSSQLSvc/sqlserver.example.com:1433 \ --labels=env=dev

Provide Active Directory parameters:

FlagDescription
--ad-keytab-filePath to Kerberos keytab file generated above.
--ad-domainActive Directory domain (Kerberos realm) that SQL Server is joined.
--ad-spnService Principal Name for SQL Server to fetch Kerberos tickets for.
Tip

You can start the Teleport Database Service using a configuration file instead of CLI flags. See the YAML reference.

Service Principal Name

You can use ldapsearch command to see the SPNs registered for your SQL Server. Typically, they take a form of MSSQLSvc/<name>.<ad-domain>:<port>.

For example, an AWS RDS SQL Server named sqlserver and joined to an AWS managed Active Directory domain EXAMPLE.COM will have the following SPNs registered:

ldapsearch -x -h example.com -D admin -W -b DC=example,DC=com servicePrincipalName

...

EC2AMAZ-4KN05DU, RDS, AWS Reserved, example.com

dn: CN=EC2AMAZ-4KN05DU,OU=RDS,OU=AWS Reserved,DC=example,DC=com

servicePrincipalName: MSSQLSvc/sqlserver-rds.example.com:1433

servicePrincipalName: MSSQLSvc/EC2AMAZ-4KN05DU.example.com:1433

servicePrincipalName: MSSQLSvc/EC2AMAZ-4KN05DU.example.com

...

Alternatively, you can look SPNs up in the Attribute Editor of the Active Directory Users and Computers dialog on your AD-joined Windows machine. The RDS SQL Server object typically resides under the AWS Reserved / RDS path:

SPN

Tip

If you don't see Attribute Editor tab, make sure that "View > Advanced Features" toggle is enabled.

Step 6/7. Create SQL Server AD users

Note

You can skip this step if you already have Active Directory logins in your SQL Server.

Connect to your SQL Server as an administrative account (e.g. sa) and create logins that will use Active Directory authentication:

master> CREATE LOGIN [EXAMPLE\alice] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english];

Step 7/7. Connect

Log in to your Teleport cluster. Your SQL Server database should appear in the list of available databases:

tsh login --proxy=teleport.example.com --user=alice
tsh db ls

Name Description Labels

--------- ------------------- -------

sqlserver env=dev

Fetch the short-lived client certificate for it using the tsh db login command:

tsh db login --db-user=teleport sqlserver
Tip

You can be logged in to multiple databases simultaneously.

Now connect to the database:

tsh db connect sqlserver
Note

The mssql-cli command-line client should be available in PATH of the machine you're running tsh db connect from.

To log out of the database and remove credentials:

tsh db logout sqlserver

Next steps

Further reading