Fork me on GitHub

Teleport

Database Access with SQL Server on Azure (Preview)

Improve

Database Access for Azure SQL Server with Azure Active Directory authentication is available starting from Teleport 11.0.

Preview

Database Access for Azure SQL Server with Azure Active Directory authentication is currently in Preview mode.

This guide will help you to:

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

Teleport Database Access Azure SQL Server Azure Active Directory Self-Hosted

Prerequisites

  • SQL Server running on Azure.
  • The Teleport Database Service running on an Azure virtual instance.
  • The tctl and tsh client tools version >= 11.0.3.

    tctl version

    Teleport v11.0.3 go1.19

    tsh version

    Teleport v11.0.3 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 and tsh client tools version >= 11.0.3, which you can download by visiting the customer portal.

    tctl version

    Teleport v11.0.3 go1.19

    tsh version

    Teleport v11.0.3 go1.19

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

  • A registered domain name.

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

    You can download these from Teleport Cloud Downloads.

    tctl version

    Teleport v10.3.8 go1.19

    tsh version

    Teleport v10.3.8 go1.19

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

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 status

Cluster teleport.example.com

Version 11.0.3

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 10.3.8

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

Tip

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
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. Enable the SQL Server Azure Active Directory integration

If you have it enabled, you can go to the next step.

Go to the Azure Portal, select Database servers, and select the database you wish to enable the Azure Active Directory integration.

Select Azure Active Directory in the left-hand column.

Select Set Admin, and choose an account that will be added as an admin login to SQL Server.

Azure SQL Server Azure Active Directory admin page

Step 4/7. Configure Azure Managed Identities

The Teleport Database Service needs access tokens from Azure AD to authenticate with SQL Server databases.

It uses the managed identities attached to its Virtual Machine to fetch the authentication token.

To create a new user-assigned managed identity, go to the Managed Identities page in your Azure Portal and click on Create. Choose a name and resource group for it and create:

Azure Create user managed identity page

Next, go to the Teleport Database Service virtual machine instance, Identity section, select User assigned, and add the identity we just created:

Azure Virtual machine user managed identities page

Step 5/7. Enable managed identities login on SQL Server

Azure AD SQL Server integration uses database-level authentication (contained users), meaning we must create a user for our identities on each database we want to access.

To create contained users for the identities, connect to your SQL server using its Activity Directory Admin and execute the query:

USE MyDatabase;
CREATE USER [sqlserver-identity] FROM EXTERNAL PROVIDER;

The newly created user will be attached to the public role, which might not have enough permissions to perform queries. Consider granting individual permissions to the user or assigning it to an existing role. For example, add the user as a member of the db_datareader role:

ALTER ROLE db_datareader ADD MEMBER [sqlserver-identity];

Step 6/7. Start Teleport Database Service

Generate a configuration file at /etc/teleport.yaml for the Database Service:

teleport db configure create \ -o file \ --token=/tmp/token \ --proxy=teleport.example.com:3080 \ --name=sqlserver \ --protocol=sqlserver \ --uri=my-server.database.windows.net:1433 \ --labels=env=dev
Tip

A single Teleport process can run multiple different services, for example multiple Database Service agents as well as the SSH Service or Application Service.

Start the Database Service:

teleport start --config=/etc/teleport.yaml

Step 7/7. Connect

Log in to your Teleport cluster. Your 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

To retrieve credentials for a database and connect to it:

tsh db connect --db-user=sqlserver-identity --db-name=master sqlserver

Where --db-user is the managed identity name.

Note

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

Troubleshooting

Could not find identity

If you see the error could not find identity "my-identity" attached to the instance when connecting to your database, then the identity you’re trying to connect with is not attached to the Teleport Database Service virtual machine. You can navigate to the Virtual Machines page within Azure Portal, open the Teleport instance, Identity section, and choose User assigned to see all identities you can connect with. If you don’t see your identity check Step 4 to see how to add it.

Login failed for the user

When connecting to your database, and you see the error mssql: login error: Login failed for user '<token-identified principal>', it means your managed identity login is not present on the SQL database. You’ll need to create their users as described in Step 5. Remember: you must create the users on all databases you want to connect.

Next steps

  • Take a look at the YAML configuration reference.