2

I have connection string in node js for SQL Server as following: 

 var webconfig = {
     user: 'sa',
     password: 'aman',
     server: 'Aman\AMAN', 
     database: 'Demo',
     options: {
         encrypt: false // Use this if you're on Windows Azure 
     }
 }

it is showing connection close error.

But if I am using network server like 192.5.5.62 etc then its working fine. I think problem in 'Aman\AMAN', because server name not accepting \ backslash (I think).

The following connection is working for me... 

var webconfig = {
         user: 'sa',
         password: 'aman',
         server: '192.5.5.62', 
         database: 'Demo',
         options: {
             encrypt: false // Use this if you're on Windows Azure 
         }
     }

If my guess of \ (backslash) is true, then please suggest me how to fix it with this server name or window authentication etc.

Help will be appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amandeep Singh
  • 372
  • 6
  • 20
  • 1
    Backslashes need to be escaped in JS strings. Try `Aman\\AMAN`. Possible duplicate of [JavaScript backslash (\) in variables is causing an error](https://stackoverflow.com/questions/3903488/javascript-backslash-in-variables-is-causing-an-error). – Tyler Roper Jul 20 '17 at 18:05
  • What sql server module are you using? mssql (https://www.npmjs.com/package/mssql#connect-callback) seems to accept named instances in the manner that you're trying to do it. – Paul Jul 20 '17 at 18:05
  • @Santi I have tried this already not working – Amandeep Singh Jul 20 '17 at 18:09
  • @Paul I'm not using like this.. i am passing this like var connection = new sql.Connection(webconfig, function(err) { //code here }) – Amandeep Singh Jul 20 '17 at 18:11

3 Answers3

1

From your comment, it seems likely that you're using the default driver, which is Tedious. Looking at their docs it seems that they have separated out the hostname and instance name, so you'd want to change your code to be like this:

var webconfig = {
     user: 'sa',
     password: 'aman',
     server: 'Aman', 
     database: 'Demo',
     options: {
         encrypt: false, // Use this if you're on Windows Azure 
         instanceName: 'AMAN'
     }
 }
Paul
  • 35,689
  • 11
  • 93
  • 122
  • Can you provide more details? there are other things that come back with that error other than just that string. Also, make sure you're actually able to connect to the SQL server instance on the default port, etc; this error might be a result of an eager firewall. – Paul Jul 28 '17 at 12:40
1

First of all I would assume here that you are using tedious module (by looking at the comment of options.encrypt). I wanted to confirm this first with comment but I lack credits to comment. I really wanted to help you because I faced the exact same issue sometime back, so here it goes.

I'll divide your questions in two parts in the context of Nodejs Tedious module.

  1. Support for Named Instances
  2. Support for Windows Authentication

Support for Named Instances

By default tedious uses server and options.port to connect. options.port is the default setting with a default value of 1433. This is the reason configuration mentioned below worked.

var webconfig = {
     user: 'sa',
     password: 'aman',
     server: '192.5.5.62', 
     database: 'Demo',
     options: {
         encrypt: false // Use this if you're on Windows Azure 
     }
 }

But if you want to use named instances in the connection configuration, then you must use options.instanceName as shown below. See the documentation.

var webconfig = {
     user: 'sa',
     password: 'aman',
     server: 'Aman', //FQDN is preferred, so assuming you can ping this
     database: 'Demo',
     options: {
         instanceName: 'AMAN'
         encrypt: false // Use this if you're on Windows Azure 
     }
 }

In your case, however it isn't required to connect using named instance because you are able to connect using hostname\IP itself.

Also, you could either use options.port or options.instanceName but not both in the configuration at a time.

This brings me to your second question.

Support for Windows Authentication

Tedious module do not support Windows Authentication to connect to SQL Server instances currently. See here. However, there are few bright minds who already are working on it. Look at this open PR #497. Feel free to contribute to the Tedious community.

BONUS TIP: Going forward, instead of guessing the root cause, you could also make use of debug event by tedious module which will route you in the right direction. Make sure to disable it in production.

Please mark this as an answer if it helped you. Cheers!

Naman
  • 51
  • 8
0

Use four backslashes to get one in output

snowflake.createStatement( { sqlText: `insert into log_table (message) values ('\\\\newdata')` } ).execute();

will result in \newdata

Junaid Masood
  • 658
  • 11
  • 20