Tuesday, December 29, 2015

SQL Server : sp_who2 - filtering and sorting the results for connections

The stored procedure sp_who2 lists all current processes connected to a SQL Server :
exec sp_who2

A typical resultset from Management Studio is :

Inline image


sp_who2 is one of the most useful and widely used stored procedures, along with its predecessor sp_who. However it is also one of the most frustrating as it only takes a single parameter and the results cannot be ordered. For a large server with a lot of connections this can be a real nuisance. I usually store the results in a temporary table and then filter and/or order the results from there :

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255), 
      BlkBy  VARCHAR(255),DBName  VARCHAR(255), 
      Command VARCHAR(255),CPUTime INT, 
      DiskIO INT,LastBatch VARCHAR(255), 
      ProgramName VARCHAR(255),SPID2 INT, 
      REQUESTID INT) 
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      * 
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY    DBName ASC
 
DROP TABLE #sp_who2
Some people encapsulate the above code in a stored procedure and run that, but my preference is always to run it as a script.

Finally

A word of warning. Sp_who2 is undocumented, meaning that Microsoft could change it in the future without warning. I've tested the code above on SQL Server 2005, 2008 and 2008 R2, however it's possible that the columns or datatypes returned could change in future versions which would require a small change in the code.

Friday, October 23, 2015

Impersonation and Elevation of Privilege - SharePoint

Introduction

Microsoft's SharePoint Services and Technologies (SharePoint) has a robust security model. Every operation attempted within the SharePoint framework is subject to the security settings and policies that apply to the affected objects. This article discusses the methods provided by the SharePoint framework that allow developers to accomplish tasks regardless of a user's permissions.

Need Alternate Security Context

When writing an application that is based on the SharePoint framework, it is common for the program to update or create a SharePoint object. A typical scenario is a feedback form on an anonymous website.The form needs to write data to a SharePoint list, but granting write permission to the anonymous user is not acceptable.
This article includes code and screenshots from anIdentity Web Part that displays information about identities used in the current request.
Note: The Identity Web Part is availablefrom the downloads library of this site.

Impersonation versus Elevation

Before discussing this topic further, we need to define the meaning of elevation of privilege and impersonation. The SharePoint Software Development Kit (SDK) provides the following descriptions:
Impersonation: Enables developer to perform actions on behalf of another user.
Elevation of privilege: Enables developers to programmatically perform actions in code using an increased level of privilege.
What is not clear in these definitions is the difference in the requirements to use these approaches. For example, in order to impersonate you typically require the user's credentials. In order to elevate, the elevated privileges must be available in the execution environment. Both of these approaches can be used in SharePoint.

Identities

SharePoint is built on the ASP.NET infrastructure. However, since a complete discussion of the ASP.NET infrastructure is outside of the scope of this paper, we will explore the concept of identities at a high level. Details that are omitted in this paper can be reviewed in the ASP.NET documentation on MSDN.
There are a few different identities in the ASP.NET pipeline. The first is the process identity that is set via the application pool in which the Web application is configured. In ASP.NET, the Default Application Pool is running with the System Identity. When SharePoint is installed, a new application pool is created and configured to use the service account specified during the configuration wizard. This is commonly referred to as the AppPool identity. (There is also a thread identity. The web server process spawns threads to handle each request, and these threads also use the process identity.)
 
The next identity is the context identity. When the ASP.NET framework processes a request, the impersonation and authorization settings in web.config indicate how the context identity is created. As indicated in the following code, the default SharePoint configuration uses Windows authentication and has impersonation enabled:
  1. <authentication mode="Windows" />
  2. <identity impersonate="true" />
Refer to the MSDN article titled "Authentication in ASP.NET: .NET Security Guidance" for more information on this topic.
The net effect of these settings is that the identity of the user running the browser is also the identity of the request. This identity is exposed to pages and controls as the Windows identity. The Windows identity is available to server-side code via the static method: System.Security.Principal.WindowsIdentity.GetCurrent().
SharePoint also requires an identity to perform security checks. In the SharePoint object model, the SharePoint identity is represented by the SPUser object. During the request pipeline, the SPUser is set to the same value as the Windows identity. However, they are not the same object, which means they can be altered independently.
Use the following code to retrieve the SharePoint value in code running on a Web page such as a Web Part:
  1. SPWeb web = SPContext.Current.Web;
  2. SPUser user = web.CurrentUser;
Note: Youcannot create an SPUser object. Instead, the SharePoint framework creates this object in the context ofa site collection (SPSite) based onthe context identity.

Identity Web Part

In Figure 1, the Identity Web Part is shown on the home page of a blank site. This Web Part displays the current values of the Windows identity and SharePoint identity. It also contains buttons to perform both elevation and impersonation. The current user account, which is Victor Visitor in this example, is a member of the site visitors group and has only read access to the site.
 
Figure 1 - Default Identities

Impersonation

There are a few scenarios in which impersonation of another user is helpful. One such scenario is the restricted access of a sensitive resource such as a database. This is the case for SharePoint when a service account is configured to access the content databases. This service account is then impersonated and the data retrieved. However, the users do not have permission to the database. The service account scenario is accomplished by storing and later retrieving the credentials of the impersonated account.
 
Note: Since the details about impersonation in ASP.NET are outside the scope of this whitepaper, refer to the "Impersonation and Delegation" article on Microsoft's ASP.NET Community site for more information on this topic.
 
Another common scenario is a background or deferred process that executes a user initiated function. This background process acts on behalf of several users, which means it runs with credentials such as LocalSystem that are appropriate for a background process. In this scenario, the requesting user account is impersonated and the requested action is performed.
In addition, there is a system account provided by the SharePoint platform that functions similar to a service account. The system account has permission to all SharePoint objects and can be used to update items using code that would denied if attempted by the requesting user account.. The system account can be impersonated without a password, which makes it a powerful alternative to granting user permissions throughout the farm.

Impersonate a User

To impersonate a user in the SharePoint framework, you must have a SPUserToken object. This object can be obtained by referencing the UserToken property of the SPUser object. However, the SPUser class cannot be created in code. Instead, a user token must be accessed and stored during a page request.
  1. SPUseruser = SPContext.Current.Web.CurrentUser;
  2. SPUserTokentoken = user.UserToken;
  3. // store token
At this point the background process can retrieve the token and use it to impersonate the requestor. The actual impersonation is performed by SharePoint during the creation of a SPSite object. Creating the SPSite object also requires the objects GUID or the site collection URL.
  1. stringurl = http://localhost; // use your url
  2. SPUserTokentoken = user.RetrieveToken(); // a custom method
  3. using (SPSite site = new SPSite(url, token))
  4. {
  5. // access the SPSite and its objects under the identity
  6. // represented by the token
  7. }
Retrievingthe SPUserToken of the system account requires similar code. Since the system account is a known principal, there is no need to store and retrieve the token.
  1. SPUsersystemUser = SPContext.Current.Site.SystemAccount;
  2. SPUserTokentoken = systemUser.UserToken;
  3. // store token
Figure 2 shows the code for the Impersonate button of the Identity Web Part that provides the user token as it creates a new SPSite object. This step is required to obtain a new SharePoint identity.

Figure 2 - Impersonate method

Effects of Impersonation

Running the impersonate method affects the SharePoint identity of the request. The Windows identity is still set by the base ASP.NET settings. Since the SPWeb object used to retrieve the current user was created within the scope of the impersonation, the SharePoint identity is set to SHAREPOINT\system, which is the predefined value of the system account (see Figure 3).

Figure 3 - Impersonated identities

When to Impersonate

Impersonating an alternate account is appropriate whenever code must perform an action within the SharePoint platform on behalf of a user. The impersonation method only changes the SharePoint identity of the request, not the Windows identity.
Best Practice:
If the code is expected to honor the permissions of the requesting user, the users token must be used to perform the impersonation.
 
Best Practice:
For code that updates SharePoint on behalf of a user without permissions, use the System Account token to perform the impersonation.
 
Best Practice:
If the current user cannot access the system account token, use the RunWithElevatedPrivileges method to retrieve the system account token and then impersonate. Do not perform all required actions with elevated privileges.
 

Elevation of Privilege

Elevation of privilege provides an increased level of privilege. However, from where does this level of privilege come? The possible choices are the involved infrastructure frameworks, which are IIS, ASP.NET, and SharePoint. The IIS process is configured to run with minimal privilege to reduce the attack surface open to malicious code and hackers. SharePoint is leveraging the identity of the HttpContext. Therefore, the natural place is from ASP.NET.
As mentioned previously, the identity of the ASP.NET process is configured in the Application Pool (AppPool). Since the AppPool identity is set during configuration of SharePoint, we can assume it has the privileges necessary to access the resources used throughout the farm including the database, servers, and services.

Elevate Level of Privilege

In the SharePoint platform, running code with elevated privileges is accomplished using the SPSecurity.RunWithElevatedPrivileges method. This method invokes a delegate that runs with the Windows identity set to the AppPool account. The password of the AppPool account is not required, as it is with Impersonation.
Figure4 shows the code for the Elevate button of the Identity Web Part. In this method, the RunWithElevatedPrivileges method runs an anonymous function, which is identitified by the delegate keyword.
 
Figure4 - Method to elevate privilege

Effects of Elevation

As discussed previously, the RunWithElevatedPrivileges method executes the specified code with the identity of the application pool. The Identity Web Part reflects this change, as shown in Figure 5.
Behind the scenes, there is significant code that creates a new application domain that has a separate security context. Next, the code provided to the RunWithElevatedPrivileges method runs within this separate application domain, which is reflected in the different Windows identity.
Figure5 - Elevated identities

When to Elevate

Since elevation of privilege does not change the SharePoint identity, performing the elevation is appropriate only when an alternate Windows identity is required. For code running on the SharePoint platform, the AppPool identity is the only available Windows identity. This means that elevation is only effective if the AppPool identity has the necessary permissions to the secured resource.
Best Practice:
Use elevated privileges to access non-SharePoint resources to which the application pool account has the necessary permissions.
 
Best Practice:
Use only the RunWithElevatedPrivileges method of the SPSecurity class to obtain a context with elevated privileges. Any other approach is not supported.
 

Summary

This articleexplains how developers can use impersonation and elevation of privilege when working with ASP.NET identities to accomplish tasks regardless of a user's permissions. The ability to use these methods allows developers to create solutions that work with, rather than work around, security settings.
 

Wednesday, May 27, 2015

SharePoint Tips: http://www.sharepoint-tips.com/

I found a good SharePoint knowledge blog http://www.sharepoint-tips.com/  SharePoint-tips and learning.

 

 

SharePoint: Finding if a site column exists in a site, by ID

Scenario - you have an ID of a site column (SPField belonging to SPWeb)and you want to find out if there is a field by that ID in the collection.

Problem: if you try something like:

web.Fields[fieldID] == null


The result is an exception if the field doesnt exist. What a shame. 

The solution is to use the Contains method of the Fields collection:

web.Fields.Contains(fieldID)



 

SharePoint: Users able to open documents using links, even without permissions

Users able to open documents using links, even without permissions

Recently I had to troubleshoot an issue where end-users were able to open links to documents they had no permissions to open. If they tried opening the library they got the "access denied" message that is expected, but clicking a link directly to a document in the library resulted in the document either opening up in the browser, or downloaded. We double checked the documents did not have item level security, and they didn't.

What a puzzle!

Turns out that those libraries were provisioned by code, and the code set a property on the library called "AllowEveryoneViewItems" (msdn documentation). This property, when set to true, means that anyone- even unauthenticated users, will be able to download and view items in the list or library - even without permissions. 

The reason to turn it to true is when dealing with anonymous sites - for example, if you have an internet site and you want to put links to documents from pages, but you don't want users to be able to browse the library itself.

 

Wednesday, April 15, 2015

Enabling OCR of TIFF images for SharePoint 2013 Search

SharePoint 2013 Enterprise Search has the built-in ability to OCR and index the content of your scanned tiff images during a crawl (whether they are are stored in SharePoint or not). This is a very powerful feature, yet a bit mysterious to configure as the configuration steps have changed since the 2010 version. I’ll outline the steps below:

1.      Using Server Manager, ensure the Windows TIFF iFilter feature is enabled on each crawl server

2.      Open the Local Group Policy Editor and locate the OCR folder beneath Computer Configuration > Administrative Templates.

3.      Edit the policy setting for “Select OCR languages from a code page”.  Choose Enabled and select the appropriate languages.

4.      Open the SharePoint Management Shell (using Run as Administrator) and run the following commands to configure content parsing for TIFF images.

5.   $ssa = Get-SPEnterpriseSearchServiceApplication

6.   New-SPEnterpriseSearchFileFormat -SearchApplication $ssa tif "TIFF Image File" "image/tiff"

New-SPEnterpriseSearchFileFormat -SearchApplication $ssa tiff "TIFF Image File" "image/tiff"

7.      Restart the SharePoint Search Host Controller service.

8.      Open the Search Service Application administration.  Under the Crawling navigation item, navigate to File Types.  Add two new File Types for tif and tiff.

9.      Perform a Full Crawl of your content.

Depending on how many TIFF images are crawled, this may be a considerably longer amount of time than your previous crawl time.  Additional planning may be necessary, such as potentially scoping a Content Source to only content that should be OCR’d, or adjusting crawl schedules.

 

Monday, March 9, 2015

Detection of product feature ‘PeopleILM’, component failed. THE RESOURCE DOES NOT EXIST (Event 1001-1004)

I recently set up the User Profile Synchronization services on SharePoint 2010 per a great article on Harbar.net: http://www.harbar.net/articles/sp2010ups.aspx

Everything seemed to run fine for several days, and then the synchronization failed to run at all and filled up the Events Log with all sorts of warning messages in regards to the MSIInstaller. 

The first issue I looked at was getting the Forefront Identity Manager Service to start following a reboot; the service simply refused to start automatically despite being configured by SharePoint to do so. Interestingly, both the User Profile Service and the User Profile Synchronization Service items listed in Central Admin's Services on Server page listed the services as running. Starting the FIM Service manually from the Windows Services snap-in succeeded. 

My solution was to set both services to start automatically at boot time after a delay by reconfiguring the startup type of BOTH services and Automatic (Delayed Start) in the Windows Services snap-in.  This at least got the services up and running, but the service would stop every time I tried to run the "Start Profile Synchronization" from the Manage Profile Service: User Profile Service Application screen.

In examining the Event Logs, I saw that there was was one more thing I apparently needed to clean up; Every time I tried to kick off the synchronization job, the logs would fill up with MSIInstaller warnings about product detection failing.  Specific was a series of 1004 and 1001 Event IDs:

Event 1004:

Detection of product '{90140000-104C-0000-1000-0000000FF1CE}', feature 'PeopleILM', component '{1AE472A9-E94A-41DC-9E98-F89A2821658F}' failed.  The resource 'C:\Program Files\Microsoft Office Servers\14.0\Tools\makecert.exe' does not exist.

Event 1001:

Detection of product '{90140000-104C-0000-1000-0000000FF1CE}', feature 'PeopleILM' failed during request for component '{1681AE41-ADA8-4B70-BC11-98A5A4EDD046}'

These were repeated for several other component GUIDs.

Now, as we know, the WMI calls are made under the credentials of the Network Service account (If in doubt about what account is trying to access the resource, the User: field is the tip-off).  For some reason during the configuration of the UPS, this account isn't given permissions on the folder indicated in the event ( "C:\Program Files\Microsoft Office Servers\14.0" ).

As there were multiple calls to various sub-directories under the "C:\Program Files\Microsoft Office Servers\14.0" folder I gave the Network Service account read and execute permissions on the folder and sub-folders.

 

After this, I went back into Central Admin –> Manage Profile Service: User Profile Service Application and clicked on "Start Profile Synchronization".  And we once again have Profile Synchronization with Active Directory working as verified by clicking on the "Synchronizing" status link and confirmed by opening the miisclient.exe on the server.

 

Monday, March 2, 2015

Free internet access : Internet.org [Facebook’s project to spread Internet connectivity to underserved areas with wireless carriers’ help.]

 

MORE details about internet.org :  

http://en.wikipedia.org/wiki/Internet.org

https://internet.org/about

 

RCom Offer Free Access To 38 Websites Including Facebook [Updated]

As expected, Reliance Communication announced their partnership with Facebook's Internet.org initiative.

The internet.org app will offer access to over 38 services (websites) completely free of cost. Reliance Communication customers in six Indian states (Tamil Nadu, Maharashtra, Andhra Pradesh, Gujarat, Kerala, and Telangana) can access to more than three dozen services ranging from news, maternal health, travel, local jobs, sports, communication, and local government information.

Here are the services offered by Internet.org

1.      Aaj Tak: Read news in Hindi

2.      AccuWeather: Get updated weather information

3.      amarujala.com: Read news in Hindi

4.      AP Speaks: Engage with local government

5.      Babajob: Search for jobs

6.      BabyCenter & MAMA: Learn about pregnancy and childcare

7.      BBC News: Read news from around the world

8.      Bing Search: Find information

9.      Cleartrip: Check train and flight schedules & buy tickets

10.   Daily Bhaskar: Read local news

11.   Dictionary.com: Search for meanings of words

12.   ESPN Cricinfo: Get cricket updates

13.   Facebook: Communicate with friends and family

14.   Facts for Life: Find health and hygiene information

15.   Girl Effect: Read articles and tips for girls

16.   HungamaPlay: Listen to music

17.   IBNLive: Read news

18.   iLearn: Learn from Women Entrepreneurs

19.   India Today: Read local news

20.   Internet Basics: Learn about the basics of the Internet

21.   Jagran: Read local news

22.   Jagran Josh: Get education and career information

23.   Maalai Malar: Read news in Tamil

24.   Maharashtra Times: Read news in Marathi

25.   Malaria No More: Learn about malaria

26.   manoramanews.com: Read local news

27.   Messenger: Send messages to friends and family

28.   NDTV: Read news

29.   Newshunt: Read news in English

30.   OLX: Buy and sell products and services

31.   Reliance Astrology: Read your horoscope

32.   Reuters Market Lite: Get farming and crop information

33.   Socialblood: Register to donate blood

34.   Times of India: Read news

35.   TimesJobs: Search for jobs

36.   Translator: Translate words and phrases

37.   Wikipedia: Find information

38.   wikiHow: Find information

How to Access?

Visit Internet.org website from your Android phone using Opera Mini mobile web browser. You will need to use Android app UC browser for internet.org. Most of the services will be available in English, Hindi, Tamil, Telugu, Malayalam, Gujarati and Marathi.