Constructor on type 'Microsoft.SqlServer.Management.Smo.SqlObjectUnion' not found.'

[jzabroski]

@Tornhoof

Thanks for your help. This is the closest Microsoft-owned repo to record this issue against. This is a pretty difficult bug, as I've tried to workaround it by using net48 and run into other issues in the process.

Issue
When using Microsoft.SqlServer.SqlManagementObjects nuget package, calling EnumObjectPermissions does not work because SqlObjectUnion class is missing a required constructor.

Details

I cannot actually use the netstandard2.0 binaries shipped as part of https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/150.18208.0 or the latest preview https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/160.1911221.0-preview

When I use the following snippet, it blows up:

using (var sqlConnection = new SqlConnection(GetConnectionString(applicationArguments)))
{
  var serverConnection = new ServerConnection(sqlConnection);
  var server = new Server(serverConnection);
  var database = server.Databases[applicationArguments.DatabaseName];

  var objectPermissionInfos = _database.EnumObjectPermissions();

[jzabroski]

I have provided a full repro here: https://github.com/jzabroski/Tools.Database.Scripting.Issue

The only requirements to run this are a localhost scoped SQL Server instance and permission to USE msdb.

[jzabroski]

EDIT: Changed my opinion on what I thought to be the root cause.

I am having a hard time understanding why this works on net48 but does not work on .NET Core.

As best I can tell, both the n461 package and the netstandard2.0 package both ship a Microsoft.SqlServer.SqlEnum.dll, and both contain the required type Microsoft.SqlServer.Management.Smo.SqlObjectUnion, and the constructor is private for this type in both assemblies.

When I run the following simple repro using net48, I get the same failure I get in netstandard2.0:

using System;
using System.Data.SqlClient;
using System.Globalization;
using System.Reflection;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace Tools.Database.Scripting.Cli
{
    class Program
    {
        static void Main(string[] args)
        {
            var asm = Assembly.Load(new AssemblyName("Microsoft.SqlServer.SqlEnum"));
            asm.CreateInstance("Microsoft.SqlServer.Management.Smo.SqlObjectUnion", false);

This appears to be the logic the net461 version is using under the hood, yet it magically works somehow when its wrapped up.

And I know the constructor exists, and I'm not going crazy, because it works when I change the last line in the above sample to just:

asm.CreateInstance("Microsoft.SqlServer.Management.Smo.SqlObjectUnion", false, BindingFlags.Instance | BindingFlags.NonPublic, null, null, CultureInfo.InvariantCulture, null);

[jzabroski]

The one curious difference I can see is that Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator is MarshalByRefObject in net461 but not in netstandard2.0. - I don't understand why that would effect anything.

[Tornhoof]

@jzabroski I think you meant @sanderstad in your mentions, not me.

[shueybubbles]

Thanx for the report. BTW I'm trying to create a SMO-specific repo here on github, at least to accumulate issues and samples. The issue is that when we first started adding NetStandard builds of SMO there wasn't full support for reflection-based binding with NonPublic binding flags and the like, so there's still code like this:

        internal static Object CreateInstance(Assembly assembly, string objectType)
        {
#if !NETSTANDARD2_0
            return ManagementUtil.CreateInstance(assembly, objectType);
#else
            return assembly.CreateInstance(objectType, false);
#endif
        }

Where ManagementUtil.CreateInstance is:

internal static Object CreateInstance(Assembly assembly, string objectType)
        {
            return assembly.CreateInstance(objectType,
                        false, BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.CreateInstance,
                        null, null, CultureInfo.InvariantCulture, null);
        }

This code just needs to be updated now that the standard supports everything. I will resolve this issue once we push SMO bits that have a fix.

[jzabroski]

@shueybubbles AWESOME! Right now I have to deploy my code as a net48 executable internally, whereas a .NET Core CLI tool would be amazing! Thanks for your prompt investigation.

Once there is a repo, I can probably help contribute some additional improvements.

[jzabroski]

@shueybubbles Missed this issue being closed during the COVID-19 hysteria - can I get an explanation on whether this is fixed in the new package https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/160.2004021.0

[shueybubbles]

yes it should be fixed in the newest package.