5

Having completed the SQL Server installer, the given connection string is Server=localhost\MSSQLSERVER01;Database=master;Trusted_Connection=True;, which seems like a strange format, and if I try to connect to the db with sequelize using that connection string:

var sequelize = new Sequelize(process.env.DB_STRING);

I get the error:

TypeError: Cannot read property 'replace' of null

at new Sequelize (C:\Users\George\Source\Repos\TestProj\node_modules\sequelize\lib\sequelize.js:132:40) at Object. (C:\Users\George\Source\Repos\TestProj\models\index.js:13:21) at Module._compile (module.js:570:32)

Vojtěch Dohnal
  • 7,867
  • 3
  • 43
  • 105
George Edwards
  • 8,979
  • 20
  • 78
  • 161

1 Answers1

5

Based on this article you should install sequelize-msnodesqlv8:

var sequelize = new Sequelize({
  dialect: 'mssql',
  dialectModulePath: 'sequelize-msnodesqlv8',
  dialectOptions: {
    instanceName: 'MSSQLSERVER01',
    trustedConnection: true
  },
  host: 'localhost',
  database: 'master'
});

or perhaps better:

var sequelize = new Sequelize({
  dialect: 'mssql',
  dialectModulePath: 'sequelize-msnodesqlv8',
  dialectOptions: {
    connectionString: 'Server=localhost\MSSQLSERVER01;Database=master; Trusted_Connection=yes;'
  },
});

But you should not leave default database as Master. Use your database name instead.

Mark this:

There are many node mssql clients and sequelize defaults to using tedious, but being pure javascript,tedious lacks support for integrated security. msnodesqlv8 is a client that interfaces with a native odbc library. This allows integrated security to be used. It does require additional binaries to deploy, but fortunately, msnodesqlv8 is distributed with binaries for the most common architectures

You are using integrated security, so you need to deal with that problem.

See also this question.

Vojtěch Dohnal
  • 7,867
  • 3
  • 43
  • 105
  • Thanks! Hmm I am getting issue `Unhandled rejection SequelizeConnectionError: [Microsoft][SQL Server Native Client 11.0]Invalid value specified for connection string attribute 'Trusted_Connection'` with your second snippet? – George Edwards Aug 27 '17 at 10:22
  • @GeorgeEdwards Try Integrated Security=SSPI; instead of Trusted_Connection=True;. Or Trusted_Connection=yes; – Vojtěch Dohnal Aug 27 '17 at 10:51
  • Hmm, now I just get `Unhandled rejection SequelizeConnectionError: [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53]` – George Edwards Aug 27 '17 at 15:21
  • Can you connect to `localhost\MSSQLSERVER01` using SSMS and Windows Authentication? You should verify that, looks like it does not allow you to connect. [Check this](https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-errors/). – Vojtěch Dohnal Aug 27 '17 at 15:25
  • @GeorgeEdwards But this is another problem which is not really connected with your original question. – Vojtěch Dohnal Aug 27 '17 at 16:19
  • I can connect fine in ssms, but not with sequelize? – George Edwards Aug 27 '17 at 19:54
  • @GeorgeEdwards Your web server probably running under system account. Either add that account to SQL logins or use sql authentication. – Vojtěch Dohnal Aug 27 '17 at 22:04
  • If I do a `whoami` in command prompt, I get `surface\george` which if I run `CREATE LOGIN [surface\george] FROM WINDOWS;` in my db, I get the error, `The server principal 'surface\george' already exists.` – George Edwards Aug 29 '17 at 09:26
  • The problém is not with your user account, but with the account you run your program, a web server I assume. – Vojtěch Dohnal Aug 29 '17 at 09:42
  • Oh I see, I am using an express on nodejs, running sequelize. How would I find out the account that is running with? – George Edwards Aug 29 '17 at 09:44
  • No idea, depends on how do you launch it. Check `services.msc` if it's there. – Vojtěch Dohnal Aug 29 '17 at 12:04
  • But more usual is to use SQL authentication for a web server, not integrated windows authentication. – Vojtěch Dohnal Aug 29 '17 at 14:50
  • This for my development, I use SQL auth for my remote (and prod) enviroments. See [this](https://stackoverflow.com/questions/45940406/logging-into-local-sql-server-instance-with-windows-auth?noredirect=1#comment78847004_45940406) question - we think maybe sequelize doesn't support this? – George Edwards Aug 30 '17 at 12:47
  • sequelize-msnodesqlv8 should supprt SQL connection with integrated authentication. Standart Sequelize does not support it. – Vojtěch Dohnal Aug 30 '17 at 13:15
  • Yes, but I can't seem to get either to support it – George Edwards Aug 31 '17 at 09:16