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

user_7999380 0
Viewed 1655

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?

tedious node.js sql-server
1 Answers

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.

user_148689970
Related