# NODE-Red doesn't have access until i run SQL console as a different user?

**URL:** <https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928>\
**Category:** General\
**Tags:** database\
**Created:** [30 August 2023 19:31 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928 "2023-08-30T19:31:57Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Fell](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fell/32/82440_2.png) [@Fell](https://discourse.nodered.org/u/Fell)\
**Post date:** [30 August 2023 19:31 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928/1 "2023-08-30T19:31:58Z")

</div>

Hello everybody

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 ?

Thanks.

---

<div class="post-metadata">

**Author:** ![KAMERONSWENSON](https://avatars.discourse-cdn.com/v4/letter/k/df788c/32.png) [@KAMERONSWENSON](https://discourse.nodered.org/u/KAMERONSWENSON)\
**Post date:** [31 August 2023 01:22 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928/2 "2023-08-31T01:22:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [31 August 2023 06:54 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928/3 "2023-08-31T06:54:34Z")

</div>

Q1: Which mssql nodes are you using (`node-red-contrib-?????`)

Q2: Are you both using SQL instances?

Q3: Have you specified the port number? **Show me your config node settings**.

Q4: Are you using mixed node authentication (SQL Server authentication mode)?

---

<div class="post-metadata">

**Author:** ![Fell](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fell/32/82440_2.png) [@Fell](https://discourse.nodered.org/u/Fell)\
**Post date:** [31 August 2023 10:12 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928/4 "2023-08-31T10:12:48Z")

</div>

Hi Steve-Mcl, nice to meet you, thanks for your fast reply

R1: I'm using the node-red-contrib-mssql-plus node

R2: I didn't understand the question, but yes, i'm using SQL instance

R3: Yes of course i did it, port 1433, see. I just a bit masked some information

R4: I didn't understand the question, but i don't think so.

![config](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/6/a657526f26e9fef2137638fff10c77f89feb7809.png)  
 ![NODES](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/c/ec38a5e857ce4d8a59320fa577547a1127f5b816.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [31 August 2023 10:32 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928/5 "2023-08-31T10:32:48Z")

</div>

> [@Fell](#):
>
> R3: Yes of course i did it, port 1433, see. I just a bit masked some information

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:

```auto
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](https://github.com/bestlong/node-red-contrib-mssql-plus/issues/63#issuecomment-1067390539) and [here](https://github.com/bestlong/node-red-contrib-mssql-plus/issues/54#issuecomment-877629920) for more cases that should help you.

> [@Steve-Mcl](#):
>
> Q4: Are you using mixed node authentication (SQL Server authentication mode)?

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?  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/f/8f79702326c3760f79435896ee5adc66da6dbe2f.png)

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.

---

<div class="post-metadata">

**Author:** ![Fell](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fell/32/82440_2.png) [@Fell](https://discourse.nodered.org/u/Fell)\
**Post date:** [31 August 2023 10:56 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928/6 "2023-08-31T10:56:02Z")

</div>

Hi

No i don't think so. It's also a domain account. See

So your suggestion is :

- Check on SQL Server Log for "Server is listening on"
- Test with this port number
- Remove the instance ? This is a bit strange for me but i can try

Ty

![sqlcon](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/9/099ff16f49bb5d634df84cbc7a647c20dd0e4f88.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [31 August 2023 10:59 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928/7 "2023-08-31T10:59:39Z")

</div>

> [@Fell](#):
>
> Remove the instance ? This is a bit strange for me but i can try

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!

---

<div class="post-metadata">

**Author:** ![Fell](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fell/32/82440_2.png) [@Fell](https://discourse.nodered.org/u/Fell)\
**Post date:** [31 August 2023 11:16 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928/8 "2023-08-31T11:16:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Fell](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fell/32/82440_2.png) [@Fell](https://discourse.nodered.org/u/Fell)\
**Post date:** [29 September 2023 10:49 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928/9 "2023-09-29T10:49:57Z")

</div>

Hi

Thanks again @Steve-Mcl we solved this issue

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)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [29 September 2023 10:49 UTC](https://discourse.nodered.org/t/node-red-doesnt-have-access-until-i-run-sql-console-as-a-different-user/80928/10 "2023-09-29T10:49:57Z")

</div>

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