Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-09-2025 01:09 AM
In the end, I had to create the connection in code and not directly reference the instance. See code below. This seems to work and give me access to all the databases we have across our different instances (providing security permissions are in place at the SQL end). This still doesn't feel quite right too me and doesn't quite have the granularity I want, but it works as we can create foreign catalogs to each database and allows us to read and use the data from it quite nicely.
Give this a go, good luck!
CREATE CONNECTION your_connection_name TYPE sqlserver
OPTIONS
(
host '999.999.999.99',
port '9999',
user 'your_sql_user',
password 'your_sql_password',
trustServerCertificate 'true'
);
CREATE FOREIGN CATALOG IF NOT EXISTS foreign_catalog_name_1 USING CONNECTION your_connection_name
OPTIONS (database 'your_sql_db_name');
CREATE FOREIGN CATALOG IF NOT EXISTS foreign_catalog_name_2 USING CONNECTION your_connection_name
OPTIONS (database 'your_sql_db_name_2');
