CODEWITHSUNDEEP
dbt
EStark
EStark

Reputation: 193

Unable to connect to my local SQL Server using dbt Core

I keep getting an error every time I try to connect to my local SQL Server using dbt Core.

Here is the configuration in my profiles.yml file:

mssql_example_1:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: 'ODBC Driver 17 for SQL Server' # (The ODBC Driver installed on your system) Found under ODBC Data Sources - Drivers
      server: GHOST # This is my local 'Server name' (login screen)
      port: 1433
      database: TEST_DBT_EXAMPLE_ONE
      schema: STG # Should this be dbo?
      windows_login: True

Error (running - dbt build):

Encountered an error:
Database Error
  ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it.
    \r\n (10061) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0);
    [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0);
    [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection
    to SQL Server. Server is not found or not accessible.
    Check if instance name is correct and if SQL Server is configured to allow remote connections.
    For more information see SQL Server Books Online. (10061)')

I've tried several approaches to resolve this (creating a separate account with password for dbt), but so far, nothing has worked. I would greatly appreciate any guidance or suggestions to troubleshoot this issue.

Note:

I'm testing a snapshot model using dbt Core.

For this, I've created a new database named TEST_DBT_EXAMPLE_ONE, and two distinct schemas within it: STG and ARCH.

The plan is to import data from various sources into the STG schema. Subsequently, I aim to experiment with generating snapshots in the ARCH schema.

Upvotes: 0

Views: 1104

Answers (1)

EStark
EStark

Reputation: 193

Got it fixed as follows:

  1. Open SQL Server Configuration Manager.
  2. Navigate to "Protocols for MSSQLSERVER".
  3. Verify that the Status for TCP/IP is set to "Enabled".

Additionally, in case of error: 'SSL Provider: The certificate chain was issued by an authority that is not trusted'

Add the following line to profiles.yml: encrypt: False

Upvotes: 2

Related Questions