Problem synching to Microsoft Fabric Warehouse

[johnoscott]

Issue Description

• Description of the issue:

I am trying to connect to a Microsoft Fabric Warehouse as a target but it fails. I had a look at the docs for https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn to find out how to connect using a Service Principal but it didnt work.

• Sling version (sling --version): Version: 1.3.2

• Operating System (linux, mac, windows): macOS

• Connections (~/sling/.env.yml)

  FABRIC_WH2: # DOESNT WORK !!!!
    type: sqlserver
    host: REDACTED.datawarehouse.fabric.microsoft.com
    user: REDACTED
    password: "REDACTED"
    database: warehouse2
    schema: src_customer
    fedauth: ActiveDirectoryServicePrincipal
    encrypt: 'true'
    trust_server_certificate: 'false'

  SQLSERVER:
    type: sqlserver
    host: localhost
    user: sa
    port: "1433"
    database: SourceDB
    password: REDACTED
    trust_server_certificate: 'true'

• Replication Configuration:

source: SQLSERVER
target: FABRIC_WH2 # see ~/.sling/env.yaml

defaults:
  object: '{target_schema}.{stream_schema}_{stream_table}'
  mode: full-refresh

streams:
  # all tables in schema, except "forbidden_table"
  dbo.*:
  dbo.MyTable:
    disabled: true

env:
  SLING_ALLOW_EMPTY: true  # allow empty files
  SLING_LOADED_AT_COLUMN: timestamp  # adds a _sling_loaded_at column with current timestamp
  # SLING_ROW_NUM_COLUMN: true  # adds a _sling_row_num column with row number of the stream (incremented by record processed)
  # SLING_ROW_ID_COLUMN: true  # adds a _sling_row_id column with random UUIDv7 value. This will be unique.
  # SLING_STREAM_URL_COLUMN: true  # adds a _sling_stream_url column with file path
  # SAMPLE_SIZE: 2000  # increase the sample size to infer types (default=900)
  # SLING_SHOW_PROGRESS: true  # show progress bar
  SLING_DISABLE_TELEMETRY: true  # disable telemetry

• Log Output (please run command with -d):

❯ sling run --debug --replication=./sling-replication-fabric.yml
2024-12-17 16:07:34 DBG processing wildcards for SQLSERVER: ["dbo.*"]
2024-12-17 16:07:34 DBG opened "sqlserver" connection (conn-sqlserver-oTc)
2024-12-17 16:07:34 DBG getting tables for dbo.*
2024-12-17 16:07:34 DBG database discover inputs: {"level":"table","pattern":"dbo.*","schema":"dbo","table":""}
2024-12-17 16:07:34 DBG unfiltered table records returned: 4
2024-12-17 16:07:34 DBG ["sourcedb.dbo.customers","sourcedb.dbo.invoicelineitems","sourcedb.dbo.invoices","sourcedb.dbo.mytable"]
2024-12-17 16:07:34 DBG wildcard 'dbo.*' matched 4 streams => ["dbo"."InvoiceLineItems" "dbo"."Invoices" "dbo"."MyTable" "dbo"."Customers"]
2024-12-17 16:07:34 INF Sling Replication [3 streams] | SQLSERVER -> FABRIC_WH2

2024-12-17 16:07:34 INF [1 / 3] running stream "dbo"."InvoiceLineItems"
2024-12-17 16:07:34 DBG Sling version: 1.3.2 (darwin arm64)
2024-12-17 16:07:34 DBG type is db-db
2024-12-17 16:07:34 DBG using: {"columns":null,"mode":"full-refresh","transforms":null}
2024-12-17 16:07:34 DBG using source options: {"empty_as_null":false,"null_if":"NULL","datetime_format":"AUTO","max_decimals":-1}
2024-12-17 16:07:34 DBG using target options: {"datetime_format":"auto","file_max_rows":0,"max_decimals":-1,"use_bulk":true,"add_new_columns":true,"adjust_column_type":false,"column_casing":"source"}
2024-12-17 16:07:34 DBG opened "sqlserver" connection (conn-sqlserver-K3X)
2024-12-17 16:07:34 INF execution failed
Read: EOF

2024-12-17 16:07:35 INF Sling Replication Completed in 1s | SQLSERVER -> FABRIC_WH2 | 0 Successes | 1 Failures

fatal:
--- proc.go:271 main ---
--- sling_cli.go:452 main ---
--- sling_cli.go:488 cliInit ---
--- cli.go:286 CliProcess ---
~ failure running replication (see docs @ https://docs.slingdata.io/sling-cli)
--- sling_run.go:199 processRun ---

--------------------------- "dbo"."InvoiceLineItems" ---------------------------
--- task_run.go:127 func2 ---
--- task_run.go:566 runDbToDb ---
~ Could not initialize target connection
--- task_run.go:322 getTgtDBConn ---
~ Could not connect to target connection
--- database_sqlserver.go:175 Connect ---
~ could not connect to database
--- database.go:663 Connect ---
Read: EOF

[flarco]

Related to microsoft/go-mssqldb#230.

Updated the library to latest version.

Feel free to try with the latest dev build: https://github.com/slingdata-io/sling-cli?tab=readme-ov-file#automated-dev-builds

[johnoscott]

Thanks @flarco gave it a try with the latest dev version but the result is the same failure:

❯ ./sling --version
Version: 1.3.4.dev (2024-12-17)
❯ sling --version 
Version: 1.3.2
❯ ./sling conns test FABRIC_WH2
fatal:
~ could not connect to database
Read: EOF
❯ 
❯ sling conns test FABRIC_WH2              
fatal:
~ could not connect to database
Read: EOF

[johnoscott]

One difference between the issue described in microsoft/go-mssqldb#230 and mine is that i am trying with fedauth: ActiveDirectoryServicePrincipal and that issue is using fedauth=ActiveDirectoryAzCli

[johnoscott]

With v1.8.2 of go-sqlcmd i was able to make a successful connection to Fabric using the ServicePrinciple authentication method. microsoft/go-mssqldb#230

[flarco]

Cool, will update once it's released.