Revit 2022 Environment Preventing SSL handshake with PostgreSQL

[Sean_Page]

I am trying to connect an Azure PostgreSQL DB to Revit via the API and I am running into a very frustrating situation and was hoping someone may have the experience I need to at least know why its failing so I can try to resolve it.

I have successfully used MySQL and MSSQL in the past without any issues, but for some reason within the Revit context I am getting and SSL error. I know this may not be a direct Revit API question, BUT a desktop application with the EXACT same executing code works flawlessly. So, this make me think there is something (maybe a reference) that is got me caught up here and that is why I am grasping here a bit hoping to get lucky.

Code is very straight forward at this point just to test the connection.

1. External Application with a single button that using an external command to call another dll (separated them for multi app support).

using Autodesk.Revit.Attributes;
using Autodesk.Revit.DB;
using Autodesk.Revit.UI;

namespace RDG_Revit_2022.Testing
{
	[Transaction(TransactionMode.Manual)]
	class Test : IExternalCommand
	{
		public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
		{
			UIApplication uiapp = commandData.Application;
			Document doc = uiapp.ActiveUIDocument.Document;
			//This is the external reference call
			pgRDG.Connection.Testing();
			return Result.Succeeded;
		}
	}
}

2. Here is the code within the Testing() method

using System;
using System.Windows;
using Npgsql;

namespace pgRDG
{
	public class Connection
    {
        public static void Testing()
		{
			string ConnString = "Server=name.postgres.database.azure.com;Database=dbName;Port=5432;User Id=Username;Password=Password;Trust Server Certificate=true;Ssl Mode=Require";
			using(NpgsqlConnection conn = new NpgsqlConnection(ConnString))
			{
				try
				{
					conn.Open();
					NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM table", conn);
					NpgsqlDataReader reader = cmd.ExecuteReader();
					while(reader.Read())
					{
						MessageBox.Show(reader.GetString(reader.GetOrdinal("command")));
					}
				}
				catch(Exception ex)
				{
					MessageBox.Show(ex.ToString());
				}
			}
		}
    }
}

3. When I run the code I receive this error message.

Again, if I run that code in a desktop app it executes without any errors which is why I am posting here wondering if anyone could give me any hints as to what within the Revit environment may be causing this issue.

Also submitted a GitHub request for reference:

Specified SSL Protocol Type is not Valid · Issue #3718 · npgsql/npgsql (github.com)

[murillo.rocha]

I'm having the same problem. I'm waiting for a solution too.

[Sean_Page]

@murillo.rocha I was able to resolve this after the information supplied on the github I had listed.  The real issue was DLL hell just like always. The NpgSQL was trying to rely on different versions of references that was loading with Revit. I had to implement an Assembly Resolver to make sure that when the call was coming from my app that the correct references and versions will being loaded.

However I did update the GitHub link with my solution, here is a excerpt from it for convenience.

https://github.com/npgsql/npgsql/issues/3718#issuecomment-897124429

public Result OnStartup(UIControlledApplication application)
{
    AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;
    return Result.Succeeded;
}

private Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
    string filename = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "References");
	if(args.Name.Contains("Dapper"))
	{
		filename = Path.Combine(filename, "Dapper.dll");
		if(File.Exists(filename))
		{
			return Assembly.LoadFrom(filename);
		}
	}
}

[murillo.rocha]

Thank you @Sean_Page!! I made it work here by changing NpgSQL version 6.0 to version 3.0, but your solution is much more practical!