Hi everyone,

I’m currently experimenting with the SQL Gateway Connection, CREATE FOREIGN SERVER, and the THROUGH command (IRIS Documentation). To speed up my tests, I’ve combined several DemoDataSets in a Docker container and would like to automate the creation of SQL Gateway Connections using code. 

(By the way, if you’re interested, the Docker container is available here: Demo DBs IRIS.)

To achieve this, I’m using SQL to create a connection by running a command like INSERT INTO %Library.sys_SQLConnection. While this successfully displays the connection in the Management Portal, it cannot actually be used.

Testing the connection results in this error: Connection failed. <ILLEGAL VALUE>decode+1^%apiGTW

After some investigation, I traced the issue to how the password is handled. Specifically, if I manually enter something into the password field in the Management Portal (even though the DuckDB InMemory DB doesn’t require a password), save it, and then test the connection again, it works perfectly.

It seems like there’s some sort of processing happening in the UI—possibly encrypting the password—that isn’t applied when the connection is created directly via SQL.

Has anyone encountered this before, or does anyone know how to handle this? I’d love to hear your insights or any possible solutions!

Thanks! 😊