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

General
1

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

1
11907Ă—872 142 KB

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

SQL-Client-Configuration
SQL-Client-Configuration1894Ă—1004 86.1 KB

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!

2

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

3

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.

4

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
5

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

6

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

7

There are many ways to do that. Do you mean the first method described in Running on Windows : Node-RED

8

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:

node-red-node-sqlite
node-red-node-sqlite1277Ă—942 40.2 KB

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

NodeRed-MSSQL-Plus-Reinstall
NodeRed-MSSQL-Plus-Reinstall1284Ă—114 7.03 KB

9

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

1 Like
10

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:

NodeRed-SQL-Querry-ok
NodeRed-SQL-Querry-ok1890Ă—835 102 KB

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:

11

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
12

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.
13

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
14

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
15

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.

16

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