Easy data encryption in Azure

For those of you who use Azure today, the security discussion must have been a thing on some occasion. Explaining to managers (and possibly colleagues) that Azure is a lot more secure than a(n) (on-premise) data center, and that Azure is easier to maintain and scalable. Trust me, we’ve all been there!

But besides the physical security, there’s also the digital security. In the world of today it’s easier to find a data-breach on the news, then it is to find an item about a bank robbery. So how can you secure your data in Azure in an easy but solid way, without the hassle of changing your applications?

Encryption could be one of your tools to achieve a secure infrastructure and/or applications. But encryption is a challenge for pretty much everyone. Almost every day we hear about companies not doing it right, or not doing it at all. But luckily, Azure helps us with setting this up with just the click of a button.

Okay, okay, you got me. Maybe a few button clicks…
Continue reading Easy data encryption in Azure

Running database maintenance on Azure SQL DB with Azure Automation

Running all of your databases in Azure SQL DB (the PaaS solution, also known as Azure SQL database) is a wonderful thing, except for one thing: you still need to run database maintenance. And because the SQL Server Agent is not available (which is a shame in my opinion), you have to find a way around that.

In one of my previous posts (Running maintenance on Azure SQL databases), I showed you how to leverage the power of the database maintenance solution, written by Ola Hallengren (Website | @olahallengren). But now that we’re moving into Azure completely, we’re losing that work-around. So how did we fix this?
Continue reading Running database maintenance on Azure SQL DB with Azure Automation

Automatically convert contained database to non-contained for replication

In one of my previous posts, I described how to setup replication to an Azure SQL database. This works like a charm, and I still highly recommend using this when you want to migrate data from an on-premise server (or Azure VM) to a Azure SQL db (PaaS).

But in our environment, we use SQL Server 2016 and contained databases for some of our datasets. Unfortunately (but totally understandable), you can’t setup replication from a contained database. So how do you deal with this? For our use-case, I’ve written a script to automatically change the database from contained to non-contained. And because I’m probably not the only one who needs to do this, I’d like to share it with you.
Continue reading Automatically convert contained database to non-contained for replication

Notifications 2.0

Roadmap is growing, more data is coming in and more travelers are becoming dependend on our services which is a great! However, everything comes with a price. The huge amount of data that Roadmap is currently processing is going to increase with the time and therefore, it is absolutely important to have a stable platform in place that can handle and support the growth.

To achieve this goal we have taken a closer look into our backend processes and namely, how the current flow works with different types of data that we process as well as into the bottlenecks that we face when it comes to communicating to the travelers.

We have learned that we need to change a big part of our system to be stable, scalable and high available. Our main focus was based on two core processes – Timeline and Notifications.
Within this blog I will dive into the migration of Notifications; How did this work in the past, what kind of issues did we encounter and how did we gain control over these issues in the current process, bearing in mind we want to extend the process in a most efficient way in the (near) future. Continue reading Notifications 2.0

Replication: Snapshot Agent fails on date conversion

In the previous post I wrote, I explained how to setup replication from an on-premise SQL Server instance to an Azure SQL database. While doing this, I came across a very strange issue (or maybe even bug) when setting up replication.

 
The problem child
After working on reproducing the issue for a day, and trying to reduce the issue to a small-scale problem, I came to the conclusion that the problem was (probably) caused by a single primary key on a table in the database:


CREATE TABLE dbo.BuggedTable
	([Day] DATE NOT NULL,
	 SomeId VARCHAR(50) NOT NULL,
	 Amount INT NOT NULL,
	 CONSTRAINT PK_BuggedTable PRIMARY KEY CLUSTERED 
		([Day] ASC,
		SomeId ASC
		)
	)
GO

 
Creating publication & subscriber
The setup of the publication and subscriber wasn’t that difficult. As I said before, there are a few things you need to configure differently then you would do for SQL Server to SQL Server replication.

So I won’t talk you through the whole process again, but refer you to the articles instead. Continue reading Replication: Snapshot Agent fails on date conversion

Setting up replication from on-premise SQL Server to Azure SQL DB

For performance reasons we are looking for a way to split our write-operations from our read operations. A good way to do that is by duplicating the database onto another server (on-premise) or platform (Azure SQL DB). When you look at the options (Availability Groups (AG), database mirroring, clustering, replication, Azure sync groups, etc.) the easiest way to quickly duplicate a database and keep the data up-to-date at the same time is SQL Server replication.

Replication to another on-premise instance is easy. You just follow the steps in the wizard, it works out-of-the-box, and the chances of this process failing are small. With replicating data to an Azure SQL database it’s a bit more of a struggle. Just one single word took me a few HOURS of investigation and a lot of swearing… Continue reading Setting up replication from on-premise SQL Server to Azure SQL DB

Alert on Login on Production environment

Almost one year ago, Roadmap was ISO 27001 certified. This was a great accomplishment for a startup of only a half a year old at that time. We weren’t entirely issue-free of course, we had a couple of minor issues to fix, but only a couple. In general, we were in control. One of these minor points was that we needed to get an alert when someone logged on to a server in the production environment. Why? Because people shouldn’t. There is no need to be on a production server for writing or deploying software, because we use Octopus deploy for deployments. The only developer who we’d expect to find logging on to servers is the one on call, he or she is in the ‘ops’ role of DevOps. And of course we’d expect Jeffrey, our dba.

Recently, someone asked me just how we created such an alert, so I decided to share how we did it.

Our first attempt of to get the alert was to leverage the windows event with id 4624 together with LogicMonitor (https://www.logicmonitor.com). It is great tool to monitor your servers, you should check it out. My thought was that I could use LogicMonitor to check the event log and warn whenever a user logs on. But the log was full of these events; Windows didn’t just log the logon events from me and my colleagues, but also from AD accounts that we use to run services / tools. We needed to filter the logins to see if there were some strange logins.  Unfortunately, I was unable to filter to just the events to the level I needed,  despite LogicMonitors powerful capabilities on filtering. Continue reading Alert on Login on Production environment

Easy pivot: From key-value pairs to columns

If there is one thing I pretty much hate doing in T-SQL it’s PIVOT and UNPIVOT. Even though I used it a few times in the last couple of years, it’s an adventure to find out how it works every time. And I know a lot of people struggle with this part of T-SQL, so let’s take a look at a (hopefully) simple example.

 
Key-Value pairs
The idea of using key-value pairs to store data isn’t new, but with the rapid development of cloud solutions like Azure and the hype around NoSQL databases, using key-value pairs to store data got a big boost. Especially developers (in my experience) love using key-value pair to store their data, because it’s easy for them to consume the data in an application. But it gives the database professional an extra challenge because we’re used to retrieve columns with values instead of a record per value. So how can we turn those key-value pairs into rows? Continue reading Easy pivot: From key-value pairs to columns

Running maintenance on Azure SQL databases

To keep your data healthy and topfit, we all know you need to run something called database maintenance on your databases. This prevents things like performance problems or unrecoverable data from happening, and that is sort of the core business of DBA’s. And when we look at how this can be performed on a regular basis, an on-premise instance is a bit easier to maintain than an Azure SQL database. That’s why I want to share our experience with you on that, hoping that it can save you some time when you start implementing this.

 
Platform setup
In order to get a better understanding of why certain choices were made, I want to share a very basic version of the setup of our environment.

Our platform consists of 2 mayor parts: a private cloud part (that we see as “on-premise”), and an Azure part. Those 2 parts combined make our platform, and this platform hosts all of our services, applications, databases, etc. The development approach we use is everything should be designed with cloud-first in mind, but only if it’s the right tool for the job, and with the exclusion of some data.

The databases we use in Azure are all what Microsoft calls “Azure SQL databases”. This means that there are no virtual machines of any kind are running on our Azure-part of the platform, also known as DBaas (Database as a Service). Continue reading Running maintenance on Azure SQL databases

Application Authentication via https using NTLM

We at Roadmap take security very seriously. Everything we do and everything we touch or make, we think about security as it is part of how we work. In fact: it is in our DNA.

Recently we have been working on a way to generate monthly reports for our customers in a sweet automatic way. To be able to download these reports the report server need to verify the given NTLM authentication credentials which are provided by the application. This was a bit of a challenge as it did not work in the way we would assume.

When you want to download a file, the .NET Framework provides you and easy and simple code snippet, which works straight away:


        public void DownloadAwesomeness()
        {
            var url = new Uri("https://DomainNameHere");
            var location = @"C:\Temp\Destination.txt";
            
            using (var client = new WebClient())
            {
                client.DownloadFile(url, location);

                Console.WriteLine("We are done!");
                Console.Read();
            }
        }

This works perfectly. How hard can it be to add some extra credentials to the WebClient? As the WebClient has a Credentials property, we will set these.


        public void DownloadAwesomeness()
        {
            var url = new Uri("https://DomainNameHere");
            var location = @"C:\Temp\Destination.txt";

            using (var client = new WebClient())
            {
                client.Credentials = new NetworkCredential("username", "password", "domain");
                client.DownloadFile(url, location);

                Console.WriteLine("We are done!");
                Console.Read();
            }
        }

When you will run this you will notice that the client.DownloadFile(url, location); will throw an ‘403 – Access Denied’. Before you start changing any usernames and/or password because you are doubting yourself if you did something wrong, let me help you out here: You didn’t do anything wrong. More interesting is; what is actually going on? What is happening with the credentials? Continue reading Application Authentication via https using NTLM