Hi, I try use microsoft sql in n8n but always keep error not connect. If I try with sql server management studio or dBeaver it’s works. The connection is to a remote server.
Mapping fields
dBeaver → n8n → example
host → Server → 12.658.625.24\ABCDE
Port → Port → 5210
Database → Database → BVTPO
Username → User → AAAAAA
Password → Password → CCCCCC
Domain is empty , not corresponding any field in dBeaver.
TDS Version 7_4
TLS enabled/disabled not working
When you did your test with SSMS and dbeaver was that also from WSL? The error back is a fairly standard network timeout so it could be that something odd is happening with the network routing from WSL. If you put in a wrong port or a wrong server do you get a different error?
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
var config = {
server: '12.658.625.24', //update me
instanceName: 'ABCDE',
authentication: {
type: 'default',
options: {
userName: 'AAAAAA', //update me
password: 'CCCCCC' //update me
}
},
options: {
// If you are on Microsoft Azure, you need encryption:
"encrypt": false,
port: 5210,
database: 'BVTPO' //update me
}
};
var connection = new Connection(config);
connection.on('connect', function(err) {
// If no error, then good to proceed.
if(err) {
console.log('Error: ', err)
} else {
console.log("Connected");
executeStatement();
}
});
connection.connect();
function executeStatement() {
var request = new Request("SELECT * FROM ABCD;", function(err) {
if (err) {
console.log(err);}
});
var result = "";
request.on('row', function(columns) {
columns.forEach(function(column) {
if (column.value === null) {
console.log('NULL');
} else {
result+= column.value + " ";
}
});
console.log(result);
result ="";
});
request.on('done', function(rowCount, more) {
console.log(rowCount + ' rows returned');
});
// Close the connection after the final event emitted by the request, after the callback passes
request.on("requestCompleted", function (rowCount, more) {
connection.close();
});
connection.execSql(request);
}
this example run directly on nodeJs works.
But If I remove instanceName and change “server” to 12.658.625.24\ABCDE or 12.658.625.24\ABCDE does not work.
n8n does not allow to send the separate instance of “server”?
That is interesting, I wasn’t sure if SSMS or dbeaver would work from WSL as the last time I used it you could use it for terminal apps but not guis.
We use node-mssql rather than tedius so there could be something there but one thing I have noticed is you are setting an instance name there which we don’t set a property for so it could be that named instances are the issue.
In the node credential can you set the server to 12.658.625.24\\ABCDE and see if that works… just noticed your edit and it looks like you noticed the same thing.
sorry what i wanted to say is running on the same machine and the example above was run on the same wsl i am working with n8n.
I used an example in node-mssql that I show below, and it worked, then I tried to run it again on n8n and it did change “Server” to 12.658.625.24 only.
const sql = require('mssql');
const sqlConfig = {
user: 'AAAAAA',
password: 'CCCCC',
database: 'BVTPO',
server: '12.658.625.24',
instanceName: 'ABCDE',
pool: {
max: 10,
min: 0,
idleTimeoutMillis: 30000
},
options: {
port: 5210,
encrypt: true, // for azure
trustServerCertificate: true // change to true for local dev / self-signed certs
}
};
(async () => {
try {
console.log("b");
// make sure that any items are correctly URL encoded in the connection string
await sql.connect(sqlConfig);
console.log("a");
const result = await sql.query`select * from tableinsql`
console.dir(result)
} catch (err) {
console.log(err);
// ... error checks
}
})();
sql.on('error', err => {
console.log(err);
// ... error handler
})
I think this is a limitation of n8n, it should have a field for the instanceName in the UI. Use 12.658.625.24\ABCDE not working , instanceName should be separated.
who has this problem try running the example above and when it works run n8n. It’s work
Did you try 2 \ with the instance? Having a field for the instance makes sense to me and it will be a fairly quick add but I want to make sure that it works with \\ as the package docs suggest.
The issue linked is about remote as well and has an error of ConnectionError: Failed to connect to remoteUrl\namedInstance in 20000ms the error from your post was Failed to connect to 12.658.625.24\ABCDE,5210 in 15000ms
The package itself should support using server\\instance, I can look at adding the instance name option to our node but I am not sure when that would be or if it would actually solve the issue given that it should be currently working.
It may be different but the package document it as a supported option and I know that WSL can sometimes be a bit odd when making network some network connections, That is what surprised me about the code working when ran from inside WSL.
I will see if I can find some time to make this change and get it tested but I am not sure on what the timelines would be for that.
Hi @jmta - true, I did, but was a connectivity issue on our side in the end. Instance named was not needed and connection is working fine, despite the fact that the instance is SQL Express.
In the n8n SQL credentials, I left domain blank, specified the IP address of the server (not the server name) and the unique port this particular server was using (65113) and all worked once my Ops guys had sorted the firewall issue !
Looks like there is a workaround for you in this case …
