Connect to local SQL Server with Sequelize

Question

I recently set up a Local SQL Server Db for development of my node app. I'm having trouble trying to connect to it from my node app.

I was able to connect with sqlcmd using the command:

sqlcmd -S <hostname>\<instanceName>

I'm using the Sequelize cli in an attempt to run a migration on my development database.

my config.json is as follows:

{
  "development": {
    "username": "<my windows domain username>",
    "password": "<my windows domain password>",
    "database": "development",
    "host": "127.0.0.1",
    "dialect": "mssql",
    "dialectOptions": {
      "instanceName": "LocalServer",
      "domain": "<my company's domain>"
    }
  }
} 

I changed the name from EXPRESS to LOCALSERVER when I set it up (don't ask me why). I also set up it up to login with Windows authentication and SQL SERVER logins. I've gotten a variety of errors with the various configs I've tried, most recently:

ERROR: Failed to connect to 127.0.0.1:undefined in 15000ms

I think I may not be understanding what values from SQL Server I should be passing to Sequelize. Where can I find the logs and view the actual connection string being used?

Answer 1

Hi in my case by following this steps it has worked fine for me:

1 installing tedious -> npm install tedious

2 then in DB utility node.js file ->

const Sequelize = require('sequelize').Sequelize;

sequelize = new Sequelize('DB_NAME', 'username', 'password', { host: 'localhost', dialect: 'mssql', dialectOptions: { instanceName: 'MSSQLSERVER' } });

module.exports = sequelize;

Answer 2

I ended up having to use sequelize-msnodesqlv8 since I am using sqlserver, that is the only way to authenticate with windows. My final connection config object looked like this:

 {
 "development": {
    "dialect": "mssql",
    "dialectModulePath": "sequelize-msnodesqlv8",
    "dialectOptions": {
        "driver": "SQL Server Native Client 11.0",
        "trustedConnection": "true"
    },
    "username": "<my windows domain username>",
    "password": "<my windows domain password>",
    "database": "development",
    "host": "127.0.0.1",
    "port": 1433,
    "logging": "true"
  }
} 

Answer 3

Try adding "port": "1433", to your configuration. The undefined in your output is where the port should be, and I don't see it in your config.

I believe that the default port for MsSql is 1443.

If that doesn't work for you, create a new script with something like the following:

const Sequelize = require('sequelize')
const sequelize = new Sequelize({
    dialect: 'mssql',
    database: 'your-database-name',
    username: 'your-username',
    host: 'localhost',
    port: '1433',
    password: 'your-password',
    logging: true,
})

Sequelize should give you more debugging info when run this way.

Answer 4

I was also running into this and found that my SQL Server Browser service was stopped. Once I started the service from SQL Server Configuration Manager I was able to connect.

It is documented in the Requirements in the GitHub project

Please make sure MS SQL Server TCP/IP connection is enabled and Server Browsing is up and running.

https://github.com/ErenYatkin/sequelizeJS-mssql

Answer 5

if someone is still struggling with this, in my case, I solved it by configuring my local SQL Server Express to be accessible via network

(see link to see how https://www.c-sharpcorner.com/article/configuring-sql-server-2016-express-on-lan-for-c-sharp-connection-string/)

And then configured sequelize with

host: 'localhost', port: '1433',

Answer 6

If someone is trying to connect sequelize with their mssql domain account, here's the code

const sequelize = new Sequelize('database', null, null, {
  dialect: 'mssql',
  dialectOptions: {
    authentication: {
      type: 'ntlm',
      options: {
        domain: 'yourDomain',
        userName: 'username',
        password: 'password'
      }
    },
    options: {
      instanceName: 'SQLEXPRESS'
    }
  }
})

Here is the documentation link https://sequelize.org/master/manual/dialect-specific-things.html

If this doesn't work, try changing instanceName value to 'MSSQLSERVER'.