Dynamic Data Masking (DDM) – Mask query results

Microsoft SQL Server

Enforcement of the new GDPR will begin in May 2018. One of the cornerstones in GDPR is that an organization must be able to protect sensitive personal information. This can be achieved in many ways and at many different levels, from infrastructure (e.g. monitoring traffic) to protecting individual database records. To protect individual data records, some different techniques are available. Examples are encryption and masking. Masking can be done in a permanent way (e.g. actually replacing a name with random characters), or it can be done dynamically (not replacing the original data, but to mask information at query time). SQL Server 2016 ships with a capability to do this dynamic data masking, DDM, and in this article I’ll cover the basics.

Read more →

From SQL Server to MSMQ using PowerShell

windows

How do you get records from a SQL Server table into a MSMQ queue as separate XML messages?

A commonly suggested way to communicate with MSMQ from within SQL Server is to create a CLR assembly. See for instance this article that has a great coverage of how to use CLR in this scenario. Note however that you have to import the System.Messaging.dll as unsafe for this to work. And you have to enable CLR obviously.

If this is not an option, another way to do it is to use PowerShell. Which is what I will cover in this demonstration.

Read more →

SQL Agent job logging with tokens

Microsoft SQL Server

A useful feature in the SQL Agent is the possibility to use tokens. Tokens are a number of system variables (e.g. $(ESCAPE_SQUOTE(DATE) that contains the current date) that gets expanded/replaced with a value when the job is run. An obvious use of tokens is to create a new log file with today’s date and time in the file name each time an SQL Agent job is run.

Read more →

Solvency II QRT Names

eiopa logo

On and off I am working in SolvencyII projects (financial reporting to governments for companies in the insurance industry). There are a lot of QRT-reports (Quantitative Reporting Templates) that has to be reported to the local authorities, and a while ago Eiopa decided to change the names on all the reports.

Almost all my code in the SolvencyII projects is based on the old names, but all new and changed directives are based on the new names. And of course I keep forgetting the new names…

Read more →

Toggl Time entries to SQL Server

Toggl is a very simple to use cloud based time tracking application. Time entries on projects and clients can be submitted through the web page or through mobile and desktop applications. Time spent by the team can be viewed in the different reports that can be accessed via their web page. There is a free version that handles much of the time tracking needs, and there are more advanced paid versions as well.

If you want to integrate the information stored at Toggl with your own enviromnent, you can use their API. One way is to download time entries to your own SQL Server database, which is the method I’ll present here.

Read more →