SQL Server link server/MSDASQL not work with some ODBC data sources

SQL Server linked server ODBC provider can not connect to an ODBC datasource whose driver loads .net compiled dll.

I have setup an odbc datasource using a driver who calls a .net dll successfully. I can use other tools like ODBCQry or open source odbc query tool to open that odbc datasource and execute some SQL sentences. I also used excel to import the data from that ODBC datasource. I’m quite sure the ODBC data source works with many applications that read and writes data through ODBC.

But the 7303 error happened when I’m using sql server’s built in MSDASQL data provider to set up a linked server for that ODBC datasource. I’m using set up that linked server so that I can access the data in MS SQL procedure or SQL queries. The link is failed due to the initialize of MSDASQL provider is failed. The ODBC driver can’t load the assembly (.net dll files) required when running in the sqlservr.exe process. When the ODBC driver is running in other process like ODBCqry.exe, it can load the assemblies normally.

I tried many methods like modifing reg tables, copying dll files to the sql server’s binary directory, modifying the sqlservr.exe.config to add bind redirections, but no one worked.

After furether search, I found sql server supports load .net assemblies into it’s running process. That’s desgined to let user write custom SQL functions or procedures. The sql server will not trust all .net assembly files, unless the administrator trust the dll files explicitly with asymetric signatures or certificates.

I guess the same restriction also exist for the MSDASQL.dll. When we using the MSDASQL to connect to other ODBC data source, the MSDASQL.dll file is firstly loaded to sqlservr.exe process. The MSDASQL.dll loads the ODBC driver’s dll into the sqlservr.exe process. The ODBC driver’s dll I use was written in C# and it will load some other .net dll files which is not trusted by sql server. The sql server modified the CLR (common langue runtime) in the process, adding a white list of allowed .net assemblies to be loaded into the same process.

I tried to use the creat assembly SQL queries to make SQL server trust them, but there’re too many dll files to add. Libarary A depends on B, B depends on C and etc. The SQL Server has its own .net runtime and that runtime has many differences with system’s .net runtime.

Anyhow, that’s only my guess, if you encounted the same issue and have no other clues to solve this issue, you can the method above. If that worked for you, please notfiy me in the comments.

I think the restrict is quite reasonable, due so many .net libarary exist in the world and some of them can act as foreground GUI/CMD program while SQL server is working as background services. Another reason is that SQL server can run on linux systems, but not all .net assemblies can run on linux system.