Hi,
We are creating a lakehouse federated connection to our 2016 On Prem SQL Server. This has an instance in place, so we only want and need to connect to this instance. From this connection, we want to create a foreign catalog of a database on the SQL Instance. Our problem is that no matter what syntax we use, we cannot get the connection to work. Our code is as follows:
CREATE CONNECTION On_Prem_SQL_Svr TYPE sqlserver
OPTIONS
(
host '192.xxx.xxx.xxx',
port 'xxxxx',
user 'username',
password 'password',
trustServerCertificate 'true'
)
On the host name we have tried every combination of syntax we can think of after the server IP address (\instance \\instance /instance %5Cinstance //instance and some other crazy ones!). We sometimes get different errors, but none work. We know connectivity and authentication all work fine, so the problem is either a) syntax or b) you can't create a connection to an On Prem SQL Instance?
We've also tried this through the create federated connection UI and hit the same problem.
Any help out there much appreciated! Anyone else managed to get this working?
Thanks
Nick
