OleDb Connection to SharePoint List Stuck

Question

Hi.

I'm trying to connect to a SharePoint list from a SharePoint web part using the OleDb connection string.

OleDbConnection conn = new
 OleDbConnection(connString);

conn.Open();

OleDbCommand comm = new
 OleDbCommand(selectString, conn);
OleDbDataAdapter adap = new
 OleDbDataAdapter(comm);
DataSet dataset = new
 DataSet();
adap.Fill(dataset, "list"
);

When it attempts to execute the last line, the browser will appear to be executing but never returns, never times out, and never throws an exception.

I'm guessing this might be a permissions problem.  I created a separate project that attempts to connect to the list using the above code.  When run it as a non-SharePoint user, it prompts for username and password.  If I provide the credentials, it works correctly.  When I run is as a SharePoint user, it does not prompt for credentials and also runs correctly.

I'm testing on a WSS 3.0 standalone installation using Windows Authentication.

Any help would be greatly appreciated.

En-jay

Answer 1

I've been looking into this problem some more, checking the WindowsIdentity when running the code in SharePoint and when running the code in a separate project on the SharePoint machine.  They both are the same user (MACHINENAME\Administrator), but there are some differences.

ImpersonationLevel of WindowsIdentity
   In SharePoint, "Impersonation"
   In seperate project, "None"

Groups of WindowsIdentity
   Some of the Groups are different

Token
   Different tokens

My question now is when a person impersonates a user account, does that have different permissions than if you log in as the user account?  Does SharePoint treat or use impersonation differently from other applications?

Any ideas?

En-jay

Answer 2

The default value to wait for a connection to open is 15 seconds.
A value of 0 indicates no limit, and should be avoided in a ConnectionString because an attempt to connect will wait indefinitely.

OleDbConnection. ConnectionTimeout Property (http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.connectiontimeout.aspx)

If you want to call OleDB object, you may need to provide the credential explicitly.

Impersonation is the ability of a thread to execute using different security information than the process that owns the thread. This means it will have the permission of the impersonated.

Impersonation(http://msdn.microsoft.com/en-us/library/aa543158.aspx)
ASP.NET Impersonation  (http://msdn.microsoft.com/en-us/library/xh507fc5%28VS.80%29.aspx)

---

Keep It Simple and Stupid.

Answer 3

Hi Charlie.

Thank you for the information on ConnectionTimeout.  I haven't changed the default, but I will try to explicitly set the ConnectionTimeout.

The OleDb connection string does not does not allow the credentials to be within the connection string so impersonation must be the way to go.

I've tried impersonation without success.  The attempt with LogonUser successfully impersonates another user, but still has the same behavior when it attempts to execute the 'adap.Fill(dataset, "list")' line.

WindowsImpersonationContext impersonationContext = null;
IntPtr handle = new IntPtr(0);
handle = IntPtr.Zero;
bool logonSucceeded = LogonUser("Administrator", "guybrush", "tempPassword", 3, 0, ref handle);
if (!logonSucceeded)
{
    // if the logon failed, get the error code and throw an exception
   int errorCode = Marshal.GetLastWin32Error();
   throw new Exception("User logon failed. Error Number: " + errorCode);
}

// if logon succeeds, create a WindowsIdentity instance
WindowsIdentity winIdentity = new WindowsIdentity(handle);

 // close the open handle to the authenticated account
CloseHandle(handle);

impersonationContext = winIdentity.Impersonate();
WindowsIdentity wiii = WindowsIdentity.GetCurrent();
adap.Fill(dataset, "list");
impersonationContext.Undo();

The exact same code work correctly in a test project that runs outside of SharePoint.

Any other ideas of what I could try or what might be causing the problem?

Thanks you the help.

En-jay

Answer 4

I've been reading more into how SharePoint handles impersonation and wonder if that is the cause of this issue.  In my case, I'm using Windows Authentication with Impersonation enabled in the web.config of the SharePoint install.

GUYBRUSH is the name of the machine I have WSS3 on and GUYBRUSH\administrator is an admin on that machine.  GUYBRUSH\administrator is also a Site Collection administrator.

When I log in to http://guybrush/, I log in with the credentials for GUYBRUSH\administrator.  In SharePoint, my user is now GUYBRUSH\administrator and has a SharePoint specific user token.  Since Impersonation is enabled, when the SharePoint user GUYBRUSH\administrator attempts to access a resource outside of the SharePoint site, the Windows Authentication user GUYBRUSH\administrator is used.

When I try to access a SharePoint list using OleDb, I need the credentials of the Windows Authentication user GUYBRUSH\administrator (I tested this by creating a c# project that accesses a SharePoint list using OleDb and running it as GUYBRUSH\administrator.  This runs correctly and does not prompt for credentials.)

Is my above understanding of SharePoint authorization and impersonation correct?  If so, has anyone else had problems with accessing things from within a SharePoint web part or SharePoint custom application but not had the same problem with a project outside of SharePoint?  Also, would it seem plausible to conclude that SharePoint impersonation does not function exactly like WindowsIdentity.Impersonate() so I may be encountering strange authorization issues (strange in that no exceptions are thrown)?

Thanks for any feedback.

Answer 5

We are totally stuck on this question. Any suggestions on where we can go to get an answer? It seems to me that this should be a well known issue.

thanks - dave

---

Windward Reports - World's Greatest SharePoint Reporting & DocGen

Answer 6

Hi Dave,

There is a lot in that other question. can you state what your question is exactly?

---

David Lozzi
Delphi Technology Solutions
SharePoint & Dev Blog  |  LinkedIn | Twitter

Answer 7

If you want to display the contents of a list in a webpart you shouldn't query the database directly as this isn't recommended by MS.  You can use the SPDataSource  http://www.sharepointnutsandbolts.com/2008/06/spdatasource-every-sharepoint-developer.html

---

My SharePoint Blog - http://www.davehunter.co.uk/blog

Answer 8

Absolutely. Here is the question we are stuck on:

I'm trying to connect to a SharePoint list from a SharePoint web part using the OleDb connection string.

OleDbConnection conn = new
 OleDbConnection(connString);

conn.Open();

OleDbCommand comm = new
 OleDbCommand(selectString, conn);
OleDbDataAdapter adap = new
 OleDbDataAdapter(comm);
DataSet dataset = new
 DataSet();
adap.Fill(dataset, "list"
);

When it attempts to execute the last line, the browser will appear to be executing but never returns, never times out, and never throws an exception.

thanks - dave

---

Windward Reports - World's Greatest SharePoint Reporting & DocGen

Answer 9

Thanks - we will try that.

---

Windward Reports - World's Greatest SharePoint Reporting & DocGen

Answer 10

I agree with Dave Hunter (Whoa 3 Davids!). The easiest and reliable method of querying a list is using the SharePoint API. If you want I can send you some sample code.

However, if you want to keep using this method, can you provide your selectString? Also, try adding the current logged in user account to SQL Security, just as a test.

---

David Lozzi
Delphi Technology Solutions
SharePoint & Dev Blog  |  LinkedIn | Twitter

Answer 11

Thank you Dave & Dave (Dave's do make the best looking programmers). I've pointed En-Jay at this help and I think with this he'll be able to get this figured out.

thanks - dave

---

Windward Reports - World's Greatest SharePoint Reporting & DocGen

Answer 12

Hi.

I'm the original questioner.  First, thank you for the replies; much appreciated.

I'm trying to add SharePoint lists as a data source to a product that can already handle OleDb connections strings and SQL.  Thus I would like to avoid using CAML and SPDataSource if possible since there would be a significant amount of work to implement and test.  I'll keep it in mind if there doesn't end up being another choice.

The select string I'm testing off of:

Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;DATABASE=http://yoshi/;LIST={7F3C65FF-E6A6-4019-8328-17D61290C476}

I've also tried opening the SharePoint database and giving the user full rights to all databases, but that doesn't seemed to have done it.

What really confuses me is that I can create a project, use WindowsIdentity.Impersonate to impersonate a user, and the code above runs fine.  If I do the same in a SharePoint web part, it never responds, doesn't throw an exception, and doesn't create an error in the logs.

Thanks,
En-jay