[KOSD Series] Read-only Users for Azure SQL Databases


It’s quite common that Business Analyst will always ask for the permission to access the databases of our systems to do data analysis. However, most of the time we will only give them read-only access. With on-premise MS SQL Server and SQL Management Studio, it is quite easily done. However, how about for those databases hosted on Azure SQL?

Login as Server Admin

To make things simple, we will first login to the Azure SQL Server as Server admin on SQL Management Studio. The Server Admin name can be found easily on Azure Portal, as shown in the screenshot below. Its password will be the password we use when we create the SQL Server.


Identifying the Server Admin of an Azure SQL Server. (Source: Microsoft Azure Docs)

Create New Login

By default, the master database will be the default database in Azure SQL Server. So, once we have logged in, we simply create the read-only login using the following command.

CREATE LOGIN <new-login-id-here>
    WITH PASSWORD = '<password-for-the-new-login>' 

Alternatively, we can also right-click on the “Logins” folder under “Security” then choose “New Login…”, as shown in the screenshot below. The same CREATE LOGIN command will be displayed.


Adding new login to the Azure SQL Server.

Create User

After the new login is created, we need to create a new user which is associated with it. The user needs to be created and granted read-only permission in each of the databases that the new login is allowed to access.

Firstly, we need to expand the “Databases” in the Object Explorer and then look for the databases that we would like to grant the new login the access to. After that, we right-click on the database and then choose “New Query”. This shall open up a new blank query window, as shown in the screenshot below.


Opening new query window for one of our databases.

Then we simply need to run the following query for the selected database in the query window.

CREATE USER <new-user-name-here> FROM LOGIN <new-login-id-here>;

Please remember to run this for the master database too. Otherwise we will not be able to login via SQL Management Studio at all with the new login because the master database is the default database.

Grant Read-only Permission

Now for this new user in the database, we need to give it a read-only permission. This can be done with the following command.

EXEC sp_addrolemember 'db_datareader', '<new-user-name-here>';


Repeat the two steps above for the remaining databases that we want the new login to have access to. Finally we will have a new login that can read from only selective databases on Azure SQL Server.



KOSD, or Kopi-O Siew Dai, is a type of Singapore coffee that I enjoy. It is basically a cup of coffee with a little bit of sugar. This series is meant to blog about technical knowledge that I gained while having a small cup of Kopi-O Siew Dai.


[KOSD Series] Certificate for Signing JWT on IdentityServer

KOSD, or Kopi-O Siew Dai, is a type of Singapore coffee that I enjoy. It is basically a cup of coffee with a little bit of sugar. This series is meant to blog about technical knowledge that I gained while having a small cup of Kopi-O Siew Dai.


Last year, Riza shared a very interesting topic twice during Singapore .NET Developers Community in Microsoft office. For those who attended the meetups, do you still remember? Yes, it’s about IdentityServer.

IdentityServer 4 is a middleware, an OpenID Connect provider built to spec, which provides user identity and access control in ASP .NET Core applications.

In my example, I will start with the simplest setup where there will be one Authenticate Server and one Application Server. Both of them in my example will be using ASP .NET Core.


How an application uses JWT to authenticate a user.

In the Authenticate Server, I register the minimum required dependencies in ConfigureServices method of its Startup.cs as follows.


I won’t be talking about how IdentityServer works here. Instead, I will be focusing on the “AddDeveloperSigningCredential” method here.

JSON Web Token (JWT)

By default, IdentityServer issues access tokens in the JWT format. According to the abstract definition in RCF 7519 from Internet Engineering Task Force (IETF) , JWT is a compact, URL-safe means of representing claims between two parties where claims are encoded as JSON objects which can be digitally signed or encrypted.

In the diagram above, the Application Server receives the secret key used in signing the JWT from the Authentication Server when the app sets up its authentication process. Hence, the app can verify whether the JWT comes from an authentic source using the secret key.


IdentityServer uses an asymmetric key pair to sign and validate JWT. We can use AddDeveloperSigningCredential to do so. In the previous version of IdentityServer, this method is actually called AddTemporarySigningCredential.

During development, we normally don’t have cert prepared yet. Hence, AddTemporarySigningCredential can be used to auto-generate certificate to sign JWT. However, this method has a disadvantage. Every time the IdentityServer is restarted, the certificate will change. Hence, all tokens that have been signed with the previous certificate will fail to validate.

This situation is fixed when AddDeveloperSigningCredential is introduced to replace the AddTemporarySigningCredential method. This new method will still create temporary certificate at startup time. However, it will now be able to persists the key to the file system so that it stays stable between IdentityServer restarts.

Anyway, as documented, we are only allowed to use AddDeveloperSigningCredential in development environments. In addition, AddDeveloperSigningCredential can only be used when we host IdentityServer on single machine. What should we do when we are going to deploy our code to the production environment? We need a signing key service that will provide the specified certificate to the various token creation and validation services. Thus now we need to change to use AddSigningCredential method.

Production Code

For production, we need to change the code earlier to be as follows.

X509Certificate2 cert = null;
using (X509Store certStore = new X509Store(StoreName.My, StoreLocation.CurrentUser))
    var certCollection = certStore.Certificates.Find(
    // Get the first cert with the thumbprint
    if (certCollection.Count > 0)
        cert = certCollection[0];


We use AddSigningCredential to replace the AddDeveloperSigningCredential method. Now, AddSigningCredential requires a X509Certificate2 cert as parameter.

Creation of Certificate with OpenSSL on Windows

It’s quite challenging to install OpenSSL on Windows. Luckily, Ben Cull, solution architect from Belgium, has shared a tutorial on how to do this easily with a tool called Win32 OpenSSL.

His tutorial can be summarized into 5 steps as follows.

  1. Install the Win32 OpenSSL and add its binaries to PATH;
  2. Create a new certificate and private key;
    openssl req -x509 -newkey rsa:4096 -sha256 -nodes -keyout cuteprogramming.key -out cuteprogramming.crt -subj "/CN=cuteprogramming.com" -days 3650
  3. Convert the certificate and private key into .pfx;
    openssl pkcs12 -export -out cuteprogramming.pfx -inkey cuteprogramming.key -in cuteprogramming.crt -certfile cuteprogramming.crt
  4. Key-in and remember the password for the private key;
  5. Import the certificate to the Current User Certificate Store on developer’s local machine by double-clicking on the newly generated .pfx file. We will be asked to key in the password used in Step 4 above again.

Importing certificate.

Now, we need to find out the Thumbprint of it. This is because in our production code above, we are using Thumbprint to look for the cert.

Thumbprint and Microsoft Management Console (MMC)

To retrieve the Thumbprint of a certificate, we need help from a tool called MMC.


Using MMC to view certificates in the local machine store for current user account.

We will then be able to find the new certificate that we have just created and imported. To retrieve its Thumbprint, we first need to open it, as shown in the screenshot below.


Open the new cert in MMC.

A popup window called Certificate will appear. Simply copy the value of the Thumbprint under the Details tab.



After keeping the value of the cert thumbprint in the appsettings.Development.json of the IdentityServer project, we can now build and run the project on localhost without any problem.

Deployment to Microsoft Azure Web App

Before we talk about how to deploy the IdentityServer project to Microsoft Azure Web App, do you realize how come in the code above, we are looking cert only My/Personal store of the CurrentUser registry, i.e. “StoreName.My, StoreLocation.CurrentUser”? This is because this is the place where Azure will load the certificate from.

So now, we will first proceed to upload the certificate as Private Certificate that we self-sign above to Azure Web App. After selecting the .pfx file generated above and keying-in the password, the cert will appear as one of the Private Certificates of the Web App.


To upload the cert, we can do it in “SSL certificates” settings of our Web App on Azure Portal.

Last but not least, in order to make the cert to be available to the app, we need to have the following setting added under “Application settings” of the Web App.


WEBSITE_LOAD_CERTIFICATES setting is needed to make the cert to be available to the app.

As shown in the screenshot above, we set WEBSITE_LOAD_CERTIFICATES to have * as its value. This will make all the certificates in the Web App being loaded to the personal certification store of the app. Alternatively, we can also let it load selective certificates by keying in comma-separated thumbprints of the certificates.

Two Certificates

There is an interesting discussion on IdentityServer3 Issues about the certificates used in IdentityServer project. IdentityServer requires two certificates: one for SSL and another for signing JWT.

In the discussion, according to Brock Allen, the co-author of IdentityServer framework, we should never use the same cert for both purposes and it is okay to use a self-signed cert to be the signing cert.

Brock also provided a link in the discussion to his blog post on how to create signing cert using makecert instead of OpenSSL as discussed earlier. In fact, during Riza’s presentation, he was using makecert to self-sign his cert too. Hence, if you are interested about how to use makecert to do that, please read his post here: https://brockallen.com/2015/06/01/makecert-and-creating-ssl-or-signing-certificates/.


This episode of KOSD series is a bit long such that drinking a large cup of hot KOSD while reading this post seems to be a better idea. Anyway, I think this post will help me and other beginners who are using IdentityServer in their projects to understand more about the framework bit by bit.

There are too many things that we can learn in the IdentityServer project and I hope to share what I’ve learnt about this fantastic framework in my future posts. Stay tuned.


Playing with Fiddler

Fiddler - HTTPS

I just downloaded Fiddler. I would like to see how I can make use of it, so I noted down some of the things that I have tried out.

Experiment 01: Process Filter

The first thing that I realized when I used Fiddler is that there are too many information being displayed especially when there are too many programs accessing the Internet. This is because, as advertised, Fiddler is a web debugging proxy for any browser (Microsoft Edge is included as well!) that works independently.

Fortunately, Fiddler providing a filtering function “Process Filter” to enable us to capture traffic coming from a particular browser, instead of all browsers.

Just drag and drop the icon on the browser you want to track.

Just drag and drop the icon on the browser you want to track.

Experiment 02: Performance Profiling

By just filtering and selecting the relevant sessions, we would be able to generate a web page performance report about total number of requests, total bytes sent and received, response time, DNS lookup time, response bytes by content type in a pie chart, etc.

Performance profiling of id.easybook.com, an Indonesia bus ticket booking website.

Performance profiling of id.easybook.com, an Indonesia bus ticket booking website.

By clicking on the “Timeline” tab, we will be able to get an overview of activities recorded. It is one of the useful features to start investigating performance issues in our web application.

Transfer Timeline diagram of id.easybook.com.

Transfer Timeline diagram of id.easybook.com.

Experiment 03: Decrypt HTTPS Traffic

By default, Fiddler disables HTTPS decryption. However, nowadays most of the websites that we would like to debug are using HTTPS encryption. So, it’s sometimes necessary to set it up to work with HTTPS traffic.

HTTPS decryption is disabled by default.

HTTPS decryption is disabled by default.

First of all, we just click Tools -> Fiddler Options.

In the “HTTPS” tab of the popup window, we need to enable both “Capture HTTP CONNECTs” and “Decrypt HTTPS Traffic”. To intercept HTTPS traffic, Fiddler generates a unique root certificate. In order to suppress Windows security warnings, Fiddler recommends to have our PC to trust the cert. Hence, there will be a warning message shown after we click on the “OK” button.

Yes, scary text! Are you sure you want to trust the certificate?

Yes, scary text! Are you sure you want to trust the certificate?

However, Windows cannot validate the certificate properly, so we will be asked if we really want to install the cert.

Are you sure you want to install certificate from DO_NOT_TRUST_FiddlerRoot?

Are you sure you want to install certificate from DO_NOT_TRUST_FiddlerRoot?

Finally, we will also be asked if we wish to add the cert to our PC’s Trusted Root List.

Adding cert to PC Trusted Root List.

Adding cert to PC Trusted Root List.

If we want to remove the cert from the PC’s Trusted Root List, we can always do so by clicking on the “Remove Interception Certificate” button in the Fiddler Options window.

Removing cert from PC Trusted Root List.

Removing cert from PC Trusted Root List.

To understand the implications of enabling HTTPS encryption and installing the cert, you can read a discussion on Information Security Stack Exchange about 3rd party root certificates.

Summer 2015 Self-Learning Project

This article is part of my Self-Learning in this summer. To read the other topics in this project, please click here to visit the project overview page.

Summer Self-Learning Banner

Protect Your ASP .NET Applications

ASP .NET MVC - Entity Framework - reCAPTCHA - OWASP - JSON

Here is a just a few items that I learnt on how to protect and secure my web applications in recent ASP .NET projects.

reCAPTCHA in Razor

CAPTCHA is an acronym for “Completely Automated Public Turing test to tell Computers and Humans Apart”.

CAPTCHA is a program to find out if the current user is whether a human or a robot by asking the user to do some challenge-response tests. This feature is important in some websites to prevent machine to, for example, auto login to the websites, to do online transactions, to register as members, and so on. Luckily, it’s now very easy to include CAPTCHA in our ASP .NET MVC web applications.

Register your website here to use reCAPTCHA: https://www.google.com/recaptcha/admin.

Register your website here to use reCAPTCHA: https://www.google.com/recaptcha/admin.

reCAPTCHA is a free Google CAPTCHA service that comes in the form of widget that can be added to websites easily. So, how do we implement reCAPTCHA in our ASP .NET MVC sites?

The library that I use is called ReCaptcha for MVC5, which can be downloaded from Codeplex. With the help of it, I am able to easily plugin reCAPTCHA in my MVC5 web applications.

After adding ReCaptcha.Mvc5.dll in my project, I will need to import its namespace to the Razor view of the page which needs to have reCAPTCHA widget.

@using ReCaptcha.Mvc5;

To render the reCAPTCHA widget in, for example, a form, we will do the following.

< div class="form-group">
    @Html.LabelFor(model => model.recaptcha_response_field, new { @class = "control-label col-md-2" })
    < div class="col-md-10">
        <!--Render the recaptcha-->
        @Html.reCAPTCHA("<public key here>")
    < /div>
 < /div>

The public key can be retrieved from the official reCAPTCHA page after you register your website there.

reCAPTCHA Widget on Website

reCAPTCHA Widget on Website

In the code above, there is a field called recaptcha_response_field, which will be added in our model class as demonstrated below.

public class RegistrationViewModel : ReCaptchaViewModel

    [Display(Name = "Word Verification")]
    public override string recaptcha_response_field { get; set; }

To do verification in the controller, we will have the following code to help us.

public async Task<ActionResult> Register(RegistrationViewModel registrationVM)

    if (!string.IsNullOrEmpty(checkoutVM.recaptcha_response_field))
        // Verify the recaptcha response.
        ReCaptchaResponse response = 
            await this.verifyReCAPTCHA(registrationVM, "<private key here>", true);

        if (response.Success)
            // Yay, the user is human!
            ModelState.AddModelError("", "Please enter correct verification word.");

The private key can also be found in the official reCAPTCHA page after you have submitted your website.

After doing all these, you are now be able to have a working reCAPTCHA widget in your website.

XSRF: Cross-Site Request Forgery

In the controller code above, there is one attribute called ValidateAntiForgeryToken. The purpose of this attribute is to prevent XSRF by adding anti-forgery tokens in both a hidden form field and the cookies to the server.

I draw a graph for me to better explain about what XSRF is.

XSRF (Cross-Site Request Forgery)

XSRF (Cross-Site Request Forgery)

Steps are as follows.

  1. The user logs in to, for example, a bank website.
  2. The response header from the bank site will contain the user’s authentication cookie. Since authentication cookie is a session cookie, it will only be cleared when the process ends. Thus, until that time, the browser will always include the cookie with each request to the same bank website.
  3. The attacker sends to the user a link and somehow encourage the user to click on it. This causes sending a request to the attacker’s server.
  4. The attacker website has the following form.
    <body onload="document.getElementById('attack-form').submit()">
        <form id="fm1" action="https://bank.com/TransferMoney" method="post">
            <input name="transferTo" value="attackerAccount" />
            <input name="currency" value="USD" />
            <input name="money" value="7,000,000,000" />
  5. Because of Step 4, the user will be forced to send a request to the bank website to transfer money to attacker’s account with the user’s authentication cookie.

Hence, the attribute ValidateAntiForgeryToken helps to avoid XSRF by checking both the cookie and form have anti-forgery tokens and their values match.

Mass-Assignment Vulnerability and Over-Posting Attack

Few years ago, Github was found to have Mass-Assignment Vulnerability. The vulnerability allows people to perform Over-Posting Attack to the site so that the attackers can modify data items which are not normally allowed to access. Due to the fact that ASP .NET MVC web application is using Model Binding, the same vulnerability can happen in ASP .NET MVC environment as well.

You want to control what is being passed into the binder.

You want to control what is being passed into the binder.

There are two my personal favourite solutions to avoid Over-Posting Attack.

One is using Bind attribute in the controller method. For example, in order to prevent users editing the value of isAdmin when they update their profile, I can do something as follows.

public ViewResult Edit([Bind(Exclude = "IsAdmin")] User user)

Alternatively, we can also use “Include” to define those fields that should be included in the binding.

Second solution is using view model. For example, the following class will not contain properties such as IsAdmin which are not allowed to be edited in the form post of profile edit.

public class UserProfileUpdateViewModel
    public string FirstName { get; set; }
    public string LastName { get; set; }

XSS: Cross-Site Scripting

According to OWASP (Open Web Application Security Project), XSS attacks

…are a type of injection, in which malicious scripts are injected into otherwise benign and trusted web sites… Flaws that allow these attacks are quite widespread and occur anywhere a web application uses input from a user within the output it generates without validating or encoding it.

Kirill Saltanov from NUS is explaining to guests about XSS during 5th STePS event.

Kirill Saltanov from NUS is explaining to guests about XSS during 5th STePS event.

Currently, by default ASP .NET will throw exception if potentially dangerous content is detected in the request. In addition, the Razor view engine protect us against most of the XSS attacks by encoding data which is displayed to web page via the @ tag.

In View, we also need to encode any user-generated data that we are putting into our JavaScript code. Starting from ASP .NET 4.0, we can call HttpUtility.JavaScriptStringEncode. HttpUtility.JavaScriptStringEncode helps to encode a string so that it is safe to display and characters are escaped in a way that JavaScript can understand.

In order to avoid our database to have malicious markup and script, we need to encode the user inputs in the Controller as well using Server.HtmlEncode.


There are some cases where our web application should accept HTML tags. For example, we have a <textarea> element in our blogging system where user can write the content of post, then we need to skip the default checking of ASP .NET.

To post HTML back to our Model, we can simply add the [AllowHtml] attribute to the corresponding property in the Model, for example

public class BlogPost {
    public int ID { get; set; }
    public string Content { get; set; }

Then in the View, we will need to use @Html.Raw to tell Razor not to encode the HTML markup.


Wait… Won’t this make XSS attack possible in our website? Yup, of course. So, we must be very careful whenever we are trying to bypass the Razor encoding. The solution will then be using AntiXSS encoding library from Microsoft.

AntiXSS uses a safe list approach to encoding. With its help, we will then able to remove any malicious script from the user input in the Controller, as demonstrated below.

public ActionResult CreatePost(BlogPost post)
    if (ModelState.IsValid)
        post.Content = Sanitizer.GetSafeHtmlFragment(post.Content);
        return RedirectToAction("Index");
    return View(post);

ASP .NET Request Validation

Previously in the discussion of XSS, we know that by default ASP .NET throws exception if potentially dangerous content is detected in the request. This is because of the existence of ASP .NET Request Validation.

However, according to OWASP, Request Validation should not be used as our only method of XSS protection because it does not guarantee to catch every type of invalid input.

HttpOnly Cookies

In order to reduce the risk of XSS, popular modern browsers have added a new attribute to cookie called HttpOnly Cookie. This new attribute specifies that a cookie is not accessible through script. Hence, it prevents the sensitive data contained in the cookie can be sent to attacker’s side via malicious JavaScript in XSS attack.

When a cookie is labelled as HttpOnly, it tells the browser that the cookie should only be accessed by the server. It is very easy to check which cookies are HttpOnly in the developer tool of modern browsers.

Microsoft Edge F12 Developer Tools can tell which are the HttpOnly cookies.

Microsoft Edge F12 Developer Tools can tell which are the HttpOnly cookies.

So, how do we create HttpOnly cookies in ASP .NET web applications? Just add a new line to set HttpOnly attribute of the cookie to true is fine.

HttpCookie myCookie = new HttpCookie("MyHttpOnlyCookie");
myCookie["Message"] = "Hello, world!";
myCookie.Expires = DateTime.Now.AddDays(30);
myCookie.HttpOnly = true;

Alternatively, HttpOnly attribute can be set in web.config.

<httpCookies httpOnlyCookies="true" ...>

However, as pointed out in OWASP, if a browser is too old to support HttpOnly cookie, the attribute will be ignored by the browser and thus the cookies will be vulnerable to XSS attack. Also according to MSDN, HttpOnly does not prevent attacker with access to the network channel from accessing the cookie directly, so it recommends the use of SSL in addition of HttpOnly attribute.

HttpOnly Cookie was introduced in 2002 in IE6. Firefox only supported HttpOnly attribute in 2007, 5 years later. However, soon people realized that in Firefox, there was still a bug in the HttpOnly implementation. Firefox allowed attackers to do an XMLHttpRequest to get the cookie values from the HTTP Response headers. 2 years later, in 2009, Mozilla finally fixed the bug. Since then, the XMLHttpRequest can no longer access the Set-Cookie and Set-Cookie2 headers of any response no matter the HttpOnly attribute is set to true or not.

Browserscope provides a good overview about the security functionalities in major browsers.

Browserscope provides a good overview about the security functionalities in major browsers.

SQL Injection and Entity SQL

When I first learned SQL in university, I always thought escaping user inputs helped to prevent SQL Injection. This approach doesn’t work actually. I just read an article written by Steve Friedl regarding how escaping the input strings does not protect our applications from being attacked by SQL Injection. The following is the example Steve gave.

SELECT fieldlist
FROM table
WHERE id = 23 OR 1=1;  -- Boom! Always matches!

When I was working in the Summer Fellowship Programme, I started to use Parameterized SQL.

SqlConnection conn = new SqlConnection(connectionString); 
string sql = "SELECT fieldlist FROM table WHERE id = @id";
SqlCommand cmd = new SqlCommand(sql); 
cmd.Parameters.Add("@id", SqlDbType.Int, id); 
SqlDataReader reader = cmd.ExecuteReader();

This approach provides a huge security performance benefits.

In January, I started to learn Entity Framework. In Entity Framework, there are three types of queries:

  • Native SQL
  • Entity SQL
  • LINQ to Entity

In the first two types, there is a risk of allowing SQL Injection if the developers are not careful enough. Hence, it’s recommended to use parameterized queries. In addition, we can also use Query Builder Methods to safely construct Entity SQL, for example

ObjectQuery<Flight> query =
    .Where("it.FlightCode = @code",
    new ObjectParameter("code", flightCode));

However, if we choose to use LINQ to Entity, which does not compose queries by using string manipulation and concatenation, we will not have the problem of being attacked by traditional SQL Injection.

JsonResult and JSON Hijacking

Using the MVC JsonResult, we are able to make our controller in ASP .NET MVC application to return Json. However, by default, ASP .NET MVC does not allow us to response to an HTTP GET request with a JSON payload (Book: Professional ASP .NET MVC 5). Hence, if we test the controller by just typing the URL directly in the browser, we will receive the following error message.

This request has been blocked because sensitive information could be disclosed to third party web sites when this is used in a GET request. To allow GET requests, set JsonRequestBehavior to AllowGet.

Since the method only accepts POST requests, unless Cross-Origin Resource Sharing (CORS) is implemented, the browser will be able to protect our data from returning the Json result to other domains.

This is actually a feature introduced by ASP .NET MVC team in order to mitigate a security threat known as JSON Hijacking. JSON Hijacking is an attack similar to XSRF where attacker can access cross-domain JSON data which is returned as array literals.

The reason why “returning JSON data as array” is dangerous is that although browsers nowadays stop us from making cross domain HTTP request via JavaScript, we are still able to use a <script> tag to make the browser load a script from another domain.

<script src="https://www.bank.com/Home/AccountBalance/12"></script>

Due to the fact that a JSON array will be treated as a valid JavaScript script and can thus be executed. So, we need to wrap the JSON result in an object, just like what ASP .NET and WCF do. The ASP.NET AJAX library, for example, automatically wraps JSON data with { d: [] } construct to make the returned value to become an invalid JavaScript statement which cannot be executed:

{"d" : ["balance", "$7,000,000,000.00"] }

So, to avoid JSON Hijacking, we need to

  1. never return JSON array
  2. not allow HTTP GET request to get the sensitive data

Nowadays, even though JSON Hijacking is no longer a known problem in modern browsers, it is still a concern because “you shouldn’t stop plugging a security hole just because it isn’t likely to be exploited“.

By the way, GMail was successfully exploited via JSON Hijacking. =)

Summer 2015 Self-Learning Project

This article is part of my Self-Learning in this summer. To read the other topics in this project, please click here to visit the project overview page.

Summer Self-Learning Banner

Summer 2015 Self-Learning

Summer Self-Learning
It has been about half a year since I started to learn ASP .NET MVC and Entity Framework (EF). In this period of time, I have learnt about not just MVC and EF, but also Azure PaaS, Google Maps API, web application security, cool jQuery plugins, Visual Studio Online, etc.

In the beginning of May, I started to note down useful things I’d learned in my learning journey. Months of bringing together information in this summer has helped me compile my notes about what I’ve learned in the past 6 months. I have currently completed compiling notes for 17 topics that I’ve learnt in this summer.

I listed down the title of the 17 posts below to give you a quick overview about all the 17 topics.


ASP .NET MVC and Entity Framework


Microsoft Azure

Google APIs

Web Development Tools

Learning After Work

I’m working in Changi Airport. The office working hour is from 8:30am to 6pm. In addition, I am staying quite far from the airport which will take about one hour for me to travel from home to office. Hence, the only time that I can have sufficient time to work on personal projects is weekends.

This summer self-learning project is originally planned to be done by the end of May. Normally, it takes me about one day to finish writing a post. After that, if I find any new materials about the topics, I will then modify the post again. Sometimes, however, I am just too tired and I would not write anything even though it’s weekend. Hence, I end up finishing all the 17 topics three months later.

This summer learning project covers not only what I’ve learnt in my personal projects, but also new skills that I learn in my workplace. I always enjoy having a chat with my colleagues about the new .NET technology, app development, Azure hosting, and other interesting development tools. So yup, these 17 articles combine all the new knowledge I acquire.

I’m also very happy that that I am able to meet developers from both .NET Developers Community Singapore and Azure Community Singapore and share with them what I’ve learnt. That gives me a great opportunity to learn from those experienced .NET developers. =)

Azure Community March Meetup in Microsoft Singapore office.

Azure Community March Meetup in Microsoft Singapore office.

I am not that hardworking to work on personal projects every day. Sometimes, I will visit family and friends. Sometimes, I will travel with friends to overseas. Sometimes, I will play computer games or simply just sleep at home. So ya, this self-learning project takes a longer time to complete. =D

Working on personal projects after work is stressful also. Yup, so here is a music that helps reducing my stress. =)

Azure Cloud Service and SSL

I recently read an article written by Jeff Atwood on Coding Horror about whether we should encrypt all the traffic to our websites. I have a website which is utilizing external accounts with the help of .NET Identity, hence I must use HTTPs for my site before enabling users to login with their Facebook or Google accounts.

Purchase SSL Certificate from RapidSSL

My website is .NET web application with MVC 5 as front-end. It is also being hosted on Azure Cloud Service. For SSL certificate, I got it from RapidSSL.

RapidSSL Enrollment

RapidSSL Enrollment

Previously when I renewed the certificate for web application hosted on virtual machine, I could easily RDP to the virtual machine to configure its IIS settings. Now, the way to do it for Azure Cloud Service on the Azure Management Portal is a bit different.

Enter Certificate Signing Request (CSR)

In the process of purchasing SSL certificate on RapidSSL, I needed to submit CSR. To generate a CSR, what I did is just launching the IIS Manager on my Windows 8.1 machine. The process is pretty straightforward, as demonstrated on DigiCert website. The steps are as follows.

  1. Double click on “Server Certificates” feature of the local server;
  2. Under the “Action” panel, choose “Create Certificate Request…” link;
  3. Then there would be a window called “Distinguished Name Properties” popped out;
  4. Key in the correct information about the Common Name (which is the domain name of my website) and organization in the window;
  5. Choose “Microsoft RSA SChannel Cryptographic Provider” as the cryptographic service provider;
  6. Input “2048” as bit length.

CSR was generated successfully. I copied the generated text to RapidSSL textbox to continue the purchase.

Install SSL Certificate

After my payment went through, I received the certificate in text format via email from RapidSSL as shown below.


<encoded data>

I then copied it to a text file and saved the file with the .cer extension.

Then, I went back to the IIS Manager on my computer. In the same Actions panel where I created the CSR, I then chose another option “Complete Certificate Request…”. In the new window, I provided the .cer file generated earlier.

Update Service Definition File

After that, in the Visual Studio Solution Window of my web project, I added a <Certificates> section, a new <InputEndpoint> for HTTPS, and a <Binding> element to map the HTTPS endpoint to my website within the WebRole section in the ServiceDefinition.csdef file.

<WebRole name="MyWebsiteWeb" vmsize="Medium">
        <Site name="Web">
                <Binding name="HTTPSEndpoint" endpointName="EndpointS" />
        <InputEndpoint name="EndpointS" protocol="https" port="443" certificate="SampleCertificate" />
        <Certificate name="SampleCertificate" storeLocation="CurrentUser" storeName="My" />

Update Service Configuration File

In addition, I edited the ServiceConfiguration.Cloud.cscfg file with one new <Certificates> section in the Role section.

<Role name="MyWebsiteWeb">
        <Certificate name="SampleCertificate" thumbprint="xxxxxx" thumbprintAlgorithm="xxx" />

Both the thumbprint and thumbprintAlgorithm can be retrieved by double clicking on the .cer file.

Thumbprint and Its Algorithm

Thumbprint and its algorithm

Export Certificate as .pfx File

When I uploaded .cer file to Azure Management Portal, it couldn’t work. I had no idea why. Hence, I tried the alternative, which is using .pfx file. To do that, I first exported the certificate as .pfx file.

Firstly, I launched the Microsoft Management Console by running mmc.exe.

Export certificate from Microsoft Management Console.

Export certificate from Microsoft Management Console.

Secondly, I did the following steps to trigger the Certificate Export Wizard.

  1. File > Add/Remove Snap-in…
  2. Choose “Certificate” under “Available snap-ins” and then click “Add >”
  3. In the popup “Certificates snap-in” window, choose “Computer account”
  4. With the previous choice, I make snap-in to always manage in “Local computer”
  5. After clicking on the “Finish” button, I then click on the “Certificates” folder under “Personal” folder under “Certificates (Local Computer)” under the “Console Root”
  6. Right-click on the certificate that I want to export and choose export
  7. Finally the “Certificate Export Wizard” appears!

Finally, in the wizard, I followed the following steps to create a .pfx file of the certificate.

  1. Choose to export private key with the certificate
  2. Format will be Personal Information Exchange – PKCS #12 with all certificates in the certification path is included, if possible
  3. Enter a password to protect the private key
  4. Export
Certificate Export Wizard - Password and Private Key

Certificate Export Wizard – Password and Private Key

More detailed instructions can be found online, for example a page on Thawte about export a certificate from Microsoft IIS 7.

Upload Certificate to Azure

I then uploaded it to Microsoft Azure. It’s very simple. Just choose the cloud service and then upload the .pfx file (and enter the password used earlier for protecting the private key) to the certificate collection of the cloud service.

Upload certificate to Microsoft Azure in the Certificates tab.

Upload certificate to Microsoft Azure in the Certificates tab.

That’s all. It’s pretty straightforward, isn’t it?

If you would like to read more about Azure Cloud Service and SSL, please read the following articles which I find to be very useful.

Translate PBE Codes from Java to C#

It’s great to accept online payment via your website, right? However, during the implementation of payment gateway on e-commerce website, we sometimes will receive requests from bank to enhance the security of our payment process.

Payment gateway is important on e-commerce.

Payment gateway is important on e-commerce.

One of the requests we received is to provide their API a new value to verify the integrity of the payment process request. According to the requirement, the new value is using a Password-Based Encryption (PBE). The value must be encrypted using MD5 and DES algorithm with Base64 encoding.

The bank provided us a sample code of the encryption in Java.

private static int ITERATIONS = 1000;

public static String encrypt(char[] password, String plaintext, String algorithm)
    throws Exception {
    byte[] salt = new byte[8]; 
    Random random = new Random(); 

    PBEKeySpec keySpec = new PBEKeySpec(password);
    SecretKeyFactory keyFactory = SecretKeyFactory.getInstance(algorithm);
    SecretKey key = keyFactory.generateSecret(keySpec);
    PBEParameterSpec paramSpec = new PBEParameterSpec(salt, ITERATIONS); 

    Cipher cipher = Cipher.getInstance(algorithm); 
    cipher.init(Cipher.ENCRYPT_MODE, key, paramSpec);
    byte[] ciphertext = cipher.doFinal(plaintext.getBytes("UTF-8"));

    BASE64Encoder encoder = new BASE64Encoder();
    String saltString = encoder.encode(salt);
    String ciphertextString = encoder.encode(ciphertext); 

    return saltString + ciphertextString;

To use that, the documentation suggests us the following codes.

import java.util.Random;
import javax.crypto.Cipher;
import javax.crypto.SecretKey;
import javax.crypto.SecretKeyFactory;
import javax.crypto.spec.PBEKeySpec;
import javax.crypto.spec.PBEParameterSpec;
    String password = "xxxxxxxxxx";
    String textToEncrypt = "Hallo, world!";
    String algorithm = "PBEWithMD5AndDES";
    encrypt(password, textToEncrypt, algorithm);

As stated in the sample above, the algorithm is called “PBEWithMD5AndDES”, the password-based encryption as defined in RSA Security Inc. It takes a user-chosen password string and combine it with salt to generate the key by doing MDF hashing. It then applies the key on DES (Data Encryption Standard) cipher.

It looks complicated to me. Fortunately, I found a diagram describing the PBE encryption. I re-draw it so that it looks clearer.

PBE Encryption

PBE Encryption

What interest me are two items. One of them is Iteration, which has a value 1000 set to it without further explanation in the given sample code. There is already a discussion about this on StackOverflow. According to the discussion, iteration count is the number of times that the password is hashed during the generation of key. It is said that a higher iteration count will make the brute force hacking the key harder.

Another item that interests me is the salt. As shown in the diagram above, it does not use raw password to generate the key. Salt, a randomly generated bytes, is appended to the password. This is to prevent dictionary attacks.

Emulating PBE with C#

Unfortunately, our e-commerce website is built with .NET technology. Hence, I need to find out a way to encrypt data in C# in the same way as Java PBEWithMD5AndDES algorithm.

Firstly, I found a very helpful code from Bob Janova, a graduate from the University of Cambridge, on CodeProject. The code basically helps us to handle the key generation with MD5. It also takes care of the DES part with the help of DESCryptoServiceProvider class. As stated in the web page, it is very easy to use.

PKCSKeyGenerator kp = new PKCSKeyGenerator();
ICryptoTransform crypt = kp.Generate(
    salt, // salt
    1000, // iterations of MD5 hashing
    1); // number of 16-byte segments to create. 1 to mimic Java behaviour.

Right after crypt is instantiated, I do the following to make sure it is Base64 encoded. Similar code can be found on a discussion on StackOverflow regarding how to encrypt a string in .NET.

MemoryStream memoryStream = new MemoryStream();

CryptoStream cryptoStream = new CryptoStream(memoryStream, crypt, CryptoStreamMode.Write);

byte[] plainBytes = Encoding.ASCII.GetBytes(textToEncrypt);

// Encrypt the input textToEncrypt string
cryptoStream.Write(textToEncrypt, 0, plainBytes.Length);

// Complete the encryption process

// Convert the encrypted data from a MemoryStream to a byte array
byte[] cipherBytes = memoryStream.ToArray();


// Convert the encrypted byte array to a base64 encoded string
string cipherText = Convert.ToBase64String(cipherBytes, 0, cipherBytes.Length);

Finally, we get the encrypted data as stored in cipherText.

Yup, it is quite straight-forward, right? =)