node-sqlserver-v8 icon indicating copy to clipboard operation
node-sqlserver-v8 copied to clipboard

Published 20 hours ago • verified publisher icon TimelordUK

msnodesqlv8 implementation using TypeOrm

Open annumsin opened this issue 3 years ago • 19 comments

I have to implement Window Authentication in Nestjs + mssql which uses TypeOrm so after lots of findings msnodesqlv8 driver was the most recommended but I don't find enough information to do so from the documentation so please advice

Issue I am facing

I found this snippet posted by people but it was not working

@module({ imports: [ ConfigModule.forRoot(), TypeOrmModule.forRoot({ type: 'mssql', port: Number.parseInt(process.env.DB_PORT), database: process.env.DB_NAME, extra: { trustedConnection: true, driver: require('mssql/msnodesqlv8'), server: 'W70100\SQLEXPRESS01', options: { trustedConnection: true, } }, }) })

I get this error - ConnectionError: Login failed for user ' '. Possibly, if someone can correct this snippet or guide why this error might be coming

Although, below snippet works fine and I can get the result from DB using Window Auth

const mypool = { driver: 'msnodesqlv8', server: 'W70100\SQLEXPRESS01', database: 'Test', port: 1433, options: { trustedConnection: true, useUTC: true }, };

const conn = new mssql.ConnectionPool(mypool).connect().then(pool => { var request = new mssql.Request(mypool); request.query('select * from [Test].[tin].[Emp]', (err, result) => { if (err) console.log('Database Connection Failed! ON RESULT: ',err); console.log(result) }) return pool; }).catch(err => console.log('Database Connection Failed! Bad Config: ', err))

Additional Context I have also made sure following points are taken care -

  • Enter wrong server details by mistake. Make sure to enter correct SQL Server instance name while making database connection.
  • Try to connect using ip address and port number instead of putting server name in connection string.
  • SQL Server Instance is not accessible due to firewall or any reason. Check firewall details. Your port 1433and UDP port (1434) of SQL - Server browser should be enabled. These ports might be blocked.
  • SQL Server service is not running on SQL Server. You can try to start or restart SQL Server services
  • Make sure to enable TCP/IP and Named Pipes, which you can check from SQL Server Configuration Manager.
  • Remote connection must be enabled to connect to databases remotely. Right click on SQL Server instance name in SSMS and choose Properties. You will get Server properties window. Click on Connections from left side pane and tick the check box on Allow remote connections to this server option from right side pane.
  • SQL browser service should not be stopped if your security policy allows it.

Software/Package versions

  • NodeJS: 14.17.3
  • mssql: 6.3.1
  • SQL Server: 18
  • Operating System: Window 10
  • TypeORM : 0.2.31
  • msnodesqlv8: 2.6.0

so, please if you can correct or provide the right snippet or even example or guidance also helps

annumsin avatar Aug 30 '22 05:08 annumsin

Hello thanks for taking interest in this library. I will try and checkout TypeORM and get an example working. I will report back shortly I have no experience with this library we do already support sequelize natively.

TimelordUK avatar Aug 31 '22 14:08 TimelordUK

Is this related

https://github.com/typeorm/typeorm/issues/8264

TimelordUK avatar Aug 31 '22 14:08 TimelordUK

Is this another query from you

https://stackoverflow.com/questions/73518917/how-to-connect-typeorm-with-msnodesqlv8-using-nestjs-window-authentication

TimelordUK avatar Aug 31 '22 14:08 TimelordUK

https://github.com/typeorm/typeorm/issues/8063

TimelordUK avatar Aug 31 '22 14:08 TimelordUK

Is this related

typeorm/typeorm#8264

not helpful solution mentioned

annumsin avatar Aug 31 '22 17:08 annumsin

Is this another query from you

https://stackoverflow.com/questions/73518917/how-to-connect-typeorm-with-msnodesqlv8-using-nestjs-window-authentication

I posted this query

annumsin avatar Aug 31 '22 17:08 annumsin

typeorm/typeorm#8063

They have not provided a working solution so not helpful basically what code should reside inside @module({ imports: [ ConfigModule.forRoot(), TypeOrmModule.forRoot({ ........???? }) because this is where the issue is coming, under 'extra' object they don't accept object: { driver: 'msnodesqlv8', server: 'W70100\SQLEXPRESS01', database: 'Test', }

annumsin avatar Aug 31 '22 17:08 annumsin

I can make some progress I do not think we can choose msnodesqlv8 from config

if I use as below but change node_modules\msssql\index.js

but it does connect to database with trusted login. I would suggest proving the driver works OK as this is a smple change to begin development.

// this needs to be further inestigated
module.exports = require('./lib/msnodesqlv8')

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { ConfigModule } from '@nestjs/config'
import { TypeOrmModule } from '@nestjs/typeorm'

@Module({
  'imports': [
    ConfigModule.forRoot(),
    TypeOrmModule.forRoot({
      type: 'mssql',
      extra: {
        server: 'localhost',
        connectionString: 'Driver={ODBC Driver 18 for SQL Server}; Server=DESKTOP-VIUCH;UID=linux; PWD=x; Database=node;TrustServerCertificate=yes;',
      }
    })],
    controllers: [AppController],
    providers: [AppService],
})
export class AppModule {}


[21:27:47] Starting compilation in watch mode...

[21:27:52] Found 0 errors. Watching for file changes.

[Nest] 15212  - 31/08/2022, 21:27:55     LOG [NestFactory] Starting Nest application...
[Nest] 15212  - 31/08/2022, 21:27:55     LOG [InstanceLoader] TypeOrmModule dependencies initialized +208ms
[Nest] 15212  - 31/08/2022, 21:27:55     LOG [InstanceLoader] ConfigHostModule dependencies initialized +1ms
[Nest] 15212  - 31/08/2022, 21:27:55     LOG [InstanceLoader] AppModule dependencies initialized +0ms
[Nest] 15212  - 31/08/2022, 21:27:55     LOG [InstanceLoader] ConfigModule dependencies initialized +1ms
[Nest] 15212  - 31/08/2022, 21:27:55     LOG [InstanceLoader] TypeOrmCoreModule dependencies initialized +270ms
[Nest] 15212  - 31/08/2022, 21:27:55     LOG [RoutesResolver] AppController {/}: +15ms
[Nest] 15212  - 31/08/2022, 21:27:55     LOG [RouterExplorer] Mapped {/, GET} route +3ms
[Nest] 15212  - 31/08/2022, 21:27:55     LOG [NestApplication] Nest application successfully started +4ms

TimelordUK avatar Aug 31 '22 20:08 TimelordUK

use string such as

Driver={ODBC Driver 17 for SQL Server};Server=(localdb)\node;Database=scratch;Trusted_Connection=yes;

TimelordUK avatar Aug 31 '22 20:08 TimelordUK

if you do not have v17 installed it can be downloaded from MS or try

"Driver={SQL Server Native Client 11.0}; Server=(localdb)\node;Database=scratch;Trusted_Connection=yes;

TimelordUK avatar Aug 31 '22 20:08 TimelordUK

this seems to work

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { ConfigModule } from '@nestjs/config'
import { TypeOrmModule } from '@nestjs/typeorm'

@Module({
  'imports': [
    ConfigModule.forRoot(),
    TypeOrmModule.forRoot({
      type: 'mssql',
      driver:require('mssql/msnodesqlv8'),
      extra: {
        driver: 'msnodesqlv8',
        server: 'localhost',
        connectionString: 'Driver={ODBC Driver 18 for SQL Server}; Server=DESKTOP-VIUCH90;UID=linux; PWD=x; Database=node;TrustServerCertificate=yes;',
      }
    })],
    controllers: [AppController],
    providers: [AppService],
})
export class AppModule {}

TimelordUK avatar Aug 31 '22 21:08 TimelordUK

this seems to work

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { ConfigModule } from '@nestjs/config'
import { TypeOrmModule } from '@nestjs/typeorm'

@Module({
  'imports': [
    ConfigModule.forRoot(),
    TypeOrmModule.forRoot({
      type: 'mssql',
      driver:require('mssql/msnodesqlv8'),
      extra: {
        driver: 'msnodesqlv8',
        server: 'localhost',
        connectionString: 'Driver={ODBC Driver 18 for SQL Server}; Server=DESKTOP-VIUCH90;UID=linux; PWD=x; Database=node;TrustServerCertificate=yes;',
      }
    })],
    controllers: [AppController],
    providers: [AppService],
})
export class AppModule {}

I really appreciate your response on my queries but here is a thing

I tried to implement above solution as per my configuration as it's window authentication required using sql-server so no uid/pwd needs to be passed

import { Module } from '@nestjs/common'; import { TypeOrmModule } from '@nestjs/typeorm'; import { ConfigModule } from '@nestjs/config'; import { ScheduleModule } from '@nestjs/schedule';

@Module({
  imports: [
    ConfigModule.forRoot(),
    ScheduleModule.forRoot(),
    TypeOrmModule.forRoot({
      type: 'mssql',
      driver: require('mssql/msnodesqlv8'),
      extra: {
        driver: 'msnodesqlv8',
        server: 'W70100\\SQLEXPRESS01',
        connectionString: 'Driver={SQL Server Native Client 11.0};Server=W70100\\SQLEXPRESS01;Database=Test;TrustServerCertificate=yes;',
      },
      port: Number.parseInt(process.env.DB_PORT),

})

here are couple of issues

  • at line driver: require('mssql/msnodesqlv8') I am getting error Argument of type '{ type: "mssql"; driver: any; extra: { driver: string; server: string; connectionString: string; }; port: number; }' is not assignable to parameter of type 'TypeOrmModuleOptions'. Object literal may only specify known properties, and 'driver' does not exist in type '{ retryAttempts?: number; retryDelay?: number; toRetry?: (err: any) => boolean; autoLoadEntities?: boolean; keepConnectionAlive?: boolean; verboseRetryLog?: boolean; } & Partial<SqlServerConnectionOptions>'

    is it version issue because currently I am using "@nestjs/common": "^7.0.0", "@nestjs/config": "^0.6.3", "@nestjs/core": "^7.0.0", "@nestjs/platform-express": "^7.0.0", "@nestjs/schedule": "^0.4.3", "@nestjs/serve-static": "^2.2.2", "@nestjs/swagger": "^4.7.13", "@nestjs/typeorm": "^7.1.5", "typeorm": "^0.2.31", "msnodesqlv8": "^2.6.0", "mssql": "^6.3.1", "typescript": "^3.7.4"

Please let me know is this the cause? or maybe the version you are using which supports driver key. Please let me know in that case.

  • On the other hand if I implement this solution

    @Module({ imports: [ ConfigModule.forRoot(), ScheduleModule.forRoot(), TypeOrmModule.forRoot({ type: 'mssql', extra: { driver: 'msnodesqlv8', server: 'W70100\SQLEXPRESS01', connectionString: 'Driver={SQL Server Native Client 11.0};Server=W70100\SQLEXPRESS01;Database=Test;TrustServerCertificate=yes;', }, port: Number.parseInt(process.env.DB_PORT) })

I am getting error ConnectionError: Login failed for user ''.

In case if you have done any other configuration that I might have missed or is missing from below list then please advice

  • Enter wrong server details by mistake. Make sure to enter correct SQL Server instance name while making database connection.
  • Try to connect using ip address and port number instead of putting server name in connection string.
  • SQL Server Instance is not accessible due to firewall or any reason. Check firewall details. Your port 1433and UDP port (1434) of SQL - Server browser should be enabled. These ports might be blocked.
  • SQL Server service is not running on SQL Server. You can try to start or restart SQL Server services
  • Make sure to enable TCP/IP and Named Pipes, which you can check from SQL Server Configuration Manager.
  • Remote connection must be enabled to connect to databases remotely. Right click on SQL Server instance name in SSMS and choose Properties. You will get Server properties window. Click on Connections from left side pane and tick the check box on Allow remote connections to this server option from right side pane.
  • SQL browser service should not be stopped if your security policy allows it.

annumsin avatar Sep 01 '22 01:09 annumsin

can you checkout https://github.com/TimelordUK/nest-torm-msnodesqlv8 run npm install change connection details

I have checked in debugger it is arriving in msnodesqkv8 connection

you are probably not even going into msnodesqlv8

image

[21:31:57] Starting compilation in watch mode...

[21:32:01] Found 0 errors. Watching for file changes.

[Nest] 24356  - 01/09/2022, 21:32:04     LOG [NestFactory] Starting Nest application...
[Nest] 24356  - 01/09/2022, 21:32:04     LOG [InstanceLoader] TypeOrmModule dependencies initialized +74ms
[Nest] 24356  - 01/09/2022, 21:32:04     LOG [InstanceLoader] ConfigHostModule dependencies initialized +1ms
[Nest] 24356  - 01/09/2022, 21:32:04     LOG [InstanceLoader] AppModule dependencies initialized +1ms
[Nest] 24356  - 01/09/2022, 21:32:04     LOG [InstanceLoader] ConfigModule dependencies initialized +1ms
[Nest] 24356  - 01/09/2022, 21:32:05     LOG [InstanceLoader] TypeOrmCoreModule dependencies initialized +353ms
[Nest] 24356  - 01/09/2022, 21:32:05     LOG [RoutesResolver] AppController {/}: +15ms
[Nest] 24356  - 01/09/2022, 21:32:05     LOG [RouterExplorer] Mapped {/, GET} route +3ms
[Nest] 24356  - 01/09/2022, 21:32:05     LOG [NestApplication] Nest application successfully started +5ms

TimelordUK avatar Sep 01 '22 20:09 TimelordUK

https://github.com/TimelordUK/nest-torm-msnodesqlv8

Thank you for creating this repo but just let you know on this line https://github.com/TimelordUK/nest-torm-msnodesqlv8/blob/master/src/app.module.ts#L16 where you are passing UID and PWD, in window authentication no username/password needs to be passed in the connection string. Thats what I tried on address on https://github.com/TimelordUK/node-sqlserver-v8/issues/260#issuecomment-1233634612 Can you help me to figure our connection string code where no UID/PWD is supposed to be passed to connection string basically what we do using window authentication in Sql-Server (Window Machine) where instance and host detail is enough to connect as in SQL Server authentication we pass UID and PWD. Maybe in case using window auth we have to pass uid/pwd then what are these details that I need to input.

annumsin avatar Sep 03 '22 08:09 annumsin

this is wrong

connectionString: 'Driver={SQL Server Native Client 11.0};Server=W70100\SQLEXPRESS01;Database=Test;TrustServerCertificate=yes;',

not TrustServerCertificate=yes

you need Trusted_Connection=yes

'Driver={SQL Server Native Client 11.0}; Server=W70100\SQLEXPRESS01;Database=Test;Trusted_Connection=yes;'

SQL Server Native Client 11.0 is a v old driver - ideally I would use a later version but msnodesqlv8 should work fine with it

TimelordUK avatar Sep 03 '22 09:09 TimelordUK

this is wrong

connectionString: 'Driver={SQL Server Native Client 11.0};Server=W70100\SQLEXPRESS01;Database=Test;TrustServerCertificate=yes;',

not TrustServerCertificate=yes

you need Trusted_Connection=yes

'Driver={SQL Server Native Client 11.0}; Server=W70100\SQLEXPRESS01;Database=Test;Trusted_Connection=yes;'

SQL Server Native Client 11.0 is a v old driver - ideally I would use a later version but msnodesqlv8 should work fine with it

This the updated code now -

@Module({ imports: [ ConfigModule.forRoot(), ScheduleModule.forRoot(), TypeOrmModule.forRoot({ type: 'mssql', extra: { driver: 'msnodesqlv8', server: 'W70100\SQLEXPRESS01', connectionString: 'Driver={SQL Server Native Client 11.0};Server=W70100\SQLEXPRESS01;Database=Test;Trusted_Connection=yes;', }, port: Number.parseInt(process.env.DB_PORT), connectionTimeout: 30000, requestTimeout: 30000,

}) })

I am getting error ConnectionError: Login failed for user ''.

this error while SQL Server Browser is running If I stop SQL Server Browser then I am getting error image

Can you help me to debug what might be causing these issue's.

Not sure in case any config is missing - here is the config of my window machine - image

annumsin avatar Sep 04 '22 01:09 annumsin

W70100\SQLEXPRESS01

The server name actually looks wrong. This should be name of server you would ping i,e, the host name.

W70100

why don’t you put a breakpoint in a debugger as I have done in my example above and connect using just mssql library which you say works. Look to see what the connection string is handed to msnodesqlv8 And then copy that same string above.

I do not think i can help much further as these issues have nothing to do with msnodesqlv8 as a library.

connectionString: 'Driver={SQL Server Native Client 11.0};Server=W70100;Database=Test;Trusted_Connection=yes;', },

TimelordUK avatar Sep 04 '22 08:09 TimelordUK

to clarify using trusted connection this woks for me using test project

    connectionString: 'Driver={ODBC Driver 18 for SQL Server}; Server=DESKTOP-VIUCH90;Trusted_Connection=yes; Database=node;TrustServerCertificate=yes;',

using sql server management studio

image

and i connect fine

image

TimelordUK avatar Sep 04 '22 12:09 TimelordUK

to clarify using trusted connection this woks for me using test project

    connectionString: 'Driver={ODBC Driver 18 for SQL Server}; Server=DESKTOP-VIUCH90;Trusted_Connection=yes; Database=node;TrustServerCertificate=yes;',

using sql server management studio

image

and i connect fine

image

Did you handle any other config like I mentioned here https://github.com/TimelordUK/node-sqlserver-v8/issues/260#issuecomment-1236230354 maybe I am missing although I have started the debugging but thought to double check with you.

annumsin avatar Sep 06 '22 12:09 annumsin