Can we get an option configure Azure SQL credential options in the connection properties?

rmagruder

Right now I have to do this:

void Main()
{

var dbContext = GetTokenizedDbContext();

to use queries against my DbContext class because the MacOS azure credential stack from Microsoft.SqlClient is not working. It hangs up trying to go through the various prioritized options for authenticating against Azure.

So the implementation of the above that works for me is:

public PopStoreContext GetTokenizedDbContext()
{
Environment.SetEnvironmentVariable("PATH", Environment.GetEnvironmentVariable("PATH") + ":/opt/homebrew/bin");

// Configure Azure credential options
var options = new DefaultAzureCredentialOptions
{
    ExcludeEnvironmentCredential = true,
    ExcludeManagedIdentityCredential = true,
    ExcludeSharedTokenCacheCredential = true,
    ExcludeVisualStudioCredential = true,
    ExcludeAzureDeveloperCliCredential = true,
    ExcludeAzurePowerShellCredential = true,
    ExcludeInteractiveBrowserCredential = true, // Exclude interactive browser if it’s causing issues on macOS
    TenantId = Environment.GetEnvironmentVariable("AZURE_TENANT_ID") // Ensure AZURE_TENANT_ID is set in your environment
};

var credential = new DefaultAzureCredential(options);

// Get token for Azure SQL Database
var tokenRequestContext = new TokenRequestContext(new[] { "https://database.windows.net//.default" });
var token = credential.GetToken(tokenRequestContext);
Console.WriteLine("Token acquired successfully. Expires on: " + token.ExpiresOn);

// Azure SQL connection string (without credentials, as token will be used)
string connectionString = "Data Source=AAAAAAA.database.windows.net;Initial Catalog=BBBBBBB;Encrypt=true;app=LINQPad;MultipleActiveResultSets=true";

// Create a new SqlConnection with the token
var connection = new SqlConnection(connectionString);

connection.AccessToken = token.Token;

    // Initialize your DbContext with the connection
var dbContextOptions = new DbContextOptionsBuilder<PopStoreContext>()
    .UseSqlServer(connection)
    .Options;

var dbContext = new PopStoreContext(dbContextOptions);
return dbContext;

}

I really wish I could put the DefaultCredentialOptions in the connection properties dialog so I didn't have to scaffold every linqpad query using Azure SQL with the above code.

JoeAlbahari

LINQPad includes numerous features to make this kind of process easier.

First, I would try to generalize your code, so that it works with all connection strings. Rather than creating a new DbContext with specific connection details, try something like this:

var options = new Azure.Identity.DefaultAzureCredentialOptions
{
    ExcludeEnvironmentCredential = true,
    ExcludeManagedIdentityCredential = true,
    ExcludeSharedTokenCacheCredential = true,
    ExcludeVisualStudioCredential = true,
    ExcludeAzureDeveloperCliCredential = true,
    ExcludeAzurePowerShellCredential = true,
    ExcludeInteractiveBrowserCredential = true, // Exclude interactive browser if it’s causing issues on macOS
    TenantId = Environment.GetEnvironmentVariable("AZURE_TENANT_ID") // Ensure AZURE_TENANT_ID is set in your environment
};

var credential = new DefaultAzureCredential (options);

// Get token for Azure SQL Database
var tokenRequestContext = new TokenRequestContext(new[] { "https://database.windows.net//.default" });
var token = credential.GetToken (tokenRequestContext);
Console.WriteLine ("Token acquired successfully. Expires on: " + token.ExpiresOn);

// Fetch the connection for the current database and populate its access token.
var connection = (SqlConnection) this.Database.GetDbConnection();
connection.AccessToken = token.Token;

// Now you can query your database.
Customers.Dump();   // Test the connection

If this works, your next step would be to save it to a script called, let's say "mscreds.linq". Make sure the language is set to C# Statements, not C# Program (this will make it easier to #load it into another script). Once you've tested it, delete the final line that dumps your table. Now create a new script that does this:

#load "mscreds.linq"
Customers.Dump();

If that works, there are a couple more things you can do to improve things further. First, go to your mscreds script and set the Connection to None and save it. This will ensure that it's not tied to a particular connection, so it will work with any EF Core connection when #loaded. Note that the script will no longer compile on its own (because "this.Database" will be unresolved), however this won't matter.

Finally, highlight #load "mscreds.linq", right-click and choose Snippets - Create New Code Snippet. Save this to a simple name such as mc.snippet. Now you can just type mc and hit Tab any time and it will insert #load "mscreds.linq".

Also, when you press Command-Shift-N (new script, same properties), the #load "mscreds.linq" directive will be automatically copied. So, you won't even need to type mc Tab.