App’s Security Terminologies

Basic terminologies when dealing with OAuth security in the context of app (web application, api, mobile application, etc).

Authentication
The process of proving you are who you say you are.

Authorization
The act of granting an authenticated party permission to do something. It specifies what data you’re allowed to access and what you can do with that data.

Identity
Refer to users who request access to resources. Users have to proof their identity is who they said they are, usually through authentication process.

Flow (aka. grant type)
Methods through which applications can gain Access Tokens and by which you grant limited access to your resources to another entity without exposing credentials.

Token
A piece of data contain information about users and apps. Common types of tokens:

  1. Id token
  2. A token used to identify user.

  3. Access token
  4. A token used to access some kind of resources, ie: api.

  5. Refresh token
  6. A token used to refresh access token.

Hash
Function that can be used to map data of arbitrary size to fixed-size values. The values returned by a hash function are called hash values, hash codes, digests, or simply hashes.

Encrypt
Convert (information or data) into a cipher or code, especially to prevent unauthorized access. Common methods of encryption:

  1. Asymmetric
  2. A form of encryption where keys come in pairs. Public keys which may be disseminated widely, and private keys which are known only to the owner. Public keys are used to encrypt and private keys are used to decrypt.

  3. Symmetric
  4. A form of encryption which only use one key (as opposed to pair of keys in Asymmetric). The key is used to encrypt and decrypt.

Decrypt
Make a coded or unclear message able to be understood.

Public private key
See asymmetric encryption above.

Federation
The linking a users’ electronic identity and attributes, stored across multiple distinct identity management systems.

References:
Microsoft
Auth0
Wikipedia

‘Microsoft.SqlServer.Types’ version 10 or higher could not be found

Tested on:
.Net Framework 4.5.1
Entity Framework 6.2.0

You get this error when using System.Data.Spatial features in .Net (for example: DbGeography Class or DbGeometry Class):

Spatial types and functions are not available for this provider because the assembly ‘Microsoft.SqlServer.Types’ version 10 or higher could not be found.

Possible solutions – you may need to implement all of them.
1. Install latest Microsoft.SqlServer.Types nuget package – this will create new SqlServerTypes folder in your project.
2. Add following code in Application_Start() method, located in Global.asax.cs file – change the SqlServerTypes version to the same version as the one you installed.

SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));
SqlProviderServices.SqlServerTypesAssemblyName =
    "Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

References:
Stackoverflow

Unit Test Entity Framework

Tested with:
.Net Framework 4.5
Entity Framework 6.2.0
Moq 4.13.1
MSTest 1.3.2

Github repository here.

This is a demo of how to write unit tests for Entity Framework.
This demo is based on Microsoft article.
It consists of testing EF query and non-query scenarios on both async and non-async calls.

The entity model looks like this.

2019-12-23 14_20_09-Infinity - Microsoft Visual Studio

The data access class look like this.

public class InfinityDataAccess
{
    private readonly InfinityEntities dbContext;
    public InfinityDataAccess(InfinityEntities dbContext)
    {
        this.dbContext = dbContext;
    }

    public IEnumerable<Misc> GetMisc()
    {
        using (this.dbContext)
        {
            return this.dbContext.Misc
                .Select(o => o)
                .ToList();
        }
    }

    public async Task<IEnumerable<Misc>> GetMiscAsync()
    {
        using (this.dbContext)
        {
            return await this.dbContext.Misc
                .Select(o => o)
                .ToListAsync();
        }
    }

    public bool InsertMisc(string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var newModel = new Misc
                {
                    Data = data,
                    Description = description
                };

                this.dbContext.Misc.Add(newModel);
                this.dbContext.SaveChanges();
                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public async Task<bool> InsertMiscAsync(string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var newModel = new Misc
                {
                    Data = data,
                    Description = description
                };

                this.dbContext.Misc.Add(newModel);
                await this.dbContext.SaveChangesAsync();
                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public bool UpdateMisc(int miscId, string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = this.dbContext.Misc.Find(miscId);

                if (dataModel != null)
                {
                    dataModel.Data = data;
                    dataModel.Description = description;
                    this.dbContext.SaveChanges();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public async Task<bool> UpdateMiscAsync(int miscId, string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = await this.dbContext.Misc.FindAsync(miscId);

                if (dataModel != null)
                {
                    dataModel.Data = data;
                    dataModel.Description = description;
                    await this.dbContext.SaveChangesAsync();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public bool DeleteMisc(int miscId)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = this.dbContext.Misc.Find(miscId);

                if (dataModel != null)
                {
                    this.dbContext.Misc.Remove(dataModel);
                    this.dbContext.SaveChanges();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public async Task<bool> DeleteMiscAsync(int miscId)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = await this.dbContext.Misc.FindAsync(miscId);

                if (dataModel != null)
                {
                    this.dbContext.Misc.Remove(dataModel);
                    await this.dbContext.SaveChangesAsync();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }
}

For query scenario on non-async call, it is pretty straight forward.

[TestMethod]
public void TestGetMisc()
{
    // Data to be returned
    var data = new List();
    data.Add(new Misc { Data = "Data1", Description = "Description1" });
    data.Add(new Misc { Data = "Data2", Description = "Description2" });
    data.Add(new Misc { Data = "Data3", Description = "Description3" });

    // Create mock data
    var mockData = new Mock();
    mockData.As().Setup(m => m.Provider).Returns(data.AsQueryable().Provider);
    mockData.As().Setup(m => m.Expression).Returns(data.AsQueryable().Expression);
    mockData.As().Setup(m => m.ElementType).Returns(data.AsQueryable().ElementType);
    mockData.As().Setup(m => m.GetEnumerator()).Returns(data.AsQueryable().GetEnumerator());

    // Create mock EF context
    var mockContext = new Mock();
    mockContext
        .Setup(mock => mock.Misc)
        .Returns(mockData.Object);

    // Assert
    var dataAccess = new InfinityDataAccess(mockContext.Object);
    var result = dataAccess.GetMisc();
    Assert.IsNotNull(result);
    Assert.AreEqual(3, result.Count());
    Assert.AreEqual("Description1", result.FirstOrDefault(o => o.Data == "Data1").Description);
}

The test for query scenario creates mock data of queried EF entity model and setup the mock’s Provider, Expression, ElementType and Enumerator. The return object of the setup are the correspondent properties of the returned data which is a type of IQueryable (see // Create mock data in the code above).
Next, the test creates mock of EF context. The mock is setup to return mock data we created earlier (see // Create mock EF context in the code above).

For non-query scenario on non-async call, it is almost similar to query scenario.

[TestMethod]
public void TestInsertMisc()
{
    // Data to be returned
    var data = new Mock();

    // Create mock EF context
    var mockContext = new Mock();
    mockContext
        .Setup(mock => mock.Misc)
        .Returns(data.Object);

    // Assert
    var dataAccess = new InfinityDataAccess(mockContext.Object);
    var result = dataAccess.InsertMisc("UnitTestData1", "UnitTestDescription1");
    Assert.IsTrue(result);
    mockContext.Verify(m => m.Misc.Add(It.IsAny()), Times.Once);
    mockContext.Verify(m => m.SaveChanges(), Times.Once);
}

The test creates mock data of EF entity model, but it is not necessary to setup the mock’s Provider, Expression, ElementType or Enumerator.
Next, the test creates mock of EF context (see // Create mock EF context in the code above).

For async calls, there are additional helper classes we need to create.
These classes are in-memory query provider to process async requests. We use custom query provider because EF use LINQ extension methods for async calls and these methods are designed to be used only with EF. If we try to use them with our unit tests, it will throw errors.
The 3 classes we need to create are:

  1. DbAsyncQueryProvider
  2. DbAsyncEnumerable
  3. DbAsyncEnumerator

With these helper classes, we can create mock data capable of in-memory async calls. This is the generic method to create the mock data.

private Mock GenerateMockDbSet(IQueryable mockData)
    where T : class
{
    var mock = new Mock();
    mock.As()
        .Setup(x => x.GetAsyncEnumerator())
        .Returns(new TestDbAsyncEnumerator(mockData.GetEnumerator()));

    mock.As()
        .Setup(x => x.Provider)
        .Returns(new TestDbAsyncQueryProvider(mockData.Provider));

    mock.As()
        .Setup(x => x.Expression)
        .Returns(mockData.Expression);

    mock.As()
        .Setup(x => x.ElementType)
        .Returns(mockData.ElementType);

    mock.As()
        .Setup(x => x.ElementType)
        .Returns(mockData.ElementType);

    return mock;
}

So for query scenario on async call, we just call the above method to create mock data for us.

[TestMethod]
public async Task TestGetMiscAsync()
{
    // Data to be returned
    var data = new List();
    data.Add(new Misc { Data = "Data1", Description = "Description1" });
    data.Add(new Misc { Data = "Data2", Description = "Description2" });
    data.Add(new Misc { Data = "Data3", Description = "Description3" });

    // Create mock data
    var mockData = GenerateMockDbSet(data.AsQueryable());

    // Create mock EF context
    var mockContext = new Mock();
    mockContext
        .Setup(mock => mock.Misc)
        .Returns(mockData.Object);

    // Assert
    var dataAccess = new InfinityDataAccess(mockContext.Object);
    var result = await dataAccess.GetMiscAsync();
    Assert.IsNotNull(result);
    Assert.AreEqual(3, result.Count());
    Assert.AreEqual("Description1", result.FirstOrDefault(o => o.Data == "Data1").Description);
}

There are more sample tests on the github repository to demonstrate every single scenarios in both async and non-async calls.

SQL Server View “Select *”

When creating a view in SQL Server, the view’s metadata is not being maintained automatically. This means if a source table’s columns are updated, SELECT * in a view would not return the updated columns.

See this post for example.

Possible solutions are:

  1. Create the view with a SCHEMABINDING option. See here. View with this option enabled won’t allow `SELECT *`, forcing developer to enter explicit columns.
  2. Related to previous solution, just don’t use `SELECT *`, explicitly define columns to return.
  3. Refresh the view’s metadata information with `EXEC sp_refreshview N’dbo.viewName’`

Refresh SQL Server View

Tested on:
SQL Server 2016 SP2
SQL Server Management Studio 14.0

To refresh SQL Server view, run following script:

EXEC sp_refreshview N'dbo.vwClass'

Example of issue with SQL Server view.

I have following table.
2019-11-12 13_58_55-Window

And a view called vwClass.

SELECT *
FROM dbo.Class

For the reasons unknown to me, when I right click on the view and choose ‘Select Top 1000 Rows’, the query does not return entire column of Class table. Instead, it return following:
2019-11-12 14_00_45-Window

After I run refresh view command above, the view return correct result.
Other possible solution maybe to avoid using SELECT * in a SQL Server view.

Setup SQL Server Connection Resiliency with Entity Framework

Tested on:
Microsoft SQL Server 13.00.5292
Entity Framework 6.2.0

When an application execute too many operations against SQL Server, a transient fault could occur.
Transient fault in SQL Server could occurred in self-hosted or cloud SQL Server. It could caused by many reasons and extremely (if not impossible) to debug.
You might encountered error messages like:

“Database * on server * is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of *.”

Other similar messages also point to transient fault errors.

Microsoft suggested solution to transient fault is to retry the executed operation, often dubbed as database connection resiliency. To set this up in Entity Framework, follow this step:

  1. Set EF DbConfiguration using code-based configuration, see this post.

  2. In the constructor, add following code

    SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy(10, TimeSpan.FromSeconds(50)));
    

  3. The first parameter of SqlAzureExecutionStrategy is the maximum number of retry and second parameter is the delay in milliseconds between retries.

Once set, EF will retry the faulted operation automatically. Consider performance when deciding how many tries and delay time to configure. The longer the try or delay time will increase operation time.

References:
Microsoft
Sql Server Backup Academy

Programmatically Configure EF DbConfiguration

Tested on:
Entity Framework 6.2.0

There are few ways to set EF configuration, this describes using code-based DbConfiguration.

  1. Create a class inherit from DbConfiguration.
    using System.Data.Entity;
    
    namespace InfinityDataModel
    {
        public class InfinityConfiguration : DbConfiguration
        {
            public InfinityConfiguration()
            {
                // Sample configuration
                SetDefaultConnectionFactory(new LocalDbConnectionFactory("InfinityDb"));
            }
        }
    }
    

  2. The class must be in same assembly as your the Entity Framework Data Model.

  3. In the constructor, set the configuration you wanted. For all possible configurations, see here.
  4. There is no need to do anything else. EF will load your configuration class when initialized.

References:
Microsoft