SQL Auditing sucks

Back in SQL Server 2008 Microsoft introduced auditing, specifically the Database Audit Specification. It’s pretty good – despite the title I do actually think its nice feature, it pretty much works and doesn’t have much of a performance impact, my problem is not much love has gone into it since it was released in SQL2008. It claims to meet various regulations such as the EU data Protection Directive, HIPAA, PCI DSS and to be fair, I’m sure it does. Assuming to implement it correctly.

For example, I have it enabled on ACS.DimPersonSensitive – as you can guess, it holds sensitive information about a individual. If I want to break it, all I need to do is use parameters, ie
declare @per_id int = 123456
select * from [ACS].[DimPersonSensitive] where per_id = @per_id

Now if I look at the logs, I literary see @per_id, so I have no idea what id that was pass. There is no column in the audit logs that tell me what that parameter was.

The other issue I was hit with today was SSDT, it really doesn’t support it. Here is the error I got today

error-audit-ssdt

It’s basically blocking me from changing the table because it has auditing enabled on it. No nice way around it. It’s another Pre-Deployment script. This isn’t terrible. The terrible bit is it doesn’t reapply the auditing once you’ve removed it and updated the table, despite the auditing being defined in SSDT project.

Feature request – Add Azure Data Catalog support to SSDT

One of the annoy parts of building a warehouse is building the staging database, its an important first step. The staging database is replica of the source systems. BIML can provide away to accelerate this process, however its not perfect. BIML is designed to create SSIS packages – and it does this very, very, well. SQL database objects, not so well. SSDT does this well.

The first step in the designing a warehouse is discovery, Azure Data Catalog is an excellent tool for doing the discovery, it allows you to connect to a wide array of data source types and gather the meta data that can be used to build the staging database.

My feature request is to add support for Azure Data Catalog to SSDT. In a nutshell add a item to this menu list

Feature request - SSDT

 

Getting a list of AD Groups and their members using PowerQuery

The Power Query Formula Language (informally known as “M”) is a powerful mashup query language optimized for building queries that mashup data. It is a functional, case sensitive language similar to F#,  which can be used with Power Query in Excel and Power BI Desktop.

A few days ago I got asked to produce a list of users (and their email address) in a number of AD Groups. I already had a SSIS package that had a script task to pull this data from Active Directory and push it into a SQL database and we have a PowerShell script to get the same data in our code repo. After work I set about repeating it but using PowerQuery.

Apart from one quirk the process was pretty straightforward. PowerQuery has built in support for Active Directory. Just click the Data tab, then select New Query > From Other Sources > From Active Directory

PowerQuery - Excel > Menu

This will then pop up with a windows asking for you to select your domain, assuming your machine is joined to the domain and your logged on as a domain user, this should be prefilled in with the correct domain.

PowerQuery - Excel > Connection

The next window just asks you confirm your credentials, you’ll most likely want to use your current windows user.

After you’ve connected to your Active Directory, you’ll be able to navigator, your be able to select your domain from your forest and then select the object you want to query. For this example, select your domain then select group, then click Edit.

PowerQuery - Excel > Navigator

This will then open up the Query Editor

PowerQuery - Excel > Query Editor

This will then list out the groups, or at least a sample. Click on the left\right arrow on securityPrincipal column header, this then bring up a filter window, select sAMAccountName and click on OK. This will give you the friendly name your more then likely to know the groups by.

Now its a question of filtering the list – if you right-click on the sAMAccountName and select Text Filters > Begin With. You can select one of the other options, if you make a mistake or you want to refine it, on the right-hand side, you have Query Settings > Applied Steps, if you click the cog next to Filtered Rows your get

PowerQuery - Filter

this hopefully doesn’t need any explanation. You can of course filter on other columns if required, such as OU group.

Adding the users involves expanding the member

And the final M script looks like field like we did with securityPrincipal until we get to the individual user object.

The last point is where I hit a few snags. Firstly, I couldn’t expand into the user object, luckily, clicking on Advanced Editor on the Home tab revealed the M code. I’ve included my code below which I hope will help.

I’ve personally found this a useful dive into PowerQuery, hopefully you have too 🙂

Is today Day Zero or Day One?

This might sound pretty pointless and silly but when talking about a 20 working day SLA a day can mean alot between a massive fine or compliance.

During one of my driving lessons with Gary we were talking about his recent streak of passes. The conversation then lead on to pass rates and us wondering how this broken down – for example, by gender, ethnicity, age, driving instructor type (intense driving school \ chain \ individual driving instructor \ family etc).

Still curious, I decided to send off an a FOI request, before doing so, I went looking to see if someone else has which lead me to find some rather shocking data (see the bottom of the post). For those who don’t know, FOI stands for Freedom of Information. This is where Joe Public can request (reasonable) information from the public sector – after all, it is your (tax) money that they are spending. Logging FOI is dead straightforward thanks to WhatDoTheyKnow. Anyway, the Freedom of Information Act requires them (public sector folks) to reply within 20 working days.

First question. If I log a call on Sunday, what day is day one? So firstly, Sunday doesn’t count. It’s not a weekday. So Monday’s day 1? That’s what I would think, and it’s even what WhatDoTheyKnow says, but the DVLA say its the Tuesday. After a few emails between them both, I went off an ask ICO, Information Commissioner’s Office who’s responsible for these things. Here is their response

counting the first working day after a valid request is received as the first day

So, if you log a FOI over the weekend, they receive it on the Monday, that’s Day Zero. Day One is actually the second day.

They also say

Working day means any day other than a Saturday, Sunday, or public holidays and bank holidays; this may or may not be the same as the days they are open for business or staff are in work.

 

So its important that public sector don’t close for extra days as they risk eating into that time they have to respond to FOI request and failure to reply can (ultimately) result in fines.

Going off topic – there are some determined people in Suffolk who really want to drive.

Source: FOI request via WhatDoTheyKnow

 

Error building SSDT package

Today my colleague had a problem opening our BI solution, the solution had multiple projects, including 3 SSDT projects. Although the project builds correctly on both my machine, the build machine and another colleague machine it refused to build stating that the reference to the object in another project was invalid.

After thinking for a few moments, I remembered I had seem this before. The problem was a bug in SSDT. The solution was to click on Tools > Extensions and Updates, then click Updates from the left-menu on the window that appears.

Logging ConnectionStrings in SSIS

Another day, another reference to an old Jamie Thomson blog post – today it was getting the ConnectionString to output to the information. Admittedly I wasn’t looking for how to fire the connection string into an information event, just the ConnectionString, still another great idea and its useful for debugging.

 

Enterprise vs Enterprise Core

This month saw the release of SQL Server 2016, which from a BI\Report point of view is huge. Once we had access to it

I spotted something odd.

Now, in SQL Server terms there are two types of licensing

  • Server license + CALs
  • Cores (processors)

Expect for Enterprise, you can only get Core licenses.

The next question was, have they made a edition for Windows Server Core, a cut-down version – but the files sizes are the same, so this was unlikely, Microsoft also hadn’t made any statement about a cut-down version (which you’d expect), so again, this was unlikely.

Once again, Twitter came to the rescue, more precisely Joey D’Antoni did. The answer is legacy. Any old Microsoft licensing agreement with software assurance (SA) can get the latest version. This means that anyone who had an old SQL Server Enterprise Server\CAL license is now on the core based licensing – there are a few gotchas which Joey refers to – your still limited my the number of CALs, but your also limited to a max of 20 cores.

So in short

  • SQL Server 2016 Enterprise Core is limited to 20 cores and is for legacy license holders.
  • SQL Server 2016 Enterprise isn’t limited.

MDSCHEMA_CUBES DMV not returning all cubes

I had previously created a SSIS package with a simple Process Full on the SSAS MD database, however, as the project has progressed this hasn’t been ideal. Its basically all or nothing. In order reduce the damage a failure can cause I’ve setup a Process Full for each dimension and cube so each is processed independently of each other. I’ve used the data from the Analysis Services Dynamic Management Views, or DMV for short, which I’ve used for the documentation to feed the foreach loop.

However there is a major problem with the DMVs. They only list processed cubes (and their dimensions). So how do you get a list of unprocessed cubes (and their dimensions)?

Answer? Using the Analysis Management Objects (AMO). This will return the same list as SQL Server Management Studio (SSMS), rather then just the processed, like the DMV or Excel lists. I’m currently trying out a few ideas to find the best solution – so as a script component (source) or a stored procedure. As the saying goes, it’s production ready, but its not GitHub ready – not yet anyway.

Thanks again to Chris Webb for pointing me in the right direction

 

New laptop, error running SSIS package

So today I went to run a SSIS package on my new laptop and bam, error message.

Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.   —> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.       at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(Object vSource, Boolean vbSourceIsLocation, IDTSEvents100 pEvents)     at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events)     — End of inner exception stack trace —at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events)     at Microsoft.SqlServer.Dts.Runtime.Project.LoadPackage(IProjectStorage storage, Package package, String streamName, IDTSEvents events)     at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events)     at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package()     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)

Translates as I hadn’t installed the Oracle client and the Microsoft Connectors v3.0. Speaking of the connectors, the Attunity Oracle adapters are amazing, if your connecting SSIS to Oracle, these are a must (especially as they are free!)