basic MSSQL database configuration

[jasonunf]

HI,

I'm looking to migrate our CD2 setup from postgres to MSSQL and I'm running into an issue. Does anybody have information on properly setting the DAP for MSSQL server? I'm not finding any information beyond the basic connection string used and i'm getting this error when doing a basic dap init command

2025-02-20 15:17:30,660 - INFO - Package versions: {'aiofiles': '24.1.0', 'aiohttp': '3.11.12', 'aiohttp-retry': '2.9.1', 'instructure-dap-client': '1.3.1', 'json_strong_typing': '0.3.6', 'PyJWT': '2.10.1', 'pysqlsync': '0.8.2', 'tsv2py': '0.7.1', 'types-aiofiles': '24.1.0.20241221'}

INFO:pysqlsync.mssql:connecting to sa@localhost:1433/canvasdata

2025-02-20 15:17:30,670 - ERROR - ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

It looks like i'm supposed to specify an ODBC driver? I've verified that i have version 17 and 18 installed on our Windows Server hosting the DB.

I'm trying to do a basic command like this: dap --base-url https://api-gateway.instructure.com --client-id=client.... --client-secret=secret.... initdb --connection-string mssql://sa:pass@server/canvasdata --namespace canvas --table users

[sgergely]

Hello,

Thank you for the question!

You need to have installed the pyodbc pip package, but if you have installed dap cli through pip, it should be installed, you can verify it by running 

pip list

The next this is what you need to verify is that you need to have ODBC driver 18 or later installed because pysqlsync uses that.

Hope this helps, let me know if that worked!

In the meantime I'm going to update our documentation with this piece of info.

[jasonunf]

thanks for this suggestion, but i do have that package already installed specifically this version 

pymssql 2.3.2
pyodbc 5.2.0

[theologeeky]

Thank you for your post. I was unaware of the dependency and was testing with ODBC 17. This was very helpful!

[JamesSorenson]

@jasonunf 

It looks to me like you're missing the DAP MSSQL "plugin."  WHen I started migrating my DAP Client from Postgres to MSSQL, I ran this: pip install --upgrade "instructure-dap-client[postgresql,mssql]" to ensure I had the MS SQL ODBC driver in the dap client.

[jasonunf · Accepted solution]

Thank you for all the replies, but as the error message stated, looks like its asking to specify a driver. Adding the ?driver={name of DSN for the sql server driver that was created} in the connection string solved my issue. 

here's my connection string:

"mssql://{username}:{password}@{ipaddress}/{databasename}?driver={name of DSN for the sql server driver that was created}"

[theologeeky]

Thank you. You got me all the way there. Note for anyone else, If you are working with SQL Server instances there is no need to escape the "\" when replacing {ipaddress} with{{ServerName}\{InstanceName}}.