Can a mobile service server script (schedule) access other Sql Azure databases?

Question

I'm looking to create a scheduled job using a Azure mobile service.

Since the service will end up calling another cloud service (website), I was wondering if the mobile script could access a database the cloud service already does.

I understand you can specify a database to use for the mobile script (I selected free for logging) but can't seem to tell if you can access other databases through the API.

var todoItemsTable = tables.getTable('TodoItems');

Hypothetically...

var todoItemsTable = databases.getDatabase('NonMobileSqlDb').tables.getTable('TodoItems');

I've already checked this question (Can you mix Azure Mobile Services with Azure Cloud Services?) but it doesn't seem to cover scripts talking to databases.

Some background...

The mobile service will (on a schedule) invoke a web service (with authorisation) that performs routine actions. I'd like to lock down this service (without ssl) and one way is to generate a key the service could use that the cloud service could verify. This key would be stored in the database both can access and only be available for a short period of time.

Do take a look at windowsazure.com/en-us/develop/nodejs/how-to-guides/… and tomasz.janczuk.org/2013/06/…. — Gaurav Mantri
Ahh ok so a 'pure' nodejs route creating connection and doing it that way. — Phil Cooper
btw Windows Azure mobile services can now accept working with NPM on it. this link might be helpful understanding how to do so. after all the WIndows Azure mobile service is a solution based on Node Js I think, weblogs.asp.net/scottgu/archive/2013/06/14/… — hhaggan
@hhaggan "You can now call this API from any Mobile Service client application" < good article, thanks. — Phil Cooper

Phil Cooper Phil Cooper · Accepted Answer · 2013-07-25T09:08:09

Yes you can.

You need to connect using the following example (uses Node.js) taken from the how-to guide:

To use the node-sqlserver, you must require it in your application and specify a connection string. The connection string should be the ODBC value returned in the How to: Get SQL Database connection information section of this article. The code should appear similar to the following:

var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 10.0};Server=tcp:{dbservername}.database.windows.net,1433;Database={database};Uid={username};Pwd={password};Encrypt=yes;Connection Timeout=30;";

Queries can be performed by specifying a Transact-SQL statement with the query method. The following code creates an HTTP server and returns data from the ID, Column1, and Column2 rows in the Test table when you view the web page:

var http = require('http')
var port = process.env.port||3000;
http.createServer(function (req, res) {
    sql.query(conn_str, "SELECT * FROM TestTable", function (err, results) {
        if (err) {
            res.writeHead(500, { 'Content-Type': 'text/plain' });
            res.write("Got error :-( " + err);
            res.end("");
            return;
        }
        res.writeHead(200, { 'Content-Type': 'text/plain' });
        for (var i = 0; i < results.length; i++) {
            res.write("ID: " + results[i].ID + " Column1: " + results[i].Column1 + " Column2: " + results[i].Column2);
        }
        res.end("; Done.");
    });
}).listen(port);

Many thanks to @GauravMantri & @hhaggan for their help in getting this far.

Can a mobile service server script (schedule) access other Sql Azure databases?

1 Answers