# Database Access with Azure SQL Server Teleport can provide secure access to Azure SQL Server via the [Teleport Database Service](https://goteleport.com/docs/enroll-resources/database-access.md). This allows for fine-grained access control through [Teleport's RBAC](https://goteleport.com/docs/enroll-resources/database-access/rbac.md). In this guide, you will: 1. Configure your Azure SQL Server database with Microsoft Entra ID-based authentication. 2. Add the database to your Teleport cluster. 3. Connect to the database via Teleport. ## How it works The Teleport Database Service runs on an Azure virtual machine with an attached Azure identity with permissions to retrieve authentication tokens from Microsoft Entra ID. When a user connects to SQL Server with Teleport, the Teleport Database service authenticates with Microsoft Entra ID, then uses an authentication token to connect to SQL Server. The Database Service then forwards user traffic to the database. **Self-Hosted** ![Access Azure SQL Server Microsoft Entra Self-Hosted](/docs/assets/images/sql-aad-2ef270c97c1b7762924fdab66d36cad8.png) **Teleport Enterprise Cloud** ![Access Azure SQL Server Microsoft Entra Cloud](/docs/assets/images/cloud-sql-aad-aed31e33d60c9f79f7d5b7625512c79e.png) ## Prerequisites - A running Teleport cluster. If you want to get started with Teleport, [sign up](https://goteleport.com/signup) for a free trial or [set up a demo environment](https://goteleport.com/docs/get-started/deploy-community.md). - The `tctl` and `tsh` clients. Installing `tctl` and `tsh` clients 1. Determine the version of your Teleport cluster. The `tctl` and `tsh` clients must be at most one major version behind your Teleport cluster version. Send a GET request to the Proxy Service at `/v1/webapi/find` and use a JSON query tool to obtain your cluster version. Replace teleport.example.com:443 with the web address of your Teleport Proxy Service: ``` $ TELEPORT_DOMAIN=teleport.example.com:443 $ TELEPORT_VERSION="$(curl -s https://$TELEPORT_DOMAIN/v1/webapi/find | jq -r '.server_version')" ``` 2. Follow the instructions for your platform to install `tctl` and `tsh` clients: **Mac** Download the signed macOS .pkg installer for Teleport, which includes the `tctl` and `tsh` clients: ``` $ curl -O https://cdn.teleport.dev/teleport-${TELEPORT_VERSION?}.pkg ``` In Finder double-click the `pkg` file to begin installation. --- DANGER Using Homebrew to install Teleport is not supported. The Teleport package in Homebrew is not maintained by Teleport and we can't guarantee its reliability or security. --- **Windows - Powershell** ``` $ curl.exe -O https://cdn.teleport.dev/teleport-v${TELEPORT_VERSION?}-windows-amd64-bin.zip Unzip the archive and move the `tctl` and `tsh` clients to your %PATH% NOTE: Do not place the `tctl` and `tsh` clients in the System32 directory, as this can cause issues when using WinSCP. Use %SystemRoot% (C:\Windows) or %USERPROFILE% (C:\Users\) instead. ``` **Linux** All of the Teleport binaries in Linux installations include the `tctl` and `tsh` clients. For more options (including RPM/DEB packages and downloads for i386/ARM/ARM64) see our [installation page](https://goteleport.com/docs/installation.md). ``` $ curl -O https://cdn.teleport.dev/teleport-v${TELEPORT_VERSION?}-linux-amd64-bin.tar.gz $ tar -xzf teleport-v${TELEPORT_VERSION?}-linux-amd64-bin.tar.gz $ cd teleport $ sudo ./install Teleport binaries have been copied to /usr/local/bin ``` * SQL Server running on Azure. * The Teleport Database Service running on an Azure virtual instance. * To check that you can connect to your Teleport cluster, sign in with `tsh login`, then verify that you can run `tctl` commands using your current credentials. For example, run the following command, assigning teleport.example.com to the domain name of the Teleport Proxy Service in your cluster and email\@example.com to your Teleport username: ``` $ tsh login --proxy=teleport.example.com --user=email@example.com $ tctl status Cluster teleport.example.com Version 19.0.0-dev CA pin sha256:abdc1245efgh5678abdc1245efgh5678abdc1245efgh5678abdc1245efgh5678 ``` If you can connect to the cluster and run the `tctl status` command, you can use your current credentials to run subsequent `tctl` commands from your workstation. If you host your own Teleport cluster, you can also run `tctl` commands on the computer that hosts the Teleport Auth Service for full permissions. ## Step 1/8. Create a Teleport user --- TIP To modify an existing user to provide access to the Database Service, see [Database Access Controls](https://goteleport.com/docs/enroll-resources/database-access/rbac.md) --- **Teleport Community Edition** Create a local Teleport user with the built-in `access` role: ``` $ tctl users add \ --roles=access \ --db-users="*" \ --db-names="*" \ alice ``` **Teleport Enterprise/Enterprise Cloud** Create a local Teleport user with the built-in `access` and `requester` roles: ``` $ tctl users add \ --roles=access,requester \ --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. | --- WARNING Database names are only enforced for PostgreSQL, MongoDB, and Cloud Spanner databases. --- For more detailed information about database access controls and how to restrict access see [RBAC](https://goteleport.com/docs/enroll-resources/database-access/rbac.md) documentation. ## Step 2/8. Enable the Microsoft Entra authentication If you have it enabled, you can go to the next step. Go to the [Azure Portal](https://portal.azure.com/), select **Database servers**, and select the database you wish to enable the Microsoft Entra authentication. Select **Microsoft Entra ID** under "Settings" 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 Microsoft Entra admin page](/docs/assets/images/azure-set-ad-admin-981a45584d364454206d16bf20261543.png) ## Step 3/8. Configure IAM permissions for Teleport The Teleport Database Service needs Azure IAM permissions to: - Discover and register SQL Server instances. - Fetch virtual machine managed identities used for login. ### Configure an Azure service principal Teleport requires the following permissions: - `/read` permissions for discovery, but only for the resource types you have. For example, `Microsoft.Sql/managedInstances/read`. - `Microsoft.Compute/virtualMachines/read`. Here is a sample role definition allowing Teleport to read **Azure SQL Servers** and **Azure SQL Managed Instances**: ``` { "properties": { "roleName": "SQLServerAutoDiscovery", "description": "Allows Teleport to discover SQL Servers and SQL Managed Instances.", "assignableScopes": [ "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" ], "permissions": [ { "actions": [ "Microsoft.Sql/managedInstances/read", "Microsoft.Sql/servers/read", "Microsoft.Compute/virtualMachines/read" ], "notActions": [], "dataActions": [], "notDataActions": [] } ] } } ``` The `assignableScopes` field above includes a subscription (`/subscriptions/`), allowing the role to be assigned at any resource scope within that subscription or the subscription scope itself. If you want to further limit the `assignableScopes`, you can use a resource group (`/subscriptions//resourceGroups/`) or a management group (`/providers/Microsoft.Management/managementGroups/`) instead. Go to the [Subscriptions](https://portal.azure.com/#view/microsoft_azure_billing/subscriptionsblade) page and select a subscription. Click on **Access control (IAM)** in the subscription and select **Add** > **Add custom role**: ![IAM custom role](/docs/assets/images/add-custom-role@2x-9b486170e269494f0a84ad7af362a346.png) In the custom role creation page, click the **JSON** tab and click **Edit**, then paste the JSON example and replace the subscription in `assignableScopes` with your own subscription id: ![Create JSON role](/docs/assets/images/create-role-from-json-e4376d3a5f6d806c848ae41da511d77d.png) ## Step 4/8. Configure virtual machine identities In the Teleport Database Service virtual machine's **Identity** section, enable the system assigned identity. This is used by Teleport to access Azure APIs. ![System assigned identity page](/docs/assets/images/system-managed-identity-16cc1328949c28f45ad33753eda08619.png) To grant Teleport permissions, the custom role you created must be assigned to the virtual machine system assigned identity. On the same page, click on the **Azure role assignments**, then on **Add role assignment**. Select the custom role and save. --- AZURE ROLE ASSIGNMENTS The role assignment should be at a high enough scope to allow the Teleport Database Service to discover all matching databases. See [Identify the needed scope](https://docs.microsoft.com/en-us/azure/role-based-access-control/role-assignments-steps#step-3-identify-the-needed-scope) for more information about Azure scopes and creating role assignments. --- ### Login identities The Teleport Database Service needs access tokens from Microsoft Entra ID 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](https://portal.azure.com/#view/hubsextension/browseresource/resourcetype/microsoft.managedidentity%2fuserassignedidentities) and click on *Create*. Choose a name and resource group for it and create: ![Azure Create user managed identity page](/docs/assets/images/azure-user-managed-identity-246d641272d0a18fbce1d22f04855925.png) 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](/docs/assets/images/azure-attach-managed-identity-vm-3361bb1d30905b19e46ebe90ed0fa4b4.png) ## Step 5/8. Enable managed identities login on SQL Server The Microsoft Entra ID 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/8. Create a Database Service configuration Install Teleport on the host where you will run the Teleport Database Service: To install a Teleport Agent on your Linux server: The recommended installation method is the cluster install script. It will select the correct version, edition, and installation mode for your cluster. 1. Assign teleport.example.com:443 to your Teleport cluster hostname and port, but not the scheme (https\://). 2. Run your cluster's install script: ``` $ curl "https://teleport.example.com:443/scripts/install.sh" | sudo bash ``` Generate a configuration file at `/etc/teleport.yaml` for the Database Service. Set the `--proxy` command-line option to the address for your Teleport cluster and the `--azure-sqlserver-discovery` to the appropriate region: ``` $ sudo teleport db configure create \ -o file \ --token=/tmp/token \ --proxy=teleport.example.com:443 \ --azure-sqlserver-discovery=eastus ``` The command will generate a Database Service configuration with Azure SQL Server auto-discovery enabled in the `eastus` region and place it at the `/etc/teleport.yaml` location. ## Step 7/8. Start Teleport Database Service Configure the Teleport Database Service to start automatically when the host boots up by creating a systemd service for it. The instructions depend on how you installed the Teleport Database Service. **Package Manager** On the host where you will run the Teleport Database Service, enable and start Teleport: ``` $ sudo systemctl enable teleport $ sudo systemctl start teleport ``` **TAR Archive** On the host where you will run the Teleport Database Service, create a systemd service configuration for Teleport, enable the Teleport service, and start Teleport: ``` $ sudo teleport install systemd -o /etc/systemd/system/teleport.service $ sudo systemctl enable teleport $ sudo systemctl start teleport ``` You can check the status of the Teleport Database Service with `systemctl status teleport` and view its logs with `journalctl -fu teleport`. --- 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. --- ## Step 8/8. 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 Allowed Users Labels Connect ------------------ ---------------------------------- ------------- ------ ------- sqlserver Azure SQL Server in westeurope [*] ... sqlserver-managed Azure Managed SQL Server in eastus [*] ... ``` 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 Either the `sqlcmd` or `mssql-cli` command-line clients should be available in `PATH` in order to be able to connect. `tsh` attempts to run `sqlcmd` first and, if it's not present on the `PATH`, runs `mssql-cli`. If you have neither command-line clients available on your system, you can run the following command to start a local proxy server that you can connect to with your SQL Server client: ``` $ tsh proxy db --db-user=teleport --tunnel sqlserver ``` Read the [Database GUI Clients](https://goteleport.com/docs/connect-your-client/third-party/gui-clients.md#sql-server-with-azure-data-studio) guide for how to connect your DB GUI client to the local proxy. --- ## 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](https://portal.azure.com/#view/hubsextension/browseresource/resourcetype/microsoft.compute%2fvirtualmachines) 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](#login-identities) 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 ''`, 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](#step-58-enable-managed-identities-login-on-sql-server). Remember: you must create the users on all databases you want to connect. ### Timeout connecting to the database If you receive the error `i/o timeout issue` when connecting to your database, please check if the Teleport Database service can reach the database on Azure. In case your database is public, you can enable it to receive connections from Azure services without creating firewall rules: Go to your database page, **Network** tab, and at the bottom of the page in the **Exceptions** section check the option "Allow Azure services and resources to access this server" and save. If your database is not public and it is using private endpoints, ensure that they're on the same VPC, or if the Teleport VM VPC is peering with the database one. To check if the VM has access, you can do the following on the VM: - Using `netcat`: `nc -v yourdatabase.database.windows.net 1433` - Using `telnet`: `telnet yourdatabase.database.windows.net 1433` ## Next steps - Learn how to [restrict access](https://goteleport.com/docs/enroll-resources/database-access/rbac.md) to certain users and databases. * View the [High Availability (HA)](https://goteleport.com/docs/enroll-resources/agents/high-availability.md) guide. - Take a look at the YAML configuration [reference](https://goteleport.com/docs/enroll-resources/database-access/reference/configuration.md). * See the full CLI [reference](https://goteleport.com/docs/enroll-resources/database-access/reference/cli.md). ## Further reading - [Microsoft Entra authentication for Azure SQL](https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview?view=azuresql)