Problem connecting to mssql express

[mc03225]

I have a local install of mssql express but I cannot connect. I tried windows authentication, then I tried creating a user, but I cannot connect either way. I have checked that mssql express service is running, and I even set the TCP/IP port to 1433, not that this should matter. So far I have not found a way to connect. To get started, I just want to create an inject node with a simple insert query into an existing table. Can you help?

[Steve-Mcl]

questions...

• Is the SQL Server on the same server/computer as node-red?
• Can you connect to the instance using SQL Server Management Studio using SQL Login (not integrated)?
• Is the install an instance?

If you can connect using SSMS using SQL Login, show me the details you used for the SSMS login And a screenshot of the MSSQL-PLUS connection config (NOTE: Obviously, mask out any sensitive info)

[oobyx]

Same problem here

• SQL Server is not on the same server/computer, but in the local network (firewall settings are done - works fine for years with some other MS Windows Client - PCs)
• The install is an instance
• I can connect the instance using SSMS and SQL Login

n

[Steve-Mcl]

@oobyx

AHH. I don't know why I forgot this.

An instance doesn't get port 1433

Connect to your SQL server and check the start up log. You should see the port number assigned. Try using that instead of 1433.

Please let me know if this works for you.

[oobyx]

Thanks Steve! It works - it was Port: 59960!

[Steve-Mcl]

@mc03225 can you try this please?

[mc03225]

Hi,

Thanks - I tried with both port 1433 and now 59960 but unable to connect with either, rest of the config is the same as oobyx, is there anything else I can try?

[mc03225]

HI Stephen, Tried, and some other variations but still unable to connect, anything else I can try? From: Stephen McLaughlin ***@***.***> Sent: 15 March 2022 00:22 To: bestlong/node-red-contrib-mssql-plus ***@***.***> Cc: Anwar Bashir ***@***.***>; Mention ***@***.***> Subject: Re: [bestlong/node-red-contrib-mssql-plus] Problem connecting to mssql express (Issue #63) @mc03225<https://github.com/mc03225> can you try this please? — Reply to this email directly, view it on GitHub<#63 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AKYUJVLU7IQGD6UT73MR53LU77JZVANCNFSM5QJGICBQ>. Triage notifications on the go with GitHub Mobile for iOS<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you were mentioned.Message ID: ***@***.******@***.***>>

[mc03225]

Hi,

Just realised that my instance might be on another port so I checked and found mine was 57708, so I tried and this still failed to connect.

[oobyx]

@mc03225

Browse to the Microsoft SQL Folder Log Folder, which is located at:
C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log

Open the File ERRORLOG.1 with the text editor.

In this file i've found this:

2022-03-10 10:49:20.44 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/XXXXXXXXX-PC:59960 ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

The SQL-Node is configured like that:

Don't put SQLEXPRESS after your server name

[Steve-Mcl]

@mc03225 when I said can you try this

@mc03225 can you try this please?

I meant try this: #63 (comment)

[mc03225]

Hi,

Okay, many thanks - that worked. See screenshot :)

[Steve-Mcl]

Glad it's ok.

[mc03225]

Hi, Thanks for getting back to me so quickly. I am a teacher and I currently show the students sqlite but I would prefer sqlserver express. To this end I simply downloaded a copy server management studio and sqlserver express onto my laptop as a local install with Windows Authentication. Yes, Node-Red is on the Windows laptop. ***@***.*** I created a few databases for testing, I wanted to try connecting Dev with Node-Red ***@***.*** I get failed to connect ***@***.*** ***@***.*** The following is the latest try, I tried various combinations. No user name or password, tried localhost, without and with port number. No luck. ***@***.*** I also checked services to check that sqlexpress is running, and it is. This I also used server configuration manager and added port 1433. I know that this is for remote connection but I was getting a bit desperate. Would be grateful for any help or guidance. Don’t want to use Azure or SqlServer 201x (too much for my needs) ***@***.*** Let me know if you need anything else. Kind regards Anwar Here is my config for the node: From: Stephen McLaughlin ***@***.***> Sent: 09 March 2022 12:59 To: bestlong/node-red-contrib-mssql-plus ***@***.***> Cc: Anwar Bashir ***@***.***>; Author ***@***.***> Subject: Re: [bestlong/node-red-contrib-mssql-plus] Problem connecting to mssql express (Issue #63) questions... * Is the SQL Server on the same server/computer as node-red? * Can you connect to the instance using SQL Server Management Studio using SQL Login (not integrated)? * Is the install an instance? If you can connect using SSMS using SQL Login, show me the details you used for the SSMS login And a screenshot of the MSSQL-PLUS connection config (NOTE: Obviously, mask out any sensitive info) — Reply to this email directly, view it on GitHub<#63 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AKYUJVJVUWQG766EFDPWMNDU7COBXANCNFSM5QJGICBQ>. Triage notifications on the go with GitHub Mobile for iOS<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you authored the thread.Message ID: ***@***.******@***.***>>

[Steve-Mcl]

@mc03225 you should visit the page on GitHub. Much of your reply is messed up. You might want to redo it