Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

Is there a way that you get information from an external list (created from SPD for a specific ECT) differently than you do from a "normal" SharePoint list when using PowerShell?

I did the following:

  • created a custom List "ListTest" with a few text columns (eID, c1, and c2).
  • created an ECT in SharePoint Designer to a SQL Server table with the same column names
  • created an external list "ExtListTest" from this ECT (created the list from SPD too).

I set full access to the ECT to the farm account I am running the PowerShell ISE as (spdev/spfarm), and while troubleshooting added access to All Authenticated Users and NT AUTHORITY\Authenticated Users too.

I then issued the following PowerShell commands:

$web = get-spweb http://<sitename>
$list = $web.Lists["ListTest"]
$elist = $web.Lists["ExtListTest"]
$listitem = $list.GetItemById(1)
$listitem["eID"] 

everything works as expected with the "normal" list up to here.

Then I enter:

$elist.GetItemById(1)

And I get the following error:

Exception calling "GetItemById" with "1" argument(s): "Attempted to perform an unauthorized operation."
At line:1 char:19
+ $elist.GetItemById <<<< (1)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

So something is different. I am doing something wrong or there is an extra step or permissions needed to access an external list via PowerShell.

==

Information on my configuration. I have a single server farm on Windows 2008 R2, with SP2010 and SQL Server 2008 R2 on the same machine. It is a member of a domain (SPDEV) and I am logged into the server as SPDEV\SPFARM (the farm account) and running PowerShell ISE (right-click run as administrator) on the same server. Update on configuration info I configured the External Content type to use Passthrough Authentication to the SQL Server database. I am using the same account (SPDEV\SPFARM).

Update 11/12/2011 I found the following error in my SharePoint logs that seems to give another clue:

External List: User passes list permissions check, but an unsupported authentication configuration was identified. Operation disallowed.

share|improve this question
    
Based on the suggestion below, I am wondering if there is some other pre-setup that I need to do. I tried "getting the context and scope" but that didn't seem to help, and I am not exactly sure how I would have known I needed to get or set the context and scope before accessing an external list. –  lwbecker2 Nov 12 '11 at 16:54
add comment

2 Answers 2

up vote 2 down vote accepted

The answer to my question is "Yes, there are potentially some differences in some parts of accessing external lists from PowerShell".

I believe I figured out all the errors I ran into that I posted in my question, updates and comments.

Exception calling "GetItemById" with "1" argument(s): "Attempted to perform an unauthorized operation."

This error appears to have been caused by using "User Identity" (or pass-through) authentication on the ECT data source. Although it worked fine in the browser, accessing from PowerShell required me to set up an entry in the Secure Store Service and use with Windows Impersonation or Custom Impersonation even though everything was set up with a single account.

After I fixed the security, I then got the error:

"Exception calling "GetItemById" with "1" argument(s): "Column 'ID' does not exist. It may have been deleted by another user."

This is because an external list actually does not have a default "ID" column like a normal list does.

To confirm this you can dump out all the fields for a normal list and an external list using the following code patten:

$elist = $web.Lists["ExtListTest"]
$i=1; foreach ($field in $elist.fields) { write-host $i,$field.Title, "(",$field.InternalName ")"; $i++}
1 BDC Identity ( BdcIdentity )
2 eID ( eID )
3 c1 ( _x0063_1 )
4 c2 ( _x0063_2 )



 $list = $web.Lists["ListTest"] $i=1; foreach ($field in $elist.fields) { write-host $i,$field.Title, "(",$field.InternalName ")"; $i++}
1 Content Type ID ( ContentTypeId )
2 Title ( Title )
3 Created By ( Author )
4 Approver Comments ( _ModerationComments )
5 File Type ( File_x0020_Type )
6 eID ( eID )
7 c1 ( _x0063_1 )
8 c2 ( _x0063_2 )
9 ID ( ID )
10 Content Type ( ContentType )
11 Modified ( Modified )
12 Created ( Created )
13 Modified By ( Editor )
14 Has Copy Destinations ( _HasCopyDestinations )
15 Copy Source ( _CopySource )
16 owshiddenversion ( owshiddenversion )
<results 17-58 snipped>

The normal list in my case has 58 fields, including one called ID (see field 9), which seems to work with the .GetItemByID() method. That method doesn't work with external lists.

With that said, accessing the elements of the external list works as expected. To access all the data in the external list, the following code works for me.

$web = get-spweb http://<site url>
$elist = $web.List["ExtListTest"]
foreach ($eitem in $elist.GetItems()) { write-host $eitem["eID"], $eitem["c1"],$eitem["c2"] }
1          A1         A2        
2          B1         B2        
3          C1         C2        
4          D1         D2        
5          E1         E2 

Another item that I saw was different is the the ItemCount property has 0 for External Lists:

$elist.ItemCount always returns 0

Note: One of comments on this question indicated that the code:

$ctx = Get-SPServiceContext http://spdevsp01/sites/resumetest
$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx

might be required prior to the Get-SPWeb call. I didn't find that I needed to do that (it works without it) but it might depend on other factors about the specific farm configuration.

share|improve this answer
    
You are totally correct on all fronts. I happened to be able to call GetItemById() against my external list because I had actually mapped a column called "ID" with an integer data type in my external list. One thing of note, though, you DO need to instantiate that SPServiceContext in order to interact with an individual list item (without being able to call GetItemById(), I indexed into the list's Items array instead). When I tried indexing into the list's Items array without instantiating the SPServiceContext, I got a "cannot index into a null array" error. –  Danny Jessee Nov 15 '11 at 23:30
add comment

I couldn't reproduce your exact error, but I set up a similar environment to what you described (external list, full access to all users, etc.) and when I first tried to call GetItemById() against the external list, I received the following error:

Exception calling "GetItemById" with "1" argument(s): "The shim execution failed unexpectedly - Proxy creation failed. Default context not found.."
At line:1 char:19
+ $elist.GetItemById <<<< (1)
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

I also looked through the SharePoint logs and noticed the same error when I was executing that command:

Proxy creation failed. Default context not found.

Based on this error message, I found KB983018 which addressed this error closely enough that I was willing to give it a shot. I executed the following in PowerShell and was then able to retrieve items and read field values from my external list without any issues:

$ctx = Get-SPServiceContext http://sp2010srv
$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx
$web = get-spweb http://sp2010srv
$elist = $web.Lists["ExtList"]
$elistitem = $elist.GetItemById(1)
$elistitem["ID"]
$elistitem["Value"]

You did not indicate that you had created an SPServiceContext object in your PowerShell script, either, so perhaps doing this may resolve your problem as well.

share|improve this answer
    
I tried adding those lines, and I still get the same error message. Exception calling "GetItemById" with "1" argument(s): "Attempted to perform an unauthorized operation." At C:\Users\spfarm\Documents\External List Test.ps1:10 char:30 + $elistem = $elist.GetItemById <<<< (1) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException –  lwbecker2 Nov 12 '11 at 16:50
    
I did find the following error in my SharePoint Log: "External List: User passes list permissions check, but an unsupported authentication configuration was identified. Operation disallowed." That at least gives me another clue. –  lwbecker2 Nov 12 '11 at 17:11
1  
What authentication mode are you using to connect to SQL Server? In my test, I used "Impersonate Windows Identity" in conjunction with credentials that had permissions to the database stored in a new Secure Store Target Application. –  Danny Jessee Nov 12 '11 at 17:58
    
I was using the pass-through authentication. I changed it to use "Impersonate Custom Identity" (and used a SQL Server authentication account) in a new Secure Store Target Application and got past the authentication error. I now get "Exception calling "GetItemById" with "1" argument(s): "Column 'ID' does not exist. It may have been deleted by another user. /sites/ResumeTest/Lists/ExtListTest" " when I execute $eListItem = $eList.GetItemByID(1) –  lwbecker2 Nov 12 '11 at 21:10
    
Did you create the external content type and external list again from scratch after changing the authentication mode? Did you verify that you can see the external list items in the browser? That error indicates something more fundamental is going wrong. This blog post is a great start for setting up an external list using a SQL Server Authentication account: sharepoint911.com/blogs/laura/Lists/Posts/Post.aspx?ID=90 –  Danny Jessee Nov 13 '11 at 15:19
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.