I've a script engine workflow who make a Query to an SQL View. The Database who contains the view is on a different server on the network.
I've a domain account (user name + password) to negociate the connection to SQL database who has the view
Node-Red service is running as local system account, standard
So, what is very strange is :
I can not load the SQL view, it doesn't work,i'e a message : "Failed to connect to MYSQLSERVER\SQLINSTANCE in 15000ms"
Then, i make a shift + right clic on the software SQL Server Management Studio (SSMS) and choose the button "Run as different user". Here, i negociate the same user that i defined in the Node-Red view query component (who has access to target DB where is the view)
After this, i can make the select to the view from the NODE-Red, flow. It works, during maybe 12h or 24h, and the next day, it doesn't work. I launch again the SSMS software as a different user, negociate the account and it work again.
It's like, when i open the SSMS with this user, it allows or etablish probably a UDP connection from MYSERVER to --> MYSQLSERVER with the user who is defined in the node-red script.
Do you have an idea, what could be the port that i should allow ? Or what could be the problem ? Should i just change the account in Windows Services and run the NODE-Red service not with the local account but with the domain account who has access to MYSQLSERVER\MYINSTANCE ?
I am having a similar issue that i cant resolve either. I am able to connect through SSMS on both local and host server in sql server but in node red with the same credentials i can not run a query on the database and get the same error message as this. Whats also weird is instead of placing the server name in the server configuration of the node i place the IP address of the server and it creates an endless loop and crashes the browser.
Instances typically do NOT live on port 1433. Non instance based SQL Servers do (by default)
Can you look in the SQL Server Log for "Server is listening on" or run this query:
USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on', N'any', NULL, NULL, N'asc'
GO
Once you have the port number, try again.
Also, try removing the instance name from the server field.
Lastly, look here and here for more cases that should help you.
As you are providing a user name an password I suspect your server is setup for mixed mode (thats good - as I know that works).
As a test, can you login to SSMS using SQL Server Authentication?
NOTE: I am not saying windows authentication will not work but that the account running Node-RED may need to be added to the SQL Server logins. But I do now SQL Authentication works best/easiest.
To be precise: "Remove the instance name from the Nodes config" - i.e. enter serverName instead of serverName\instanceName
All the instance name does is tell the client connecting to "find the port this SQL Server is running on" - if you enter the CORRECT port number that the instance is running on, there is no need to enter an instance name. Nothing strange about it at all TBF!
Hi,
it works without the instance, but only if i open the Management Studio Console with negociate the account before
I think i've first to find which port i've to use instead 1433
Ty
The port TCP of the instance was 50000. When i tried to connect it directly it was also not working, then we opened the TCP port 50000 and now it works with both (instance name or port)
