Skip to content
/

In a previous post I have written about Using the people picker over a one-way trust. In this post I use STSADM commands as there are no other ways to configure this. A downside of the STSADM command is your domain password being visible on the command prompt in clear text for everybody to read, or to retrieve from the command line history.

SharePoint 2010 introduces several cmdlets to replace the “old” STSADM commands. Microsoft has posted an overview of the STSADM to Windows PowerShell mapping. However the commands for configuring the people picker are not available.

In a previous post I have written about . In that post I use STSADM commands as there are no other ways to configure this. A downside of the STSADM command is your domain password being visible on the command prompt in plain text for everybody to read.

With SharePoint 2010 Microsoft introduces several cmdlets to replace the “old” STSADM commands. But looking at the you will see the commands for configuring the people picker are not present.

Creating my own script

PowerShell contains the which uses a dialog to request credentials from the user and stores the password in a . This triggered me to write a PowerShell script which will work the same as STSADM -o setproperty -pn peoplepicker-searchadforests, but instead of typing the credentials on the command line it will use the credential dialog for every trusted domain.

As written in my previous post the configuration is done in two steps.

SetAppPassword

First you need to create a secure store for the credentials. This is done by executing the SetAppPassword command on every server in your SharePoint Farm with the same password.

STSADM

stsadm -o setapppassword -password <password>

PowerShell

Set-AppPassword "<password>"
function Set-AppPassword([String]$password) {
  $type = [Microsoft.SharePoint.Utilities.SPPropertyBag].Assembly
                           .GetType("Microsoft.SharePoint.Utilities.SPSecureString")
  $method = $type.GetMethod("FromString", "Static, NonPublic", $null,
                                                                 @([String]), $null)
  $secureString = $method.Invoke($null, @($password))
  [Microsoft.SharePoint.SPSecurity]::SetApplicationCredentialKey($secureString)
}

PeoplePickerSearchADForests

The second step is to register the (trusted) domains to be visible in the people picker. Remember this setting is per web application and zone.

STSADM

stsadm -o setproperty -url <url> -pn "peoplepicker-searchadforests" -pv
  "forest:<source forest>;domain:<trusted domain>,<trusted domain>\<account>,<password>"

PowerShell

Set-PeoplePickerSearchADForests "<url>" "forest:<source forest>;domain:<trusted domain>"
function Set-PeoplePickerSearchADForests([String]$webApplicationUrl,
                                         [String]$value) {
  $webApplication = Get-SPWebApplication $webApplicationUrl

  $searchActiveDirectoryDomains = $webApplication.PeoplePickerSettings
                                                      .SearchActiveDirectoryDomains
  $searchActiveDirectoryDomains.Clear()

  $currentDomain = (Get-WmiObject -Class Win32_ComputerSystem).Domain

  if (![String]::IsNullOrEmpty($value)) {
    $value.Split(@(';'), "RemoveEmptyEntries") | ForEach {
        $strArray = $_.Split(@(';'))

        $item = New-Object Microsoft.SharePoint.Administration
                                         .SPPeoplePickerSearchActiveDirectoryDomain

        [String]$value = $strArray[0]

        $index = $value.IndexOf(':');
        if ($index -ge 0) {
            $item.DomainName = $value.Substring($index + 1);
        } else {
            $item.DomainName = $value;
        }

        if ([System.Globalization.CultureInfo]::InvariantCulture.CompareInfo
                                       .IsPrefix($value, "domain:","IgnoreCase")) {
            $item.IsForest = $false;
        } else {
            $item.IsForest = $true;
        }

        if ($item.DomainName -ne $currentDomain) {
            $credentials = $host.ui.PromptForCredential("Foreign domain trust"
              + " credentials", "Please enter the trust credentials to connect to"
              + " the " + $item.DomainName + " domain", "", "")

            $item.LoginName = $credentials.UserName;
            $item.SetPassword($credentials.Password);
        }

        $searchActiveDirectoryDomains.Add($item);
    }

    $webApplication.Update()
  }
}

Using the script

I have attached the script so you can use it in any way you want. You can put the commands in you own .ps1 file, or load the script in your current session using the following syntax:

. .\<path to file>PeoplePickerSearchADForests.ps1

(yes, that’s a dot, then a space, then the path to the script)

PeoplePickerSearchADForests.zip

/

With the introduction of LINQ the difference between writing code for accessing a lists of objects in memory and accessing a list of data in an external data source like SQL is vanishing. Combining a in memory with a external list in a single query was not yet possible. With the introduction of .NET Framework 4.0 this has changed.

With the introduction of LINQ the difference between writing code for accessing a lists of objects in memory and accessing a list of data in an external data source like SQL Server is vanishing. Combining an “in memory” list with an external list in a single query was not yet possible. With the introduction of .NET Framework 4.0 this has changed.
In this post I want to filter my SQL data using a list of integers I have stored in memory.

Data model

I have created a small data model as illustration. This will be used for the examples. Follow the link on the image below for a larger version. The model is part of the sample code.

Visual representation of the datamodel as described above.
Data model

IQueryable.Join

The first option you can use is to use the .

var entities = new DemoModelContainer();
var countryIds = Enumerable.Range(1, 3);

var query = from i in entities.Items
            from c in i.Countries
            join cid in countryIds on c.Id equals cid
            select i;

var items = query.ToList();

When you look at the generated SQL Statement (I love IntelliTrace!) you can see a temporary table is created by using the .

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name]
FROM
    [dbo].[Items] AS [Extent1]
    INNER JOIN [dbo].[CountryItem] AS [Extent2]
                               ON [Extent1].[Id] = [Extent2].[Items_Id]
    INNER JOIN (
        SELECT
            [UnionAll1].[C1] AS [C1]
        FROM (
            SELECT 1 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable1]
            UNION ALL
            SELECT 2 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable2]
        ) AS [UnionAll1]
        UNION ALL
        SELECT 3 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable3]
    ) AS [UnionAll2] ON [Extent2].[Countries_Id] = [UnionAll2].[C1]

What happens is that 2 values are combined in a set, this set is extended with the next value, this set is extended with another value, and so on, and so on, until all values are present.

This works nicely, but as you can imagine, the SQL Query will grow rapidly when the IEnumerable list gets larger.

When you change the following:

-var countryIds = Enumerable.Range(1, 3);
+var countryIds = Enumerable.Range(1, 50);

you will run into a SqlException:
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
SQL Server has a hard-coded limit of nesting queries, it doesn’t matter which version or edition of SQL Server you use.

IEnumerable Contains

Joining tables is one way of filtering data, an alternative is to use the . This can be done by using the .

var entities = new DemoModelContainer();
var countryIds = Enumerable.Range(1, 3);

var query = from i in entities.Items
            from c in i.Countries
            where countryIds.Contains(c.Id)
            select i;

var items = query.ToList();

When you look at the generated SQL Statement (Still loving IntelliTrace!) you can see now the is used.

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name]
FROM
    [dbo].[Items] AS [Extent1]
    INNER JOIN [dbo].[CountryItem] AS [Extent2]
                               ON [Extent1].[Id] = [Extent2].[Items_Id]
WHERE
    [Extent2].[Countries_Id] IN (1,2,3)

This SQL Query is much smaller and filtering against a larger set will not give you the exception.

This is how the WHERE statement looks when filtering on 50 values:

WHERE [Extent2].[Countries_Id] IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
                                   16,17,18,19,20,21,22,23,24,25,26,27,
                                   28,29,30,31,32,33,34,35,36,37,38,39,
                                   40,41,42,43,44,45,46,47,48,49,50)

Conclusion

You can use both methods to filter SQL data with a list in memory. But when choosing for the join method you should be really sure your query will not reach the maximum level of nesting.

I prefer to use Contains method.

Although I used a many-to-many relation in this example, the same applies for a one-to-many scenario.

Sample code

I’ve added a small Visual Studio 2010 project so you can see the SQL statements for yourself.

You will need SQL Server Express 2008 R2 installed on your machine to run the code.

IQueryableIEnumerableDemo.zip

Filed under C#, SQL
Last update:
/

With SharePoint 2010 the amount of databases on your SQL server has grown quite a bit. By default most of these databases have their recovery model set to 'FULL'. After some time you will discover you're running out of space.

With SharePoint 2010 the amount of databases on your SQL Server has grown quite a bit. By default most of these databases have their recovery model set to FULL. After some time you will discover you’re running out of space.

The problem

Most likely the problem lies with the transaction logs of your databases. With the recovery model set to FULL they will keep storing every transaction until you make a backup. Chances are you don’t configure a backup plan for you development environment as most development databases don’t need a backup as your sources will be stored in a source control system.

The (manual) solution

To solve this problem you can change the recovery model of each database by hand. For this you can open SMSS (SQL Server Management Studio), open the properties screen for a database and navigate to the options tab. There you will find the recovery model option.

Database Properties screen with the Recovery model set to 'Simple'
Database Properties screen with the Recovery model set to Simple.

Saving this change will empty your transaction log. But it will not shrink the physical file on disk. To shrink this file you can look at the Shrink task.

The context menu's to shrink the size of the log files
The context menu's to shrink the size of the log files.

The (automated) solution

Executing this step for every database manually is quite some work. So you want the easy solution.

The following TSQL script will change the recovery model for every database to Simple and shrinks the database.

USE [master]
GO

DECLARE @dbname SYSNAME
DECLARE @altercmd NVARCHAR(1000)
DECLARE @shrinkcmd NVARCHAR(1000)

DECLARE [dbcursor] CURSOR FOR SELECT [name] FROM sysdatabases

OPEN [dbcursor]
FETCH NEXT FROM [dbcursor] INTO @dbname

WHILE
  @@FETCH_STATUS = 0
BEGIN
  IF
    (SELECT DATABASEPROPERTYEX(@dbname, 'RECOVERY')) != 'SIMPLE'
    AND
    @dbname != 'tempdb'
  BEGIN
    SET @altercmd = 'ALTER DATABASE "' + @dbname + '" SET RECOVERY SIMPLE'
      EXEC (@altercmd)

      SET @shrinkcmd = 'DBCC SHRINKDATABASE ("' + @dbname + '")'
      EXEC (@shrinkcmd)

      PRINT @dbname
  END

  FETCH NEXT FROM [dbcursor] INTO @dbname
END

CLOSE [dbcursor]
DEALLOCATE [dbcursor]
/

When you have a SharePoint farm and you want to use accounts from another domain you need a partial (one-way) or a full (two-way) trust between those domain.
A full trust is not always desirable and there your problem begins. After setting up the one-way trust you can authenticate with an account from the trusted domain, but the SharePoint People Picker doesn't show any accounts from this domain.
It has been documented by others before, but as I ran into this recently I'll give my summary how I fixed this.

When you have a SharePoint farm and you want to use accounts from another domain you need a partial (one-way) or a full (two-way) trust between those domains.
A full trust is not always desirable and there your problem begins. After setting up the one-way trust you can authenticate with an account from the trusted domain, but the SharePoint People Picker won’t show any accounts from this domain.

It has been documented by others before, but as I ran into this recently I’ll give my summary how I fixed this.
This solution is the same for WSS 3.0/SharePoint 2007 as SharePoint 2010.

The problem

When using a one-way trust you don’t see any accounts from the other domain in the people picker.

SharePoint People Picker not showing any accounts.
People picker not showing accounts from the other domain.

The reason

This is an example of how you could use a partial trust.

Architecture with a company and a development domain setup with a partial trust.
Example of a one-way trust architecture.

You want to allow employees to authenticate in a development farm, but you don’t want to allow any test or service account from the development domain to authenticate in the company domain.

As the application pool account is based in the development domain it doesn’t have the right to query the company domain.

The solution

Using STSADM we can configure which forests and domains are searched for accounts by setting the peoplepicker-searchadforests property. The best part is that we can supply a username and password for a trusted domain.

SharePoint doesn’t allow you to store this username and password in plain text on the server. So you will have to configure a secure store. If you skip this step, configuring the search account for trusted domains will always fail with the following message.
Cannot retrieve the information for application credential key.
To create a credential key you will have to use the following command.

stsadm -o setapppassword -password <password>

This command has to be executed on every server in the farm.

Now you can configure the forests and domains you want to search using the following command.

stsadm -o setproperty -url <web application url> -pn peoplepicker-searchadforests
-pv forest:<source forest>;domain:<trusted domain>,<trusted domain>\<account>,<password>

You can combine any number of forests and domains, but you need to specify at least one.
You also need to include all forests and domains in one statement because every time you execute this command it will reset the current settings.

Also note this setting is per web application, and even per zone.

SharePoint People Picker showing an account from the one-way trusted domain.
People picker showing accounts from the other domain.
/

The other day I attended a meeting where the presenter switched from a PowerPoint slide to demonstrate an application. When he made the switch it was quite obvious the beamer was setup to only display the 4:3 slides to the maximum of the white screen. Since his desktop was in a 16:10 resolution the application was falling of the screen on both sides.
While I was preparing a presentation myself I wanted to be sure my presentation would be in the same resolution as my desktop as I would be switching between my slides and Visual Studio.

The other day I attended a meeting where the presenter switched from a PowerPoint slide to demonstrate an application. When he made the switch it was quite obvious the beamer was setup to only display the 4:3 slides to the maximum of the white screen. Since his desktop was in a 16:10 resolution the application was falling off the screen on both sides. Which was quite a distraction.

While I was preparing a presentation myself I wanted to be sure my presentation would be in the same resolution as my desktop as I would be switching between my slides and Visual Studio.

Choosing your Aspect Ratio

For presentations on a beamer or screen there are 3 common at the moment: 4:3, 16:9 and 16:10. If you don’t know the ratio you can use the maximum resolution to determine the required ratio.

4:3

This is the classic resolution for computer screens. This is also the default for PowerPoint. 800x600, 1024x768, 1152x864 and 1280x960 are common resolutions used with the ratio.

If you show a slide on wide screen you will get black bars on the side.

A PowerPoint slide with a 4:3 ratio shows black bars on the side using a 16:10 screen.
A 4:3 slide on a 16:10 screen. Showing black bars on the side.

16:9

This ratio is commonly used by HD screens and beamers. 1366x768, 1280x720, 1600x900 and 1920x1080 are common resolutions used with this ratio.

If you show a slide in this ratio on a 4:3 or 16:10 screen you will get black bars on the top and bottom.

A PowerPoint slide with a 16:9 ratio shows black bars on the top and bottom using a 16:10 screen.
A 16:9 slide on a 16:10 screen. Showing black bars on the top and bottom.

16:10

This ratio was commonly used by modern screen and beamers, but is now disappearing. 1280x800, 1440x900 and 1920x1200 are common resolutions used with the ratio.

If you show a slide in this ratio on a 4:3 screen you will get black bars on the top and bottom. On a 16:9 screen you will get black bars on the side.

A PowerPoint slide with a 16:10 ratio shows no black bars on a 16:10 screen.
A 16:10 slide on a 16:10 screen. No black bars showing.

Setting the aspect ratio in PowerPoint

It’s easy to set the required aspect ratio:

  1. In PowerPoint go the Design tab on the ribbon.
  2. Click on Page Setup.
    The PowerPoint application with the "Design" tab selected on the ribbon.
  3. At Slides sized for set the desired ratio.
    The "Page Setup" dialog with the ratio dropdown box displaying several ratio's including 4:3, 16:9 and 16:10.
  4. Press the OK button and you’re set.
Filed under Office
Last update: