Cannot connect to the Azure SQL Server using ad admin account

Question

I have created an Azure SQL Server and assigned an ad administrator to it using terraform

resource "azurerm_mssql_server" "server" {
  name                         = "adauth-sqlserver"
  resource_group_name          = azurerm_resource_group.rg.name
  location                     = azurerm_resource_group.rg.location
  version                      = "12.0"
  administrator_login          = "faadmin"
  administrator_login_password = "faadmin-password"

  azuread_administrator {
    login_username = "SP-NCS-CREATED-FOR-DB"
    tenant_id = "12345fd6-1234-4871-a542-eb12a5a7800c"
    object_id = "d2db88ce-1234-1234-8d93-b256be13bd27"
  }
  public_network_access_enabled = true
  azure_ad_authentication_only = false
}

The administrator is a Service Principal. I can see in the portal and also query it

PS C:\SqlServerAADAuthenitcation> az sql server ad-admin list --resource-group databaseadauthRG --server-name adauth-sqlserver
[
  {
    "administratorType": "ActiveDirectory",
    "azureAdOnlyAuthentication": false,
    "id": "/subscriptions/1e27b63d-d44b-478e-afe9-08e5bf46eb7f/resourceGroups/databaseadauthRG/providers/Microsoft.Sql/servers/adauth-sqlserver/administrators/ActiveDirectory",
    "login": "SP-NCS-CREATED-FOR-DB",
    "name": "ActiveDirectory",
    "resourceGroup": "databaseadauthRG",
    "sid": "d2db88ce-1234-1234-8d93-b256be13bd27",
    "tenantId": "12345fd6-1234-4871-a542-eb12a5a7800c",
    "type": "Microsoft.Sql/servers"
  }
]

When I try to connect to the server using 'Active Directory Password' authentication, it throws an error. Another test fails with the error Login failed for user token-identified principal.

How is one supposed to connect to an azure ad admin user so that to be able to add more acive directory users and groups

I have read this a couple of time. https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-powershell#create-contained-users-mapped-to-azure-ad-identities

Thanks in advance for anyone who looks into this.

Answer 1

When I try to connect to the server using 'Active Directory Password' authentication, it throws an error. Another test fails with the error Login failed for user token-identified principal.

Thanks to Nick.McDermaid for suggesting the same.

It is not possible to log in to the Azure SQL server via GUI when using a service principal. Service principals are intended for automation purposes only, and you cannot use any UI tools to authenticate with Azure SQL using service principal credentials.

To create Azure SQL with Azure AD authentication, you can follow the below Terraform code.

provider "azurerm" {
  features {}
}
resource "azurerm_resource_group" "rg-name" {
  name     = "rg-name"
  location = "West Europe"
}
resource "azurerm_mssql_server" "samplesql" {
  name                         = "mssqlserverdemotest"
  resource_group_name          = azurerm_resource_group.venkat-rg.name
  location                     = azurerm_resource_group.venkat-rg.location
  version                      = "12.0"
  administrator_login          = "missadministrator"
  administrator_login_password = ""
  minimum_tls_version          = "1.2"

  azuread_administrator {
    login_username = "<user principal name>"
    object_id      = "<user principal object ID>"
  }
}

I logged into Azure Sql DB with azure AD user as below.

Note: Make sure to you use user principal name and object ID

References:

1. Azure SQL database using service principal | Thomas Thornton