This article is contributed. See the original author and article here.
We are making available the private preview of Azure Arc enabled SQL Server. To read more, see the original article in the SQL Server Tech Community.
-Marko
This article is contributed. See the original author and article here.
|
We continue to expand the Azure Marketplace ecosystem. For this volume, 80 new offers successfully met the onboarding criteria and went live. See details of the new offers below: |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
This article is contributed. See the original author and article here.
Last Hallowe’en, it was my pleasure to help launch the Azure Mystery Mansion, a text-based game built using Twine. Users explore the various rooms of an old house, picking up keys that allow final access to the attic where she or he can claim the deeds to the house. In the process, rooms are unlocked by means of discovering solutions to small puzzles and gathering clues from Microsoft Learn.
I learned a great deal on the making of this type of game from Em Lazer-Walker, who led the development of the original Mystery Mansion and enhanced Twine to work better for multiple developers. She also perfected its integration with PlayFab, the game’s backend.
The game was a hit. A big hit. So big that it caught the imagination of many folks at Microsoft. We were soon making plans for a V2 of the Mystery Mansion, which I envisioned as a continuation of the old house motif. But the Mansion storyline ends so nicely, with the user gaining the keys to get the house deeds in the attic, that I couldn’t seem to find a good way to continue the trope. Do we continue with a tour of outbuildings and old antiques stuck in a shed? Somehow the “Mystery Outhouse” doesn’t have quite the same panache. Clearly, a new tale had to be told. In addition, we wanted to somehow localize the experience to make it more pertinent to a regional audience.
Enter my work with my brother, an Art Historian at Cal State Chico, Matthew Looper. Inspired by his work, it struck me that a game centered around ancient Maya culture would resonate with our pan-Americas group with Cloud Advocates and Project Managers who cover Canada, US, and LATAM who were particularly interested in continuing the project. We gathered a team of regional PMs and marketing experts to make this idea a reality. Foremost in my mind was to not spin up an ‘Indiana Jones’ type mish-mash of exploitative cultural tropes, but to create something genuinely respectful of ancient Maya culture that was both educational and fun.
And so our path became plain. We would partner with experts to create a game that taught about Maya glyphs while exploring a mysterious pyramid, encountering surprises along the way. Working with an illustrator who is an expert in creating architectural drawings and reproducing glyphs would ensure its cultural appropriateness, so we contracted with illustrator Dana Moot II to create accurate depictions of a fictional Maya pyramid.
In addition, our experience in December with the success of our 25DaysOfServerless event, also a large group effort centered on exploring aspects of Microsoft Azure, provided a supplemental road map for the game. By building a scavenger hunt that also pushed users to solve puzzles by actually shipping code, we would showcase product launches progressively over a three-month three part release. The first one would be Azure Static Web Apps, and the user would be given a challenge to discover the meaning of one of the three glyphs that makes up the temple’s name. Not to reveal too much, but didn’t you always want to chat with a goddess? You’ll deploy a chat interface built with React and use Azure Static Web Apps to view it live, learning via chat part of the name of the mysterious pyramid.
While Twine worked brilliantly for the Mystery Mansion I wanted to try a tool that gave me a little more design freedom and was a JavaScript-native tool that would foster collaboration. I wanted something that would enable many people to write parts of a game or to localize it, and that would be easier to scale and maintain. And of course, I reached for a tool that was most familiar to me (my blog is built using it): VuePress.
As a Vue.js developer I immediately gravitated towards VuePress, a static site generator that would work well for storytelling using files written in markdown. By using this toolset, which allows both styling via Tailwind.css and importation of standard Vue.js single-file components, we were able to get better control over the interface and more flexibility in the way we designed it, while keeping the ‘vintage’ look of the text-based game intact.
An example of the storytelling-friendly format of the game can be seen by perusing the markdown files that tell the tale of the pyramid exploration:
---
backdrop: images/1-nostairs-closest.png
---
# The West Wall
Walking west, you encounter thick underbrush. Vegetation has grown in close to the wall, and dangling vines whack your face as you push through.
As you move vines aside to pass, your hand brushes a carving embedded in the wall. It's another glyph.
<Item id="7" />
<Page url="398" instructions="Another puzzler. Your guidebook provides another clue: '3: Machine Learning can help you predict the prevalence of this kind of species.'" action="Walk south" condition="7" />
Child components display a glyph to collect, and once that condition is solved, the matching page navigation is displayed. Props are sent from the parent page to its child, to display instructions and actions to take. Item clues are contained in a localized json file:
"gameItem": "snake glyph",
"filename": "snake",
"initialHide": true,
"clues": {
"es": {
"clue": "4: El lenguaje que lleva el nombre de esta criatura convierte el código fuente en código de bytes que se puede ejecutar en cualquier plataforma compatible."
},
"en": {
"clue": "4: The language named after this creature turns source code into byte code which can be run on any supported platform."
},
"fr": {
"clue": "4: Le langage nommé d'après cette créature transforme le code source en code octet qui peut être exécuté sur n'importe quelle plate-forme prise en charge."
},
"pt": {
"clue": "4: A linguagem com o nome desta criatura transforma o código-fonte em código de bytes que pode ser executado em qualquer plataforma suportada."
}
},
Jen: “Let’s use VuePress! How do we make a game in VuePress?”
Chris Noring: “Hold my Glögg”
Within a ridiculously short amount of time, Chris had spun up a basic VuePress-based game engine using scoped slots, local storage, and a basic storyline written in markdown. I took this kernel of a game and built it into a journey to explore a pyramid, taking pictures and solving puzzles to restore glyphs vandalized by looters.
A camera interface displays the ids of images stored in local storage:
showCameraItems() {
var ids = getItems();
this.polaroids = ids.map(id => items.find(item => item.id == id));
},
Since we knew, given that the game was for the Regional team, that we would need to localize it, providing translations in French, Spanish, and Portuguese as well as English, we had to create an interestingly hybrid approach to translate both the markdown files supported by VuePress as well as the localizable strings used in the Vue.js files for the more complicated game play.
While VuePress has a built-in way of handling translations using routing (/zh vs. /fr routes, for example, will display appropriately translated content), there needed to be a way to propagate those changes to nested Vue.js files, such as puzzles embedded as child components. Enter the EventBus, a good way to signal to child components that changes to translations have occurred, and to pick up different translated strings.
When the UI is told to switch locales, the EventBus emits a command:
EventBus.$emit("lang_changed", lang);
And this command is acted upon, informing the child components that the language has changed and that the locale controlled by the i18n plugin must be switched
EventBus.$on("lang_changed", lang => (this.$i18n.locale = lang));
Since this site is deployed on Azure Static Web Apps, the game is contained in /app and any API calls are contained in /api. Using the PlayFab SDK for Node, we are able to consolidate all API calls to the backend in this separate area and use Azure functions to invoke them. A login request can thus occupy only 20 lines:
const { PlayFabClient } = require('playfab-sdk');
module.exports = function (context, req) {
var request = {
Email: req.body.email,
Password: req.body.password,
RequireBothUsernameAndEmail: false,
};
PlayFabClient.settings.titleId = <This is the game's title id, set in PlayFab console>;
PlayFabClient.LoginWithEmailAddress(request, function (error, result) {
if (error == null) {
context.res = { body: result.data };
context.done();
} else {
context.res = { body: error };
context.done();
}
});
};
Dogfooding is a great exercise, and the Azure Maya Mystery lives in the very product showcased by its embedded challenge, Azure Static Web Apps. It was shockingly easy to deploy the app; the challenge for us occurred when we were obliged to move the game into a subfolder so that it could be deployed on the Microsoft.com subdomain. This requirement forced some reshuffling of assets and the addition of a postinstall script after the app is built by VuePress, to copy images to an internal folder, but the deployment process was quite smooth.
Learn how to Publish an Angular, React, Svelte, or Vue JavaScript app and API with Azure Static Web Apps.
And also check out:
When developing this type of game, especially one with an educational component, it was really important to have playtesting by a diverse set of users. Interestingly, but not surprisingly, it was the 10 year old son of a Microsoft employee in the UK who gave us particularly solid feedback. He first noted that there weren’t enough spiders (stay tuned, Tommy) and that we needed more puzzles (I added two!). Thanks, intrepid testers!
So, with a bit of imagination, some useful QA from our creative director, Em, and a lot of rethinking, rewriting, and translating, we were able to build the first part of a three part adventure that should satisfy the hardiest of explorers. It was exciting to see folks who had never tried to deploy a website on Azure suddenly able to solve the coding challenge and complete the code challenge, gaining entrance into the mysterious pyramid. In future months, we will allow users to continue their progress, exploring surprises that appear underneath the pyramid and working up to the summit, where the name of the temple and its owner will be revealed. Get ready for a true adventure, explorers!
Playtesting revealed another interesting possibility for our mysteries. We have talked to Museums who were curious about their potential application as they allow for online walk-throughs of their collections. Tommy and his Dad recommended that we create a stripped-down version of the game engine and release it for students to fork and alter for their own needs, to learn how to manage a project in GitHub and how to handle building and deploying a web site. So, I did! You can use this repo to build your own game.
This engine has unlimited potential for helping build both gamified experiences and for helping teach concepts such as basic CS and programming curriculum. As students write their storyline, they learn how the parts of the site fit together and how to gradually alter a codebase to suit their needs. I’m encouraging folks to tell me about their games in the Issues tab on GitHub.
What will our next stop be? For the kids, a trip through space? For the Azure Maya Mystery, a trip to the depths of the pyramid…hint, I hope you know how to swim!
The blue glyph
This article is contributed. See the original author and article here.
For years Microsoft SQL Server has served as a backbone of critical applications for enterprises.
Due to the nature of critical data stored on the SQL Server databases, it has always been a point of Interest for internal or external adversaries and one of the primary targets for exploitation.
It is important to monitor all your SQL database instances and servers for any sign of threats.
If you are running Azure SQL or Azure SQL Managed Instances or Azure Synapse Analytics, Azure Data Security (ADS) provides comprehensive database security in relation to Data Discovery and Classification, Vulnerability assessment and Advanced Threat Protection.
We recently released a public preview of ADS for SQL Machines to extend all the capabilities to SQL Server running on machines.
This post compliments the capabilities of ADS by enabling monitoring of SQL Server databases running on Windows Server VMs on premises or on Cloud IaaS by ingesting SQL Server Audit events into Azure Sentinel, build various custom threat hunting queries, correlate events and create alerts.
Let us get started.
Step 1 – Enable audit on SQL server and create a policy Audit:
As a first step we need to enable auditing to track and log various types of events on the Server and Database levels. For this post, I have enabled these following specifications on my database.
More information on enabling audit can be found here.
Step 2 – Write SQL Server Audit Events to the Security Log
One of the easiest ways of getting logs from SQL servers to your Azure Sentinel workspace is to write SQL Audit Events into Windows Security Events or Application Events. For this blog post, I am configuring my SQL Server to write audit events into Application logs of Windows events.
Step 3 – Sending logs from SQL Server to Azure Sentinel using Microsoft Monitoring Agent.
If you are writing SQL Audit events to Windows Security Events, you may use the Azure Sentinel Security Event Connector to collect the logs from the SQL Server system using the MMA Agent.
In this post, I am writing the SQL Audit events to the Windows Application log and hence it requires an additional step of collecting Application Log from the systems in the advanced Settings of your Log Analytics workspace.
PS: It is recommended to apply this configuration only to the systems from where we need these logs as MMA agent will collect all Application logs.
Once the MMA agent starts sending the events logs to the Azure Sentinel, you should see all the logs in the “Event” table.
Event
| where Source has "MSSQL"
| project TimeGenerated, Source, Computer, EventID, RenderedDescription
All the critical information about these audit events is available in the RendererdDescription column including Action ID, Client IP, Current Logged on User, Database Name, Object Name and SQL Statement.
Action ID is a critical item which illustrates a specific activity on the SQL Server which you could choose to monitor.
Here are some of the Critical Action IDs recommend to review:
|
AL |
ALTER |
|
CR |
CREATE |
|
APRL |
ADD MEMBER |
|
DPRL |
DROP MEMBER |
|
DL |
DELETE |
|
DR |
DROP |
|
IMP |
IMPERSONATE |
|
LGIF |
LOGIN FAILED |
|
LGIS |
LOGIN SUCCEEDED |
|
PWC |
CHANGE PASSWORD |
|
PWPL |
PASSWORD POLICY |
|
TO |
TAKE OWNERSHIP |
Now that we have our logs coming into Azure Sentinel, we need to parse the “RenderedDescription” field to provide relevant information for us to create and test threat hunting queries against.
// KQL SQL Audit Event Parser
// SQL Server
//
//
// Parser Notes:
// This parser works against the SQL Audit events being written to Application Log of Windows Events.
//
// Usage Instruction:
// Paste below query in log analytics, click on Save button and select as Function from drop down by specifying function name and alias (e.g. SQLEvent).
// Function usually takes 10-15 minutes to activate. You can then use function alias from any other queries (e.g. SQLEvent | take 10).
// References:
// Using functions in Azure monitor log queries :queries: https://docs.microsoft.com/azure/azure-monitor/log-query/functions
// Tech Community Blog on KQL Functions : https://techcommunity.microsoft.com/t5/Azure-Sentinel/Using-KQL-functions-to-speed-up-analysis-in-Azure-Sentinel/ba-p/712381
//
//
let SQlData = Event
| where Source has "MSSQL"
;
let Sqlactivity = SQlData
| where RenderedDescription !has "LGIS" and RenderedDescription !has "LGIF"
| parse RenderedDescription with * "action_id:" Action:string
" " *
| parse RenderedDescription with * "client_ip:" ClientIP:string
" permission" *
| parse RenderedDescription with * "session_server_principal_name:" CurrentUser:string
" " *
| parse RenderedDescription with * "database_name:" DatabaseName:string
"schema_name:" Temp:string
"object_name:" ObjectName:string
"statement:" Statement:string
"." *
;
let FailedLogon = SQlData
| where EventLevelName has "error"
| where RenderedDescription startswith "Login"
| parse kind=regex RenderedDescription with "Login" LogonResult:string
"for user '" CurrentUser:string
"'. Reason:" Reason:string
"provided" *
| parse kind=regex RenderedDescription with * "CLIENT" * ":" ClientIP:string
"]" *
;
let dbfailedLogon = SQlData
| where RenderedDescription has " Failed to open the explicitly specified database"
| parse kind=regex RenderedDescription with "Login" LogonResult:string
"for user '" CurrentUser:string
"'. Reason:" Reason:string
" '" DatabaseName:string
"'" *
| parse kind=regex RenderedDescription with * "CLIENT" * ":" ClientIP:string
"]" *
;
let successLogon = SQlData
| where RenderedDescription has "LGIS"
| parse RenderedDescription with * "action_id:" Action:string
" " LogonResult:string
":" Temp2:string
"session_server_principal_name:" CurrentUser:string
" " *
| parse RenderedDescription with * "client_ip:" ClientIP:string
" " *
;
(union isfuzzy=true
Sqlactivity, FailedLogon, dbfailedLogon, successLogon )
| project TimeGenerated, Computer, EventID, Action, ClientIP, LogonResult, CurrentUser, Reason, DatabaseName, ObjectName, Statement
Save this as a KQL Function with the name SQLEvent(). More Information on using KQL functions can be found here
We can now call this function [ SQLevent() ] and project our relevant information extracted like Action, ClientIP, DatabaseName, Statement and more.
Next, we go hunting based on the information that we have parsed from the logs.
Here are a few examples of hunting queries which can also be used to create analytics rules for alert creation after further tuning.
All Failed Logons
SQLEvent
| where LogonResult has "failed"
| summarize count() by CurrentUser, Reason
Notice the use of the parser where we have extracted the SQL statement into a separate column “Statement”. All of the actions made to the SQL server or the database are listed here. Now your hunting capabilities are enormous by simply parsing this field at query time. There is no need to tax the system by parsing it in the primary parser when there is no use case for it. This will allow you to get results more efficiently.
Here are some of examples of hunting for various anomalies based on the text in the “Statement” column.
New User Created
New user creation on SQL Server should be monitored for any suspicious patterns like, New User created by unauthorized accounts.
//This query checks for new user account created on SQL Server using the SQLEvent() parser
//
SQLEvent
| where Statement has "Create Login"
| parse Statement with "CREATE LOGIN [" TargetUser:string
"]" *
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
Here the CurrentUser is the current logged-on user who has created the new user account i.e TargetUser. We can easily extend this query to see if CurrentUser != “[Authorised User Name]” to find if the new user creation was done by non-authorized accounts.
User Role Altered in SQL
Also, monitoring changes made to user roles on SQL server can help with detecting anomalous privilege escalations.
// This query looking for Alter role commands and extracts username which was altered and target objectName
//
SQLEvent
| where Statement contains "Alter role" and Statement has "add member"
| parse Statement with * "ADD MEMBER [" TargetUser:string
"]" *
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
User added in SecurityAdmin Role
Users being added to some of the critical groups of SQL server should be monitored for anomalous persistence and privilege escalation.
SQLEvent
| where Statement has "Alter Server role" and Statement has "add member"
| parse Statement with * "ADD MEMBER [" TargetUser:string
"]" *
| where ObjectName has "securityadmin"
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
Removed User from Database
Large numbers of users being removed from the databases correlated with other changes like ALTER ROLE can help detect ongoing attacks on critical databases.
// This query checks for user removed from a database by parsing the statement field at the query time.
//
SQLEvent
| where Statement has "Alter role" and Statement has "drop member"
| parse Statement with * "DROP MEMBER [" TargetUser:string
"]" *
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
User Removed from ServerRole
SQLEvent
| where Statement has "Alter Server role" and Statement has "drop member"
| parse Statement with * "DROP MEMBER [" TargetUser:string
"]" *
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
User removed from SecurityAdmin Role
SQLEvent
| where Statement has "Alter Server role" and Statement has "drop member"
| parse Statement with * "DROP MEMBER [" TargetUser:string
"]" *
| where ObjectName has "securityadmin"
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
You can now create similar hunting queries based on the information available in the Statement column with the combination of Action Ids like:
Now, go ahead and create some Analytics rule for Incident creation.
Multiple Failed Logons in short span of time
//This detection rules checks for multiple failed logon attempts within short span of time.
// the timeframe and threshold can be changed below as per requirement
//
let TimeFrame = 10m;
let failedThreshold = 3;
SQLEvent
| where TimeGenerated > ago(TimeFrame)
| where LogonResult has "failed"
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), TotalFailedLogons = count() by CurrentUser
| where TotalFailedLogons >= failedThreshold
| project CurrentUser, TotalFailedLogons
Multiple Failed Logons by multiple accounts from same IP
//This detection rules checks for multiple failed logon attempts from same IP within short span of time.
// the timeframe and threshold can be changed below as per requirement.
//
let TimeFrame = 10m;
let failedThreshold = 3;
SQLEvent
| where TimeGenerated > ago(TimeFrame)
| where LogonResult has "failed"
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), TotalFailedLogons = count() by ClientIP, CurrentUser
| where TotalFailedLogons >= failedThreshold
| project ClientIP, TotalFailedLogons, CurrentUser
With this blog post, we are just scratching the surface with the kind of custom threat hunting possible on SQL Server environments with Azure Sentinel, hope this helps you to get started.
Enable audit on SQL server and create a policy Audit:
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15
Write Audit SQL log to Windows Event
Advanced data security for SQL machines (Preview)
Hunting queries and detection queries are available on our GitHub community page.
This article is contributed. See the original author and article here.
Azure SQL has a lot of built-in features that make it the best database in the cloud for developers. In this first part of this two-part series, Davide Mauro discusses what those features are and which kinds of customers can benefit most from them.
View/share our latest episodes on Channel 9 and YouTube!
Recent Comments