Azure SQL AAD authentication for application from other tenant

2
votes

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

2
sql-serverazurepowershellazure-active-directory
Tenant A wont be able to access the resource on Tenant B. The security group from Tenant B, is not shared with Tenant A. Instead of protecting it with groups, roles might be a better approach. Also, check this resource about MT Database – TiagoBrenck
@TiagoBrenck a mult-tenant app in tenant A with service principal in tenant B which has all needed permissions should have access to resources in tenant B. Thet's the goal of multi-tenant AAD applications. – Vasyl Zv

2 Answers

1
votes

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

1
votes

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>'.