1

I am researching stack for a new application we need to work on. One part of it's functionality is being able to connect to a SQL Server database (2012 and newer for now).

I have a Java MVP working very well with almost everything we need: it uses JTDS driver to connect to SQL Server I think using NTLM authentication. Since our web stack is in NodeJS I was also looking at doing the same thing in Node+Electron and ran into problems and realized that I don't understand how parts of the authentication mechanism fit together.

I'd like to summarize some findings here hoping to help others and get some help myself.

I started with tedious since that's recommended in MSDN's docs, could not get the app to connection due to login issues: Login failed with ELOGIN error without extra error information. That's when I read that tedious does not handle Windows Authentication completely: one needs to provide username and password, which is currently being worked on. Providing that still caused the same error. Then I found tedious-ntlm that handles NTLM authentication, which also did not work for me:

Untrusted domain error

Since Java's JTDS NTLM authentication mechanism works well, I take it tedious-ntlm does something different. Also tedious-ntlm got node 6 and 7 to complain about a deprecated crypto function, which was easy to fix, but still tells me the module is not maintained well.

I also tried other drivers and ORMs: mssql, edge, sequelize, seriate, node-sqlserver, node-sqlserver-unofficial without any luck to connect to the database. ORM's like sequelize and seriate use tedious for the driver. Mssql also seems to use tedious - it installs tedious in lib/ and errors come from tedious.

Finally, last confusing came from reading about what exactly is SSPI, Windows Authentication and NTLM and when they are used. Good read on it is here, but in a nutshell: SSPI is the interface that Windows operating systems use to allow applications/users to authenticate themselves using Kerberos, NTLM or other protocols. NTLM seems to be used for local connections, while Kerberos will be used for remote connections, but this might be an oversimplification. This post also goes into good detail about SSPI.

Now here are the questions:

  1. When I want to connect to SQL Server with the following conditions:

    • from localhost to localhost (desktop application)
    • without providion username/password, using current user
    • using Windows Authentication, trusted connection
    • not allowed to create a new user on SQL Server to use SQL Server auth

    ...will I be using NTLM protocol or Kerberos?

  2. Can tedious or any other drivers for Node JS do this?

  3. What from what I've said above is incorrect that's throwing me off?

P.S. I wrote a longer post to show that I've done quite a bit of research, but I am clearly missing something and needs some help beyond what I've already read. Hopefully, this post will also help some people that just started doing this. I will also be happy to update this post with an errata to make it useful for people.

Community
  • 1
  • 1
Serge Poele
  • 117
  • 11

0 Answers0