Using Sharepoint List Source with Basic Authentication? (pending review)

May 12, 2009 at 11:35 PM

I apologize if this is a really newbie question. After installing the list source and destination, I can point to a Sharepoint site which uses Windows authentication - I guess it passes the credentials of the logged on user. I need to query a site which only has basic authentication. Is there a way to do this or am I stuck with writing a custom application to do this? Thanks for any help!

Jul 20, 2009 at 11:11 AM
Edited Jul 20, 2009 at 11:13 AM

Hi rrkaushik, I am having the exact same start-up problem. I connected the component to our internal SP site in 5 minutes (user running the package may access list) but now I want to connect to an external site and I don't know how to provide username/password. Did you ever solve this problem? Or anyone else on this board?

Any help would be appreciated!
-Thijs Nijhuis

Jul 20, 2009 at 3:19 PM

Hi Thijs.

I think with the current version, its simply not possible to use basic authentication. I ended up writing a console application that uses lists.asmx web service to retrieve Sharepoint list items and then writes to a SQL database. Not as elegant but works and can be scheduled. Hopefully future version or some new Microsoft component will address this gap. Sorry - probably not the answer you were looking for.

Jul 20, 2009 at 3:26 PM

Well, at least not what I was hoping for ;) Seems to me this should be basic functionality. Anyway, thanks a lot for your response!

Greetz,

-Thijs

Van: rrkaushik [mailto:notifications@codeplex.com]
Verzonden: maandag 20 juli 2009 16:20
Aan: Thijs Nijhuis
Onderwerp: Re: Using Sharepoint List Source with Basic Authentication? [SQLSrvIntegrationSrv:56119]

From: rrkaushik

Hi Thijs.

I think with the current version, its simply not possible to use basic authentication. I ended up writing a console application that uses lists.asmx web service to retrieve Sharepoint list items and then writes to a SQL database. Not as elegant but works and can be scheduled. Hopefully future version or some new Microsoft component will address this gap. Sorry - probably not the answer you were looking for.

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLSrvIntegrationSrv@discussions.codeplex.com)

To start a new discussion for this project, email SQLSrvIntegrationSrv@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Sep 29, 2009 at 6:11 AM

If you don't mind getting your hands dirty, then I'd suggest you download the code and look at making the changes where I'm passing in the credentials as NTLM to Basic.  I'm not doing that now, but it should be fairly straightforward (i'd hope).  This is a good thing and perhaps an item for a future feature request.

 

May 26, 2010 at 1:26 PM
Edited May 26, 2010 at 1:27 PM
I modified the samples to use basic authentication, and I've detailed this and pointed to the source on my blog here.
May 26, 2010 at 3:07 PM
Thank you very much for taking the time to add the Basic authentication! Would you mind if I was to roll that in the main package at some time? I would love to have that in the main component as well.
May 26, 2010 at 3:29 PM
Edited May 26, 2010 at 3:30 PM
No problem. That sounds good to integrate it in (instead of having separate CodePlex projects).
May 28, 2010 at 8:51 AM

Sounds great! We have been able to create a workaround for this so far but development would be a lot easier for us if we can just hookup to the MOSS instance from outside the domain. Do I understand it correctly that this - at some point - will result in  a new setup for the component in which I can fill in username/password? Will keep an eye on de project site for new developments. Btw, using these components has been very succesfull in our current project! Just wanted to express my compliments and gratitude for that.

May 28, 2010 at 3:34 PM
Thanks! Yes, you can use the DLLs included with this forked project's release now and you'll have modified list source and destination adapters that tar a username and password.

On May 28, 2010, at 1:21 PM, thijsnijhuis <notifications@codeplex.com> wrote:

From: thijsnijhuis

Sounds great! We have been able to create a workaround for this so far but development would be a lot easier for us if we can just hookup to the MOSS instance from outside the domain. Do I understand it correctly that this - at some point - will result in  a new setup for the component in which I can fill in username/password? Will keep an eye on de project site for new developments. Btw, using these components has been very succesfull in our current project! Just wanted to express my compliments and gratitude for that.

Jun 3, 2010 at 12:42 AM

I quickly reviewed over the code you had, and it's perfect. Exactly the way I would have done it - nicely done :)

As far as Basic and such, it seems you're still using Windows Authentication - you just happen to have it with a custom credential (as opposed to using the SSIS Service account) which would enable access to SharePoint sites outside of the primary domain.

I do not think it would work with an external site using if using basic authentication. Would it?

.

 

Jun 3, 2010 at 6:24 AM

I synched us up with the SharePointUtility, however I think my opinion will diverge for the adapter. I think the username/pw needs to be stored in a connection manager (supports better support of sensitivity and such).  I am just not sure of the security it would have being raw in the package; logging, in memory, etc.  And would have to hope using the connection manager to store that kind of information is safer overall and standard practice with other components.

The DataServices (Cloud) component does have its own connection manager. I'm not thinking the URL would be in there, just the credentials used by the SSIS (if custom is added, it would be optional in my opinion to use it).

Jun 3, 2010 at 6:56 AM
Edited Jun 3, 2010 at 6:59 AM
kevinidzi wrote:

I quickly reviewed over the code you had, and it's perfect. Exactly the way I would have done it - nicely done :)

As far as Basic and such, it seems you're still using Windows Authentication - you just happen to have it with a custom credential (as opposed to using the SSIS Service account) which would enable access to SharePoint sites outside of the primary domain.

I do not think it would work with an external site using if using basic authentication. Would it?

 

I'm still using Windows Authentication because, when I tried to change it to use Basic, our SharePoint site kicked back an error (something like "Basic auth not supported"). I wasn't sure if it was how our server was configured, or if it was how SharePoint worked. I was, however, accessing the SharePoint server remotely, and it wasn't in our intranet for example.

Jun 3, 2010 at 7:05 AM
kevinidzi wrote:

I synched us up with the SharePointUtility, however I think my opinion will diverge for the adapter. I think the username/pw needs to be stored in a connection manager (supports better support of sensitivity and such).  I am just not sure of the security it would have being raw in the package; logging, in memory, etc.  And would have to hope using the connection manager to store that kind of information is safer overall and standard practice with other components.

The DataServices (Cloud) component does have its own connection manager. I'm not thinking the URL would be in there, just the credentials used by the SSIS (if custom is added, it would be optional in my opinion to use it).

Thanks again for looking at this. The way I did it was quick and dirty, and I realize that having the credentials in the adapter is probably insecure. I'm not very familiar with SSIS, so I didn't think about using a connection manager for this.

That being said, I don't know what the DataServices component is, and I'd like to know more about how you use it in conjunction with the adapters.

Jun 3, 2010 at 8:03 AM
No problem - we've all done quick and dirty. I have a shelveset i'm working on that has a connection manager. I need to consult some folks though before i can get it in the main code.
Jun 4, 2010 at 5:27 AM

I just checked in some code to make the adapters use a Credential Connection Manager to store the credentials - custom credentials are optional. It is a trivial upgrade for existing components. 

It's in the source code section. Copy the connection dll to your SQL Server\100\DTS\Connections folder after it builds.

Please try this out, upon hearing responses for if it's working as expected, I'll make a release out of it.

Using a connection manager can be sort of a +1 on the complexity scale for using this component.  If you want to use default credentials, it's still almost as easy.  If you want a different username/password, this site has a great article on the different options available since I made it a connection manager: http://support.microsoft.com/kb/918760

 

Jul 16, 2010 at 4:10 PM
Any chance of a Beta version for SQL2005?
Jul 18, 2010 at 12:18 PM
kevinidzi wrote:

I just checked in some code to make the adapters use a Credential Connection Manager to store the credentials - custom credentials are optional. It is a trivial upgrade for existing components. 

It's in the source code section. Copy the connection dll to your SQL Server\100\DTS\Connections folder after it builds.

Please try this out, upon hearing responses for if it's working as expected, I'll make a release out of it.

Using a connection manager can be sort of a +1 on the complexity scale for using this component.  If you want to use default credentials, it's still almost as easy.  If you want a different username/password, this site has a great article on the different options available since I made it a connection manager: http://support.microsoft.com/kb/918760

 

Sorry, but I'm just getting around to looking at this.

I pulled the latest source down, and when I try to compile the connection manager project, I'm missing a reference to Microsoft.DataTransformationServices.Design.

Do you know what I need to install to get this?

Thanks again!

Jul 19, 2010 at 2:20 AM

Hi Guys,
  Can someone help mw with the link to the one that supports FBA Auth?
I can't get to see it any where.Thanks a lot

Jul 19, 2010 at 4:50 PM
Edited Jul 19, 2010 at 5:49 PM

Doh, i thought i had the 2005 version up there. I will put it up.

NOTE: It's up. I recompiled the other one to add the latest updates that happened after the last compile. I did not have a chance to smoke this as i did the other though (and it is compiling with vs2010 on my box), so let me know if you run across anything amiss.

Aug 2, 2010 at 2:29 PM

I updated my SSIS package (that formerly used my own custom adapters) to use the beta version of these official adapters. It seems to be working very well for me - thanks!

I'm looking into deleting the separate Codeplex project I made for my adapters.

Aug 3, 2010 at 1:07 AM

Hi dmorrison,
                  Thanks for the update and do appreciate the time you are putting on this.
But can you be specific where to download it from? The one that supports FBA.
There are too many of them in the download section.
Thanks alot

Aug 3, 2010 at 7:17 AM

I'm using SQL Server 2008, so I used "BetaSharePointListAdaptersSetup.msi".

I don't think it's quite FBA, but rather it lets you supply credentials for NTLM authentication. Using the SharePoint Connection Manager in SSIS that the msi installs, you'll need to supply it with a user name in the form "Domain\UserName" and a password.

Aug 3, 2010 at 7:27 AM

Sorry for the pain dmorrison - I just haven't had the cycles to make sure i get a fresh build of the components (need to pull out sharepointutility) and update the necessary docs for that credential usage.  I'm glad that it's working out ok for you however!

Aug 10, 2011 at 5:19 AM

Hi,

I also need to use Basic Authentication to authenticate to a SharePoint site.  From the discussion above it appears that Basic Auth is not implemented, only the ability to provide different credentials than that of the running process or default (in the case of SSIS).  Is this the case?

If so, can you point me in the direction of the connection method, where you provide the NTLM authentication?  I would like to see if it is possible to use Basic Authentication.

 

Thanks

Steve

Aug 11, 2011 at 2:37 AM

Only Ntlm.  You can find that in the SharePoint utility all, I think it's in the Listadapter. It takes the credentials and sets up the wcd settings, search for ntlm.

Aug 7, 2012 at 5:03 PM

I've been receiving a similar error after moving my SSIS project from one machine to another.  I now receive the following error:

The component has detected potential metadata corruption during validation.
Error at Data Flow Component [xxxxx]:
System.ServiceModel.Security.MessageSecurityException: The HTTP request is unauthorized with client authentication scheme 'Ntlm'. The authentication header received from the server was 'NTLM'. --->
System.Net.WebException: The remote server return an error: (401) Unauthorized.
  at ...

The only difference I see between the client scheme and the server's request is a difference in case between 'Ntlm' and 'NTLM'.

I'm using the 17 Oct 2011 version and have not yet looked into the source code.  However, this same package was working with no error on the first machine, but now it is failing when loaded into a different environment.