Node.js connection to SQL Server using instance name, port, and domain

Question

I'm building a Node.js application which is trying to connect to a very specific SQL Server configuration: It's running as an instance, with a custom port and users need to login through Active Directory-- hence the connection should be done using domain

I'm using mssql package using tedious API, and this is my configuration:

connectionOptions = {
    server: 'USRCMX01\\INSTANCE1',
    port: 1367,
    domain: 'stagingsv',
    user: 'x.myuser.01',
    password: 'myuserpassword',
    options: {
        instanceName: 'INSTANCE1',
        trustServerCertificate: true,
        enableArithAbort: true
    }
};

However, when using this configuration I get a timeout error from SQL Server. After initiating SQL Server Browser service and opening port 1434 from my SQL Server instance server, I tried again but had the same luck

The connection string I was using before from SQL Server Management studio looks like this:

Driver={SQL Server Native Client 11.0};Server={USRCMX01\\INSTANCE1,1367};Uid={stagingsv\\x.myuser.01};Pwd={myuserpassword};Trusted_Connection={true};

.. And it works flawlessly

Is there any way to connect to a SQL Server instance using instance name, domain, and port at the same time from Node.js?

Try removing the \\INSTANCE1 from the server name. Also either use port or instanceName not both — Charlieface
When you use an instance name the client needs to query the SQL Browser Service on udp/1434 to resolve the instance name to a tcp port number. If you already know the port then just use servername,port in the connection string. — AlwaysLearning

Charlieface Charlieface · Accepted Answer · 2021-01-27T21:27:30

Windows Authentication for SQL does not use passwords. It uses the credentials of the existing logged-in user.

So your SSMS connection string is actually ignoring the username and password, and you shouldn't pass it. To get the same effect with Tedious, it looks from the docs like you need to set:

 authentication.type = 'ntlm'

But if not then try some of the other options from there also.

The way Windows Auth works is that it passes the Kerberos ticket (for AD users) or NTLM hash (for non-AD) that the user already has from when they logged in. There is no password in the connection string

Caveat: if you are using Azure AD, as opposed to on-premise, then that works differently. You can either use a password or the Kerberos ticket.

Node.js connection to SQL Server using instance name, port, and domain

1 Answers