topic Re: Revit 2022 Environment Preventing SSL handshake with PostgreSQL in Revit API Forum

Revit 2022 Environment Preventing SSL handshake with PostgreSQL

Sean_Page — Tue, 11 May 2021 10:58:20 GMT

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)

Re: Revit 2022 Environment Preventing SSL handshake with PostgreSQL

murillo.rocha — Thu, 13 Jan 2022 11:57:16 GMT

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

Re: Revit 2022 Environment Preventing SSL handshake with PostgreSQL

Sean_Page — Thu, 13 Jan 2022 16:17:07 GMT

@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); } } }

Re: Revit 2022 Environment Preventing SSL handshake with PostgreSQL

murillo.rocha — Fri, 14 Jan 2022 14:46:38 GMT

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!