Azure SQL AAD authentication for application from other tenant

Question

I'm trying to set up AAD authentication to Azure SQL from multi-tenant AAD application and another tenant then where database is. For this I created:

1. An AAD multi-tenant application test-multitenant in tenant A
2. A service principal in tenant B for application test-multitenant.
3. Azure SQL database test-db in subscription which is in tenant B.
4. A security group test-group in tenant B and set it as AAD administrator for SQL server (test-server) of database test-db.
5. Add application test-multitenant service principal in tenant B to test-group security group. So, it has all permissions of test-group security group.
6. Created this PowerShell script to test connectivity

# get db token
$clientId = '<test-multitenant-app-id>' # test-multitenant
$clientSecret = '<test-multitenant-app-secret>' # test-multitenant
$credentials = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential]::new($clientId, $clientSecret)
$tenant = '<tenant-A-id>' # test-multitenant
$authority = "https://login.windows.net/$tenant" 
$context = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
$authTokenTask = $context.AcquireTokenAsync('https://database.windows.net/', $credentials)
$token = $authTokenTask.GetAwaiter().GetResult().AccessToken

# connect
$connectionString = 'Server=test-server.database.windows.net;Initial Catalog=test-db;Integrated Security=false;'
$connection = [System.Data.SqlClient.SQLConnection]::new($connectionString)
$connection.AccessToken = $token
$command = [System.Data.SqlClient.SqlCommand]::new('select count(*) from [dbo].[test]', $connection)
$connection.Open()

$result = $command.ExecuteScalar()

"Result: $result"

And unfortunatelly I'm getting this error

Login failed for user '<token-identified principal>'.

But interesting if to use application from tenant B and do the same everything works fine.

Does anybody know whether this scenario is supported by Azure SQL and AAD? Thank you

Answer 1

It's not true. You have a misunderstanding of the concept of multi-tenancy.

Multi-tenant application is for offering a Software as a Service (SaaS) application to many organizations. It doesn't mean that the original service principal can access the resources from other tenants who have consented. It can only allow those tenants to access their own resources.

Multi-tenant application is equivalent to providing a function out of the box. Even if you sometimes think that you are accessing the resources of Tenant B as Tenant A, you are actually using the service principal in Tenant B to access the resources.

So in your script, specify the tenant as tenant B: $tenant = '<tenant-B-id>'.

Answer 2

Please double check that you are acquiring a security token in a context of the tenant B.