Encrypt values of a column - SQL Server

We can use T-SQL EncryptByKey method to encrypt data in a specific column and DecryptByKey method to decrypt that. In this post I’ll explain how to use those methods.
As the first task we will create a symmetric key and a certificate as below.

USE Staff;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Passwordabcd';
GO
CREATE CERTIFICATE Prod_Cert WITH SUBJECT = 'Key Protection';
GO

CREATE SYMMETRIC KEY My_Key WITH

    KEY_SOURCE = 'This is the symmetric key',
    ALGORITHM = AES_256, 
    IDENTITY_VALUE = 'This is the symmetric key'
    ENCRYPTION BY CERTIFICATE Prod_Cert;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE dbo.Employee
ADD EncryptedAge varbinary(128); 
GO

After that we can insert/update data to the column we just created while encrypting the data.

USE Staff;

GO

OPEN SYMMETRIC KEY My_Key
   DECRYPTION BY CERTIFICATE Prod_Cert;

UPDATE dbo.Employee
SET EncryptedAge
    = EncryptByKey(Key_GUID('My_Key'), 'my new value');
GO

Values in the column are encrypted as below.


Use below query to decrypt the value

OPEN SYMMETRIC KEY My_Key

   DECRYPTION BY CERTIFICATE Prod_Cert;
GO

SELECT Name, EncryptedAge 
AS 'Encrypted Age',
CONVERT(varchar, DecryptByKey(EncryptedAge)) 
AS 'Decrypted Age'
FROM dbo.Employee;

GO

You can get the decrypted value as below

Upload files to libraries using SharePoint services

We can use copy.asmx SharePoint web service to upload files when we can’t use SharePoint object model.
It’s very simple. you need to do something like below.

   1: const string webUrl = "http://sp13:8080/sites/hr/";

   2: const string sourceUrl = @"C:\Copy\emp.doc";

   3: string[] destinationUrl = { @"http://sp13:8080/sites/hr/Documents/emp.doc";

   4: CopyService.CopyResult[] resultArray;

   5: byte[] fileContents;

   6:  

   7:  var copyService = new CopyService.Copy

   8:   {

   9:     Url = webUrl + "/_vti_bin/copy.asmx",

  10:     Credentials = System.Net.CredentialCache.DefaultCredentials

  11:   };

  12:  

  13:  

  14:  var filedInfo = new CopyService.FieldInformation

  15:   {

  16:     DisplayName = "Title",

  17:     Type = CopyService.FieldType.Text,

  18:     Value = "Test"

  19:   };

  20:  

  21: //add values to fields in the library using fieldInfoArray array

  22: CopyService.FieldInformation[] filedInfoArray = { filedInfo };

  23:  

  24: //upload file using a file stream

  25: using (var stream = new FileStream(sourceUrl, FileMode.Open, FileAccess.Read))

  26:  {

  27:    fileContents = new Byte[stream.Length];

  28:    var read = stream.Read(fileContents, 0, Convert.ToInt32(stream.Length));

  29:    stream.Close();

  30:   }

  31:  

  32: var copyResult = copyService.CopyIntoItems(sourceUrl, destinationUrl, filedInfoArray, fileContents, out resultArray);

Disable offline synchronization for all libraries with specific template

Recently we got a requirement to disable offline synchronization for all Picture Libraries in a web application. Reason for the exclusion was that, those libraries contain large amount of pictures which take long time to download to the client.
We use following PowerShell script to disable offline synchronization for all Picture Libraries currently exist in the web application

   1: Start-SPAssignment -global

   2: $webs = Get-SPWebApplication "http://sp13" | Get-SPSite -Limit All | Get-SPWeb -Limit All | Foreach-Object { 

   3: Foreach ($list in $_.Lists | Where-Object { $_.BaseTemplate -eq "PictureLibrary"}){

   4:  $list.ExcludeFromOfflineClient=1; 

   5:  $list.Update()

   6:  }

   7: }

   8: Stop-SPAssignment –global

As a result, the synchronization process skips Picture Libraries

Configure SharePoint People Picker to include One-way trusted domains

If you have multiple one-way trusted domains in your SharePoint environment, you should explicitly configure people picker control. Otherwise users from those domains will not be searched.
To configure the people picker you need to follow these steps.

Configure encryption key
You need to type below command in each WFE servers

STSADM.exe -o setapppassword -password "key"

Configure people picker
Then you need to execute below command with privileged user for each domain. This command will configure people picker per web application and zone.

STSADM.exe -o setproperty -pn peoplepicker-searchadforests -pv "forest:sp.local;domain:apac.contoso.com,apac\user,*****;domain:emeia.contoso.com,emeia\user,*****" -url https://www.sp13

That’s all !. Now you can  search for users in one-way trusted domains in your people picker

Convert claims based login name in SharePoint

In SharePoint 2010 and 2013 we can have classic mode authentication as well as claims based authentication. In a classic mode scenario, if we get LoginName for a SPUser object it would result something like “Domain\LoginName”

But if the web application is configured with claims based authentication it will provide us something like “i:0#.w|Domain\LoginName”.

Let’s say we don’t need the prefix (e.g: i:0#.w ) and require only the valid login name portion (in this case Domain\LoginName). How do we get it ? Will something like below work ?

   1: var user = @"i:0#.w|dev\john";

   2: var encodedUser = user.Split('|')[1];

OK. it will work, but for this scenario only.

This is because that claims based environment is highly scalable where you can plug so many authentication providers. If you plug another authentication provider instead of windows authentication you may get different forms of encoded strings. Let’s assume you have federated authentication using email as login you would get something like below for LoginName

   1: i:05.t|Azure|myemail@gmail.com

You can see that string operations (e.g:split) we’ve used in above code will not work for this scenario. Furthermore there are so many other claim representations as well for other providers. You can get a complete list by referring this post from Wictor Wilen’s blog.

What is the recommended way to decode claims encoded string. We can get the help from “SPClaimProviderManager” class. Following code will decode claims string.

   1: private string GetLoggingName(string name)

   2: {

   3:     var manager = SPClaimProviderManager.Local;

   4:     if (manager != null){

   5:         return SPClaimProviderManager.IsEncodedClaim(name) ? manager.DecodeClaim(name).Value : name;

   6:     }

   7:     return name;

   8: }

It’s better to avoid string operations to decode/encode claims as we don’t know what providers will be plugged or unplugged in our claims environment future.

Set SharePoint default compatibility range after migration

SharePoint 2013 supports a great level of backward compatibility, where it allows us to run sites either in 2010 or 2013 mode. You can get more information on SharePoint 2010 and 2013 modes by referring to this article.
This feature comes in very handy for a migrated environment which has following requirements.

• Need to create new sites, but they should have SharePoint 2010 look and feel
• Site collection administrators should not be able to upgrade there sites to SharePoint 2013 until they feel it's 100% ready to do so

You may think above requirements are absurd. Why do we migrate an entire environment, and still keep the older look and feel. But they have some valid points.

• Users are not still ready for the new look and feel, and migrated environment should not cause any confusion for end users
• End user, super user, site administrator training is not complete yet.
• Policy where sites need to be upgraded gradually. (e.g.: home site upgraded first, and department site collections each week)

So how do we achieve the goal ?
We can modify the compatibility range web application property to arrive at a solution. We can use following Powershell commands to see current compatibility range

   1: $wa=Get-SPWebApplication "http://sp2013"

   2: $wa.CompatibilityRange

This provides us following information



The meaning of the above is that, if we create a site collection with default settings, the farm will create a site in SharePoint 2013 mode as the DefaultCompatibilityLevel is set to 15. Since the MinCompatibilityLevel is 14, it is possible to create sites with 2010 compatibility mode as well as 2013 mode with default settings (As shown in below image).



Apart from that, site administrators are notified that their SharePoint 2010 sites can be upgraded to SharePoint 2013 mode as the MaxCompatibilityLevel is set to 15.



Solution

We need to modify the compatibility range to suit our requirement. To do that we will execute following Powershell commands

   1: $wa=Get-SPWebApplication "http://sp2013"

   2: $range = New-Object Microsoft.SharePoint.SPCompatibilityRange(14,14)

   3: $wa.CompatibilityRange = $range

   4: $wa.Update()

   5: $wa.CompatibilityRange

The New-Object Microsoft.SharePoint.SPCompatibilityRange(14,14) instructs that both MinCompatibilityLevel and MaxCompatibilityLevel is set to 14 (SharePoint 2010 mode). So if we create new site collections, they will be in 2010 mode. (You can’t see the SharePoint 2013 option)



Furthermore site administrators are not notified about possible upgrade options.
If we need to revoke the setting later to allow SharePoint 2013 to be the default we need to execute following Powershell commands

   1: $wa=Get-SPWebApplication "http://sp2013"

   2: $range = New-Object Microsoft.SharePoint.SPCompatibilityRange(14,15)

   3: $wa.CompatibilityRange = $range

   4: $wa.Update()

   5: $wa.CompatibilityRange

In this scenario, MinCompatibilityLevel is set to 14 and MaxCompatibilityLevel is set to 15. The DefaultCompatibilityLevel is set to 15 which is as same as MaxCompatibilityLevel.

In a SharePoint migration scenario with strict policies and guidelines, CompatibilityRange property can be a lifesaver.

SharePoint 2010 mode in SharePoint 2013

If you’ve done a migration from SharePoint 2007 to SharePoint 2010, You may remember the visual upgrade features features of SharePoint 2010.

It was a cool feature. If the environment is still not ready for the drastically different SharePoint 2010 UI, They can still live with old SharePoint 2007 style UI.

But it had some drawbacks which makes it less usable. For an instance let’s assume we had web parts and some elements which we used in our SharePoint 2007 environment. Most of the time we get errors or they me not render properly in our new migrated environment.

SharePoint 2013 has improved a lot in the visual upgrade process and it provides us some additional benefits as well.

When we do a fresh installation, it’ll create both 14 and 15 folders in “Web Server Extensions” folder. (So we can say both 14 hive and 15 hive exists in SharePoint 2013 environment).

Why do we need both 14 and 15 hives in our SharePoint environment and what are the improvements in visual upgrade process ?

As far as I see, there are 2 main benefits

• Since it has Features, Layouts and Assemblies related to SharePoint 2010 solutions(WebParts and other elements) in 14 hive solutions will run seamlessly in a migrated environment.
• Apart from that when we deploy new SharePoint solutions (WSPs) we can target a specific compatibility level (SharePoint 2010 and 2013) as well by using the CompatibilityLevel Switch.
• We can scope site collections or entire web applications to SharePoint 2010 mode. If it is scoped in that way newly created site(s) will contain SharePoint 2010 UI and features. Simply say we can create sites in SharePoint 2010 compatibility mode as well as SharePoint 2013 compatibility mode.

In a separate post I’ll show some additional benefits of SharePoint 2013 compatibility modes.