Proper configuration of a Windows environment to access an on-prem sqlserver instance

[cks2018ngc]

I am getting the following error when trying to execute "dbt debug":

Encountered an error:
DLL load failed: The specified module could not be found.

example:

(dbt-env) C:\Users\cs\dbt\windowsShell\projExample>dbt debug
Running with dbt=0.19.1
dbt version: 0.19.1
python version: 3.7.9
python path: c:\users\cs\dbt\windowsshell\dbt-env\scripts\python.exe
os info: Windows-10-10.0.18362-SP0
Using profiles.yml file at C:\Users\cs.dbt\profiles.yml
Using dbt_project.yml file at C:\Users\cs\dbt\windowsShell\projExample\dbt_project.yml

Encountered an error:
DLL load failed: The specified module could not be found.

my profiles.xml looks like the following:

projExample:
target: dev
outputs:
dev:
type: sqlserver
driver: 'ODBC Driver 17 for SQL Server'
server: # like sqlserver.mydomain.com
port: 1433
authentication: ActiveDirectoryIntegrated
client_id: cs
encrypt: True
trust_cert: False
database:
schema:

I did a pip install of dbt-sqlserver
and also installed the ODBC Driver. My instinct is that something is hosed with dbt being able to access the ODBC but don't know how to debug further nor check additional configurations.

[dataders]

@cks2018ngc I've seen this error before as well. It seems its related to the pyodbc package. In mkleehammer/pyodbc#663, someone comments recommending reinstalling C++

I can confirm the report from @CodeX1101 that installing the latest "Microsoft Visual C++ Redistributable for Visual Studio 2015, 2017 and 2019" from the links at

https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads

(vc_redist.x64.exe in my case) resolves the issue.

Want to try that and see if it works?

p.s. this is a good example of an "Issue" rather than a "Discussion".

[dataders]

also, another person recommends upgrading the version of pyodbc

[cks2018ngc]

Hi Thanks for the help. I've cleared the dll issue by doing the following:

Uninstalled all MV C++ versions including the most recent one. Used the vc_redist.x63.exe and re-installed.
uninstalled the dbt-sqlserver and re-installed
I also had pyodbc at 4.0.30 the latest

This did not work.

I then uninstalled python 3.7.x and installed 3.8.x
deleted my dbt-env dir
python -m venv dbt-env
pip installed pyodbc, dbt-redshift, dbt-sqlserver, then dbt

At that point, I've eliminated the dbt debug issue regarding the missing dll

However, there is a new rub:

Configuration:
profiles.yml file [OK found and valid]
dbt_project.yml file [OK found and valid]

Required dependencies:

• git [OK found]

Connection:
server: myServersCname
database: myDatabase
schema: dbo
port: 1433
UID: None
client_id: cs
authentication: ActiveDirectoryIntegrated
encrypt: True
trust_cert: True
Connection test: ERROR

dbt was unable to connect to the specified database.
The database returned the following error:

Runtime Error
list.remove(x): x not in list

Check your database credentials and try again. For more information, visit:

[cks2018ngc]

Ah - I found the issue. I flipped from ActiveDirectoryIntegrated to Windows Login.

All is good now.

Thanks @swanderz