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.

[johnoscott]

@flarco is it the go-mssqldb 1.8.0 version you need to be using now (which is used by go-sqlcmd v1.8.2 which has the fix) ?

[flarco]

Yea, 1.3.5.dev already does: https://github.com/slingdata-io/sling-cli/blob/v1.3.5/go.mod#L43
I'm not quite sure, it might be the way the url is built by sling.
Can you run with DEBUG=TRACE, it will print the connection string url right before connecting. Perhaps you can spot some issue? I can't test this on my end since I don't have a Fabric instance with that auth setup.

[johnoscott]

@flarco Give me a couple of days (i need to get access to another Fabric capacity)

[johnoscott]

ok here are my latest attempt at a connection test with

connections:
  FABRIC_JOHNMOSA:
    type: sqlserver
    host: REDACTED.datawarehouse.fabric.microsoft.com
    user: REDACTED_USER
    password: "REDACTED_PASSWORD"
    database: johnmosa
    schema: dbo
    fedauth: ActiveDirectoryServicePrincipal
#    encrypt: 'true'
#    trust_server_certificate: 'false'

fails as below

./sling --version
Version: 1.3.5.dev (2025-01-09)
DEBUG=TRACE ./sling conns test FABRIC_JOHNMOSA
2025-01-09 18:19:01 TRC no type detected
2025-01-09 18:19:01 TRC no type detected
2025-01-09 18:19:01 TRC no type detected
2025-01-09 18:19:01 TRC connURL -> sqlserver://REDACTED_USER:[email protected]:1433?app+name=sling&database=johnmosa&fedauth=ActiveDirectoryServicePrincipal
2025-01-09 18:19:03 DBG closed "sqlserver" connection (conn-sqlserver-Wof)
2025-01-09 18:19:04 TRC post event response: 202 Accepted
ok
fatal:
--- proc.go:271 main ---
--- sling_cli.go:474 main ---
--- sling_cli.go:510 cliInit ---
--- cli.go:286 CliProcess ---
--- sling_conns.go:184 processConns ---
--- connection_local.go:64 Test ---
~ could not test FABRIC_JOHNMOSA
--- connection_discover.go:24 Test ---
~ could not connect to FABRIC_JOHNMOSA
--- database_sqlserver.go:175 Connect ---
~ could not connect to database
--- database.go:669 Connect ---
Read: EOF

[johnoscott]

@flarco let me know if you need any more info.

[johnoscott]

@flarco see successful connection and query details using Sling dev Version: 1.3.6.dev (2025-01-22) microsoft/go-mssqldb#230 (comment)

[flarco]

Great. New version (1.4.0) already released. Closing.

[johnoscott]

Thankyou for your perseverence. Fabric is going to be important to a lot of people.

[johnoscott]

@flarco I tested 1.4.0 and its working but it is showing all the debug output (without the --debug flag) ; is this intentional ?

[flarco]

You mean the SQL Server log? Do you have log=33279 in your env.yaml?

[johnoscott]

Oh yea I do ! ~ that explains it. Thanks