Create Secure Login with SQL, VS2015, Web Services in C#

A question that most developers sooner or later ask themselves is:

How to securely authenticate a user who is going to use my application or services?

This is what I want to tackle in this blog. As I stumble over the different parts involved, it will probably grow over time.

A lot of very good Frameworks and API’s exist to do this, but sometimes it is more fun (cheaper, prevents spam, …) to code ;-)

The info I provided here, is just for the fun of developing it myself and meanwhile playing around with the current tools and overall learning (the hard way) about all the components involved, which will probably make me appreciate the public (payable?) services on the internet.

So, let’s begin

1. Datastore for Login Info

First of all, we need a place to store the login information.

In the past I developed some LOB applications, which evolved using the different SQL server generations (6.5, 7.0, 2000, 2005) at that time, so looking at SQL Server 2014 for this personal project, seems to be the logical choice.

Here I describe the table structure: Table Structure for secure Login

I’m a big fan of creating small tables with not too many fields and have each additional need for related data (like address info, birth dates, whatever) to be stored in separate tables, and let the relational database do the joining.

As the table structure might change over time, and the so-called ‘business logic’ on what needs to happen in the background when accessing data (adding data, modifying and deleting it), I like to use Stored Procedures.

This way, when the underlying things change, I do not need to adapt any code in my applications and services that use it.

Here I show the Stored Procedure: Stored Procedure to Add Login

Security advise:

• Create specific internal SQL-server accounts and passwords to protect the tables, stored procedures,, …
• Change the access rights on the SQL server table which stores the login info, so that only the specific SQL-server account (and the administrator off course) can access it.
• Change the access rights of the Stored procedure likewise

2. Web Service to handle the Login actions

As I want to be able to use the authentication of the user from different platforms (currently Windows WPF and Windows 10 UAP, maybe later I will give a try for Android and IOS) and I’m not sure from what network, the applications will try to access it, using a WebService on the standard http/https ports would fit most scenario’s, as this can be used on internet connections routed through a proxy and most networks allow these ports (80/443) to pass unhindered.

Here I describe on how to Create the project in Visual Studio 2015: Create the Project

The minimal methods I implement here are:

• HelloWorld
  This is returning a fixed string, so I can see if the Service is running correctly
• AddUser
  This returns a unique Identifier, when a new Login is created in the database
• GetUserIdentifier
  This returns the unique Identifier of the user

The code for these is described here: Minimal Methods

Deploying the WebService

In order to be able to use this WebService from the internet, we need to deploy it to a web server.

This is how to use the Publish Wizard: Publish Website

For now, I’m not going into detail on how to configure your web server (IIS, or hosted on Azure, …). Send me a post if you would like me to describe this as well.

3. Create Client applications to test the service

We will create a WPF Client application to test the service we created earlier.

The finished project will look like this:

These are the steps to create the Project and add the testing code: Create the WPF Client to test the Web Service

to do – show the UAP Creation and calling of the WebService

4. Add a OneTime Password (OTP) for Login and WebService Calls

While having to use a password is a basic requirement to authenticate the user, I think it has a lot of advantages to force the use of a Onetime Password as well.

This not only to authenticate the user , but also to make sure that when your WebService needs to do something, it has an additional check to make sure that this request was asked by the user or application on behalf of the user and is within an appropriate timeframe. (This should protect a bit against somebody capturing your web-request and replaying it)

This can also be used to make sure that when you receive a request back from the WebService, that you have a bit more guarantee that it actually comes from that WebService.

Some background: Create and use OneTime Passwords

As I want to use this OneTime Password (OTP) both in our clients and our WebService, I think it is best to use a Shared project.

The steps to follow to create the Shared Project and the OTP code, can be found here: Create Shared Project for OTP handling

Unfortunately in Visual Studio 2015, you can not (due to bug ?) use the GUI to add a reference to the Shared project. To do this yourself, close Visual Stidio, and open the AuthenticatorService.csproj file in notepad, and above the tag <ProjectExtensions>, add the line:
<Import Project=”..\OTPSharedProject\OTPSharedProject.projitems” Label=”Shared” />

To handle the Logon of a user, we will adapt our SQL server database, by adding a table to log all the Login attempts and create an additional Stored Procedure.

Here I describe the table structure: Table structure to Track Logins

Here I show the Stored Procedure: Stored Procedure to Track User Login

In Visual Studio, select your WebService project and add the Method LogonUserWithOTP

to do – show Add the Shared Project to the Client projects

5. Secure the WebService Calls

So far, the communication between the clients and WebService all happened in plain text, using the http protocol.

To augment the security, it is best to swap to using https:

For this, you need to purchase a public SSL certificate, and install this on your webserver. I’m using some from SSLS.com to do – show how to modify the WebService and Client projects to use https.