Tuesday, January 15, 2019

PowerShell to validate certificate


There is a WPF based enterprise application working based on certificate issued into the system by the certificate authority. When the application starts it make sure there is a certificate in the store which is issued by the enterprise. For remote service calls, certificate chain based trust is used to ensure the WCF communication is secured. The servers will accept the client request which comes with certificate issued by the same certificate authority of what is in server.


One day accidentally someone issued one more cert to the client machine in the same name. Then the poor application stopped working as it doesn't know what to do when there are more than one certificate.

Panic situation started. Some are sure that in such situation one of the certificate should be revoked. Some tells application should have intelligence to choose the cert with longest expiry, etc... 

How to understand what is really happened? Need to look into one of the client machine to which developers barely have access. So don't even think about installing or giving an test application. 

Modifying the application is not a big deal but without understanding what is happening is waste of time. The problem here is understanding what is happening in client machine. This might seem very silly to a fresher or someone working for public products. Welcome to Enterprise, it doesn't work that way in Enterprise.

PowerShell to rescue

The PowerShell is really a revolution where it helps developers to run code in restricted environments otherwise they cannot do anything via installer or utility exe. .bat files were there, but that won't let developers run C# code as is in a machine without compilation.

Lets see the snippet which will help us to check whether the certificates are revoked or not.

Get-childitem Cert:\CurrentUser\My -recurse | %{ write-host $_.Subject ; Test-Certificate -cert $_ }
The main API is the Test-Certificate command let. The initial code fragments are used to iterate the certificate store.

Happy scripting...

Tuesday, January 8, 2019

Searching for users in Active Directory

As developers for windows based systems, there are situations where we need to deal with Active Directory. This post is about searching user in Active Directory from C#. It can run from desktop as well as web applications as long as the user running application has the permission to AD and AD is reachable via network.


Knowledge about Active Directory and terms such as Forest, Global Catalog etc... are preferred to understand this post

Searching in particular AD Forest

Here the input is the search string and the name of the AD Forest

public static IEnumerable<ADMember> GetUsersByADForestName(string searchString, string nameOFADForest)
    DirectoryContext rootDomainContext = new DirectoryContext(DirectoryContextType.Forest, nameOFADForest);
    Forest forest = Forest.GetForest(rootDomainContext);
    return GetUsersFromForest(searchString, forest);

As seen above, the Forest object is created using DirectoryContext which is created using the name of AD Forest.

The GetUsersFromForest() method is given below which does the search

private static IEnumerable<ADMember> GetUsersFromForest(string searchString, Forest forest)
    GlobalCatalog catalog = forest.FindGlobalCatalog();
    using (DirectorySearcher directorySearcher = catalog.GetDirectorySearcher())
        //Use different options based on need. Below one is for searchnig names.
        directorySearcher.Filter = $"(&(objectCategory=person)(objectClass=user)(|(sn={searchString}*)(givenName={searchString}*)(samAccountName={searchString}*)))";

        SearchResultCollection src = directorySearcher.FindAll();

        foreach (SearchResult sr in src)
            yield return new ADMember
                Title = GetPropertyValueFromSearchResult(sr, "title"),
                FirstName = GetPropertyValueFromSearchResult(sr, "givenName"),
                MiddleName = GetPropertyValueFromSearchResult(sr, "middleName"),
                LastName = GetPropertyValueFromSearchResult(sr, "sn"),
                Phone = GetPropertyValueFromSearchResult(sr, "phone"),
                Email = GetPropertyValueFromSearchResult(sr, "mail"),
                DisplayName = GetPropertyValueFromSearchResult(sr, "name"),
                UserName = GetPropertyValueFromSearchResult(sr, "samAccountName")

Hope the above code snippet is self explanatory. The ADMember is just a class with required properties. Below goes the GetPropertyValueFromSearchResult method.

private static string GetPropertyValueFromSearchResult(SearchResult searchResult, string property)
    return searchResult.Properties[property].Count > 0 ? searchResult.Properties[property][0].ToString() : string.Empty;

The assembly references needed are given below
1. System.DirectoryServices.dll
2. System.DirectoryServices.AccountManagement.dll

Searching in current AD Forest and its trusted Forests

There could be scenarios where we need to search the current AD Forest and any other forests trusted to it. Below goes the code for it.

private static IEnumerable<ADMember> GetUsersByADForestNameAndItsTrustedForests(string searchString)
    searchString = EscapeForSearchFilter(searchString);
    List<ADMember> userListFromActiveDirectory = new List<ADMember>();

    var currentForest = Forest.GetCurrentForest();
    userListFromActiveDirectory.AddRange(GetUsersFromForest(searchString, currentForest));

    IEnumerable<ADMember> userListFromGlobalCatalog = GetUsersFromTrustedForests(searchString);

    return userListFromActiveDirectory;
private static IEnumerable<ADMember> GetUsersFromTrustedForests(string searchString)
    var forest = Forest.GetCurrentForest();
    List<ADMember> userInfo = new List<ADMember>();
    var relations = forest.GetAllTrustRelationships().Cast<TrustRelationshipInformation>();
    var filteredRelations = relations.Where(IsTheTrustValid);
    Parallel.ForEach(filteredRelations, (TrustRelationshipInformation trust) =>
        Trace($"TrustedRelation. Source {trust.SourceName}, TargetName {trust.TargetName},{trust.TrustDirection},{trust.TrustType}");
            DirectoryContext rootDomainContext = new DirectoryContext(DirectoryContextType.Forest, trust.TargetName);
            Forest trustedForest = Forest.GetForest(rootDomainContext);
            var userDetails = GetUsersFromForest(searchString, trustedForest);
            if (userDetails.Any())
        catch (Exception ex)
            Trace($" Searching exception {ex.Message} for TrustedRelation. Source {trust.SourceName}, Destination {trust.TargetName}.  Continuing...");
    return userInfo;
private static bool IsTheTrustValid(TrustRelationshipInformation trust)
    return (trust.TrustDirection == TrustDirection.Bidirectional || trust.TrustDirection == TrustDirection.Outbound)
        && trust.TrustType == TrustType.Forest;

All the 3 methods are present in the above snippet which helps to filter the trust relation and to create object of Forest from TrustRelation object. 

Please note the code snippet is for demonstration purpose only. It has to be tweaked for production especially when we are dealing with parallelism.

Tuesday, December 11, 2018

Encrypting the ADO.Net connection to SQL Server and verification

Why should we encrypt ADO.Net communication

We all might have heard about encrypting the web server traffic using http(s) protocol. It make sense to any beginner that it should be encrypted since the communication is going via vulnerable public internet. But ADO.Net is also providing us option to encrypt the connections to SQL Server Database. Lets why some reasons to encrypt.

Connecting from client machine to database server

Nobody might be doing direct connections from client to database using windows identity or custom identities. But it was an option people used earlier and if we ended up in legacy systems this is one thing to take care. A low hanging fruit to increase security.

Connecting from web or queue processing server to ADO.Net

This is more often scenario. The client machine to web server communication is already encrypted. Nobody can intercept it. Once the communication reaches the hosting environment, it is difficult for an outside attacker to intercept the communication between web server and database server. This is true if we are in a corporate environment with someone else taken care of the network level protection.

Still there are chance that some insider attacker can intercept.

When we are in cloud environment

Another reason to encrypt the database communication is the cloud hosting. Though the cloud vendor says they are the best in the world to secure everything and obliged to keep things secret, there is still a factor of belief. What if something goes wrong and someone intercept the communication? So it is better to encrypt than taking a chance.

If someone hacks into the environment and obtain the encryption key as well, there is nothing to be done. For example the cloud provider is in a country which is in war with our country and our application is significant enough to help them to win the war. Its Unavoidable. If there is effect, there will be some side effects. We save cost but exposed to less secure environment. 

How to encrypt the SQL Server Communication

Though encrypting the communication is to be final answer to the security, let's see how to encrypt. SQL Server supports mainly certificate based encryption. In other words the certificate as key for encryption or used for key exchange to finalize the encryption. Digging into the details of how it works is not in the scope of this post. Below are some links as those are readily available.



More visual help here.

When the connection gets encrypted? It is a great question. There are so many combinations of configuration and below link explains when the SQL Server encrypt, when it use self signed certificate, when it fails etc...

Problem 1 - When connecting from SQL Profiler

Client unable to establish connection ssl provider the target principal name is incorrect


We have to use the full computer name when connecting.

Other problems

There are variety of problems users reported when they enable encryption without reading all the docs. Some are related to permission of SQL Account to the certificate, some related to expired or wrong issued to certificates, some experienced because the certificate was in wrong store etc...

Verify the ADO.Net connection is encrypted

As we saw earlier, even after doing the settings there are chances of connection falling back to plain mode without encryption. This section is about how to ensure the connections are encrypted.

Using the sys.dm_exec_connections

There is a DMV called dm_exec_connections. It can be simply queries like below to check the connections and its encryption status.

SELECT session_id, connect_time,client_net_address, net_transport, auth_scheme, encrypt_option, local_tcp_port
FROM sys.dm_exec_connections
WHERE net_transport = 'TCP'

Play with the above code to explore more properties of  SQL server connections. If we need to check whether the connection is encrypted from .Net code, we have to use the @@SPID to get the connection detail in the same DMV.

What is the certificate used for encryption? - Registry

After we enable the encryption via user interface, we can go to registry and ensure the certificate. Below is the location in registry.

HKLM:\Software\Microsoft\Microsoft SQL Server\<SQL InstanceID>\MSSQLServer\SuperSocketNetLib

As instructed in the guidelines, the encryption is enabled per SQL Instance. So one machine can have 2 SQL Instances. One encrypted and other not.

What is the certificate used for encryption? - SQL ErrogLogs

The SQL logs is another place to ensure the certificate what is used for encryption.



Tuesday, December 4, 2018

AngularJS Sunset

I was in the mood that AngularJS will live for some more time after the introduction of Angular. That came when the team announced that they will monitor the downloads of AngularJS and define the retirement strategy later. 

But now things have changed. Angular team announced a road map to the end of AngularJS with 1.7 version. When they say end, its the end of new releases and official support meaning there won't be any security patches to the AngularJS framework after the currently set LTS period.

What is AngularJS sunset means to us

The period stared from Jun 30, 2018 and it is till June 30, 2021. It doesn't mean they will release patch for each and every thing. The exact criteria is outlined in the announcement. 


We can use AngularJS ever after that period but if there are new security holes identified, hackers can exploit that to do whatever they can. So it is better to migrate to Angular from now itself. We have 3 years to get the migration done.

Bye bye AngularJS

Tuesday, November 27, 2018

Architecture v/s Code - Validating sys admins in SQL Server

In the misused agile world, it is always difficult to make sure the code is following the Architecture or Architecture document is following the code. 'Misused agile' means, using the word agile to make developers work all day and night for frequent releases. They struggle to meet the releases and there are high chances for taking deviations, shortcuts or hacks which eventually ends up Architecture and code going in 2 separate directions.

One if the best way to ensure they are inline by using Simon Brown;s C4 architecture model. C4 clearly document the structure of the code. One area which that model is not exactly covering as is the security model of the execution. Architects has to review the deployment document before any deployment or at least after deployment to ensure the security model is right.

With no further introduction let us take a problem of service accounts having higher permissions than needed. Either as Architects we can review each and every DB Server Instance and raise alarms or defects. Or make it automated.

Below script gives the list of logins. By looking at the sysadmin flag we can determine the role and raise a alarms. This can even be integrated with CI/CD systems as validation rule to block the deployments, if database updates are going via CI/CD pipeline.

SELECT loginname, sysadmin

FROM sys.syslogins

ORDER by sysadmin DESC

Happy reviewing. 

Tuesday, November 20, 2018

Detecting whether the code is running under Karma test runner


Putting the disclaimer in the beginning as it is a bad practice to check whether our code is running under the test and do actions based on it. Ideally it should be done via dependency injection and mock objects.

But still to document there is an option to check whether the code is running under test and trying to explain why its needed in one scenario.

How to check code is running by Karma

The idea came from the below SO question.


Before going into the code let us take some time to understand Karma. It is a test runner. It runs the JavaScript application by starting browser instance(s). Those instances can be either with UI or headless. Headless means no UI or consider as in memory.

Lets understand the code

function isKarmaRunning(){
  let isKarmaRunning = false;
  if (typeof window["__karma__"] !== "undefined") {
    isKarmaRunning = true;
  console.log(`[bootstrap] isKarmaRunning - ${isKarmaRunning} ${typeof window["__karma__"]}`); // still undefined
  return isKarmaRunning;  

It is self explanatory and show the danger itself. It assumes when the code is run by Karma test runner, the window object will get a properly called __karma__. We can detect the presence of Karma by checking that property.

The danger is that in future when Karma decided to stop setting the __karma__ property or change its name, our code will fail.

Thanks to laukok for his question with solution.

One scenario to detect whether Karma is running

This is not a scenario where we must check for Karma. Rather its hack and you are free to suggest how to do it right.

The scenario is where AngularJS and PWA (Progressive Web Apps) are combined. PWA's ServiceWorker feature is used to intercept the web requests to populate cache or serve from cache instead of hitting server. The cache is filled to ensure that the application can be used even without internet connection.

The ServiceWorker has fetch API which is used to intercept the web requests. fetch API hook starts only after the service worker is properly initiated. It may take up to 100ms as per different sources. If AngularJS application is bootstraped before ServiceWorker comes into active mode, it will not catch the web requests hence not cached. The web requests include the requests from ng to get the html views

If the user is still online and use the application, there are chances that those web requests are again intercepted and stored into cache. But what if the user first launch the application and went to offline. The cache is not populated hence error.

In order to work the initialization delay one of the solutions is to explicitly bootstrap the Angular application once the ServierWorker has started. Then the problem is that the tests written will complain that the Angular application is not bootstraped. Then either we need to change all the tests to wait for the ServiceWorker to initialize or have the application changes to do below

If the application is running under Karma, boo Angular immediately. Else wait for the ServiceWorker to initialize.

Too complicated. Isn't it. As an software engineer we should have gone back to original problem and solved the issue by separating the components and directives to separate ng module or one of the other approaches. 

Hopefully once there is enough time to analyze there will be a follow up post with better solution. In mean time feel free to pour your comments.

Tuesday, November 13, 2018

Freemium - What is always free in clouds platforms


There are any different pricing model in the software industry which doesn't require any money to be spend to use software. Trial, totally free, freemium, sand boxed trial account etc...They are different in their own way. Totally free and freemium are the models which really help us build something based on the offering. Freemium might not be a new word for a follower of this blog. There were posts which has freemium reference such as Serverless monitoring, online diagramming tools, tool box for open source more precisely developers who don't want to invest on tools.

Once again Freemium is a model where we can use systems in free but for advanced usage we need to pay. Event if we don't want advanced features the basic features are free for ever. Refer wiki for the Freemium

Freemium cloud features

Lets see what is there from Cloud providers in freemium tier. ie what is there always free which can help us to develop production prototypes and start own company without investing any money other than domain if we want.

Please note that, these are not the free account or free trial which we cannot depend on to put something into production.


Since the cloud providers may add more services as freemium, only links are provided not another copy paste which needs more time to make it up to date.

Freemium doesn't necessarily guarantee that it its free for lifetime. The vendors have their rights to change the pricing.

More links