Auth0 Home Blog Docs

Connection to Azure SQL Server not working inside rule

azure-sql

#1

I’ve created a rule from the “Add user roles from a SQL Server database” template.
I’ve modified the code with my credentials and a custom query, both fully functional.
However, I’m getting this message:

ERROR: Script execution did not complete within 20 seconds. Are you calling the callback function?

Any idea of what’s going on? I’m also getting the following on the Webtask log:

finished webtask request 1XXXXXXXXX with HTTP 500 in 35062ms
(changed the code with Xs).

The query and credentials are correct as I’m using them constantly without problems.

Thanks,
Ivan


#2

Managed to solve it by creating the connection myself using mssql.


#3

How did you manage to solve this using the connection string yourself? I’m struggling with the same problem


#4

Hi, sorry for the late reply, hope it’s not to late to help out. This is the code I used:

function (user, context, callback) {

var mssql = require(‘mssql@3.3.0’);
var connection = mssql.connect({ user:
’’, password: ‘’, server: ‘’,
database: ‘’, options: { encrypt:
true, } }).then(function(pool) {
return pool.request() .query(‘YOUR
QUERY’); }).then(function(result) { //
DO WHATEVER WITH RESULT callback(null,
user, context); }); }


#5

I can’t for the life of me get the code to look good, hope it’s clear enough


#6

Thanks a lot! I’ll try this out


#7

Awwww damn, it actually works! You’re a lifesaver!


#8

@baltzar Glad I could help!


#9

I’ll share my code, with an example of replacing parameters to avoid sql injection:

function (user, context, callback) {
  
  var mssql = require('mssql@3.3.0');
  
  var query = "SELECT ur.Name " +
                "FROM dbo.Users u " +
                "INNER JOIN dbo.UserRoleUsers urr " + 
                "ON urr.User_UserId = u.UserId " +
                "INNER JOIN dbo.UserRoles ur " +
                "ON ur.Id = urr.UserRole_Id " + 
                "WHERE u.Email = @email";
  
  var connection = mssql.connect( { 
    user: '', 
    password: '', 
    server: '', 
    database: '',
    options: {
      encrypt: true
    },
  }).then(function(pool) {
    return pool.request()
      // To avoid sql injection
      .input("email", mssql.VarChar, user.email)
      .query(query);
  }).then(function(result) {
    context.idToken"https://mydomain.com/roles"] = result;
    callback(null, user, context);
  });
}

#10