Fork me on GitHub

Frequently Encountered Problems (F.E.Q.)

Any Problem

First step to debug any problems is to ensure that tedious is up to date.


tediousjs > node-mssql and tedious-connection-pool

Neither of these repositories are maintained by Tedious. These are third-party libraries that use Tedious.

node-mssql is currently being maintained by a third party.

tedious-connection-pool is not being actively maintained, however, still valid to some extent.

If using either of these, ensure that you're using the latest versions and that their package.json has the latest tedious version as its dependency.


Setting up config

To set up a configuration object for a Connection to SQL server, see connection page.

To set up configuration for running tests locally, see tests page.


Connection to local SQL Server

SQL Server Configurations

Ensure SQL Server Browser and SQL Server Agent is running (Agent can only run on SQL Server Developer/Enterprise, NOT SQL Server Express).

Enable TCP/IP port via SQL Configuration Manager (C:\Windows\SysWOW64\SQLServerManager14.msc for windows).

Ensure user is enabled: SQL Server Management Studio -> Security -> logins -> enable user (that matches the userName in the 'config' object in Tedious).

Tedious Configurations

Ensure port number of the server matches the port number in the 'config' object, and ensure the port number is in the options field of the config object itself, and NOT the Authentication options.

E.g.,

        var config = {
            server: 'localhost',
            authentication: {
              type: 'default',
              options: {
                userName: 'sa', //Username 'sa' was added to server 'users'
                password: 'Password_123', //Password set up during addition of 'sa' user. 
              }
            },
            options: {		
              port: 60543, //This port was specific to my server. Yours might be different. 
            }
          };

Providing a client ID when using azure-active-directory-password

The clientId field is now mandatory when using the azure-active-directory-password authentication type.

Microsoft-owned drivers have a default hard-coded client ID. Because Tedious is NOT a Microsoft-owned driver, the previously provided default client ID has been removed from Tedious. It will expire in the near future and stop working.

Please refer to the connection API documentation for more information and alternate Azure connection methods.


Remote Azure Connection

Tedious version 6.3.0 and above is required for MSI Authentication.

Check configuration in Active Directory's Firewall.


"Requests can only be made in the LoggedIn state, not the SentClientRequest state"

Only one query can be executed on a connection at a time. You need to wait until the request callback is executed, either with an error or with the result before making another request.
(Refer to the TDS protocol state diagram [bottom-half of the diagram] for a high level understanding of the TDS lifecycle).


JavaScript number type limitation

If the number you're trying to insert is not in -9007199254740991 and 9007199254740991 (inclusive), use VarChar type instead.


Connecting to SQL Server 2000

use TDS version 7_1 specified in the config: 

        var config = {
            options: {		
                ...,
                tdsVersion: '7_1'
            }
          };

The Behavior Change of connect() function

Originally the connect() is used like this: 

    const connection = new Connection(config);
    connection.on('connect', (err) => {});
    connection.connect();
It has to work with a event handler function call: connectionObject.on('connect',callback()). Users still have the option to use connect() in this fashion.

However, the new connect() can be used like this: 

    const connection = new Connection(config);
    connection.connect((err) => {});
Users can pass the event handler call back function as a parameter, the connect() will handle the supported events : connect and error based on the callback function that is passed in.

The change not only makes this part of the code simpler, but it was also implemented to deprecate automatically opening a connection when building a Connection instance. Here are links to the related original issue and changes: 730, 1069, 1102, 1145, 1202


Using NTLM authentication with Node.js 17

Node 17 has upgraded to OpenSSL 3.0, which considers md4 encryption a legacy type due to md4 being insecure. Since NTLM uses md4 encryption, you must enable the `--openssl-legacy-provider` command line option in order to use NTLM authentication with Node 17. If you attempt to use NTLM without the option enabled, it will throw an error and not allow you to connect.

Enabling the flag in package.json: 

{
    "scripts": {
        "start": "node --openssl-legacy-provider example.js"
    }
}

Enabling the flag in the command line: 

C:\Tedious> node --openssl-legacy-provider example.js

You can find more information on this here:


The relations between options.trustServerCertificate, options.encrypt, and options.cryptoCredentialDetails

  • If your server has encryption set to 'ON' or 'REQ', and:
    • encrypt = true in your config, the driver will start TLS with the corresponding trustServerCertificate settings.
    • encrypt = false, the driver will throw an error stating that Server requires encryption, set 'encrypt' option to true. then close the connection.
  • If encrypt = true and trustServerCertificate = false, then you may be required to provide your certificate details using cryptoCredentialsDetails. Details on how to provide the cryptoCredentialsDetails object can be found in the connection page of the documentation.
  • If encrypt = false, then trustServerCertificate is ignored.

TLS 1.0 compatibility with Node 12 and newer

The default TLS settings were tightened in Node 12 and newer, requiring at least TLS 1.2 or above. You can find documentation on how to upgrade your server to TLS 1.2 here.

It is NOT RECOMMENDED to continue to use TLS 1.0 due to the associated security risks. However, as a last resort, you can continue to use TLS 1.0 by adding the --tls-min-v1.0 command line flag, or by passing a custom cryptoCredentialsDetails option to the Connection constructor that specifies minVersion: 'TLSv1'.


How Tedious handles Unicode datatype conversions when using parameters

Since all JavaScript strings are Unicode, when using a non-Unicode datatype (such as VarChar), then Tedious will do the conversion from a Unicode string to a non-Unicode string using the iconv-lite library then send the data to the server. The conversion might not match the way SQL Server would do the conversion in some edge cases (like characters that can't be translated to the target encoding).

There are some alternatives to this. You can use a Unicode datatype as the parameter type. Tedious will send the string in the SQL server's default encoding, then the SQL server will do the conversion, which may cause a performance hit in certain situations.

Another option is to use varbinary, if the binary data is encoded in the matching database column encoding. This will bypass any conversion at any point.


Why use aggregate error instead of the normal error object

Users has reported issues that Request and connection do not return the full backtrace of errors.

For Request, this is the related issue: Issue 968

In order to review all the errors, an aggregate error object is ideal to record the errors along the process and allow users to access them easily at the end. To output the errors, users need to do an additional step to output each error one by one using a loop.

For Connection, Tedious used to overwrite the errors during the Azure token retrieval process, and returns a generic error message. With this behavior, users will miss the details from the original error from the SQL server. Using an aggregate error object, Tedious stacks both the generic error message along with the original error object from the SQL server. To output the errors, users need to do an additional step to output each error one by one using a loop.


Other

For more complex issues, please raise an issue on the github page!

If you've found a solution to what you think may be a common problem, please make a pull request on gh-pages branch with the problem and solution, or raise an issue .