Azure SSAS with data source to Azure SQL Server database - TOM C#

Question

I have a project of Net Core which it create database, tables and measures in a Tabular model.
I would like to create a data source to Azure SQL Server Database (I can do it whit sql server on premise).
My problem is in the connection string to azure.

I have seen in the Microsoft documentation that I need a ProviderDataSource to connect to a sql server on premise. The following example is working, but I can't do it with Azure SQL Database.
I couldn't find an example

ProviderDataSource ProviderDS = new ProviderDataSource()
        {
            Name = datasourceName,
            Description = "A data source definition that uses explicit Windows credentials for authentication against SQL Server ....",
            ConnectionString = "Provider=MSOLEDBSQL; Data Source=" + ServerNameSQL + ";Initial Catalog=" + DataBaseNameSQL + ";Integrated Security=SSPI;Persist Security Info=false",
            ImpersonationMode = ImpersonationMode.ImpersonateAccount,
            Account = @"Domain/user",
            Password = "mypassword",
        };

After, I use it in my model

database.Model.DataSources.Add(ProviderDS);

I would like to see two examples and their best practices:

1. How to create a data sources for my model using Azure AD?
2. How to create a data sources for my model using DB User and password ?

Answer 1

To create database in Azure SQL server you can use "Microsoft.Data.SqlClient" Library. I tried with below code to create database by using user name and password:

using Microsoft.Data.SqlClient;

class Program
{
    static void Main(string[] args)
    {
        string serverName = "<serverName>.database.windows.net";
        string databaseName = "<databaseName>";
        string userName = "<userName>";
        string password = "<password>";

        
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = serverName;
        builder.UserID = username;
        builder.Password = password;
        builder.InitialCatalog = "master";

       
        using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
        {
            connection.Open();

           
            using (SqlCommand command = new SqlCommand($"CREATE DATABASE {databaseName}", connection))
            {
                command.ExecuteNonQuery();
                Console.WriteLine($"Database {databaseName} created successfully.");
            }
        }
    }
}

It executed successfully.

And database is created successfully in my Azure SQL server.

Answer 2

I can do it using ImpersonateServiceAccount and adding the Provider

In this code I'm using Legacy Data Sources (Provider)

I'm not entirely sure about ImpersonationMode, but it works for me. I need to go deeper into this but I can't find documentation

public static readonly ProviderDataSource ProviderDSAzure = new ProviderDataSource()
{
    Name = datasourceName,
    Description = "A data source that uses Impersonation Service Account for Azure and for authentication against SQL Server",
    ConnectionString = "Data Source=" + ServerNameSQL + ";Initial Catalog=" + DataBaseNameSQL + ";User Id=" + userIdDBAzure + ";Password=" + passwordDBAzure + ";Persist Security Info=true;Encrypt=true;TrustServerCertificate=false",
    ImpersonationMode = ImpersonationMode.ImpersonateServiceAccount,
    Provider = "System.Data.SqlClient",
};