Node-red-contrib-mssql-plus 0.5.1 --> Can't connect to MSSQL

Hello guys!
I tried to query simple data from local MS SQL but not success.

I also tried with another SQL Client software with same configuration, it worked well.

I tried to reinstall it and run again, but it doesn't work. Can some body help me, please?

My nodes:
[{"id":"b1936c43.ce1ad","type":"tab","label":"Flow 7","disabled":false,"info":""},{"id":"1b87abb0.34259c","type":"inject","z":"b1936c43.ce1ad","name":"{\"count\": 5, \"age\": 35}","props":[{"p":"payload"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"{\"count\": 5, \"age\": 35}","payloadType":"json","x":300,"y":140,"wires":[["8ce9ae3.82a295"]]},{"id":"c6cb6799.2f54b","type":"debug","z":"b1936c43.ce1ad","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":350,"y":260,"wires":[]},{"id":"8ce9ae3.82a295","type":"MSSQL","z":"b1936c43.ce1ad","mssqlCN":"66a6a2e2.728704","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"SELECT *\nFROM SQLDBLeaningPurpose.dbo.ParameterData WHERE ParameterName = 'G25_S1_acc_work'","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","params":[],"x":320,"y":200,"wires":[["c6cb6799.2f54b"]]},{"id":"66a6a2e2.728704","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"My SQL Server","server":"localhost","port":"1433","encyption":false,"database":"SQLDBLeaningPurpose","useUTC":false,"connectTimeout":"2000","requestTimeout":"2000","cancelTimeout":"2000","pool":"5","parseJSON":false,"enableArithAbort":true}]

Thanks, guys!

You entered localhost for the server. Is node-red backend and SQL server running on the same machine?

Yes, node-red, SQL server and SQL Client software are running on the same machine. SQL Client software can connect to SQL server and query data properly, but node-red doesn’t.

So you are running Node-RED under windows? Did you try a simple password with no chance to misspell it?
BTW I just saw in the flow, that there seems to be an error with the node: "encyption"!

1 Like

What operating system are you using and how did you install node-red?

I am using Window10 64bit, I installed node-red via Command Prompt

There are many ways to do that. Do you mean the first method described in https://nodered.org/docs/getting-started/windows

Yes, I followed that link to installed via Command Prompt with the command npm install -g --unsafe-perm node-red
I also tried to install Node-red-node-SQLite https://flows.nodered.org/node/node-red-node-sqlite
But not success:

Then I tried to reinstall Node-red-contrib-mssql-plus 0.51 again, and observed some warning of deprecation :frowning: :

The sqite error is, as it says in the log, that you need to install python.

1 Like

I changed password of user "sa" (default user of MS SQL) to "123456789password" and tried with this account. It worked :smiling_face_with_three_hearts:

I tried again with my previous account, it still not work.
Maybe there is some issue with my account & password (even though it worked well with MS SQL server Management Studio, and SQL Client software (SQLecton)).
Thank you very much, guys! :beers:

If you could do some testing to find what the problem is (maybe particular chars in password or something) then I am sure the author of the node would like to know in order to fix it.

1 Like

I tried:

  1. change password of my previous account to: "123456789password" --> it worked--> no issue with my account's role or permission.
  2. add "@" to my pass: "123456789password@" --> it doesn't work any more --> may be some issue if there is character "@" in password.

Thanks for the info, I'll be sure to check the source and fix it if necessary.

Glad you have a working connection now @kabuto-gyu was worried for a minute I'd messed something up on the last release.

1 Like

Hi @kabuto-gyu I have tried setting a SQL 2017 "SQL account" password to 123456789password@ and everything worked correctly.

The only thing I can think is if your keyboard mapping was incorrect & the @ was being entered in the password field as some other character?

If you wish to test this, type your password into a visible editor (notepad / vscode etc) copy the password and paste it into the field (to ensure the correct characters are entered)

Cheers, Steve.

1 Like

Dear Steve,

--> I tested as you said, typed password (with special character "@") into notepad, copy & paste it into the field carefully, check if it same as original, then it worked.
I tried to type password manually into the field of user (visible), the character, which is supposed to be "@", has become letter (" ), wrong keyboard mapping as you guessed :scream: . Maybe it happens because my keyboard is mapping to multiple languages.
It will be great if you add a button to visible password for checking purpose.
Thank you very much.

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.