Unable to connect with MSSQL

[alexeisenhart]

I've modified settings.json as follows to enable MSSQL as the database storage technology.

I'm using a default, unnamed, installation of MSSQL. Port 1433 isn't redirecting correctly for some reason, but I determined that it's listening on 1434 by using the script below.

There was an issue with self signed certificates (ConnectionError: Failed to connect to (server name here) - self signed certificate) which was resolved by adding trustServerCertificate: true.

At this point it's failing with ConnectionError: Login failed for user 'eptest'. I think this might be more on the node side rather than the database server, because I can connect to the MSSQL server using SSMS with the user's credentials and the user can access the configured database. I browsed through various js files related to connections and the connection pool, and the mssql implementation in ueberdb2 specifically, but nothing jumped out at me.

I added a firewall exception, just in case that was the issue, as described here: https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver15#BKMK_dynamic_ports

Any suggestions? Is there any way to turn on more verbose logging?

Settings.json snippet

  /*"dbType": "dirty",
  "dbSettings": {
    "filename": "var/dirty.db"
  },*/

  "dbType": "mssql",
  "dbSettings": {
    "user":     "eptest",
    "password": "user's password here",
    "host":     "localhost",
    "port":     1434,
    "database": "etherpad_lite_db",
    "options": {
        "trustServerCertificate": true
    }
  },

For future users, here's the script to find the port that MSSQL is listening on:

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on' 
GO

[JohnMcLear]

Can you please connect an MVP using: https://github.com/ether/ueberDB ?

We don't test MSSQL in CI so it's possible something is awry

[JohnMcLear]

const ueberdb = require('ueberdb2');

// mysql
const db = new ueberdb.Database('mssql' , {
  "user":     "eptest",
  "password": "user's password here",
  "host":     "localhost",
  "port":     1434,
  "database": "etherpad_lite_db",
  "options": {
      "trustServerCertificate": true
  }
}

(async (){
  await db.init();
})()

[alexeisenhart]

Thanks for your fast response! That's a good idea. How should I go about running that snippet? I threw it into a test.js file in the node folder (same folder as server.js) and tried to run it with node, but I'm getting syntax errors.

Sorry, I don't have much experience with node.

[alexeisenhart]

Ah, ignore that. I found the syntax error.

I get the same exception: Login failed for user 'eptest'. I did modify the config to add the correct user password.

[alexeisenhart]

Script:

const ueberdb = require('ueberdb2');

// mysql
const db = new ueberdb.Database('mssql' , {
  "user":     "eptest",
  "password": "test123",
  "host":     "localhost",
  "port":     1434,
  "database": "etherpad_lite_db",
  "options": {
      "trustServerCertificate": true
  }
});

 db.init();
/*(async (){
  await db.init();
})()*/

Output:

C:\Users\alexe\Downloads\etherpad-lite-develop-d7ed71e-2\etherpad-lite-develop>node node_modules\ep_etherpad-lite\node\test.js
tedious deprecated The default value for "config.options.validateBulkLoadParameters" will change from `false` to `true` in the next major version of `tedious`. Set the value to `true` or `false` explicitly to silence this message. node_modules\ep_etherpad-lite\node_modules\mssql\lib\tedious\connection-pool.js:62:23
(node:28252) UnhandledPromiseRejectionWarning: ConnectionError: Login failed for user 'eptest'.
    at C:\Users\alexe\Downloads\etherpad-lite-develop-d7ed71e-2\etherpad-lite-develop\node_modules\ep_etherpad-lite\node_modules\mssql\lib\tedious\connection-pool.js:65:17
    at Connection.onConnect (C:\Users\alexe\Downloads\etherpad-lite-develop-d7ed71e-2\etherpad-lite-develop\node_modules\ep_etherpad-lite\node_modules\tedious\lib\connection.js:1017:9)
    at Object.onceWrapper (events.js:421:26)
    at Connection.emit (events.js:314:20)
    at Connection.EventEmitter.emit (domain.js:483:12)
    at Connection.emit (C:\Users\alexe\Downloads\etherpad-lite-develop-d7ed71e-2\etherpad-lite-develop\node_modules\ep_etherpad-lite\node_modules\tedious\lib\connection.js:1045:18)
    at Connection.message (C:\Users\alexe\Downloads\etherpad-lite-develop-d7ed71e-2\etherpad-lite-develop\node_modules\ep_etherpad-lite\node_modules\tedious\lib\connection.js:2738:18)
    at Connection.dispatchEvent (C:\Users\alexe\Downloads\etherpad-lite-develop-d7ed71e-2\etherpad-lite-develop\node_modules\ep_etherpad-lite\node_modules\tedious\lib\connection.js:1641:15)
    at MessageIO.<anonymous> (C:\Users\alexe\Downloads\etherpad-lite-develop-d7ed71e-2\etherpad-lite-develop\node_modules\ep_etherpad-lite\node_modules\tedious\lib\connection.js:1434:14)
    at MessageIO.emit (events.js:314:20)
(node:28252) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
(node:28252) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

[JohnMcLear]

the error is bubbling up from tedious..

https://github.com/tediousjs/node-mssql#config

[alexeisenhart]

I'm making progress. Thanks for the tip. I'll hopefully be able to respond with the resolution.

[alexeisenhart]

@JohnMcLear I have Etherpad up and running on my Windows dev box using MSSQL! Below are my debugging notes, which will hopefully help someone else (or me!) in the future. Thank you again for your help!

---

I didn't know this existed, but there are great SQL Server logs that might be useful to people who are diagnosing this issue. https://docs.microsoft.com/en-us/sql/relational-databases/performance/view-the-sql-server-error-log-sql-server-management-studio?view=sql-server-ver15

In my case, I'm seeing two errors for each login attempt:

Login failed for user 'eptest'. Reason: Login-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission. [CLIENT: 127.0.0.1]

Error: 18456, Severity: 14, State: 149.

This lead me to: https://dba.stackexchange.com/questions/214374/login-lacks-connect-endpoint-permission-in-sql-server-error-18456-state-149

In SQL Server, run this. You should have at least one record returned. I have a record, so IDK what to do if you don't, but that is something to look into.

SELECT * FROM sys.server_permissions AS sp2 
    JOIN sys.server_principals AS sp
        ON sp2.grantee_principal_id = sp.principal_id
    LEFT OUTER JOIN sys.endpoints AS e
        on sp2.major_id = e.endpoint_id
WHERE sp2.permission_name = 'CONNECT' 
AND sp2.class_desc = 'ENDPOINT'

-- Should be at least one record matching the following
AND sp2.state_desc = 'Grant' and sp.name = 'public' and e.name = 'TSQL Default TCP'

Following that, I granted CONNECT permissions for the TSQL Default TPC endpoint. First I granted to only this SQL user and then I granted to public; neither resolved the issue.

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO sptest;
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public;

Maybe your TCP/IP connection is disabled. This was my problem. I have a default installation of developer edition of MSSQL that I just set up this week. I've been using SQL Server with ASP.NET, services, Winforms, WPF, etc for 15 years and I've never had reconfigure this. I believe that SSMS will usually connect with named pipes rather than a local TCP/IP connection, so testing the login credentials with SSMS was not a valid test.

Use the 'SQL Server Configuration Manager', go to the network configuration section and confirm that TCP/IP is enabled (enable it if you need to). Open the properties of TCP/IP and confirm that IP4 is active and take note of the configured Port.

After making these changes, I adjusted my connection configuration to use the correct and default port, 1433, and the SQL user was able to connect in both the MVP test and in etherpad.

[JohnMcLear]

Maybe your TCP/IP connection is disabled. This was my problem. I have a default installation of developer edition of MSSQL

Wow... That's the latest hipster changes all databases have been making TBF :\ It's kinda annoying..

[JohnMcLear]

I guess as you have championed this would you mind updating the docs in the wiki please too? Good work and thank for taking the lead, I appreciate this type of professionalism 💯

[alexeisenhart]

Thanks! Yeah, I'd be happy to help, if you don't mind a few newbie questions along the way. I haven't contributed to an open source project on Github before. I should make my changes and submit a pull request?

I think everything in this issue was related to TCP/IP being disabled, so I think this item can be closed. If it's okay with you then I'll continue recording issues and hacky fixes related to Windows on #4800? Once I resolve everything, I can consolidate all of the notes and update the wiki.

[JohnMcLear]

I haven't contributed to an open source project on Github before.

Well you are doing great :)

If the changes require changes to source code or docs within the repository (README.md etc)then you would make a PR. If the docs are just within the wiki, they are editable without any peer review.

This item can be closed. I'm still working on windows in parallel to you btw, it is something we will fix in core that wont require any windows hackery.