Driver SQL Server Native Client 11.0 has been removed from SQL Server 2022

[safwanmasarik]

Expected behaviour:

Using module mssql/msnodesqlv8. When using Windows, creating new connection pool, the driver is always defaulted to SQL Server Native Client 11.0. Expected to have connections established successfully.

Actual behaviour:

In a fresh Windows 10 machine, installing SQL Server 2022 Developer edition, the ODBC driver SQL Server Native Client 11.0 is no longer included, as Microsoft does not recommend using the driver for new application development. Source https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/installing-sql-server-native-client?view=sql-server-ver16. Therefore on connect, we'll receive error [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

Workaround:

Current work around for now is applying beforeConnect method to override the driver to a supported ones such as ODBC Driver 17 for SQL Server.

import sql from "mssql/msnodesqlv8";

const config = {
        user: process.env["SQLUsername"],
        password: process.env["SQLPassword"],
        server: process.env["SQLServer"],
        database: process.env["SQLDatabase"],
        options: {
            encrypt: false, // set to true for Azure SQL
            trustedConnection: true, // set to true if use Windows authentication for local db
        },
        beforeConnect: function (bcConfig) {
            // Modify the connection options here
            /** The Driver SQL Server Native Client has been removed from SQL Server 2022. 
             *  Source https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/installing-sql-server-native-client?view=sql-server-ver16  
             *  ODBC Driver 17 for SQL Server is tested working well with SQL Server 2019 & 2022 */
            bcConfig.conn_str = bcConfig.conn_str.replace("SQL Server Native Client 11.0", "ODBC Driver 17 for SQL Server");
        }
    };

    let pool = new sql.ConnectionPool(config);

My github repo reference.

Software versions

• NodeJS: v18.14.2
• node-mssql: ^9.1.1
• msnodesqlv8: ^3.0.1
• SQL Server: SQL Server 2022
• Windows 11 Enterprise 64-bit operating system, x64-based processor

[dhensby]

I believe this would be solved by https://github.com/tediousjs/node-mssql/pull/1430 - but that PR hasn't had the feedback (tests not passing) addressed, so needs to be picked up

[safwanmasarik]

Noted @dhensby. Appreciate the attention to solve this 🙂, thanks.

[Falven]

Any update?

[TomMiller-mas]

Is this also a problem when using TDS? I am using the connection string vs the connection object and it won't connect to SQL Server Express 2022. No problem with Developer 2019.

[dhensby]

I don't believe it is a problem when using TDS

[TomMiller-mas]

Does it matter that I am using the connection string vs a connection object? The string below logs in fine to SQL Server 2019. The same database was restored to Express 2022, the same user login and everything is the same except the Server type and version (Developer => Express, 2019 => 2022). And I have tried every combination of server and port and still nothing.

server=localhost\SQLEXPRESS;database=MyDatabase;user Id=abcabc;password=xyxyxyxyxyxyxyxy;encrypt=false;trustServerCertificate=true

[dhensby]

That should not make any difference. Connection strings are just parsed into an object.

The Native Client does seem to have been removed from SQL server 2022, so may need to be downloaded manually: https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/installing-sql-server-native-client?view=sql-server-ver16

[TomMiller-mas]

Does TDS have legs? Is this something that will be supported for a long time? I see more and more people using odbc sql v8 driver. I want to stay away from MS ODBC driver as there a lot of bugs in it. Devart has an ODBC driver that fixes many of the bugs ($400), but prefer a native connection to the database. I think the bug is with Tedious, so will continue asking tech questions there. Thanks!

[TomMiller-mas]

This code finally worked. I think it was the trusted certificate was all lower case. But it worked on 2019. After changing about 20 times, I am not sure what change really fixed it. Here is what is working. We run a multitenant system, we look up the connection info from an encrypted file based on the database connection id.

if (data[k].port != "") {dbPort = "," + data[k].port;} var mssqlConnStr = "Server=" +data[k].server + dbPort + ";Database=" + data[k].database + ";User Id=" +data[k].usrNm + ";Password=" + data[k].usrPwd + ";encrypt=false;trustServerCertificate=true";