HLS Microsoft Teams Live Events and Stream AMA Webcast – Recording

by Contributed | Dec 2, 2020 | Technology

This article is contributed. See the original author and article here.

  By Request! Over the last several months more organizations than ever are leveraging the power of Live Events webcasting to deliver training, executive engagement, and more within their organizations. With this proliferation of the use of both Live and On-Demand rich media content challenges have arisen around network configuration, production best practices, and more.

During this recorded webcast Microsoft’s Samantha Brown and Michael Gannotti answer questions during this customer requested Ask Me Anything webcast focusing on Microsoft Teams Live Events and Microsoft Stream. This event took place on Wednesday December 2^nd.

Resources:

• NewTek Tricaster https://www.newtek.com/


• Skype TX for Broadcaster https://www.skype.com/en/content-creators/for-broadcasters/


• Skype TX Support https://support.skype.com/en/faq/FA34767/what-is-skype-tx


• NDI https://ndi.tv/


• Microsoft Teams How To Enable NDI https://support.newtek.com/hc/en-us/articles/360055420373-Microsoft-Teams-how-to-enable-NDI


• Upcoming Teams Templates: https://techcommunity.microsoft.com/t5/healthcare-and-life-sciences/teams-templates-upcoming-collabcast-with-sam-amp-pete/ba-p/1930979


• External Presenters in Teams Live Events: https://techcommunity.microsoft.com/t5/healthcare-and-life-sciences/best-practices-for-external-presenters-in-your-teams-live-event/ba-p/1910023  


• API Change Log: https://docs.microsoft.com/en-us/graph/changelog?view=graph-rest-beta  


• iPad Roadmap: https://www.microsoft.com/en-us/microsoft-365/roadmap?SilentAuth=1&rtc=1&filters=&searchterms=68867  


• This goes into the advanced comm sku too: https://docs.microsoft.com/en-us/microsoftteams/teams-add-on-licensing/advanced-communications  


• We will go over these limits! https://docs.microsoft.com/en-us/microsoftteams/limits-specifications-teams#teams-live-events  


• Best Practices for Producing: https://support.microsoft.com/en-us/office/best-practices-for-producing-a-teams-live-event-e500370e-4dd1-4187-8b48-af10ef02cf42  


• Usage Reports: https://docs.microsoft.com/en-us/microsoftteams/teams-analytics-and-reports/teams-live-event-usage-report  


• Live Events Assistance: https://docs.microsoft.com/en-us/stream/live-event-troubleshooting#before-a-live-event


• Download the webcast VTT transcript file 

Connect with Sam and Mike on LinkedIn:

• Samantha Brown on LinkedIn 


• Michael Gannotti on LinkedIn 

Thanks for visiting – Michael Gannotti   LinkedIn | Twitter  

Logging Schema-changes in a Database using DDL Trigger

by Contributed | Dec 2, 2020 | Technology

This article is contributed. See the original author and article here.

The article is published here on the Azure SQL Blog: Logging Schema-changes in a Database using DDL Trigger – Microsoft Tech Community

Logging Schema-changes in a Database using DDL Trigger

by Contributed | Dec 2, 2020 | Technology

This article is contributed. See the original author and article here.

Every so often I have been asked “how it is possible to log and review the logs when Developers change objects but do not have access to the SQL Audit?”.

Now, even a security person like me should try to understand the real motivation for this before jumping to the conclusion that Auditing is the only right solution.

So first let’s understand the potential motivations:

What is the purpose?

Very often, the background is simply to keep a record of who changed which database-objects to answer typical question like:

• “Did anyone change any index lately?”


• “I thought we had an Index here on this table, did anyone drop it?”


• “Who changed my proc?” (Although I would advise to keep a record of all changes within the header of any module as a best practice)


• “When did the last deployment run/stop?”


• “My old SQL-Function code is gone; does anyone have a copy?”

The motivation is roughly about troubleshooting schema-changes. The aim is not to detect security breaches or even tamper-proof security investigations.

If that is the case, my advice here is to not use Auditing at all for such purpose.
The Auditing feature in SQL Server and Azure SQL Database is really meant to be used by security personae, or Administrators with high privileges:

• The ability to create change, stop and remove Audits is collected under one permission: either ALTER ANY SERVER AUDIT or ALTER ANY DATABASE AUDIT, depending on the scope.


• To read the audit result, at a minimum the ALTER ANY SERVER AUDIT is required for Audits to the Application or Security Log but CONTROL SERVER for Audits to a file. In Azure SQL Database CONTROL DATABASE is when using the system function sys.fn_get_audit_file to read from a File target in Blob Storage. Event Hub or Log Analytics are accessed outside from the SQL engine and not controlled with SQL-permissions.

The above request is much more easily fulfilled with a simple solution that allows the results of the DDL-activities to be stored directly within a database table. This will provide a convenient way to understand what scripts or ad-hoc changes to a database schema happened – but it will not serve as measure against eval actors/attackers.

Security-Note
If the requirement is, to provide an Audit trail for Security and Compliance reasons, out of reach for normal Database Developers or anyone accessing SQL Server alone, then SQL Auditing is the right solution. DDL Triggers and local tables can be easily tampered with once an attacker elevated to basic datawriter or ddl_admin-level permissions.

The solution

If you need to support your Engineering team with a simple record of all DDL statements (Data Definition Language) run against a database, then I recommend using DDL Triggers.

A DDL Trigger is comparable to a stored procedure except it gets activated when any DDL event for which it was created occurs. The list of Events that can be used is here: DDL Events – SQL Server | Microsoft Docs.

Personal anecdote
Over the years working on customer systems, I personally found it to be invaluable and as best practice equipped any database that I designed with such a small trigger and DDL-log-table, just in case. It has helped many times to quickly solve issues with deployments scripts, non-scripted changes to the systems, problems with Source Control and simply getting answers quickly.
The concept is almost trivial and because DDL changes are usually not in performance-critical code-paths, the theoretical overhead on the DDL statement-runtimes is not relevant. (Unless frequent schema-changes are part of a performance-sensitive workload – in which case I would then question if using DDL is a good idea at all in such a place. Note that temporary tables are not caught by DDL Triggers.)

If we want to log “almost any DDL statement”, we can use the keyword DDL_DATABASE_LEVEL_EVENTS and then within the body of the trigger we can ignore certain events.

Note
Events that typically make sense to ignore are: regular Index Rebuild, Reorganize and Update Statistics – routines.
– Rebuild and Reorganize are both covered by the ALTER_INDEX event. But I would recommend keeping an eye on ALTER INDEX … DISABLE, therefore in my example you will find a bit of logic to exclude only ALTER INDEX that is not caused by the DISABLE-option. (“Enable” would be done via a Rebuild, so unfortunately there is a small gap: we will not know when the Index is enabled again. If this turns out to be important, include the Rebuild-option as well, but expect a high volume of events in that case.)

Inside the Trigger we have access to the EVENTDATA()-Function which returns the details on the event in xml-format. The trigger-code extracts the most useful data from it.
The so captured events can then be written to a local database table, which is much more convenient for the purpose of troubleshooting than having to go to a Security Audit.

Note
If you are interested in the Set-Options used when the command was run (this can be useful for debugging purposes), then the SetOptions-Node below TSQLCommand comes in handy.

Finally, we need to make sure that the Trigger will succeed in writing to the table even if the User who triggered the event may not have explicit permissions to write to any table. To keep this example simple my code grants INSERT onto the dedicated table to public. This is the easy way and sufficient for many scenarios. If you need extra security, I recommend using a special account for impersonation during the trigger execution only, using the EXECUTE AS-clause. At the end of the script, you will find an example of how to go about that. That way you can make sure to have least privileges applied and only when really needed.

Attached you can find the T-SQL Script which will create the following objects:

• A Database-Level DDL Trigger


• A Table in a dedicated Schema


• A stored procedure to purge data from the table when needed.

Also, I include a Script with some queries to test.

This is how the results in the table look like after running some DDL-commands:

Documentation-links:

• DDL Triggers


• EVENTDATA (Transact-SQL)


• Transact-SQL statements – SQL Server


• EXECUTE AS Clause (Transact-SQL) – SQL Server


• SQL Server Audit (Database Engine)


• Azure SQL Auditing for Azure SQL Database and Azure Synapse Analytics – Azure SQL Database

I hope you find this script a useful example.

Let me know in the comments how you solve similar requirements in the comments below.

Andreas