Node-mssql: Fail : connection with mssql server which run in remote windows server using windows authentication mode

[mafais]

Im try to make connection with mssql server which run in remote windows server using windows authentication mode.
But it give me connection fail error.
How to make the windows authentication for remote sql server ?

[tracker1]

You still need to set the username+password, it won't actually use the windows credentials for the run-time user. You may need to set "domain\\user" if you're running under a windows domain (double \ is to escape the character.

[mafais]

@tracker1 Thanks for your reply,
But i have used the exact way that you mention above,Further details i have added the code.

var config = {
        user: 'CSL\\sccmadmin', //appuser
        password: 'xxxx1234xx', //  
        server: '172.25.104.213', //localhost       
           port : 1433 ,
        database: 'brandix_etl',  //test
        encrypt: true,
        debug: true 

};
sql.connect(config,function(err){   
    if(err)
    {
        console.log(err)
    }
    else
        console.log('connected.....')
});

Error Details are Below.

Apps Running on port 1212 { [ConnectionError: Login failed for user 'CSL\sccmadmin'.] name: 'ConnectionError', message: 'Login failed for user \'CSL\\sccmadmin\'.', code: 'ELOGIN' }

And sql server Event Viewer showing

Login failed for user 'CSL\sccmadmin'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.10.100.74]

But SQL sever can logged using SCL\sccmadmin in windows authentication mode

[mafais]

I found the solution just used like below code.

sql.connect("Data Source=172.25.x.x,1433;User Id=CSLx\\Name;Password=xxxxxx1234;Initial Catalog=giveTHedataabseNamel;Integrated Security=True",function(err){ }

[ppsimatikas]

If you need to use windows integrated security without providing username and password you should use the driver "msnodesqlv8" as mentioned here :
https://github.com/patriksimek/node-mssql#microsoft--contributors-node-v8-driver-for-nodejs-for-sql-server

But in order to use this driver you will need to have the SQL Server Native client installed in your machine, else you are going to receive this error message :
{ ConnectionError: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
as mentioned here : http://stackoverflow.com/a/42119634/4824461

Instructions bellow :

Installation instructions can be found under : https://www.microsoft.com/en-us/download/details.aspx?id=29065 the section : "MICROSOFT SQL SERVER CONNECTIVITY FEATURE PACK COMPONENTS"

To check if the client was properly installed follow the steps bellow :

1. Go to "Start"
2. Click on "Control Panel"
3. Click on "Administrative Tools"
4. Double click on "Data Sources (ODBC)"
5. Click on the "Drivers" tab and make sure there is an item in the
   list with name "SQL Server Native Client 11.0"

[patriksimek]

Thank you posting this guide, I'm closing this as it seems to be resolved.

[JedatKinports]

Thanks @mafais your solution worked for me as well.

[aaroncalderon]

@JedatKinports Would it be possible to share a sample config?

I'm struggling with my config.json. I keep getting an error. It seems I am not able to tell the driver (msnodesqlv8) that we are using __trusted connections__. I have tryed a few variations with no success.

sample config.json

The below configuration will

{
        "host": "dbHost",
        "database": "dbName",
        "driver": "msnodesqlv8",
        "dialectOptions": {
            "connectionString": "Driver={SQL Server Native Client 11.0};Server=dbHost;Database=dbName;Trusted_Connection=yes;",
            "trustedConections": true
        }
}

Error

{ ConnectionError: [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ''.
    at c:\dev\p\poc-sql-ts\node_modules\mssql\lib\msnodesqlv8.js:222:25
    at Immediate.<anonymous> (c:\dev\p\poc-sql-ts\node_modules\msnodesqlv8\lib\ConnectionWrapper.js:336:11)
    at runCallback (timers.js:574:20)
    at tryOnImmediate (timers.js:554:5)
    at processImmediate [as _immediateCallback] (timers.js:533:5)
  name: 'ConnectionError',
  message: '[Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user \'\'.',
  code: 18456 }

[aaroncalderon]

@JedatKinports I think got it...

{
        "host": "dbHost",
        "database": "dbName",
        "driver": "msnodesqlv8",
        "connectionString": "Driver={SQL Server Native Client 11.0};Server=dbHost;Database=dbName;Trusted_Connection=yes;",
        "dialectOptions": {
            "trustedConections": true
        }
}

Seems like __patriksimek__ shared a proper configuration without a "connectionString", I share a _json_ formatted snippet below:

{
    "driver": "msnodesqlv8",
    "server": "db3dev",
    "database": "EForms",
    "options": {
        "trustedConnection": true,
        "useUTC": true
    }
}

[acoroian]

what if you are on a linux server. msnodesqlv8 does not work. any other integration options? Azure has a token