One of the greatest features of the Windows operating system is Active Directory. Password-less authentication usually "just works" for logins in an Active Directory configuration, and SQL Server takes full advantage of this feature. If you enable mixed mode authentication in SQL Server you can securely connect to the instance without sending a password thanks to Kerberos tokens.

Things don't quite work this way in the Platform as a Service (PaaS) offering of SQL Server, Azure SQL Database. Your PaaS SQL DBs can only integrate with Azure Active Directory, and even if you use Azure AD Connect to connect it with an on-premises domain, you just can't connect to Azure SQL Database via Kerberos. Luckily there is an alternative, Java Web Tokens (JWTs), and luckily it supports guest accounts, the somewhat unloved step children of Azure Ad principles.

In this article I will demonstrate how to use the Azure CLI (az) to generate a JWT, and use it to connect to an Azure database and run some Ad-Hoc queries against it.

Wait, what's a guest account?

A guest account, sometimes referred to as a B2B account is simply a user account in AzureAD that is linked to either a Microsoft account or another AzureAD account. The typical use case for this are to allow access for IT consultants and vendors. If you hire me as a consultant to write some software for you and deploy it to your cloud, I can give your AzureAD admins my gmail address, which is associated with my Microsoft account and you can add me as a guest user. I can't log into the office 365 portal, and by default there are some limitations on the portal like I can't list users on the domain. However, you can give me permission to do all the things I'd typically need to do as a consultant. I can be granted access to create and administer resources in a subscription, and connect to your Azure Devops projects.

Guest users can use Token based authentication with Azure SQL DB. However, there is one small and often non-obvious step when doing so.

That's great I don't have a guest account!

That's fine. I think you should keep reading this article. Everything else applies but one last step, and its good to know what that step is.

So what's a JWT?

JWTs are the objects used by OAuth 2.0, which is the authentication used by Azure AD. JWTs allow two parties (in this case, you, and the Azure Portal) to securely transmit authentication and authorization claims. OAuth distinguishes between authentication (proving you are you you say you are) and authorization (proving you can take a specific action). The tokens we will be generating are of the Authorization type, and are called Authentication tokens. The generation and transmission of tokens will occur over the HTTP protocol.

OK, let's make a token.

If you haven't already installed the Azure CLI do so now. Once it's installed, open up PowerShell, cmd.exe, or bash and make sure it's in the path.

C:\Users\Justin Dearing> az
     /    /  \    _____   _ _  ___ _
   / /\ \  |_  / | | | \'__/ _  / ____ \  / /| |_| | | |  __/
 /_/    \_\/___|\__,_|_|  \___|

Welcome to the cool new Azure CLI!
Use az --version to display the current version.
Here are the base commands:
    account           : Manage Azure subscription information.
    acr               : Manage private registries with Azure Container Registries.
    acs               : Manage Azure Container Services.
    ad                : Manage Azure Active Directory Graph entities needed for Role Based Access
                       Control.
<snip/>
    tag               : Manage resource tags.
    vm                : Manage Linux or Windows virtual machines.
    vmss              : Manage groupings of virtual machines in an Azure Virtual Machine Scale Set
                       (VMSS).
    webapp            : Manage web apps.
C:\Users\Justin Dearing>

Now type az login. A web browser will open up and ask you for your password if you are not already logged into Azure on this machine. The length of this session will depend on many factors, but its akin to logging into Windows and creating a session that can create many Kerberos tickets. If successful you will see a list of all the subscriptions you have access to. Each entry looks like this:

  {
    "cloudName": "AzureCloud",
    "id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    "isDefault": false,
    "name": "Pay-As-You-Go",
    "state": "Enabled",
    "tenantId": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    "user": {
      "name": "something@gmail.com",
      "type": "user"
    }
  },

Pick the id of the subscription you want to use and place it in this command:

 az account --set subscription=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX

Now, lets generate a token and save it to a PowerShell variable. If we are using a guest account, we will need the tenant id.

C:\Users\Justin Dearing> $sql_access_token = $(az account get-access-token --subscription='XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --resource 'https://database.windows.net/' --query accessToken -otsv)
C:\Users\Justin Dearing> $sql_access_token
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

If you want to see what the decoded token looks like, visit https://jwt.ms or https://jwt.io to decode the token. The former is run by Microsoft, and gives a little more information about Azure tokens. The latter has more reference information.