Can't connect Node.js server to Azure SQL Database

Question

I'm running a simple Node.js server on Heroku. I've set up an Azure SQL database and I'm just trying to establish a connection to it from the server. I'm using tedious.js to connect. As far as I can tell, I'm following the patterns in the docs, but the connection doesn't go through. This is the code I have (altered username and password). For now, the connect function is called upon a GET request to the "/data" page from my browser, but the page never loads and the connection never goes through. Any pointers?

var azure = require("azure-storage");

var Connection = require("tedious").Connection;

var config = {
  Server : "my-db.database.windows",
  username : "XXXXX",
  password : "XXXXX",
  options : {
    port: 1433,
    Database : "my-db",
    connectTimeout : 3000,
  },
};


var connection = new Connection(config);

function connect(request, response) {
  connection.on("connect", function(error) {
    //If no error, then good to go
    console.log("Connected to database! Booyah.");
    executeStatement();

    response.send("Connected to database! Booyah.");
  }, function (info) {
    console.log(info);
  });
}

exports.connect = connect;

I know you intentionally obscured username and password but... the Server uri is missing .net at the end. Not sure if your real code is missing it too - that would be an issue. Also: Did you open the SQL Database firewall to allow for incoming connections from your node app? — David Makogon
– David Makogon, Commented May 10, 2016 at 17:36
The .net was missing in the code - not sure how that happened. I added that. And I've setup a firewall rule for my IP address. Should I be using my machine's IP? Or is there a separate IP I should use from heroku for the server? — cweber105
– cweber105, Commented May 10, 2016 at 17:55
it's whatever IP your traffic is coming from, into SQL Database. I'll post as answer as well. — David Makogon
– David Makogon, Commented May 10, 2016 at 17:57

Community · Accepted Answer · 2017-05-23 11:44:03Z

2

I echo the answers provided by the community. Here is a quick code sample that can help you get started -

var Connection = require('tedious').Connection;
var config = {
    userName: 'yourusername',
    password: 'yourpassword',
    server: 'yourserver.database.windows.net',
    // When you connect to Azure SQL Database, you need these next options.
    options: {encrypt: true, database: 'AdventureWorks'}
};
var connection = new Connection(config);
connection.on('connect', function(err) {
    // If no error, then good to proceed.
    console.log("Connected");
    executeStatement();
    //executeStatement1();

});

var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;

function executeStatement() {
    request = new Request("SELECT TOP 10 Title, FirstName, LastName from SalesLT.Customer;", function(err) {
    if (err) {
        console.log(err);} 
    });
    var result = "";
    request.on('row', function(columns) {
        columns.forEach(function(column) {
          if (column.value === null) {
            console.log('NULL');
          } else {
            result+= column.value + " ";
          }
        });
        console.log(result);
        result ="";
    });

    request.on('done', function(rowCount, more) {
    console.log(rowCount + ' rows returned');
    });
    connection.execSql(request);
}
function executeStatement1() {
    request = new Request("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES (@Name, @Number, @Cost, @Price, CURRENT_TIMESTAMP);", function(err) {
     if (err) {
        console.log(err);} 
    });
    request.addParameter('Name', TYPES.NVarChar,'SQL Server Express 2014');
    request.addParameter('Number', TYPES.NVarChar , 'SQLEXPRESS2014');
    request.addParameter('Cost', TYPES.Int, 11);
    request.addParameter('Price', TYPES.Int,11);
    request.on('row', function(columns) {
        columns.forEach(function(column) {
          if (column.value === null) {
            console.log('NULL');
          } else {
            console.log("Product id of inserted item is " + column.value);
          }
        });
    });     
    connection.execSql(request);
}

About the firewall rule, it depends on where you are running the app. If you are running it on Heroku, you have to add the IP of the Heroku server. Is it a Linux VM? Here is a stack overflow answer that you might want to check out.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered May 10, 2016 at 18:13

meet-bhagdev

2,74820 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

cweber105 Over a year ago

Ok, I think I've narrowed this down a bit. First, it wasn't catching the username properly because my code had "username", instead of the proper "userName". So I fixed that, and now it recognizes my username when I try to make a connection. But the login still fails...the firewall rule is set. This is the error I get in the console, along with the stack trace: "message: 'Login failed for user \'cweber105\'.', code: 'ELOGIN' }"

meet-bhagdev Over a year ago

are you specifying encrypt = true?

cweber105 Over a year ago

Yes, should I not be?

cweber105 Over a year ago

If I don't include encrypt, it has a default value of false, and the body of connection.on() doesn't even execute. If it's true, the body executes, but I get an error about the login failing.

meet-bhagdev Over a year ago

You definitely should be! It might be a good idea to make sure your username+password combo is correct. Did you try GUI tools like SSMS to make sure its working?

|

David Makogon · Accepted Answer · 2016-05-10 17:58:46Z

0

First: the connection string needs to be cultureofthefewpractice.database.windows.net - you're missing .net at the end.

Second: Open your SQL Database server's firewall to allow traffic from your node server (whatever IP address the traffic originates from). SQL Database allows you to specify IP ranges (and multiple ranges).

answered May 10, 2016 at 17:58

David Makogon

71k22 gold badges145 silver badges204 bronze badges

3 Comments

cweber105 Over a year ago

All traffic to the database is coming through the node server on heroku. How can I find out which IP to set up the firewall for?

David Makogon Over a year ago

You'll need to figure out your ip address from heroku - I don't work with heroku, couldn't guide you there. For a test, at least, you can simply open up the full ip range on SQL Database (0.0.0.0-255.255.255.255) just to see if traffic flows through.

cweber105 Over a year ago

Ok. Well, I added firewall rule for all IP's, but still no go. Any other thoughts? Is there a way I can view a log of what's happening? I'm on a Mac, and the console is printing a few things. Gibberish to me: 5/10/16 2:09:46.863 PM SpotlightNetHelper[675]: tcp_connection_tls_session_error_callback_imp 4467 __tcp_connection_tls_session_callback_write_block_invoke.434 error 22 5/10/16 2:09:49.917 PM WindowServer[186]: send_datagram_available_ping: pid 547 failed to act on a ping it dequeued before timing out.

Collectives™ on Stack Overflow

Can't connect Node.js server to Azure SQL Database

2 Answers 2

10 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related