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

Question

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.

Answer 1

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.