msnodesqlv8 implementation using TypeOrm #260

annumsin · 2022-08-30T05:20:14Z

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

TimelordUK · 2022-08-31T14:18:31Z

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 · 2022-08-31T14:21:09Z

Is this related

typeorm/typeorm#8264

TimelordUK · 2022-08-31T14:22:25Z

Is this another query from you

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

TimelordUK · 2022-08-31T14:47:26Z

typeorm/typeorm#8063

annumsin · 2022-08-31T17:19:39Z

Is this related

typeorm/typeorm#8264

not helpful solution mentioned

annumsin · 2022-08-31T17:19:56Z

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 · 2022-08-31T17:23:10Z

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',
}

TimelordUK · 2022-08-31T20:31:17Z

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 · 2022-08-31T20:35:40Z

use string such as

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

TimelordUK · 2022-08-31T20:37:18Z

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 · 2022-08-31T21:41:04Z

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 {}

annumsin · 2022-09-01T01:43:25Z

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'

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.

TimelordUK · 2022-09-01T20:35:39Z

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

[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

annumsin · 2022-09-03T08:13:56Z

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 #260 (comment)
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.

TimelordUK · 2022-09-03T09:38:58Z

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

annumsin · 2022-09-04T01:42:27Z

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

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 -

TimelordUK · 2022-09-04T08:57:18Z

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 · 2022-09-04T12:44:57Z

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

and i connect fine

annumsin · 2022-09-06T12:36:36Z

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

and i connect fine

Did you handle any other config like I mentioned here #260 (comment) maybe I am missing
although I have started the debugging but thought to double check with you.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

msnodesqlv8 implementation using TypeOrm #260

msnodesqlv8 implementation using TypeOrm #260

annumsin commented Aug 30, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

annumsin commented Aug 31, 2022

annumsin commented Aug 31, 2022

annumsin commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

annumsin commented Sep 1, 2022

TimelordUK commented Sep 1, 2022

annumsin commented Sep 3, 2022

TimelordUK commented Sep 3, 2022 •

edited

Loading

annumsin commented Sep 4, 2022 •

edited

Loading

TimelordUK commented Sep 4, 2022

TimelordUK commented Sep 4, 2022

annumsin commented Sep 6, 2022

msnodesqlv8 implementation using TypeOrm #260

msnodesqlv8 implementation using TypeOrm #260

Comments

annumsin commented Aug 30, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

annumsin commented Aug 31, 2022

annumsin commented Aug 31, 2022

annumsin commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

TimelordUK commented Aug 31, 2022

annumsin commented Sep 1, 2022

TimelordUK commented Sep 1, 2022

annumsin commented Sep 3, 2022

TimelordUK commented Sep 3, 2022 • edited Loading

annumsin commented Sep 4, 2022 • edited Loading

TimelordUK commented Sep 4, 2022

TimelordUK commented Sep 4, 2022

annumsin commented Sep 6, 2022

TimelordUK commented Sep 3, 2022 •

edited

Loading

annumsin commented Sep 4, 2022 •

edited

Loading