loopback-connector-mssql windows authentication

[Unarine]

Hi,
Does loopback allow for windows authentication when using the mssql connector? If so, how can I achieve this?

[jacko]

The same issue, is there any solution ?

[Unarine]

So far none that I've seen

[jannyHou]

@Unarine loopback-connector-mssql is using nodejs driver require('mssql'), but the windows authentication is only supported in another driver require('mssql/msnodesqlv8')
see https://github.com/tediousjs/node-mssql#microsoft--contributors-node-v8-driver-for-nodejs-for-sql-server
That driver is from another dependency npm install msnodesqlv8

I am not sure what is the compatibility between those two drivers...and how much effort we need to do a driver switch.

[raymondfeng]

We only use the tedious driver for the connector. But I believe that tedious also supports Windows authentication.

[jannyHou]

Thanks Raymond, the issue&PR for supporting windows authentication in tedious:
tediousjs/tedious#246
tediousjs/tedious#624

[jannyHou]

See issue tediousjs/tedious#246 (comment) connecting using windows authentication can work by modifying the datasources.json within the LoopBack application to include the "domain" attribute. Something like this:
"myds": {
"connector": "mssql",
"host": "company.com",
"database": "demo",
"domain": "DOMAIN_NAME_CAPITALIZED_AND_NOT_FQDM",
"server": "servername",
"username": "user",
"password": "password"
}

---

I verified it works.

I created an loopback 3 app with template "Note", which comes with a default "Note" model. Then created a mssql datasource with the following configure(please note the "domain" property is provided), and attached the "Note" model to that datasource:

"mssql": {
"host": "127.0.0.1",
"port": 1434,
"domain": "LOCALHOST",
"database": "mydatabase",
"password": "mypassword",
"user": "myusername",
}

Migrate the model and create two sample instances from the explorer.
Switch to the mssql terminal and select all instances from the table "Note", you can see the new instances are added to the database.

I am closing this issue. Feel free to reopen it if you still have questions. Thanks

[Aditya-Thakur]

I have tried all the things still getting the error:....
hiding the username for obvious reasons
"error": {
"statusCode": 500,
"name": "ConnectionError",
"message": "Login failed for user '\Aditya*****'.",
"code": "ELOGIN",
"originalError": {
"message": "Login failed for user '\Aditya*****''.",
"code": "ELOGIN"
},

here's the code in datasources.json
"SQLEXPRESS": {
"host": "localhost",
"port": 1433,
"database": "EBS_QA",
"password": "",
"name": "SQLEXPRESS",
"domain":"",
"user": "aditya***",
"driver":"mssql/msnodesqlv8",
"connector": "loopback-connector-mssql",
"lazyConnect": true,
"options": {
"encrypt": false
}
I don't understand what else I am missing... The username and password I am using is my windows credentials.

here's the stack of error:
Unhandled error for request POST /api/my_users: ConnectionError: Login failed for user 'ITLINFOSYS\aditya31.TRN'.
at Connection.tedious.once.err (C:\Users\aditya****\Desktop\EBS\src\EBS-BackEnd\node_modules\mssql\lib\tedious.js:239:17)
at Object.onceWrapper (events.js:273:13)
at Connection.emit (events.js:182:13)
at Connection.processLogin7NTLMAck (C:\Users\aditya****\Desktop\EBS\src\EBS-BackEnd\node_modules\tedious\lib\connection.js:1342:16)
at Connection.message (C:\Users\aditya***\Desktop\EBS\src\EBS-BackEnd\node_modules\tedious\lib\connection.js:1876:14)
at Connection.dispatchEvent (C:\Users\aditya****\Desktop\EBS\src\EBS-BackEnd\node_modules\tedious\lib\connection.js:1012:38)
at MessageIO. (C:\Users\aditya****\Desktop\EBS\src\EBS-BackEnd\node_modules\tedious\lib\connection.js:891:18)
at MessageIO.emit (events.js:182:13)
at Message. (C:\Users\aditya****\Desktop\EBS\src\EBS-BackEnd\node_modules\tedious\lib\message-io.js:60:15)
at Message.emit (events.js:187:15)
at endReadableNT (C:\Users\aditya****\Desktop\EBS\src\EBS-BackEnd\node_modules\readable-stream\lib_stream_readable.js:1010:12)
at process._tickCallback (internal/process/next_tick.js:63:19)