Blog Post

Azure Migration and Modernization Blog
2 MIN READ

Automating Migration from SQL Server Authentication to OAuth for Tabular Models: A Python Solution

arunsaxena's avatar
arunsaxena
Icon for Microsoft rankMicrosoft
Aug 25, 2023

Introduction: The transition from SQL Server Authentication (SSA) to OAuth for our tabular modernization journey required changes in our legacy tabular models. These models used SQL queries in import tables, which are now required to be changed to M queries to implement OAuth authentication. This paper discusses the challenges faced during this migration and presents a Python script as a solution to automate the conversion process.

Background: Tabular models are structured as BIM files containing various elements like data sources, measures, relationships, dimensions, queries, and roles in a nested JSON format. Changing the data sources from SQL server auth (SSA) which requires hardcoded username and passwords to OAuth which works on AAD required us to change various import statements from SQL to M compatible code as plain SQL is not supported in OAuth.  Converting the SQL queries to M queries manually within this nested JSON structure is laborious and prone to errors.

Solution: To address this issue, we developed a Python script that utilizes JSON libraries to parse the BIM files and employs an intelligent iterator to locate and modify legacy SQL queries to M queries automatically. The script operates in two phases: Phase-1 creates M queries for all objects, and Phase-2 replaces old elements in the BIM File's nested JSON with new M query elements while eliminating unnecessary ones. The result is a BIM file compliant with OAuth authentication.

Benefits: Using our Python script eliminates the need for manual intervention, significantly reducing the chances of errors. The script has been successfully employed to migrate all our legacy tabular models, ensuring its reliability and effectiveness. In a large model with 64 tables, the manual migration process would take approximately 7 days and with 7 such models it can go up to 20-30 days. However, with our script, this task can be completed in a matter of seconds, resulting in a considerable reduction in time and effort.

Conclusion: Our customized Python script proved to be a valuable solution for our migration needs. This script holds the potential to serve as a migration tool for a seamless transition from SQL Server Authentication to OAuth for your tabular models.

Code Base: Please clone this Git repo and start using the script for your migration needs.

Updated Aug 25, 2023
Version 1.0
  • DanielOtykier's avatar
    DanielOtykier
    Copper Contributor
    Aug 29, 2023

    Great post, thanks for sharing.

     

    Tabular Editor users may want to use the following C# script, which does the same thing (converting SQL partitions to M/Power Query partitions on the currently loaded model):

     

    // This Tabular Editor script will convert all legacy (SQL) partitions on a model, to corresponding M
// (Power Query) partitions, that use the Value.NativeQuery function on the original SQL expression:

const string mQuery = "let\n    Source = #\"{0}\",\n    Data = Value.NativeQuery(Source, \"{1}\")\nin\n    Data";

foreach(var table in Model.Tables.Where(t => t.SourceType == PartitionSourceType.Query))
{
    table.Partitions.ConvertToPowerQuery();
    foreach(MPartition partition in table.Partitions)
    {
        partition.Expression = string.Format(mQuery,
            Model.DataSources.First().Name,
            partition.Expression.Replace("\"", "\"\""));
    }
}