mssql: login error: Login failed for user '<token-identified principal>

[brewhtr]

When trying to connect to sql using the provider via a pipeline I get the error below. The pipeline is using an SPN which has full access in the estate (for testing purposes), and is the same SPN, which is configured as the entra id admin.

I am using provider version 0.4.1

====

Error: Plugin did not respond │ │ The plugin encountered an error, and failed to respond to the │ plugin6.(*GRPCProvider).ReadDataSource call. The plugin logs may contain │ more details. ╵ ╷ │ Error: Failed to establish an SQL connection │ │ with module.sql_permissions.data.azuresql_role.db_datawriter, │ on modules\sql_database_permissions\data.tf line 6, in data "azuresql_role" "db_datawriter": │ 6: data "azuresql_role" "db_datawriter" { │ │ mssql: login error: Login failed for user ''.

[signal 0xc0000005 code=0x1 addr=0x20 pc=0x141fe14]

goroutine 48 [running]: database/sql.(DB).conn(0x0, {0x1d6ff18, 0xc00047bf50}, 0x1) database/sql/sql.go:1282 +0x54 database/sql.(DB).query(0xc00047bf50?, {0x1d6ff18, 0xc00047bf50}, {0x1c58f81, 0x100}, {0xc0004d70a0, 0x1, 0x1}, 0x0?) database/sql/sql.go:1721 +0x57 database/sql.(DB).QueryContext.func1(0x80?) database/sql/sql.go:1704 +0x4f database/sql.(DB).retry(0x20?, 0xc0004d6fb0) database/sql/sql.go:1538 +0x42 database/sql.(DB).QueryContext(0x1b597e0?, {0x1d6ff18?, 0xc00047bf50?}, {0x1c58f81?, 0xc000282cdb?}, {0xc0004d70a0?, 0xc000282d02?, 0x11?}) database/sql/sql.go:1703 +0xc5 database/sql.(DB).QueryRowContext(...) database/sql/sql.go:1804 terraform-provider-azuresql/internal/sql.GetRoleFromName({0x1d6ff18, 0xc00047bf50}, {0x0, {0xc000282cd0, 0x43}, {0xc00047c000, 0x81}, {0xc000282cd0, 0x9}, {0xc000282cdb, ...}, ...}, ...) terraform-provider-azuresql/internal/sql/role.go:111 +0x185 terraform-provider-azuresql/internal/services/role.(providerConfig).Read(0xc00009c1d8, {0x1d6ff18?, 0xc00047b440?}, {{{{0x1d748f8, 0xc00047bc80}, {0x1ade1e0, 0xc00047bbf0}}, {0x1d76840, 0xc00009b900}}, {{{0x0, ...}, ...}, ...}}, ...) terraform-provider-azuresql/internal/services/role/role_datasource.go:90 +0x27d github.com/hashicorp/terraform-plugin-framework/internal/fwserver.(Server).ReadDataSource(0xc0000849c0, {0x1d6ff18, 0xc00047b440}, 0xc00047b4d0, 0xc0004d76d8) github.com/hashicorp/terraform-plugin-framework@v1.6.1/internal/fwserver/server_readdatasource.go:79 +0x433 github.com/hashicorp/terraform-plugin-framework/internal/proto6server.(Server).ReadDataSource(0xc0000849c0, {0x1d6ff18?, 0xc00047b320?}, 0xc0003d1cc0) github.com/hashicorp/terraform-plugin-framework@v1.6.1/internal/proto6server/server_readdatasource.go:55 +0x41c github.com/hashicorp/terraform-plugin-go/tfprotov6/tf6server.(server).ReadDataSource(0xc00028a0a0, {0x1d6ff18?, 0xc00047aba0?}, 0xc000461450) github.com/hashicorp/terraform-plugin-go@v0.22.0/tfprotov6/tf6server/server.go:686 +0x416 github.com/hashicorp/terraform-plugin-go/tfprotov6/internal/tfplugin6._Provider_ReadDataSource_Handler({0x1be4060?, 0xc00028a0a0}, {0x1d6ff18, 0xc00047aba0}, 0xc0003ee580, 0x0) github.com/hashicorp/terraform-plugin-go@v0.22.0/tfprotov6/internal/tfplugin6/tfplugin6_grpc.pb.go:572 +0x169 google.golang.org/grpc.(Server).processUnaryRPC(0xc0002ac000, {0x1d6ff18, 0xc00047ab10}, {0x1d75488, 0xc0001e5040}, 0xc000479200, 0xc000099d70, 0x23f2400, 0x0) google.golang.org/grpc@v1.61.1/server.go:1385 +0xe03 google.golang.org/grpc.(Server).handleStream(0xc0002ac000, {0x1d75488, 0xc0001e5040}, 0xc000479200) google.golang.org/grpc@v1.61.1/server.go:1796 +0xfec google.golang.org/grpc.(Server).serveStreams.func2.1() google.golang.org/grpc@v1.61.1/server.go:1029 +0x8b created by google.golang.org/grpc.(Server).serveStreams.func2 in goroutine 8 google.golang.org/grpc@v1.61.1/server.go:1040 +0x135

Error: The terraform-provider-azuresql_v0.4.1.exe plugin crashed!

This is always indicative of a bug within the plugin. It would be immensely

====== Current code:

provider "azuresql" { }

provider data "azuresql_sqlserver" "server" { name = var.sql_server_name }

data "azuresql_database" "database" { server = data.azuresql_sqlserver.server.id name = var.sql_database_name }

resource "azuresql_login" "login" { for_each = local.localusers

server = data.azuresql_sqlserver.server.id name = each.value }

locals { localusers = toset (["testuser1", "testuser2"]) }

[jonascrevecoeur]

Thanks for reporting this issue!

I was able to reproduce the plugin error, which as resolved in #58. However, this doesn't solve the underlying issue of the login failing for user ''. If you are trying to connect using a SPN with environment credentials.

Can you specify which authentication mechanism the SPN is using? In case of authentication via environment variables, please check that AZURE_CLIENT_ID, AZURE_CLIENT_SECRET and AZURE_TENANT_ID are set correctly.

[ivanl-out]

Hi @jonascrevecoeur, I think I am getting the same issue when trying to read the db_datareader role from a Synapse serverless SQL pool database.

│ Error: Failed to establish an SQL connection
│ 
│   with data.azuresql_role.db_datareader,
│   on main.tf line 488, in data "azuresql_role" "db_datareader":
│  488: data "azuresql_role" "db_datareader" {
│ 
│ mssql: login error: Login failed for user '<token-identified principal>'.

This error occurs when running the terraform script in an Azure DevOps pipeline. The service connection being used to run the script is of type Azure Resource Manager using workload identity federation with openid connect.

When testing the script locally from my machine, it does work. My account is part of the Entra ID admin group configured on the Synapse workspace.

Both the system assigned managed identity of the build agent as well as the Azure DevOps service connection service principal have also been added to the Entra ID admin group.

Do you think the issue might be how this terraform provider tries to connect to the Synapse serverless SQL pool if a build agent is running the terraform script?

[ivanl-out]

Looks like I had to wait a long time, not sure how long tokens are cached for, but when I tried re-running the pipeline the next morning, it worked. Thanks for a great Terraform provider!

[jonascrevecoeur]

Thanks @ivanl-out, good to hear the issue resolved itself! I will close this ticket again