Azure SQL Database Log Analytics NodeJS

End to end authentication &Authorization using Azure AD for Azure SQL Database (Node.JS)

E2E authentication using Azure AD for Azure SQL Database

img

To use table of contents access and up to date code examples gitHub

Benefits

Provides end to end Azure AD authorization and logging all the way to authorizing user in SQL table level.

Source LogName
Azure AD ➡ AADNonInteractiveUserSignInLogs, SigninLogs
Web App ➡ WebApp Logs (AppServiceConsoleLogs...,...,)
Database / Table 📑 AzureDiagnostics - SQLSecurityAuditEvents

Setup

setting value
Tested on Azure SQL Database Standard S1: 20 DTUs & Basic
Azure AD Application Configured for API scope https://database.windows.net/user_impersonation
MS Docs Source 1 provision-azure-ad-admin-sql-database
MS Docs Source 2 connect-query-nodejs
app that you can use to get access tokens I am using simple Node.JS app for this. various tutorials exist for this part if needed

Ensure Azure AD Application has permissions for Azure SQL Database

img

Enable Azure AD Admin on database

Add principal for the database and grant access to existing database using the azure ad admin user

  • You can use the query editor in portal to create the user in DB img

  • Create table Persons and insert some records there

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
);
INSERT INTO [Persons]
VALUES (122,'john','doe');
CREATE USER [Dennis.Hale@dewi.red] FROM EXTERNAL PROVIDER;
GRANT SELECT ON [dbo].[Persons] TO  [Dennis.Hale@dewi.red];
EXEC sp_table_privileges   
   @table_name = 'Persons%';  

Query with Node.JS using tedious

  • Below is example snippet
// Token is provided by app of your choosing (Not included in this sample)
const config = {
    server: "latestings.database.windows.net",
    authentication: {
        type: 'azure-active-directory-access-token',
        options:{
            token
        }
    },
    options: {
        database: 'userdbs2',
        encrypt: true,
        port: 1433,
        token,
    }
};

const connection = new Connection(config);

// Attempt to connect and execute queries if connection goes through
connection.on("connect", err => {
  if (err) {
    console.error(err.message);
  } else {
    queryDatabase();
  }
});

connection.connect();

function queryDatabase() {
  console.log("Reading rows from the Table...");

  // Read all rows from table
  const request = new Request(
    `SELECT * FROM [dbo].[Persons]`,
    (err, rowCount) => {
      if (err) {
        console.error(err.message);
      } else {
        console.log(`${rowCount} row(s) returned`);
      }
    }
  );

  request.on("row", columns => {
    columns.forEach(column => {
      console.log("%s\t%s", column.metadata.colName, column.value);
    })
  });

  connection.execSql(request);
}

  • Example result
Reading rows from the Table...
PersonID        1234
LastName        John
FirstName       Doe
PersonID        334
LastName        jake
FirstName       as
2 row(s) returned

Logging example

img

union AADNonInteractiveUserSignInLogs, SigninLogs
| where ResourceDisplayName == "Azure SQL Database"
| summarize make_set(Location) by UserPrincipalName, ResourceDisplayName, tokenRefreshed= TimeGenerated, AppDisplayName
| join kind=inner (AzureDiagnostics
| where ResourceProvider =="MICROSOFT.SQL" | project session_server_principal_name_s, statement_s, sqlTime=TimeGenerated) on $left.UserPrincipalName == $right.session_server_principal_name_s
| project session_server_principal_name_s, tokenRefreshed, sqlTime, query=statement_s, app=strcat(AppDisplayName, '-', ResourceDisplayName)

0 comments on “End to end authentication &Authorization using Azure AD for Azure SQL Database (Node.JS)

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out /  Muuta )

Google photo

Olet kommentoimassa Google -tilin nimissä. Log Out /  Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out /  Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out /  Muuta )

Muodostetaan yhteyttä palveluun %s

%d bloggaajaa tykkää tästä: