Encrypt database using Transparent Data Encryption (TDE) technology in SQL Server

The Transparent Data Encryption (TDE) encryption technology protects the data in the database by encrypting the underlying files of the database, and not the data itself. So not just the sensitive data but all data in the database will be encrypted. This prevents the data from being hacked.

Assume you are having a database SampleDatabase with credit card details. If you open the backup file in Notepad and search for any card number, you should be able to see the actual data in the backup file. Hence anyone having access to the backup file can read the actual data, without restoring it.

To protect the sensitive data from users who do not have appropriate permission, encrypt the data using TDE feature as given below:

Step 1:

Create a Master Key or Encryption Key in the master database.

Use master;

Create Master Key Encryption By Password = 'C0mplexP@ssw0rd!';

Step 2:

Create a certificate for use as the database encryption key (DEK) protector and is protected by the DMK.

Create Certificate Cert4TDE

With Subject = 'TDE Certificate';

Step 3:

Create a database encryption key (DEK) encrypted with the certificate created in previous step. The supported encryption algorithms are AES with 128-bit, 192-bit, or 256-bit keys or 3 Key Triple DES. The created Master Key and the Certificate will be stored in the master database in an encrypted format.

Use SampleDatabase

Create Database Encryption Key With Algorithm = AES_256 

Encryption By Server Certificate Cert4TDE

Step 4:

Encrypt the data using the Master Key created in previous step.

Alter Database SampleDatabase

Set Encryption ON

Step 5:

We can verify the encryption using the below database query.

SELECT db.name,
    db.is_encrypted,
    ddek.encryption_state,
    ddek.key_algorithm,
    ddek.key_length,
    ddek.percent_complete
FROM sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys ddek
ON db.database_id = ddek.database_id;

GO

From the result we can see the SampleDatabase database is encrypted.

name	is_encrypted	encryption_state	key_algorithm	key_length	percent_complete
SampleDatabase	1	3	AES	256	0

Step 6:

Repeat Step 3 to backup the database again (with encrypted data) and open the backup file in Notepad. Now search the same card number and see that the data in the backup file is encrypted and secured.

Sample C# program to get all possible pairs in a list

This is a sample C# program to get possible pairs from a list of integer values. Duplicate and reverse pair values would be discarded in the program.

using System;

using System.Collections.Generic;



namespace PairFromList

{

    class Program

    {

        static void Main(string[] args)

        {

            var items = new List<int>() { 1, 2, 3, 4, 5 };

          

            for (var i = 0; i < items.Count - 1; i++)

            {

                for (var j = i + 1; j < items.Count; j++)

                {

                    Console.WriteLine(items[i] + "-" + items[j]);

                }

            }



            Console.Read();

        }

    }

}

Output :

1-2
1-3
1-4
1-5
2-3
2-4
2-5
3-4
3-5
4-5

Add Authorization Header Textbox in Swagger UI for Web API Basic Authentication

I wanted to test the Web API methods using Swagger UI. The Web API has Basic authentication enabled. For each request I need to pass the username and password in the format of base64 encoded. But by default the Swagger UI doesn't have any textbox to accept Authorization credentials parameters. To enable it I had to use the below code in SwaggerConfig.cs file. It should be available inside project's App_Start folder.

internal class AddRequiredHeaderParameter : IOperationFilter

{

 public void Apply(Operation operation, SchemaRegistry schemaRegistry, ApiDescription apiDescription)

 {

  if (operation.parameters == null)

   operation.parameters = new List<Parameter>();



  operation.parameters.Add(new Parameter

  {

   name = "Authorization",

   @in = "header",

   type = "string",

   description = "Authorization Header",

   required = true

  });

 }

}

Then add the below code inside the ConfigureSwagger(SwaggerDocsConfig config) method to register it.

private static void ConfigureSwagger(SwaggerDocsConfig config)

{

 // existing code

 // ...

 // ...

 config.OperationFilter<AddRequiredHeaderParameter>();

}

How to get a value from Active Directory using C# DirectoryEntry Class

Here is a sample C# program to get email id from active directory by username using DirectorySearcher class.

using System.DirectoryServices;

public string GetEmailIdFromActiveDirectory(string userName)
{

 var emailId = string.Empty;

 string activeDirectory_LDAP = "LDAP://server";

 string activeDirectory_User = "ad_username";

 string activeDirectory_Password = "ad_password";

 var directoryEntry = new DirectoryEntry(activeDirectory_LDAP, activeDirectory_User, activeDirectory_Password) { AuthenticationType = AuthenticationTypes.Secure };

 var directorySearcher = new DirectorySearcher(directoryEntry);

 directorySearcher.Filter = "sAMAccountName=" + userName;

 directorySearcher.SearchScope = SearchScope.Subtree;



 SearchResult searchResult = directorySearcher.FindOne();

 if (searchResult != null)

 {

  emailId = searchResult.GetDirectoryEntry().Properties["email"].Value.ToString();

 }



 return emailId;

}

How to get a value from Active Directory using C# PrincipalSearcher Class

Here is a sample C# program to get email id from active directory by username using PrincipalSearcher class.

using System.DirectoryServices.AccountManagement;

public string GetEmailIdFromActiveDirectory(string userName)

{

 var emailId = string.Empty;



 string activeDirectory_LDAP = "server";

 string activeDirectory_User = "ad_username";

 string activeDirectory_Password = "ad_password";



 PrincipalContext ctx = new PrincipalContext(ContextType.Domain, activeDirectory_LDAP);



 bool isCredentialsValid = ctx.ValidateCredentials(activeDirectory_User, activeDirectory_Password);



 if (isCredentialsValid)

 {

  UserPrincipal userPr = new UserPrincipal(ctx);

  userPr.SamAccountName = userName;



  PrincipalSearcher srchUser = new PrincipalSearcher(userPr);

  UserPrincipal foundUsr = srchUser.FindOne() as UserPrincipal;



  if (foundUsr != null)

  {

   emailId = foundUsr.Email;

  }

 }



 return emailId;

}

Create custom SQL exception with custom message in C# (CSharp)

Below is a sample code which is used to create a custom SQL exception with custom message. The parameter accepts the message to be thrown. It is mostly used in Unit testing scenario where you need to validate for a custom message.

Sample Code

private SqlException GetSqlException(string message)
{
    SqlErrorCollection collection = Construct();
    SqlError error = Construct(-2, (byte)2, (byte)3, "Server", message, "Prcedure", 100, (uint)1);

    typeof(SqlErrorCollection)
        .GetMethod("Add", BindingFlags.NonPublic | BindingFlags.Instance)
        .Invoke(collection, new object[] { error });

    var e = typeof(SqlException)
        .GetMethod("CreateException", BindingFlags.NonPublic | BindingFlags.Static, null, CallingConventions.ExplicitThis, new[] { typeof(SqlErrorCollection), typeof(string) }, new ParameterModifier[] { })
        .Invoke(null, new object[] { collection, "11.0.0" }) as SqlException;

    return e;
}

private T Construct(params object[] p)
{
    return (T)typeof(T).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance)[0].Invoke(p);
}

What is the Difference between String and string in C#?

String stands for System.String and it is a .NET Framework type. string is an alias in the C# language for System.String. Both of them are compiled to System.String in IL (Intermediate Language), so there is no difference. At execution time there is no difference. As far as guidelines, it's recommended to use string any time you're referring to an object.

Example:

string place = "Hello World";

It's generally recommended to use String if you need to refer specifically to the class.

Example:

var place = 'World!'

string message = String.Format("Hello {0}!", place);

Similarly for other datatypes,

object:  System.Object

string:  System.String

bool:    System.Boolean

byte:    System.Byte

sbyte:   System.SByte

short:   System.Int16

ushort:  System.UInt16

int:     System.Int32

uint:    System.UInt32

long:    System.Int64

ulong:   System.UInt64

float:   System.Single

double:  System.Double

decimal: System.Decimal

char:    System.Char

(Solved) Unable to access the IIS metabase error in Visual Studio project

Solution:

Navigate to the below path using command prompt.

On Windows 8 Pro:

%systemroot%\inetsrv\config

On Windows 7 and 8.1 and 10:

%systemroot%\System32\inetsrv\config (Where %systemroot% is usually C:\Windows)

You will be blocked access with a popup which says:

"You don't have access to this folder - Click continue to permanently get access to this folder"

Just click "Continue".

Reload your project again.