Unsuccessful Silent Connection to SQL Endpoint

[Omar_Osman]

Hello Fabric Community,

I hope this message finds you well. I'm reaching out because I've encountered a roadblock in establishing a silent connection to the SQL endpoint within Fabric. Despite being able to access the endpoint successfully via SSMS with MFA, my attempts at a silent connection have been met with an "unauthorized" response.

Here are the details I'm currently working with:

// Your Data Warehouse details
const dataWarehouseEndpoint = 'your_data_warehouse_endpoint'
const databaseName = 'your_database_name'
const clientId = 'your_client_id'
const clientSecret = 'your_client_secret'
const authority = 'https://login.microsoftonline.com/'your_tenant_id'

I have been able to access the endpoint using SSMS with MFA, but I'm specifically seeking a silent connection for more streamlined integration. The goal is to connect without user interaction.

If anyone in the community has faced a similar challenge or has insights into how to resolve this issue, I would greatly appreciate your guidance. Additionally, if there are specific configurations or adjustments that need to be made in the provided code, please share your expertise.

Thank you in advance for your help.

[Omar_Osman]

Hi. Unfortunately, the connection issue persists despite thorough verification, even when utilizing a username and password. The error encountered is "ConnectionError: Connection lost - socket hang up," and this issue replicates when experimenting with an alternative library.
Here is an excerpt from the code I'm currently using:

var Connection = require("tedious").Connection;

var config = {
  server:
    //"****.datawarehouse.pbidedicated.windows.net",
    "*****.datawarehouse.fabric.microsoft.com",
  database: "Gold",
  options: {
    encrypt: true,
    trustServerCertificate: false,
  },
  authentication: {
    //type: "azure-active-directory-password",
    type: "azure-active-directory-service-principal-secret",
    options: {
      //userName: "@.onmicrosoft.com",
      //password: "****",
      clientId: "****",
      tenantId: "****",
      clientSecret: "****",
    },
  },
};

var connection = new Connection(config);

connection.connect((err) => {
  if (err) {
    console.log("Connection Failed");
    throw err;
  }

  console.log("Custom connection Succeeded");
  connection.close();
});

ConnectionError: Connection lost - socket hang up
     {
    code: 'ESOCKET'
  }

I'm open to any further suggestions or insights you may have. Your expertise is highly appreciated.

Best Regards

[v-yiruan-msft]

Hi @Omar_Osman ,

Base on your description, it sounds like you're experiencing an issue with establishing a silent connection to a SQL endpoint within Fabric. Could you please check or try the following info?

1. Check if have been granted the proper permission

• Check the required scope (it could be Dashboard.ReadWrite.All or Dashboard.Read.All) and whether admin consent is required.
• Token: You could use JWT.io to decode the token to check the aud and permissions.
• Check if the client ID and client secret you're using are correct and authorized to access the SQL endpoint. And verify that the authority URL is correct and that the tenant ID is properly

2. Use a service principal or managed identity for authentication instead of a user account. This would allow for a more streamlined and automated connection process.

• Create a Microsoft Entra app and service principal in the portal 
• Configure Microsoft Entra authentication - Azure SQL Database & SQL Managed Instance & Azure Synapse... 

Best Regards