Making about:config a trusted sites

 I need this because IE tries to load about:config twice, which throws errors in the secure mode of IE/Edge, and I needed to script a way around it. 

Thanks to Joe for the tip on quotes.

if (-not (Test-Path -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains\blank'))

{

    $null = New-Item -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains\blank'

}

Set-ItemProperty -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains\blank' -Name "about" -Value 2 -Type DWord

Azure Managed Instance to Parquet using CETAS and t-sql

And we're back! Not that anybody noticed.  'Scool. IP agreements and the like. 

I'll make it brief since I logged on at 11pm to get this working.

TL;DR - the below lines will allow you to query a table on your MI, creating Parquet files in Azure blob storage. And you can query it! Next up is partitioning over time, etc, etc. But this is freaking fantastic. I have a python script I wrote that does it, but it's nowhere as nice/easy as this.

Why do you care? Because it's a fantastically easy way to archive older data to blob storage, and I suspect (need to test) that if you do it right, you can then have it go to cool/archive storage via a lifecycle setup, so that if you need it much later, you can. Parquet is a columnar compressed format that can then feed other things like Snowflake, Hadoop, Redshift Spectrum, AWS Athena, etc. For me it's a platform-agnostic, portable, highly-compressed data format that I can use to better archive older data. 

CETAS: Create External Table As Select

1) Walking the dog and reading my RSS feeds on Feedly, I see this:
https://azure.microsoft.com/en-us/updates/azure-sql-general-availability-updates-for-lateapril-2023/

2) reading through, go to this:
SQL Managed Instance now supports CETAS (“Create External Table As Select”) functionality. This feature allows users to export data from local database tables into parquet and CSV files in Azure storage and creates external tables for easy querying of the exported data through data virtualization.

3) Run back home and log in. My poor dog was out of breath.

4) After dinking for 30 minutes trying to get my local copy of the az powershell module to work with the command, (still isn't working, seriously, wtaf), and being unable to use cloud shell since it requires cloud storage and permissions are locked down too much, I finally use -UseDeviceauthentication and run these commands on my instance:

# Enable ServerConfigurationOption with name "allowPolybaseExport"

Set-AzSqlServerConfigurationOption -ResourceGroupName "myresource_group_name" -InstanceName "myManagedInstanceName" `

-Name "allowPolybaseExport" -Value 1

5) Verify it works.

SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';

Returns 1!

6) now, I use the following t-sql, pretty much verbatim from their example and a SAS token. The only magic I've added is the 10 minutes on how to use a SAS token. 

USE my

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password would go here';

GO
CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]
    WITH IDENTITY ='SHARED ACCESS SIGNATURE',
SECRET = 'sp=rwl&st=2023-04-27T04:37:23Z&se=2023-05-26T12:37:23Z&spr=https&sv=2021-12-02&sr=c&sig=stringofstuffremovedhahahahahthisisfromthesastokenA%3D' ; --Removing leading '?'
GO

 

CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
    LOCATION = 'abs://mycontainer@mystorageacct.blob.core.windows.net',
    CREDENTIAL = [CETASCredential] );
GO

 

CREATE EXTERNAL FILE FORMAT [CETASFileFormat]
WITH(
    FORMAT_TYPE=PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );
GO

 

-- Count how many rows we plan to offload
SELECT COUNT(*) FROM my.dbo.table

CREATE EXTERNAL TABLE mytable_polybase_example

WITH (
    LOCATION = 'I/get/multiple/paths/for/multiple/tables!/',
    DATA_SOURCE = [CETASExternalDataSource],
    FILE_FORMAT = [CETASFileFormat])
AS 
    SELECT 
        *
    FROM 
       my.dbo.table

 

-- you can query the newly created external table
SELECT COUNT (*) FROM mytable_polybase_example;

[WAT] fun with declare @blah = varchar!

Another in my "WAT" file. (Go find it on youtube, you'll laugh and groan) 

What happens if you don't assign a length to varchar? I swear I'd learned that it became varchar(30).
Running on SQL Server 2014 SP3. Bold are the selects that are getting returned.

DECLARE @blah VARCHAR 

SET @blah = '123456' 

SELECT @blah 

DECLARE @date DATETIME=getdate() 

SELECT CONVERT(VARCHAR, @date, 112) 

SELECT @blah = CONVERT(VARCHAR, @date, 112) 

SELECT @blah 

1 

2030302

2

Severity 10 - using Extended Events to get code errors

I wish I knew whom to thank for this.  An absolute genius idea. Probably Ozar, possibly Bertrand, could be White or a bunch of other #SQLFamily.  Warning: once you use this, you'll want it on all your servers. 

Premise: run an Extended Event looking for all errors over Severity 10, saving in a 10mb memory buffer within the SQL Server instance. Keep the rolling last errors so you have a decent amount of history. I believe the original was Severity 10 and above, but that one was too chatty for me, whereas 11+ seems to be ideal. But I refer to it as my "Sev10" code, so here we are. This includes all sorts of stuff you won't normally see - errors from stored procedures, errors in replication, errors in SSMS, etc. 

Testing it is easy: create session & start it, run "select 1/0", then run the query.

I do have a version that I run that saves logs out, but honestly, that's a different post. This Xevent uses 10mb from the Ring Buffer, constantly filling and keeping the last 10mb of errors. Performance hit seems minimal, and we're doing millions of transactions a day.

Performance has always been the kicker on this - using Xquery directly against the ring buffer is slow as heck, and even after solving that via temp tables, I still ran into problems with one particular server. That made me realize I don't appear to have posted this. By splitting it via NODE to multiple rows to a temp table, then querying the temp table to shred the XML, the performance is vastly improved.  In my case, from 8 minutes to 16 seconds. And on most servers, under 5. 

Other thoughts - you might consider adding another WHERE clause to the ADD EVENT; maybe filter end-users' machines or SSMS. I like having it, but you do you. Turns out SSMS is damn chatty sometimes, also. "View server state permission was denied " and "Cannot drop the table '#SVer'" abound.

Want to run this in Azure SQL DB? Sure, just do it against each database, replacing the "ON SERVER" with "ON DATABASE". The table names you query against change slightly (it's commented), and the time zone calculation doesn't work yet. Booooooooo.

I'm working on a collector, just haven't finished yet.

Bonus! Frames! Take the Frames provided in the Event_Data, and go use their amazing code to tell you exactly what piece of code in what function/stored procedure, and what line, caused the issue. Is it in a stored procedure calling a stored procedure calling a function? This will tell you where and what. Genius, and kudos to Tom.  https://straightforwardsql.com/posts/investigating-errors-with-extended-events/

Cheers!

Azure SQL Managed Instance - a simple Extended Event to track logins

 This is a barebones Xevent (Extended Event) . I'm not using blob storage, just the existing "eh, you have a couple hundred meg worth of ring buffer you can use". But I needed to see who was using one of our dev instances. I thought we'd moved everything over to the Azure SQL DBs, since there's a ton of reasons to do so.

Here's some quick off-the-cuff reasons why I prefer Azure SQL DB to Managed Instance:

1. Spins up/down FAST.  Seconds to minutes. Have a 20gb database? Takes 2 minutes. 30gb? 3.
2. More availability! Technically more servers!
3. Serverless options! Allows you to just pay for the price of storage, not compute. Doubly useful for QA/Dev boxes that aren't always used. Also allows us to auto scaleup/down as traffic changes.
4. Works with our super-limited networking.

Yes, there are downsides. CLR, Replication, Agent, Mail, others. But the wins of using Azure SQL DB are very tangible.  < / soapbox > 

Anyhow, script:

@@

/* 

CREATE EVENT SESSION [Logins] ON SERVER 

ADD EVENT sqlserver.login(

ACTION(sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.database_name,sqlserver.username))

ADD TARGET package0.ring_buffer(SET max_memory = 4096)  --max_event_limit threw error!

go

ALTER EVENT SESSION [Logins] 

ON SERVER 

STATE = start ;

GO*/

if object_id('tempdb..#xevent') is not null

    DROP TABLE #xevent

CREATE TABLE #xevent (target_data XML)

INSERT INTO #xevent (    target_data)

SELECT target_data

 FROM sys.dm_xe_session_targets        AS st

    INNER JOIN sys.dm_xe_sessions       AS se

         ON CAST(se.address AS BINARY(8)) = CAST(st.event_session_address AS BINARY(8))

WHERE

se.name = 'Logins'

DECLARE @count VARCHAR(10) 

SELECT @count = target_data.value('/RingBufferTarget[1]/@eventCount','varchar(10)') 

FROM #xevent --null means it's not on or not "installed"

PRINT 'eventCount = ' + @count

;with events_cte as(

SELECT 

ed.c.value('(@timestamp)[1]', 'DATETIMEOFFSET') at TIME ZONE 'Central Standard Time' AS err_timestamp,

--xevents.event_data.value('(RingBufferTarget/event/@timestamp)[1]', 'datetime2')) AS [err_timestamp],

ed.c.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS client_app_name,

ed.c.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS client_hostname,

ed.c.value('(action[@name="database_id"]/value)[1]', 'int') AS database_id,

ed.c.value('(action[@name="database_name (Action)"]/value)[1]', 'nvarchar(max)') AS nt_username,

ed.c.value('(action[@name="username"]/value)[1]', 'nvarchar(max)') AS username

, ed.c.query('.') AS event_data  --we leave this off since it makes this run SO MUCH LONGER.

FROM #xevent a

cross apply (select CAST(target_data as XML) as event_data) AS xevents

CROSS APPLY event_data.nodes('/RingBufferTarget/event') AS ed(c)

)

SELECT *

--, events_cte.event_data

from events_cte 

order by err_timestamp desc;

@@

Azure SQL Database Auditing gotchas.

A couple of notes when using Azure SQL DB Auditing 

1) this is actually kinda cool, overall. I plan on mining this in the future for code changes, etc. 

2) You can filter it! predicateexpression is the term you're looking for, available via the powershell module 3) if you're trying to add predicates, it uses SQL Audit syntax. In our case, we wanted to ignore a particular stored procedure call, so we used: 

"(NOT [statement] like '%myspnamehere%')" 

4) BEFORE YOU DO THIS and add the predicate: turn all the audits off on that "server" first. 

 The database AND the server-level. The regular AND the microsoft. 

We had a heck of a time figuring out why it wasn't working right. 

Between this and making sure the Database-Level Auditing was off, this worked. 

Disconnect-AzAccount myguidhere

Connect-AzAccount -Tenant myguidhere

Set-AzContext -SubscriptionId Get-AzSqlServerAudit -ResourceGroupName "ourRGname" -Servername "ourSERVERname" 

Set-AzSqlServerAudit -ResourceGroupName "ourRGname" -ServerName "ourSERVERname" -PredicateExpression "(NOT [statement] like '%myspnamehere%')" -WorkspaceResourceId "ourworkspaceidforourLogAnalyticsTarget" -LogAnalyticsTargetState ENABLED

Set-AzSqlServerMSSupportAudit -ResourceGroupName "ourRGname" -ServerName "ourSERVERname" -WorkspaceResourceId "ourworkspaceidforourLogAnalyticsTarget" -LogAnalyticsTargetState ENABLED

Azure SQL Database - something like sp_send_dbmail

 I needed the ability to send emails from Azure SQL DB, which doesn't support it. The long-term goal is to use LogicApps or SendGrid or something like that, but while I wait for permissions and the like, let me share this instead. It acts like sp_send_dbmail, and will even run queries in the database (or other databases on that server!) and send them from your on-prem server.

This is based on others. They had the awesome parts, my parts are the crappy glue making it work. : )

The emailqueue table looks like this:

CREATE TABLE [dbo].[EmailQueue](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[Recipients] [VARCHAR](250) NOT NULL,
[Cc_recipients] [VARCHAR](250) NULL,
[Email_Subject] [VARCHAR](250) NOT NULL,
[Email_body] [VARCHAR](MAX) NULL,
[Email_body_format] [VARCHAR](10) NULL,
[Query] [NVARCHAR](MAX) NULL,
[profile_name] [VARCHAR](250) NULL,
[QueueTime] [DATETIME2](7) NOT NULL,
[SentTime] [DATETIME2](7) NULL,
[Importance] [VARCHAR](10) NULL,
[attach_query_result_as_file] [BIT] NULL,
[query_attachment_filename] [VARCHAR](200) NULL,
[query_result_header] [BIT] NULL,
[query_result_width] [INT] NULL,
[query_result_separator] [VARCHAR](2) NULL,
[query_result_no_padding] [BIT] NULL,
[bcc_recipients] [VARCHAR](255) NULL,
[execute_query_database] [VARCHAR](255) NULL,
[from_address] [VARCHAR](MAX) NULL,
PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  CONSTRAINT [Co_Importance]  DEFAULT ('Normal') FOR [Importance]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  DEFAULT ((0)) FOR [attach_query_result_as_file]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  DEFAULT ((1)) FOR [query_result_header]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  DEFAULT ((0)) FOR [query_result_no_padding]
GO

And the SP: 

CREATE PROCEDURE [dbo].[log_dbmail] 

   @profile_name               sysname    = NULL,        

   @recipients                 VARCHAR(MAX)  = NULL, 

   @copy_recipients            VARCHAR(MAX)  = NULL,

   @blind_copy_recipients      VARCHAR(MAX)  = NULL,

   @subject                    NVARCHAR(255) = NULL,

   @body                       NVARCHAR(MAX) = NULL, 

   @body_format                VARCHAR(20)   = NULL, 

   @importance                 VARCHAR(6)    = 'NORMAL',

   @sensitivity                VARCHAR(12)   = 'NORMAL',

   @file_attachments           NVARCHAR(MAX) = NULL,  

   @query                      NVARCHAR(MAX) = NULL,

   @execute_query_database     sysname       = NULL,  

   @attach_query_result_as_file BIT          = 0,

   @query_attachment_filename  NVARCHAR(260) = NULL,  

   @query_result_header        BIT           = 1,

   @query_result_width         INT           = 256,            

   @query_result_separator     VARCHAR(2)       = ' ',

   @exclude_query_output       BIT           = 0,

   @append_query_error         BIT           = 0,

   @query_no_truncate          BIT           = 0,

   @query_result_no_padding    BIT           = 0,

   @mailitem_id               INT            = NULL OUTPUT,

   @from_address               VARCHAR(MAX)  = NULL,

   @reply_to                   VARCHAR(MAX)  = NULL

AS 

INSERT INTO dbo.EmailQueue

(

    Recipients,

    Cc_recipients,

Bcc_recipients,

    Email_Subject,

    Email_body,

    Email_body_format,

Query,

Execute_Query_Database,

attach_query_result_as_file,

query_attachment_filename,

query_result_header,

query_result_width,

query_result_separator,

query_result_no_padding,

    profile_name,

    QueueTime,

    SentTime,

Importance

)

VALUES

(   @recipients,            -- Recipients - varchar(250)

    @copy_recipients,          -- Cc_recipients - varchar(250)

@blind_copy_recipients,

    @subject,            -- Email_Subject - varchar(250)

    @body,          -- Email_body - varchar(max)

    @body_format,          -- Email_body_format - varchar(10)

@query,

@execute_query_database,

@attach_query_result_as_file,

@query_attachment_filename,

@query_result_header,

@query_result_width,

@query_result_separator,

@query_result_no_padding,

@profile_name,          -- profile_name - varchar(250)

    CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME), -- QueueTime - datetime2

    NULL,           -- SentTime - datetime2

@importance

    )

The powershell that pulls it all together: 

Read your Microsoft Teams messages using Microsoft Graph and Powershell

 I suck at time cards. Part of it is that I'm jumping between 30 tasks at once - help this team, help that team, training, documentation, and my sprint work. At the end of the day I have a bare-ass notebook with a few cryptic lines about what I did today - if I'm lucky.  

But a lot of it is available elsewhere. We're all virtual now, so all my interactions are either voice calls, meetings, or chats.  I want to list out what I did in a given day so I can reconstruct it - how much time I spent helping whom.

Enter Microsoft Graph. It's an API that selects from it. There's a set of cmdlets that let you do quite a bit.  I really do wish the get-mgchatmessagedelta properly handled "-filter" to make this faster, but I'll cope.

This will rip through ALL your chats, then for each one get the most recent 200 messages, filter it down to the past day or two, then toss it out on a format-table. 

My pain, your gain.  It's always fun when there's 2 google results for something. 

Install-Module Microsoft.Graph
Import-Module Microsoft.Graph.Teams
$RequiredScopes = @("Chat.ReadBasic", "Chat.ReadWrite")
Connect-MgGraph -Scopes $RequiredScopes
#at this point a browser window should appear - allow it.
#I had to find my user id in graph explorer UI by running GET   V1.0    https://graph.microsoft.com/v1.0/me
#unsure how to get it otherwise - but you don't need it with get-mgchat
get-mgchat
#take one of those IDs
#let's look at this chat:
get-mgchat  -ChatId 19:deadbeefb2d949d88d4455f5279e5d8b@thread.v2
get-mgchatmessage -ChatId 19:deadbeefb2d949d88d4455f5279e5d8b@thread.v2 

#this nests and walks through properly, strips HTML, but lord this will be slow.  And shows more than one line per message, even when I try now to.
#By default you can get all your chats by running get-mgchat.  -all and -pagesize 50 is required for the module to paginate the request and get you everything. But in my case it grabbed all 2000 chats. The -first 5 is for testing.
$tzone = Get-TimeZone  # conversion from GMT to local time. https://jdhitsolutions.com/blog/powershell/7962/convert-to-local-time-with-powershell/
$mychats = get-mgchat -all -PageSize 50  |select -first 5
$all_chat_info = @() #force-setting to an array
$all_chat_info = foreach ($chat in $mychats) { 
    $chatinfo = get-mgchat -ChatId $chat.id #get base details about the CHAT itself
    #set some details about the chat itself for the later query
    $chatname = $chat.Topic
    $members = $chat.Members
    $chattype = $chat.chattype
    #now get every message from that chat since midnight yesterday.  Note LastModifiedDateTime is GMT.    The jdhit page says -($tzone...), but all I had to do was .tolocaltime() ... I think.
    #the -top 200 -pagesize 50 is to get the most recent 200 messages, and again you have to paginate.   
    $recentchatmessages = get-mgchatmessage -ChatId $chat.id -top 200 -pagesize 50 |where {$_.LastModifiedDateTime.tolocaltime() -gt (get-date).date.AddDays(-1)} # all from after midnight yesterday |select -first 5
    #and now use select expression to add the above fields and parse the below fields, stripping out HTML (but I can't seem to only get the first line in OGV)
    $recentchatmessages | select @{Label='LastModified';Expression={($_.LastModifiedDateTime.tolocaltime())}}, @{Label='ChatName';Expression={($chatname)}}, @{Label='members';Expression={($members)}}, @{Label='ChatType';Expression={($chattype)}}, 
    @{Label='From';Expression={($_.from.user.displayname)}}, @{Label='Body';Expression={ ($_.Body.content -split '\n')[0] -replace '<[^>]+>',''}}
    #@{Label='From';Expression={($_.from.user.displayname)}}, @{Label='Body';Expression={( ($_.Body.content -replace '<[^>]+>','').split([Environment]::NewLine)|select -first 1)}}
}
$all_chat_info|format-table 
#and now close/disconnect
Disconnect-MgGraph

Xquery - a surprising difference between Azure SQL Database and Azure Managed Instance with Quotes

I'm moving processes into Azure.  Given that it's me, probably poorly. But I'm using this as an opportunity to improve things where possible, specifically looking at moving some processes to Azure SQL Database. 

Why? I love them for several reasons: 

• Simpler
• Cheaper
• Quick to create
• Quick to modify the instance (seconds, not hours)

Yes, there are still gotchas (Elastic Jobs, no cross-db queries, no CLR, can't replicate from it), but overall? Soooo nice.

---

I was trying to get some Xquery parsing working, and ran into a weird error:

"CONDITIONAL failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'"

Weird.  Doubly so because this code has worked on-prem for a decade, and the Managed Instance is running it now.  And because the SP specifically has a SET QUOTED_IDENTIFIER ON; 

Okay, let's take alook online.

https://stackoverflow.com/questions/47091181/jobs-failing-due-to-incorrect-settings-quoted-identifier

Yeah, that's the problem.  And indeed, my code has similar xquery to the example:

set @myval = @xml.[value]('(*/A[B="C"]/D[E="F" or G="H"]/I)[1]', 'varchar (35)');

What's the fix? Use two single-quotes instead of the double quote.

set @myval = @xml.[value]('(*/A[B=''C'']/D[E=''F'' or G=''H'']/I)[1]', 'varchar (35)');

We stand with Ukraine

growing up, my Baba always pronounced it weirdly to me - you-craw-een. Unsurprisingly, this turns out to be the right way. watching the footage,i cant help but feel for them. I don't know how this is going to shake out, but they're in the right and russia is wrong.