Cannot connect with SMO to Azure SQL database from console app. Works in VS2022 debug, but get NullReference when running app

I have struggled with this for several days now, and believe that it must be something basic that I am overlooking, but I cannot figure out what.

I have a .NET 7 console application that uses SMO to connect to a database and make changes to it based on some input. This works on a on-premise SQL Server using Windows auth.

I have tried porting the same code to connect to an Azure SQL database using AAD MFA. The following code works - but only when I debug from VS2022, but not when I deploy the application (to the same machine).

When I do that, then the (SMO) ServerConnection object fails with a NullReference exception.

The following code works from VS2022, but not when I compile the code. I have tried connection string with and without MFA, all works in VS2022, so it's not an access problem. The error I get is:

> Unhandled exception. System.NullReferenceException: Object reference not set to an instance of an object.
>
> at Microsoft.Data.SqlClient.SqlConnectionEncryptOption.op_Implicit(Boolean value)

private ServerConnection GetServerConnection( string ConnectionString )
{           
    var Connection = new SqlConnection(ConnectionString);

    var conn = new ServerConnection(Connection); //This fails

    try
    {
        Console.WriteLine($"Inside server tryclause.");
        var srv = new Server(conn);
        Console.WriteLine($"{Environment.NewLine}Server: {srv.Name}");
        Console.WriteLine($"Edition: {srv.Information.Edition}{Environment.NewLine}");
        conn.Disconnect();
    }
    catch (Exception err)
    {
        Console.WriteLine(err.Message);
        throw;
    }

    return conn;
}

The following also works in VS2022 (uses userid and pw, not my preferred method) but still fails when I compile with

> Unhandled exception. System.NullReferenceException: Object reference not set to an instance of an object.

Code is inspired by this SMO guide

var srvConn2 = new ServerConnection(remoteSvrName)
                   {
                       DatabaseName = database,
                       LoginSecure = false,
                       Login = sqlServerLogin,
                       Password = password
                   };

var srv3 = new Server(srvConn2);

When reading around, it seems that I have a similar problem to this, but I am not using System.Data.SqlClient.

Any suggestions on where the error is would be greatly appreciated.

Instead of using SQLConnection, you could create an instance of ServerConnection with the connectionString and then use it to connect to the SQLServer via ConnectionContext.

I have developed the below sample code, which you can leverage to connect to the Azure SQL Database. Hope that helps.

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace SQLConnectionConsole
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            // Update the below variables before running the code
            string serverName = "MySQLServer.database.windows.net";
            string dbName = "MySQLDBName";
            string username = "MyUserName";
            string password = "MyPassWord";

            // Frame the SQL connection string
            string connectionString = $"Server={serverName};Database={dbName};User Id={username};Password={password};";

            try
            {
                // Create a SQL Server Connection
                ServerConnection serverConnection = new ServerConnection();
                serverConnection.ConnectionString = connectionString;
                Server server = new Server(serverConnection);

                // Code to connect to the SQL Server
                server.ConnectionContext.Connect();
                Console.WriteLine($"Connected to server: {serverName}, database: {dbName}");

                Console.WriteLine($"{Environment.NewLine}Server: {server.Name}");
                Console.WriteLine($"Edition: {server.Information.Edition}{Environment.NewLine}");

                //Disconnect from Azure SQL Server
                server.ConnectionContext.Disconnect();
                Console.WriteLine($"Disconnected from server: {serverName}, database: {dbName}");
            }
            catch (Exception err)
            {
                //catch the exception and display it
                Console.WriteLine(err.Message);
                throw;
            }

            Console.ReadLine();
        }
    }
}

Please note, Before running the sample ensure that the SQL Server resource has the Public Network Access enabled. See detailed code here.