Teleport
Database Access with Microsoft SQL Server with Active Directory authentication (Preview)
How to Connect to Microsoft SQL Server Remotely Using Teleport
Length: 36:23
Database Access for Microsoft SQL Server with Active Directory authentication
is available starting from Teleport 9.0
.
Database Access for Microsoft SQL Server is currently in Preview mode.
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.
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 SQL Server network listener configured with a Certificate using Subject Alternative Names
- 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
tctl
andtsh
client tools version >= 11.3.2.tctl versionTeleport v11.3.2 go1.19
tsh versionTeleport v11.3.2 go1.19
See Installation for details.
-
A host where you will install the Teleport Auth Service and Proxy Service.
-
A registered domain name.
-
The
tctl
andtsh
client tools version >= 11.3.2, which you can download by visiting the customer portal.tctl versionTeleport v11.3.2 go1.19
tsh versionTeleport v11.3.2 go1.19
-
A host where you will install the Teleport Auth Service and Proxy Service.
-
A registered domain name.
-
The
tctl
andtsh
client tools version >= 11.2.1.You can download these from Teleport Cloud Downloads.
tctl versionTeleport v11.2.1 go1.19
tsh versionTeleport v11.2.1 go1.19
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
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.
To connect to Teleport, log in to your cluster using tsh
, then use tctl
remotely:
tsh login --proxy=teleport.example.com [email protected]tctl statusCluster teleport.example.com
Version 11.3.2
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 statusCluster myinstance.teleport.sh
Version 11.2.1
CA pin sha256:sha-hash-here
You must run subsequent tctl
commands in this guide on your local machine.
Step 2/7. Create a Teleport user
To modify an existing user to provide access to the Database Access service, see Database Access Access Controls
Create a local Teleport user with the built-in access
role:
tctl users add \ --roles=access \ --db-users=\* \ --db-names=\* \ alice
Flag | Description |
---|---|
--roles | List of roles to assign to the user. The builtin access role allows them to connect to any database server registered with Teleport. |
--db-users | List of database usernames the user will be allowed to use when connecting to the databases. A wildcard allows any user. |
--db-names | List of logical databases (aka schemas) the user will be allowed to connect to within a database server. A wildcard allows any database. |
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
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 updatesudo apt-get -y install sssd realmd krb5-user samba-common packagekit adcli
sudo yum -y updatesudo 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:
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 listexample.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.listsudo apt-get updatesudo ACCEPT_EULA=Y apt-get install -y adutil
sudo wget -qO /etc/apt/trusted.gpg.d/microsoft.asc https://packages.microsoft.com/keys/microsoft.ascsudo curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.listsudo apt-get updatesudo 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.reposudo ACCEPT_EULA=Y yum install -y adutil
Log in to Active Directory using the kinit
command:
kinit [email protected]
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 aliceadutil 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.
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.keytabKeytab name: FILE:teleport.keytab
KVNO Principal
---- --------------------------------------------------------------------------
5 [email protected] (aes256-cts-hmac-sha1-96)
2 [email protected] (aes256-cts-hmac-sha1-96)
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:
Next, use the appropriate commands for your environment to install your package.
Teleport Edition
Add the Teleport repository to your repository list:
Download Teleport's PGP public key
sudo curl https://apt.releases.teleport.dev/gpg \-o /usr/share/keyrings/teleport-archive-keyring.ascSource variables about OS version
source /etc/os-releaseAdd the Teleport APT repository for v11. You'll need to update this
file for each major release of Teleport.
Note: if using a fork of Debian or Ubuntu you may need to use '$ID_LIKE'
and the codename your distro was forked from instead of '$ID' and '$VERSION_CODENAME'.
Supported versions are listed here: https://github.com/gravitational/teleport/blob/master/build.assets/tooling/cmd/build-os-package-repos/runners.go#L42-L67
echo "deb [signed-by=/usr/share/keyrings/teleport-archive-keyring.asc] \https://apt.releases.teleport.dev/${ID?} ${VERSION_CODENAME?} stable/v11" \| sudo tee /etc/apt/sources.list.d/teleport.list > /dev/nullsudo apt-get updatesudo apt-get install teleport
Source variables about OS version
source /etc/os-releaseAdd the Teleport YUM repository for v11. You'll need to update this
file for each major release of Teleport.
Note: if using a fork of RHEL/CentOS or Amazon Linux you may need to use '$ID_LIKE'
and the codename your distro was forked from instead of '$ID'
Supported versions are listed here: https://github.com/gravitational/teleport/blob/master/build.assets/tooling/cmd/build-os-package-repos/runners.go#L133-L153
sudo yum-config-manager --add-repo $(rpm --eval "https://yum.releases.teleport.dev/$ID/$VERSION_ID/Teleport/%{_arch}/stable/v11/teleport.repo")sudo yum install teleportTip: Add /usr/local/bin to path used by sudo (so 'sudo tctl users add' will work as per the docs)
echo "Defaults secure_path = /sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin" > /etc/sudoers.d/secure_path
Optional: Use DNF on newer distributions
$ sudo dnf config-manager --add-repo https://rpm.releases.teleport.dev/teleport.repo
$ sudo dnf install teleport
In the example commands below, update $SYSTEM-ARCH
with the appropriate
value (amd64
, arm64
, or arm
). All example commands using this variable
will update after one is filled out.
curl https://get.gravitational.com/teleport-v11.3.2-linux--bin.tar.gz.sha256<checksum> <filename>
curl -O https://cdn.teleport.dev/teleport-v11.3.2-linux--bin.tar.gzshasum -a 256 teleport-v11.3.2-linux--bin.tar.gzVerify that the checksums match
tar -xvf teleport-v11.3.2-linux--bin.tar.gzcd teleportsudo ./install
In the example commands below, update $SYSTEM-ARCH
with the appropriate
value (amd64
, arm64
, or arm
). All example commands using this variable
will update after one is filled out.
After Downloading the .deb
file for your system architecture, install it with
dpkg
. The example below assumes the root
user:
dpkg -i ~/Downloads/teleport-ent_11.3.2_.debSelecting previously unselected package teleport-ent.
(Reading database ... 30810 files and directories currently installed.)
Preparing to unpack teleport-ent_11.3.2_$SYSTEM_ARCH.deb ...
Unpacking teleport-ent 11.3.2 ...
Setting up teleport-ent 11.3.2 ...
After Downloading the .rpm
file for your system architecture, install it with rpm
:
rpm -i ~/Downloads/teleport-ent-11.3.2..rpmwarning: teleport-ent-11.3.2.$SYSTEM-ARCH.rpm: Header V4 RSA/SHA512 Signature, key ID 6282c411: NOKEY
curl https://get.gravitational.com/teleport-ent-v11.3.2-linux--bin.tar.gz.sha256<checksum> <filename>
curl -O https://cdn.teleport.dev/teleport-ent-v11.3.2-linux--bin.tar.gzshasum -a 256 teleport-ent-v11.3.2-linux--bin.tar.gzVerify that the checksums match
tar -xvf teleport-ent-v11.3.2-linux--bin.tar.gzcd teleport-entsudo ./install
For FedRAMP/FIPS-compliant installations of Teleport Enterprise, package URLs will be slightly different:
curl https://get.gravitational.com/teleport-ent-v11.3.2-linux--fips-bin.tar.gz.sha256<checksum> <filename>
curl -O https://cdn.teleport.dev/teleport-ent-v11.3.2-linux--fips-bin.tar.gzshasum -a 256 teleport-ent-v11.3.2-linux--fips-bin.tar.gzVerify that the checksums match
tar -xvf teleport-ent-v11.3.2-linux--fips-bin.tar.gzcd teleport-entsudo ./install
In the example commands below, update $SYSTEM-ARCH
with the appropriate
value (amd64
, arm64
, or arm
). All example commands using this variable
will update after one is filled out.
After Downloading the .deb
file for your system architecture, install it with
dpkg
. The example below assumes the root
user:
dpkg -i ~/Downloads/teleport-ent_11.2.1_.debSelecting previously unselected package teleport-ent.
(Reading database ... 30810 files and directories currently installed.)
Preparing to unpack teleport-ent_11.2.1_$SYSTEM_ARCH.deb ...
Unpacking teleport-ent 11.2.1 ...
Setting up teleport-ent 11.2.1 ...
After Downloading the .rpm
file for your system architecture, install it with rpm
:
rpm -i ~/Downloads/teleport-ent-11.2.1..rpmwarning: teleport-ent-11.2.1.$SYSTEM-ARCH.rpm: Header V4 RSA/SHA512 Signature, key ID 6282c411: NOKEY
curl https://get.gravitational.com/teleport-ent-v11.2.1-linux--bin.tar.gz.sha256<checksum> <filename>
curl -O https://cdn.teleport.dev/teleport-ent-v11.2.1-linux-amd64-bin.tar.gzshasum -a 256 teleport-ent-v11.2.1-linux-amd64-bin.tar.gzVerify that the checksums match
tar -xvf teleport-ent-v11.2.1-linux-amd64-bin.tar.gzcd teleport-entsudo ./install
Before installing a teleport
binary with a version besides v11,
read our compatibility rules to ensure that the binary is compatible with
Teleport Cloud.
When running multiple teleport
binaries within a cluster, the following rules
apply:
- Patch and minor versions are always compatible, for example, any 8.0.1 component will work with any 8.0.3 component and any 8.1.0 component will work with any 8.3.0 component.
- Servers support clients that are 1 major version behind, but do not support
clients that are on a newer major version. For example, an 8.x.x Proxy Service
is compatible with 7.x.x resource services and 7.x.x
tsh
, but we don't guarantee that a 9.x.x resource service will work with an 8.x.x Proxy Service. This also means you must not attempt to upgrade from 6.x.x straight to 8.x.x. You must upgrade to 7.x.x first. - Proxy Services and resource services do not support Auth Services that are on
an older major version, and will fail to connect to older Auth Services by
default. This behavior can be overridden by passing
--skip-version-check
when starting Proxy Services and resource services.
Teleport Database Service must run on a Linux server joined to the same Active Directory domain as the SQL Server.
Configure the Teleport Database Service. Make sure to update --proxy
to
point to your Teleport Proxy Service address and --uri
to the SQL Server
endpoint.
sudo teleport db configure create \ -o file \ --token=/tmp/token \ --proxy=teleport.example.com:443 \ --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:
Flag | Description |
---|---|
--ad-keytab-file | Path to Kerberos keytab file generated above. |
--ad-domain | Active Directory domain (Kerberos realm) that SQL Server is joined. |
--ad-spn | Service Principal Name for SQL Server to fetch Kerberos tickets for. |
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:
If you don't see Attribute Editor tab, make sure that "View > Advanced Features" toggle is enabled.
Step 6/8. Start the Database Service
Start the Database Service:
teleport start --config=/etc/teleport.yaml
Step 7/8. Create SQL Server AD users
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 8/8. 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=alicetsh db lsName Description Labels
--------- ------------------- -------
sqlserver env=dev
To retrieve credentials for a database and connect to it:
tsh db connect --db-user=teleport sqlserver
The mssql-cli
command-line client should be available in PATH
of the machine
you're running tsh db connect
from.
mssql-cli
is not required for SQL Server connections. Use tsh proxy db --db-user=teleport --tunnel sqlserver
to connect from other DB Clients such as Microsoft SQL Server Management Studio.
To log out of the database and remove credentials:
tsh db logout sqlserver
Troubleshooting
Certificate error
If your tsh db connect
error includes the following text, the certificate used by SQL Server is not a known Certificate Authority.
Error message: TLS Handshake failed: x509: certificate signed by unknown authority
Add the Certificate Authority (CA) ca_cert_file
into the tls
section so Teleport can validate the certificate.
databases:
- name: sqlserver
protocol: sqlserver
uri: sqlserver.example.com:1433
ad:
keytab_file: /path/to/teleport.keytab
domain: EXAMPLE.COM
spn: MSSQLSvc/sqlserver.example.com:1433
static_labels:
"env": "dev"
tls:
ca_cert_file: /var/lib/teleport/cert.pem
Next steps
- Learn how to restrict access to certain users and databases.
- View the High Availability (HA) guide.
- Take a look at the YAML configuration reference.
- See the full CLI reference.
Further reading
- Manually join a Linux instance in the AWS documentation.
- Introduction to
adutil
in the Microsoft documentation.