DbUp SqlConnection AAD outh?

Question

Due to security reason SQL authentication is disabled, only through Azure SPN can I login. From below link, in C#, we are able to connect:

https://techcommunity.microsoft.com/t5/azure-sql-database/azure-ad-service-principal-authentication-to-sql-db-code-sample/ba-p/481467

How to pass the above successful access token authentication connection in below DbUp program.cs ?

var upgrader = DeployChanges.To.AzureSqlDataWarehouse(connectionString)
                            .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
                            .LogToConsole()
                            .LogScriptOutput().WithExecutionTimeout(TimeSpan.FromSeconds(60))
                            .Build();
var result = upgrader.PerformUpgrade();

score 0 · Answer 1 · answered Jul 28 '20 at 04:20

Not sure if you have resolved this issue but wanted to provide a suitable answer as it may assist others looking for the same or similar solution. Firstly, in looking at DbUp, I didn't see any native support for Azure AD authentication but I think the following is what you are looking for.

Token-based authentication support for Azure SQL DB using Azure AD auth

The linked Tech Community Blog discusses all the Azure AD methods you can leverage to authenticate with Azure SQL. It contains a sample application (TokenReadme.Zip) demonstrating token based authentication, where the included program.cs example in the TokenReadme.docx is as follows:

using System;
using System.Data;
using System.Data.SqlClient;

namespace ClinicService
  {
class Program
{
    static void Main()
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder["Data Source"] = "aad-managed-demo.database.windows.net"; // replace with your server name
        builder["Initial Catalog"] = "demo"; // replace with your database name
        builder["Connect Timeout"] = 30;

        string accessToken = TokenFactory.GetAccessToken();
        if (accessToken == null)
        {
            Console.WriteLine("Fail to acuire the token to the database.");
        }
        using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
        {
            try
            {
                connection.AccessToken = accessToken;
                connection.Open();
                Console.WriteLine("Connected to the database");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        Console.WriteLine("Please press any key to stop");
        Console.ReadKey();
    }
  }
}

You should be able to leverage the TokenReadme example to modify your DbUp .NET Library solution to leverage token based authentication. Regards, Mike

This is actually not a correct assumption. DbUp does not expose or accept a connection object to the caller, its current support is based entirely around setting a Boolean parameter in the function that is taking the connection string at which point it will internally handle the authentication process itself. There is no way to handle the token validation manually and pass it along as of 3 months ago. — Paul Swetz, Jan 11 '22 at 14:21

score 0 · Answer 2 · answered Jan 11 '22 at 14:26

0

DbUp should expose a Boolean in the DeployChanges.To.AzureSqlDataWarehouse call to internally use token authentication. You cannot pass in a token you already have unfortunately.

DeployChanges.To.AzureSqlDataWarehouse(connectionString,true)

answered Jan 11 '22 at 14:26

Paul Swetz

2,234
1
11
28

DbUp SqlConnection AAD outh?

2 Answers2