Synapse sql login not working

[prestonh0]

We still need sql login functionality, getting SQL Authentication is not supported by Microsoft Fabric, any options here to get this to work?

[dataders]

@prestonh0 to what platform are you trying to use with dbt?

Microsoft Fabric straight up does not support SQL Auth, however "classic" Synapse (this repo), does.

Platform	dbt adapter	hostname like
Azure Synapse Analytics Dedicated SQL Pools	dbt-synapse (this repo)	<INSTANCE>.sql.azuresynapse.net,1433
Microsoft Fabric Synapse Datawarehouse	dbt-fabric (different rep)	<LONG_GUID>.datawarehouse.pbidedicated.windows.net

[prestonh0]

profiles.yml

project_dbt: target: dev outputs: dev: type: synapse driver: 'ODBC Driver 18 for SQL Server' host: myhostname.sql.azuresynapse.net port: 1433 database: mydatabase schema: dbo authentication: sql user: adminuser password: 'adminpass'

Using Synapse classic sql dedicated pools, with dbt debug --log-level debug I get this:

15:23:02 Registered adapter: synapse=1.7.1 15:23:02 Acquiring new synapse connection 'debug' 15:23:02 Using synapse connection "debug" 15:23:02 On debug: select 1 as id 15:23:02 Opening a new connection, currently in state init 15:23:02 fabric adapter: Using connection string: DRIVER={ODBC Driver 18 for SQL Server};SERVER=myhostname.sql.azuresynapse.net,1433;Database=mydatabase;UID={adminuser};PWD=***;encrypt=No;TrustServerCertificate=No;APP=dbt-synapse/1.7.4;ConnectRetryCount=1 15:23:02 fabric adapter: Error running SQL: select 1 as id 15:23:02 fabric adapter: Rolling back transaction. 15:23:02 On debug: No close available on handle 15:23:02 Connection test: [ERROR]

15:23:02 1 check failed: 15:23:02 dbt was unable to connect to the specified database. The database returned the following error:

Database Error ('01S00', '[01S00] [Microsoft][ODBC Driver Manager] Invalid connection string attribute (0) (SQLDriverConnect)')

[prestonh0]

FYI I got this working. My password had } in it, replaced with }} and it worked.