Connect to Azure SQL database is not working (as custom database)

Help
,
1

I’m trying to use a Azure SQL database for custom login data. After selecting the template for SQL Server - and editing the script - the following code is not working (e.g. create script):

image
image786×321 31.4 KB

And here comes the corresponding log file:
image
image1054×438 34.4 KB

It seems the tedious library is the reason, but I’m not sure. And it was not possible for me to find a solution.
Is there a chance to let Auth0 API work together with Azure SQL?

2

It seems by adding a parameter “cryptoCredentialsDetails” the connection works:
image
Some other issues found now, but step by step…

3

No chance, for me it was not possible to get data from Azure SQL database (GetUser scrip). Connection works now, but reading query result not.
Based on SQL Server template the result row is not readable (empty), although row count is 1. Following message comes up:

image
image732×265 36.8 KB

Is anybody here with same environment (simple Azure SQL database, no AD) and a really working GetUser script?
And before questions comes: I do not have defined any hook or rule.

4

Can be closed, works now.

5

What did you change to get it working eventually? I still get in because i’m ‘not allowed to access the server’

6

do we have any solution for this issue

7

It is a long time ago, but for me it works. May be my GetUser script helps:

function getByEmail(email, callback) {
  //this example uses the "tedious" library
  //more info here: http://pekim.github.io/tedious/index.html
  const sqlserver = require('tedious@1.11.0');

  const Connection = sqlserver.Connection;
  const Request = sqlserver.Request;
  const TYPES = sqlserver.TYPES;

  const connection = new Connection({
    userName:  configuration.User,
    password:  configuration.Password,
    server:    configuration.Server,
    options:  {
      database: configuration.Database,
      encrypt: true,
      rowCollectionOnDone: true
    }
  });

  const query = 'SELECT Id, UserName, Email FROM dbo.Users WHERE Email = @Email';

  connection.on('connect', function (err) {
    if (err) return callback(err);

    const request = new Request(query, function (err, rowCount, rows) {
      if (err) return callback(err);
      if (rowCount < 1) return callback(null);
    });

    request.on('row', function (columns) {
      var profile = {
        	user_id: columns[0].value.toString(),
          username: columns[1].value,
          email: columns[2].value
      };
      callback(null, profile);
    });
    request.on('done', function (rowCount, more, rows) {
      callback(null);
    });
    
    request.addParameter('Email', TYPES.NVarChar, email);
    connection.execSql(request);
  });
}

It is also important to open the right ports for communication with Azure SQL database!

1 Like
8

Thanks for the quick reply it’s working fine. i need one more help. what about create. I’m getting the error when I’m calling create event

10:37:16 PM:
 {
"code": 500,
"error": "Script generated an unhandled asynchronous exception.",
"details": "Error: No event 'socketConnect' in state 'SentPrelogin'",
"name": "Error",
"message": "No event 'socketConnect' in state 'SentPrelogin'",
"stack": "Error: No event 'socketConnect' in state 'SentPrelogin'\n at Connection.dispatchEvent (/data/_verquire/_node16/tedious/1.11.0/node_modules/tedious/lib/connection.js:754:26)\n at Connection.socketConnect (/data/_verquire/_node16/tedious/1.11.0/node_modules/tedious/lib/connection.js:777:17)\n at Socket.<anonymous> (/data/_verquire/_node16/tedious/1.11.0/node_modules/tedious/lib/connection.js:3:59)\n at Socket.emit (node:events:539:35)\n at Socket.emit (node:domain:537:15)\n at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1173:10)\n at TCPConnectWrap.callbackTrampoline (node:internal/async_hooks:130:17)"
}
10:37:16 PM:
 finished webtask request
10:37:16 PM:
 Code generated an uncaught exception: Error: No event 'socketConnect' in state 'SentPrelogin'
at Connection.dispatchEvent (/data/_verquire/_node16/tedious/1.11.0/node_modules/tedious/lib/connection.js:754:26)
at Connection.socketConnect (/data/_verquire/_node16/tedious/1.11.0/node_modules/tedious/lib/connection.js:777:17)
at Socket.<anonymous> (/data/_verquire/_node16/tedious/1.11.0/node_modules/tedious/lib/connection.js:3:59)
at Socket.emit (node:events:539:35)
at Socket.emit (node:domain:537:15)
at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1173:10)
at TCPConnectWrap.callbackTrampoline (node:internal/async_hooks:130:17)
9

Here is my create script:

function create(user, callback) {
  //this example uses the "tedious" library
  //more info here: http://pekim.github.io/tedious/index.html
  const bcrypt = require('bcrypt');
  const sqlserver = require('tedious@1.11.0');

  const Connection = sqlserver.Connection;
  const Request = sqlserver.Request;
  const TYPES = sqlserver.TYPES;

  const connection = new Connection({
    userName:  configuration.User,
    password:  configuration.Password,
    server:    configuration.Server,
    options:  {
      database: configuration.Database,
      encrypt: true
    }
  });

  const query = 'INSERT INTO dbo.Users (UserName, Email, Password, Created) VALUES (@UserName, @Email, @Password, getutcdate())';

  connection.on('connect', function (err) {
    if (err) return callback(err);

    const request = new Request(query, function (err, rows) {
      if (err) return callback(err);
      // console.log('rows: ' + rows);
      callback(null);
    });

    bcrypt.hash(user.password, 10, function(err, hash) {
      if (err) return callback(err);
      request.addParameter('UserName', TYPES.NVarChar, user.username);
      request.addParameter('Email', TYPES.NVarChar, user.email);
      request.addParameter('Password', TYPES.NVarChar, hash);
      connection.execSql(request);
    });
  });
}
1 Like
10

Thanks,
let me try that. will get back to you soon

11

it’s Working fine. Sorry to bother you again. could you please put login script too

12

Sure :wink:

function login(authInput, password, callback) {
  //this example uses the "tedious" library
  //more info here: http://pekim.github.io/tedious/index.html
  const bcrypt = require('bcrypt');
  const sqlserver = require('tedious@1.11.0');

  const Connection = sqlserver.Connection;
  const Request = sqlserver.Request;
  const TYPES = sqlserver.TYPES;

  const connection = new Connection({
    userName:  configuration.User,
    password:  configuration.Password,
    server:    configuration.Server,
    options:  {
      database: configuration.Database,
      encrypt: true,
      rowCollectionOnDone: true
    }
  });

  const query = 'SELECT Id, UserName, Email, Password FROM dbo.Users WHERE (Email = @AuthInput OR UserName = @AuthInput)';
  
  connection.on('connect', function (err) {
    if (err) return callback(err);

    var request = new Request(query, function (err, rowCount, rows) {
      if (err || rowCount < 1) return callback(err || new WrongUsernameOrPasswordError(authInput));
    });
    
    request.on('row', function (columns) {
      bcrypt.compare(password, columns[3].value, function (err, isValid) {
        if (err || !isValid) return callback(err || new WrongUsernameOrPasswordError(authInput));

        callback(null, {
          user_id: columns[0].value.toString(),
          nickname: columns[1].value,
          email: columns[2].value
        });
      });
    });
    request.on('done', function (rowCount, more, rows) {
      callback(new WrongUsernameOrPasswordError(authInput));
    });

    request.addParameter('AuthInput', TYPES.NVarChar, authInput);
    connection.execSql(request);
  });
}
13

getting below error

Request to Webtask exceeded allowed execution time

14

May be a timeout? For me the script works.

15

yes that’s true, now it’s working. Thanks for your time