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.

UserTable

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:

IdentityClient

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 Studio, 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.

UserLogonHistory

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 Code for LogonUserWithOTP

To be able to use the code we created in Create Shared Project for OTP handling, we need to reference this in our client applications.

In the Solution Explorer Window, Right-click the IdentityClient and select Add | Reference and choose your Shared Project

Add_Reference_Shared Project

Adapt the IdentityClient as described here: Code to adapt WPF client to use OTP WebService

This will produce this test-output:

IdentityClient_Run02

Showing a successful login.

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.

(function(i,s,o,g,r,a,m){i[‘GoogleAnalyticsObject’]=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,’script’,’//www.google-analytics.com/analytics.js’,’ga’);

ga(‘create’, ‘UA-72228030-1’, ‘auto’);
ga(‘send’, ‘pageview’);

Create the Table to securely store the Login and Password

First of all, we need a table to store the data related to the login in a safe way.

The table we will create, will look like this:

UserTable

This Table has these fields

  • ID: Autoincrement Integer internal database field as Primary Key
  • Identifier: Guid to use as Unique identifier in your application
  • Login: This is the unique login (typically e-mail address)
  • PasswordHash: This is the Hashed string of the actual password
  • Salt: This is the unique Salt field
  • Registered: This stores if the user is already registered or this is still pending
  • Active: This is a field that indicates if the account is already/still active

Why do I use a Hash and Salt fields

Hash

When we hash a string, we¬†receive a string that is impossible to transform back to it’s original form. But the most important fact¬†is that we will always get the same result¬†when we¬†use the same hash algorithm.

Salt

When two or more users would have chosen the same password, it would also be stored as the same Hash. This might be something we want to avoid, so we need to add some random pattern, which is unique to the user but only internal to the database.


Follow this link to get the SQL Server script: Script to create the table