Clickbait and tracking cookies

What is it and why it is dangerous to you?

You decide to check the weather on your computer, and for convenience, you click the new Windows 10 icon in the taskbar.

Your eye is caught by the Microsoft news and articles and before you know it, you’re looking at things that you had no intention of looking at. This could happen from pretty much any web page, as most pages have some adverting because, well, because they can!

Your interest in the weather is a distant memory and suddenly you realise that hours have passed by as you browse health, sex, cars, finance, holidays, or entertainment articles etc.

How did this happen? You never used to follow the adverts.

Websites have long had adverts and you have generally ignored them but now you can’t, because now they look interesting, not because you have changed, but because the content changed, and the articles got more interesting. Why?

Many assume this is a result of Amazon shopping, Google and Bing search history and that damn voice assistant that you know is listening to your every word. It probably isn’t. At least, not just those services.

Click just one of those pages and they start logging your activity and profiling your personality. Over time they build an increasingly accurate picture of your interests. Connected (partner) websites using get to know what you like, even if you have never visited their site, and if they purchase the advertisement placeholders in affiliate sites, they can target you with their product, articles, and propaganda.

Every time you click (or linger too long) on an affiliate site, your actions may be logged without you even knowing it is happening. You are being profiled!

First; A walkthrough of how the evil magic happens.

Harry Potter

I will use a fairly innocuous example: Harry Potter. Imagine you are browsing for something and an advert arises: “Harry Potter actors, then and now?”. Images of a fresh-faced Daniel Radcliffe with his trademark round glasses, wand and Hogwarts uniform appear next to a bestubbled, spiky-haired, 30-year-old man without glasses, and this draws your attention, and you can’t help but feel a pang of nostalgia from when you watched Harry, Ron, and Hermione getting one over on Draco, Snipe and he-who-must-not-be-named. You click the link!

The sites you visit from these types of links are always very short on content with a brief description and one or two relevant images at most per page. But there will be many pages, each accompanied by the inevitable moving buttons (hasting clicking will whisk you away to the target of popup advert that somehow always loads too slowly, despite your blindingly fast broadband connection, pushing the buttons down a few centimetres).

Eventually, you learn the patience of waiting just long enough for the adverts to load and the page to stop jumping around so that you can quickly navigate to page 34 using the NEXT button. It’s like a weird internet game (you want to know what’s on page 34 now don’t you?). Can you even remember the character you were wondering about how they have changed?

Maybe you’ll get bored after a few pages and click some other image.

As soon as you enter the site, you’re asked to accept cookies.

You have seen this prompt a million times thanks to EU cookie directives and now every site has an annoying opt-in to accept cookies and a disclaimer that nobody reads and few understand, so you just click Agree, Next or whatever gets you into the site and off you go; This is where the tracking starts!

The truth is, the tracking probably started months ago on another machine, pc, phone, tv etc. Your current device may just be additional.

For reference: here is the URL of a site that shows the then and now actors in the Harry Potter series.

https://<<Removed>>.com/trending/40-harry-potter-stars-who-look-entirely-different-without-their-costumes-sdfsfsd-fdgd

And here is a screenshot from the page for Harry (er, I mean Daniel Radcliffe).

Near the usual PREV and NEXT buttons, we see some adverts for other things that you might like.

Hang on, is that Emma Stone wearing a see-though knitted sweater? We are going to have to investigate that!

Actually, Let’s not. We all know what will happen. This is classic click-bait.

Instead, let’s right-click the image and choose inspect from the context menu, and see where the link goes in the dev tools? (blurred so you don’t get the actual link from me, because I disapprove of this)

(Even the image name doesn’t hide what it is)

Search the internet for “Emma stone 03/06/2021”, and you may find the real image, which looks a bit different.

The thumbnail image comes from the fake news and porn site “cellebrity jihad”, is resized by taboola.com, and takes you to a trash site with more click-bait than you can shake a shiny stick at.

https://<<Removed>>.com/top-10-most-beautiful-women-in-the-world

Appended to this URL was a link tracking id for the referral site (they must get paid too).

Of course, you won’t find the see-through sweater image in the target site; That is only shown in the thumbnail image (that’s the definition of clickbait). The fake wonky boob version of Emma is quite unrealistic when it isn’t a thumbnail.

The target site presents thousands of similar articles, each with multiple pages in the familiar list format and each page containing more clickbait and adverts, to keep viewers totally hooked.

Basically, it’s just a horde of lists, from the sublime to the ridiculous.

  • Top n most x in the world
  • n ways to do x with y
  • Photos taken in supermarket/airport/beach etc.
  • n Cancer Symptoms You Are Most Likely to Ignore
  • Top n Reasons Why Cats Follow Us to The Bathroom
  • n HARD TRUTHS YOU HAVE TO ACCEPT TO BE SUCCESSFUL
  • Take This Each Morning & Flush Away Belly Fat

The more you scroll on the page, the more articles you see. The site uses an infinite-scroll technique whereby an AJAX script detects when you are near the bottom of the page and fetches more content from the server which is appended seamlessly, providing an infinite page of adverts/articles.

The site eventually includes a disclaimer, stating its information “is not intended or implied to be a substitute for professional health, nutrition, or diet advice”. Really? Due to the infinite scroll, you’ll be hard-pressed to find this text though.

If you don’t scroll, parts of the page will auto-update anyway to change the currently displayed content (that you obviously aren’t interested in because you haven’t clicked anything yet), with new content that they hope you are interested in. Anything to get you to click!

  • When you click,  your interest is logged, and your profile is updated.
  • When you don’t click, your lack of interest is logged, and your profile is updated.

Back-end server algorithms identify articles that do and don’t work for you by logging everything against your anonymous tracking id.

Er, tracking id what?

This is how their artificial intelligence (AI) profiles you. Your name is “46281832-ea6c-448e-86b4-2e40ea101fbc” (or similar). This is a GUID (globally unique identifier) that uniquely defines you as a visitor. When you ignored all of the cookie warnings (of course you did), a cookie was created in your browser cache containing this id. That is probably all it contains, nothing suspect, just something that the website can identify you by the next time you visit their page or an affiliate (of which there may be thousands). The browser doesn’t need to store anything about you because you agreed to the cookie policy. All it needs to do is link your hardware and browser to the data it stores in the server.

Now, every time you visit any page affiliated with this system, they will read the cookie, identify you, and continue to profile you and your interests. Do you keep clicking the beautiful women, the fast cars, the fitness tips, or the health and ailments etc? They know.

The hope is that once you are on the site, you will just click and click until it is time to go to bed. Then do it again tomorrow, next week etc.

Every page you click will show adverts and every advert displayed will earn them money. Very little per advert (actually, very little per thousand of page impressions) but if thousands of people click multiple times per day… you do the math. 

They also hope that you will click on some of the many adverts too, as that’s also a money-spinner!

Who cares?

So they have some id that proves a random somebody clicked a few (dirty) images right? Why would you care?

Did you read that cookie policy? I thought not.

You were warned

You were given a choice (sort of) and you chose to accept the cookies didn’t you?

If it was a simple YES or NO without consequence then everybody would say no, but then the site wouldn’t work properly, but you weren’t given a YES / NO, you were given a YES or something vague. And who are these partners? Do you feel that your privacy is valued, or don’t you care?

These options are designed to make you just accept everything because you know… TLDR;?

You have to accept the cookies before you even know what the site is about and whether or not you can trust it.

What did you sign up for?

Instead of YES. CONTINUE, let’s try HELL NO! (aka: More Options)

First up we see tracking geolocation. You don’t want people knowing where you live, so keep this off.

Hang on; Why is that scroll bar slider so short? (A short scroll control means a large scroll area)

That’s the small print! This is the bit where that EU cookie directive states that a site must display all parties (partners) that have access to the data, and what the data can be used for etc. Companies design these to be small and hard to use, and they appear on first-entry only, in a small scrollable window with loads of text displaying just a few lines at a time. how do you change your settings if you aren’t happy with your choice?

For this website, there were 259 affiliated partners that can access the data stored about you.

Let’s have a quick look at some other bits you probably missed.

“Technically deliver ads or content: Your device can receive and send information that allows you to see and interact with ads and content.”

“Match and combine offline data sources: Data from offline data sources can be combined with your online activity in support of one or more purposes”

“Link different devices: Different devices can be determined as belonging to you or your household in support of one or more of purposes.”

“Receive and use automatically-sent device characteristics for identification: Your device might be distinguished from other devices based on information it automatically sends, such as IP address or browser type.”

“Vendors can: Create an identifier using data collected automatically from a device for specific characteristics, e.g. IP address, user-agent string. Use such an identifier to attempt to re-identify a device.”

This last point is particularly interesting in that it highlights that even if you delete the cookies, your IP address (web address of your broadband router) or browser id may still be logged and used to identify you.

Still don’t know why you would care?

Do you ever think that someone has hacked your phone, listened through the microphones, tracked your activity? Did you blame Amazon, Google and Facebook when you’re thinking about something and then see ads for it, even though you haven’t specifically looked for it on your hardware? This is machine learning (part of AI).

In this example, with a single click on entering an unknown site, you have granted permission to 259 potentially suspicious companies to identify you and your interests as a collective. Remember that you have not signed into this companies website. Every time you return they will identify you based on cookie and/or IP address. This is not an isolated instance. Loads of websites do this.

Will any of these companies that you have never heard of, and never contacted you before, start spamming you or pushing their products onto your Facebook, Amazon, Google, general browsing sites?

If a website knows anything additional information about you, name, gender, location, job, friend list, email address, income, or possibly sensitive, do you want them to also gain access to this tracking profile and how it categorised you?

If the categorisation is based on a shared pc, IP address etc., does this reflect you in the way you would like?

How have you been categorised? Is it a correct assessment of your personality, traits and beliefs? You haven’t logged in; whatever they have decided about you cannot be altered.

When you search for something on the internet and cannot find the results you want but just keep getting the same shit as last time you looked, but your mate gets different, possibly better results, do you know what that is? Profiling.

The targeted advertising and articles issue raised by the Facebook Cambridge Analytica scandal is a direct result of this problem of shared tracking. People were manipulated by marketeers, governments and large corporations.

  • What will they do with the data?
  • Will they sell your information?
  • Will any of them identify you and join the dots. Will they sell that too?
  • Who will they sell it to?

That’s none of your business, apparently.

Protection

Apart from not visiting these sites and never succumbing to click-bait, the best protection to regularly clean the cache and relevant cookies, however, there are some sites we want to retain cookies on so clearing everything and faffing with cookie deletion site-by-site is a pain.

I suggest a good firewall, anti-virus and cleaner is a good start. At least this way, some malicious tracking cookies will be prevented. I use McAfee Small Business – PC Security suite, but it isn’t the best and there are plenty to choose from.

McAfee blocked some suspicious activity from the site mentioned in this article. This site appears to be linked on many other sites. I got to it from Windows 10 system tray!

Please note: The websites mentioned in this article are just a few of many such websites, and as such, this article is not intended as a slur on them or any specific individual or organisation, but purely to provide an informational article to educate in the safe use of the internet and assist with the understanding of how tracking cookies work and the dangers of not managing your own privacy.

Posted in Uncategorized | Leave a comment

Use Microsoft Teams outside your organisation

Do you want to use Microsoft Teams outside your organisation?

Simple right? Just log in with a different account.

Trouble is, you already use teams inside your organisation, and that means you already have a Microsoft account signed in to the Teams app, Windows, all of your browsers and apps, etc. Singing out and singing back in again is a pain, problematic, maybe impossible.

Incognito / In-private to the rescue

The most obvious solution is to use the web-based app in a browser that is hidden from your local logins and for this, you use Incognito in Chrome or In-Private browsing in Edge (other browsers are available). e.g. Open Chrome, click the ellipsis (three dots in the top right) and choose “New Incognito Window”.

Now you can go to the Microsoft Teams website (https://teams.microsoft.com) and sign in using your Microsoft credentials for the remote account and…

“To re-enable the application the tab needs to be refreshed”? What the hell does that mean?
You can repeatedly click the “I’m back” button, but it does nothing but waste more of your time (a link to documentation might help here Microsoft).

How do we fix this?

There is a really bad solution that many have blogged about and you MUST NOT DO IT.

DO NOT TURN OFF BLOCK THIRD PARTY COOKIES!

That is dangerous. Global settings affect all sites and in this age of ransomware attacks, you don’t want to open this feature to all sites.

DO NOT CLEAR YOUR CACHE! This won’t help. Many have stated this as a solution but it does nothing apart from losing your history and the helpful cache data.

The good solution

This issue is purely a result of Chrome and Edge correctly protecting you from malicious third-party sites (like tracking cookies), hiding in other sites that either track your movement or attempt to steal your passwords, so you do want to allow third-party cookies, but only on this one site, not all sites.

I’m going to show you how to allow third-party cookies for just the Microsofr Teams site.

Open Chrome in standard mode (not incognito) and go to

Settings -> Privacy and security -> Cookies and other site data

Scroll down to “Sites that can always use cookies” and click the Add button

Quick link (chrome://settings/cookies)

In the Site textbox, type https://teams.microsoft.com

Check the box that says “Including third-party cookies on this site

What you have just done is allow a single trusted site to use third party cookies.

If you see the “Current Incognito session only” (shown above) then you haven’t been paying attention; I said don’t open in incognito mode to edit settings. Don’t worry though, just make sure you don’t check it and you’ll be fine. This box only appears when you are in incognito, and it allows you to make settings changes that only last until you close the browser window and you would have to do it all again next time you want to run teams.

Microsoft Teams will now work in an incognito window in Chrome.

  • To open Teams for your local organisation use a Standard Chrome window (or use the app)
  • To open Teams for a remote organisation use an Incognito Chrome window (app won’t work)

Open incognito window quick link (ctrl + shift + N)

https://teams.microsoft.com

Sign in using your remote organisation login details. Use Teams.

Ignore any prompts to save your sign-in details or remember the browser for n days because the incognito window won’t do it anyway. That’s the whole point of incognito. It’s not just for hiding your nefarious browsing history!

Benefits of using teams like this are

The ability to connect to many other organisations Teams accounts

Leave the app signed in to your organisation

You can use screen share, video and text chat

If you normally connect to the remote organisation using a jump box (remote machine) and run teams in that, you will notice latency whereby people can hear themselves. This is caused by the slight delay in your speaker emitting their voice, which is picked up by your microphone and played back to them. Teams attempts to cancel this noise but for this to happen, latency must be tiny. When operating through one or more remote desktops the latency becomes too great and cancelling feedback sounds would probably clip wanted sounds too, so the process fails and feedback is heard. By running teams locally this issue is completely removed.

Posted in Developer stuff, Operating Systems, Other stuff | Leave a comment

Time to wake the blog

It’s been a very long time since I showed an interest in this blog. Too long.

I have been very busy and for this I am thankful. Work and family have kept me very busy and I was one of the lucky self-employed people who worked pretty much throughout the pandemic lockdowns. In the few months that I wasn’t working, I built a home office, where I seem to spend most days.

Recently I have done a lot of data engineering projects. I love data engineering and software development equally and am always a little torn between them because this IT industry constantly wants to pigeon-hole people into being specialists. Well, tough! I am proud to be multi-skilled. Sometimes I act as a solution architect too (probably my favourite position tbh.

This Word-press site has been up for about 12 years now and still runs on the older classic model. The past articles are probably of little use to anybody and I think I am going to create quite a few new ones in the next few months.

Initially, these will probably be about Power BI because that’s what I am working with at the moment. Not so much the pretty visualisations; More the back-end code and infrastructure. Getting deep and dirty in DAX Studio, Power Query and T-SQL.

I have also done quite a bit of Azure data lake ingestion, analysis with Azure Databricks and Azure Data Factory too, and still found time to create a rather useful file sorting manipulation utility using .net core 3.1 in a Windows application. In fact, I have been so busy, I think I need to document it just to prevent myself from moving on and forgetting everything I have learned, bu it is late in the day and I have to be up for another hard day at the keyboard tomorrow, so let’s see what happens after that eh?

My first task is to try to remember how to work Word-press.

Posted in Uncategorized | Leave a comment

Output sub query as CSV when Joining tables (one to many relationship)

This will show you how to get something like this from a table join and then I’ll explain how the SQL works.

Name csv
UK London,Manchester,Birmingham
USA New Your,Washington,Kentucky
France Paris,Leon,Frejus

Suppose we have two tables

-- Create base table
CREATE TABLE [dbo].[TableA](
[id] [int] NOT NULL,
[Name] [varchar](50) NULL
)

-- Create secondary table with foreign ley
CREATE TABLE [dbo].[TableB](
[fk_tableA] [int] NOT NULL,
[Detail] [varchar](50) NULL,
)

Lets put countries in the first one and cities in the second

-- Create test data (countries)
INSERT INTO TableA (id,Name) VALUES (1,'UK')
INSERT INTO TableA (id,Name) VALUES (2,'USA')
INSERT INTO TableA (id,Name) VALUES (3,'France')

-- Create secondary test data (cities)
INSERT INTO TableB (fk_tableA,Detail) VALUES (1,'London'),(1,'Manchester'),(1,'Birmingham')
INSERT INTO TableB (fk_tableA,Detail) VALUES (2,'New Your'),(2,'Washington'),(2,'Kentucky')
INSERT INTO TableB (fk_tableA,Detail) VALUES (3,'Paris'),(3,'Leon'),(3,'Frejus')

Now lets read the data from these tables in the usual way. That is: joined using INNER JOIN and output as a big multi row list.

-- Show the data linked in the data in usual way
SELECT A.Name, B.Detail
FROM TableA A
INNER JOIN TableB B ON B.fk_tableA = A.id
ORDER BY A.Name, B.Detail

Name Detail
France Frejus
France Leon
France Paris
UK Birmingham
UK London
UK Manchester
USA Kentucky
USA New Your
USA Washington

What if we want to see this instead

Name csv
UK London,Manchester,Birmingham
USA New Your,Washington,Kentucky
France Paris,Leon,Frejus

We can join the tables using this weird query with XML

SELECT
A.Name
,STUFF(SELECT ',' + CAST(B.detail AS VARCHAR(MAX))
FROM TableB B WHERE B.fk_tableA = A.id
FOR XML PATH('')),1,1,'') AS csv
FROM TableA A
GROUP BY
A.id, A.Name

Why does this work?

Well obviously the outer query just gets the list of country names and groups them by name so ignore that

Looking at just the inner query with the STUFF and XML removed

SELECT ',' + CAST(B.detail AS VARCHAR(MAX)) FROM TableB B WHERE B.fk_tableA = X

For each value of X this simply gets “, value of detail” so where X=id if UK we get…

,London
,Manchester
,Birmingham

FOR XML PATH(''))

simply wraps this in to a single line of text. The ” bit prevents the surrounding XML data node

“,London,Manchester,Birmingham”

Actually it goes into an automatically named xml column that SQL can easily convert to a string if it needs to

for xml path

Note that the text has a leading comma which we do not want. We are going to get rid of this with the stuff but before we do that we need the xml converted to varchar as stuff is a string function.

CAST(B.detail AS VARCHAR(MAX))

Now we have string “,London,Manchester,Birmingham” so get rid of the leading comma

Some of you may ask “Why did we not use SubString?”
Substring requires a length argument and in this case we do not know what this is. We could do it this way but we may get unpredictable results including string truncation. e.g. the following will work fine.
SUBSTRING( ',London,Manchester,Birmingham' ,2,100)

but this would be disastrous
SUBSTRING( ',London,Manchester,Birmingham' ,2,10)

By using Stuff we are simply inserting nothing in to position 1 and replacing 1 character which is the comma

i.e. ‘,London,Manchester,Birmingham’ becomes ‘London,Manchester,Birmingham’

So now the outer query handles the grouping in to the master data presented by the base table and the subquery substitutes the value of the outer column in to the inner query.

Its quite simple really but I think the combination of statements as a whole is a little daunting, particularly the XMl bit that throws people in to believing they are using XML in some mysterious way which although you are, you are really just using a feature of the conversion to XML (smashing data in to a single unit) and no actual XML is involved.

Here’s the code as a single block that you can copy and paste in to SQL Management Studio. Just create a new test database, open a new query, cut, paste and run this entire script to make the tables and show the output.

Complete example code

———————————————————

-- Create base table
CREATE TABLE [dbo].[TableA](
[id] [int] NOT NULL,
[Name] [varchar](50) NULL
)
-- Create secondary table with foreign ley
CREATE TABLE [dbo].[TableB](
[fk_tableA] [int] NOT NULL,
[Detail] [varchar](50) NULL,
)

— Create test data (countries)
INSERT INTO TableA (id,Name) VALUES (1,’UK’)
INSERT INTO TableA (id,Name) VALUES (2,’USA’)
INSERT INTO TableA (id,Name) VALUES (3,’France’)

— Create secondary test data (cities)
INSERT INTO TableB (fk_tableA,Detail) VALUES (1,’London’),(1,’Manchester’),(1,’Birmingham’)
INSERT INTO TableB (fk_tableA,Detail) VALUES (2,’New Your’),(2,’Washington’),(2,’Kentucky’)
INSERT INTO TableB (fk_tableA,Detail) VALUES (3,’Paris’),(3,’Leon’),(3,’Frejus’)

— Show data
SELECT * FROM TableA
SELECT * FROM TableB

— Show the data linked in the data in usual way
SELECT A.Name, B.Detail
FROM TableA A
INNER JOIN TableB B ON B.fk_tableA = A.id
ORDER BY A.Name, B.Detail

SELECT A.Name
,STUFF((
SELECT ‘,’ + CAST(B.detail AS VARCHAR(MAX))
FROM TableB B WHERE B.fk_tableA = A.id
FOR XML PATH(”)),1,1,”) AS csv
FROM TableA A
GROUP BY A.id, A.Name

———————————————————

Posted in Uncategorized | Leave a comment

Creating and Migrating an encrypted database.

This is a simple tutorial on how to…

  • Create a database
  • Setting up encryption
  • Add encrypted data to a table
  • Migrate the encrypted database to another server

The tutorial is supplied without warranty and David Bridge and David Bridge Technology Limited accept no responsibility for its use. If you wish to follow the code here then you must do so on a test machine in a controlled and safe environment.

Creation and population

To start with we will create a new database called “EncryptionTest” and add an employee table. This employee table is going to hold sensitive information about each employee’s income. We do not want unauthorised people to read or alter this information. Without encryption the sensitive data would be stored in an integer or string column but for encryption we need to store the data in a binary column.

The size of the binary column will depend on the size of the source data but it’s not a 1 to 1 relationship: You need to experiment to determine the size of the varbinary column (max 8,000 bytes). For the following example varbinary(300) will be way more than enough as it’s just going to store an integer.

CREATE DATABASE [EncryptionTest]
GO

CREATE TABLE [dbo].[Employee](
[id] [int] NULL,
[Name] [varchar](50) NULL,
[Salary] [varbinary](300) NULL
) ON [PRIMARY]
GO

Note that the table does not know that the data within it is encrypted; it’s just basic binary data. We are going to use a function to encrypt the data before we put it in to the binary column. If we simply write data to the column then the data will be stored as binary and can also be read as binary

To put data into the varbinary column we need to create a MASTER encryption key and to do this we need a master password. The following is not a good password but it’s good for an example.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyMasterPassword'

We also need to provide a subject name for the certificate that will be used to encrypt the data. Again, not a good name for a production system.

CREATE CERTIFICATE cert_sk_admin WITH SUBJECT = 'Admin key';

The Master password and the subject for the certificate are very important. If you lose then there will be no way to retrieve data from the database so you need to keep them very safe both from loss and prying eyes. Write them down on a piece of paper and lock them away in a sealed envelope in a secure fireproof safe and treat them like the crown jewels!

We now need to create another symmetric key that will be used to encrypt the data. This key will be encrypted using the certificate that we just created so there is no visible security on this key as the certificate was encrypted and stored in the database. This means that the code we will create later to read, write and update the table will not need to contain any sensitive data, just a reference to the stored certificate: Clever eh?

CREATE SYMMETRIC KEY sk_admin
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE cert_sk_admin;

As I mentioned earlier the table simply contains a binary column that knows nothing about the encryption. To store encrypted data in the column we open the database symmetric key then call a function that will encrypt the data. Then we simply write the encrypted data in to the binary column.

The encryption function encryptbykey takes the GUID of a locally stored symmetric key as its first parameter followed by the data to be encrypted. The guid can be obtained by calling the key_guid() function, passing in the name of the key.

Let’s populate the employee table with encrypted Salary details

open symmetric key sk_admin
decryption by certificate cert_sk_admin;

insert into employee (id, name, salary)
values (1, 'A' , encryptbykey(key_guid('sk_admin'), '50000', 1, '1'))
insert into employee (id, name, salary)
values (2, 'B', encryptbykey(key_guid('sk_admin'), '50000', 1, '2'))
insert into employee (id, name, salary)
values (3, 'C', encryptbykey(key_guid('sk_admin'), '50000', 1, '3'))

And now let’s read the table using a simple select statement that will display the binary data

SELECT *
FROM [EncryptionTest].[dbo].[Employee]

Id Name Salary
1 A 0x0013CFB6E4F55745BFAE11A22DEF9969010000004E20F5770518E0AD4…
2 B 0x0013CFB6E4F55745BFAE11A22DEF996901000000EA49FC95777D6A0A1…
3 C 0x0013CFB6E4F55745BFAE11A22DEF9969010000009E62C1816144EF6CEF…

Although all of the Salaries are the same value (50k), the encrypted data is different for each row stored. This is because each row uses a row specific constant (row authenticator) in the encryption process. In the above case this row constant is the value of id but it could be anything that you either store with the row or that could be stored elsewhere but is specific to the row (i.e. employee NI, payroll number etc.); as long as we can pass the data to the encryptbykey function. The function is using the same symmetric key that we created above to perform the encryption but is performing the encryption with a modifier that is specific to the data being stored. You do not have to use a row authenticator: That magic number 1 in the example is the flag that states we are using one here.

Note that the function call to key_guid is being called for each row. This is not efficient. If updating in a loop or updating several rows like above, it is better to call the function once and store its value.

DECLARE @key uniqueidentifier = key_guid('sk_admin')
insert into employee (
id
, name
, salary
)
values (
1
, 'A'
, encryptbykey(@key, '50000', 1, '1')
)

Reading the encrypted data

To decrypt we simply use a decryption function with the same symmetric key. Note that the decryptbykey function does not require us to specify the key. You have to open the key before you call the function.

OPEN SYMMETRIC KEY sk_admin
DECRYPTION BY CERTIFICATE cert_sk_admin;
SELECT
Id
, Name
, CONVERT(VARCHAR(10),
DECRYPTBYKEY(Salary, 1, CONVERT(VARCHAR(5), id))
) as Salary
FROM Employee

Id            Name    Salary

1              A             50000

2              B             50000

3              C             50000

Now we have encrypted data in a table which we can read, update and add to as long as we have access to the symmetric key and authenticator that was used to encrypt the data, which in turn required that we can access the certificate that is stored in the same database which itself is encrypted by the master key, also stored in the same database.

Migration

Any backup of the database will contain the Keys and the certificate but it is a good idea to back up the keys and certificate manually and store them in a secure location.

Note that you DO NOT have to restore any keys or certificates to the target server when migrating a database which contains encrypted data. The database master key and certificate are contained in the backup file but they are encrypted so they are also useless to another server.

The correct way to migrate a database with encrypted data is to use a temporary migration symmetric key. The following code is going to show you how to do this

NOTE: Very important

Contrary to what you might read on the internet you do not have to drop the service master key on the source database before backing it up. DO NOT DO THAT!

Also you do not have to MIGRATE the service master key to the migration server. In fact this would be a silly thing to do. DO NOT DO THAT EITHER!

Check this before starting…

You may not be able to do the following using a local SQL Server management console connected to a remote source server due to windows permissions regarding file access rights on the source server and therefore might have to RDP to the machine and do it locally.

Overview of migration

SQL can encrypt data as many times as you ask it to using a different method each time and possibly multiple passwords. When SQL tries to read encrypted data it will decrypt the database master key using the service master key by default. If this fails it will try to decrypt it using the SQL Server Service account.

When you open a migrated encrypted database the system will attempt to decrypt the database master key using the service master key on the migration server and this key is likely different to the source server’s key so the decryption will fail. When this happens the migration server will try to open the database master key using the SQL service account. If it is different to the source server (common) then this will also fail. As sql failed to open the database master key then you will get the following message.

Msg 15581, Level 16, State 3, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.

Ignore the “create a master key” bit as this is not the issue (read the “or” bit of the message): The database contains a master key but SQL cannot decrypt it using its service master key (if one exists, which it nearly always does).

Note: You do not need to change any settings for certificates or symmetric keys as these are already in the backup and they are fine. You do need to update the database master key though. Do NOT drop any keys! You just need to open the database master key using the migration password that you used to create it and then drop the encryption by the remote server’s service master key and then re-encrypt by the local server’s service master key.

NOTE: Do not misread that last bit: I did NOT say to drop any key – I said drop the “encryption” of the database master key by the service master key!

Following this simply close all keys (including the master) and then re-open the symmetric key used for querying the data and it will all work fine. This is because SQL is now able to decrypt the certificate and use it.

Note that when SQL decrypts the new database master key successfully with the newly applied service master key then it will also update its SQL Service encryption method too.

Migration process – step by step

If the remote server’s database master key does not have password encryption or you do not know the password but have access to the source server then you will need to add a migration password to the source database and then back it up.

 

Method in English

  1. On the source server
  2. Add password encryption first if not already exists.
  3. Back up the database
  4. Remove the migration password encryption if you created it in step 1a
  5. On the target machine
  6. Restore the database to the target server and select it
  7. Open the database master key using the migration password (1a)
  8. Drop the service master key from the encryption methods
  9. Add the service master key to the encryption methods (this will use local)

Method in Code

At the source server – backup

Add migration password and backup the database

alter master key add encryption by password = 'Migrat10n';
GO

Backup the database

BACKUP DATABASE [DBName]
TO DISK = N'folder\DBfilename.bak' WITH NOFORMAT, NOINIT
,  NAME = N'DBName -Full Database Backup', SKIP, NOREWIND, NOUNLOAD
,  STATS = 10
GO

Migration server restore.

RESTORE DATABASE [DBName]

FROM  DISK = N'folder\filename.bak' WITH  FILE = 1
,  MOVE N'DBName' TO N'folder\filename.mdf'
,  MOVE N'DBName_log' TO N'folder\filename.ldf'
,  NOUNLOAD
,  STATS = 10
GO

Now if you try to read the data you will see it complaining about the master key being missing or closed

open symmetric key sk_admin decryption by certificate cert_sk_admin;

select
Id
, Name
, convert(varchar(10), decryptbykey(Salary, 1, convert(varchar(5), id))) as Salary
from Employee

Msg 15581, Level 16, State 3, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.

This is is because, by default, SQL Server will try to decrypt the key using the local service master key and that key is not capable of doing that. We need to update it.

Remember that when you backed up the data you created a new password encryption of the master key using the password ‘Migrat10n’? You can open the master key with that password.

open master key DECRYPTION BY PASSWORD = 'Migrat10n';

Test this works by running the select statement again after opening the key this way. It should work (but once you close it SQL will revert to trying to open it with the service key again).

You must now drop the encryption by service master key (legacy from the old server) and add it back in again for this server’s master key like this…

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
GO

ALTER MASTER KEY ADD  ENCRYPTION BY SERVICE MASTER KEY
GO

Now test that it works by closing all keys (this also closes the database master key)

close all symmetric keys

then run the select again, first opening the key with the certificate (i.e. without any password)

open symmetric key sk_admin decryption by certificate cert_sk_admin;

select
Id
, Name
, convert(varchar(10), decryptbykey(Salary, 1, convert(varchar(5), id))) as Salary
from Employee

Success!

Id            Name    Salary

1              A             50000

2              B             50000

3              C             50000

Additional recommended backup of certificate (not required)

backup master key to file = N'folder\filename.mkey' encryption by password = 'Migrat10n';
GO


backup certificate cert_sk_Admin
to file = 'folder\filename.cert'
with private key (
file = 'folder\filename.skey'
,encryption by password = ' Migrat10n');
GO

Posted in Uncategorized | Leave a comment

How to create empty guid in SQL Server – c# Guid.Empty() equivalent

In dotnet you can create a new Guid by using the static Guid class like this

(c# syntax)

Guid g = Guid.NewGuid();

And you can create an empty guid like this

Guid g = Guid.Empty;

In SQL Server you can create a new guid like this

DECLARE @g uniqueidentifier = newid()

select @g

But SQL Server does not have a function for creating an empty guid.

One way (a poor way) of achieving this is to create one from a string.

DECLARE @g uniqueidentifier = '00000000-0000-0000-0000-000000000000'

But if you get this wrong then an error will occur and its not easy to see why. E.g.

DECLARE @g uniqueidentifier = '00000000-0000-0000-0000-00000000000'

(hint: its got a 0 missing)

A safer way is to cast 0 to a binary and then the result to a uniqueidentifier

Declare @g uniqueidentifier = cast(cast(0 as binary) as uniqueidentifier)

SELECT @g

I hope this helps someone out there to produce safer, more reliable and readable code.

If you use this often and really want to make your code more readable then encapsulate this in a function.

 

-- =============================================
-- Author     : David Bridge
-- Create date: June 2014
-- Description: Returns an empty guid
-- =============================================
CREATE FUNCTION GuidEmpty()
RETURNS uniqueidentifier
AS
BEGIN
RETURN cast(cast(0 as binary) as uniqueidentifier)
END

Then use

select dbo.GuidEmpty()

Posted in Developer stuff, SQL Stuff | Tagged , , , , , | 1 Comment

MOTOROLA Universal Bluetooth Keyboard & Mouse – pc use – missing backslash

I bought a Motorola droid bluetooth keyboard which is supposed to be for a mobile phone or tablet (Xoom) but it has no backslash on it which is annoying when you connect it to a pc (which obviously you can or I would not be writing this. No really, I am using it to write this).

motorola bluetooth keyboard

Now this is no ordinary 103 key QUERTY keyboard. I wanted a small, black, pretty, and quality hideaway keyboard for a Windows 8 box that I use with a TV in the bedroom. Most of the time the pc gets used for NETFLIX but sometimes gaming and a bit of surfing. It’s plugged in to a 40 inch LED TV so it is kind of cool, especially when you factor in the specs. The pc is a tiny Gigabite Brix that is about 4 inches square and an inch tall with 16GB memory and 250GB SSD. Its a great gaming rig for Asphalt 8, ideal for Netflix and it boots into windows from cold in about 3 seconds. It shuts down in about the same time too.

Anyway, digression over. The keyboard is normally tucked away in a draw or under the telly so I wanted a small Bluetooth keyboard and mouse and opted for the Motorola Droid.

Its great but does not have a backslash. Here’s how to get one….

 

Find and hold “Alt Gr” to the right of the SPACE bar and click the # key (also has the pipe “|” character on it). That’s it!

Another top tip is the Euro symbol – €

To get this also use the “Alt Gr” key in combination with the $ key.

 

Now this may not be a SQL or code tip but if like me you like your gadgets and thing a dainty travel size but useful keyboard is a good idea then this could be something that will help you when you use that pc for something other than a bit of gaming. And its always good to know how to get the euro sign up isn’t it.

Dave

 

 

Posted in Hardware stuff, Other stuff | Tagged , , , , , , , , , , , | 1 Comment

SSRS report print button missing in IE11

If you use reporting services though a report viewer control then you might be missing the print button when viewing in IE11.

SSRS_NoPrintButton

To fix this, install the latest reportwiewer.exe.

This will return the print button but annoyingly it also changes the colour scheme and toolbar layout. It’s actually a nicer colour scheme but it might not fit with your page in both size and colour.

SSRS_NoPrintButton_restored

Direct download location=

http://www.microsoft.com/en-us/download/confirmation.aspx?id=35747

or if you don’t trust links, go to http://www.microsoft.com

and search for “MICROSOFT® REPORT VIEWER 2012 RUNTIME

Posted in Developer stuff, SQL Stuff | Tagged , , , , | Leave a comment

SSRS report hangs in IE11

If your SSRS report viewer’s “Loading” popup message never goes away (the report never renders) then chances are you have a reference to an image that does not exist in your report.

SSRS_Loading

I got this because I have a report template that I use for many customers and I like to show the customer’s log with an image with expression like

=Parameters!Customer.Value & ".jpg"

Which then requires that I copy a jpeg file called customername.jpg to my reports folder. If I did not have the logo, which was common for new customers then the logo was just blank but they Microsoft released IE11 and reports without a logo failed to render at all.

I have not found a way to fix this as there is no native IfFileExists() function (reports do not have access to the file system directly).

I tried updating the report viewer control to the latest version and this fixes other issues like the print button not working properly but it does not appear to fix this missing image issue.

You could move the jpegs to another place like a database or create a CLR extension but that’s a bit of overkill for a single browser issue (that shouldn’t be your responsibility to fix).

Workaround is therefore to simply make sure the file exists

Note:

The reports render fine in other browsers with the Report Viewer control!

The reports render fine when using SSRS directly without the Report Viewer control. So this is definitely a Report Viewer issue.

Posted in Developer stuff, SQL Stuff | Tagged , , , , , | Leave a comment

SSAS / Excel 2010 – An error was encountered in the transport layer

Connecting to SQL Server Analysis Services from a remote domain / login

When connecting to SSAS from a different remote domain / login using Excel 2010 where you have set up a windows user at the SSAS server, provided adequate permissions at the SSAS server to read the cube etc. but still cannot connect from a local Excel the issue is probably on account of the different domains / logins and a lack of trust between the servers.

All of your settings are correct, its probably just the windows being over cautious.

I found two workarounds for this

  • Create a local user with the same login details. Login as that user and then connect (not ideal)
  • Connect using the runas command and include the /netonly switch

A little more info on the second method….

  1. Navigate to excel in Windows explorer (“C:\Program Files (x86)\Microsoft Office\Office14”)
  2. hold down the right shift key then right click the explorer window and choose Open command window here
  3. now paste this in to the window “runas /netonly /user:REMOTEDOMAIN\USERNAME EXCEL
  4. You will be prompted for a password. Enter it and press the ENTER key
  5. Excel will load.

You will now be able to connect to the Analysis Services on the remote machine with the same details as your excel session will be trusted.

If this has fixed your problem you might like to create a shortcut.

Simply create a standard shortcut to EXCEL and then edit the command to this

C:\Windows\System32\runas.exe /netonly /user:DOMAIN\USER "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"

 

NOTE: This also works for other programs exhibiting the same behaviour.
The reason this works is that you are allowing the remote server to trust your connection. Obviously you will still need all of the appropriate permissions and ports open etc. so this article has assumed that you have done this already.

Posted in Developer stuff, SQL Stuff | Tagged , , , , , , | 1 Comment