0

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.

1
  • 1
    Can you access your local db via terminal? If yes then you should take a look at SQL server config, try to change the server to your machine IP Commented Dec 6, 2023 at 2:02

1 Answer 1

2

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.