SSIS is not using the provided custom Sharepoint credentials

Jul 25, 2012 at 8:23 PM

Hello,

I have multiple packages that need to access a Sharepoint server. So I am using the Sharepoint List Source/Destination plugin I found here.

The way I have it set up, is that there is a job in SQL Server that runs multiple packages every day, however when it tries to run them, it does not work. I am getting authentication and validation errors.

When I run the packages locally, it works fine.

After investigating, I found out that the credentials I provide to connect to Sharepoint are not used. The package uses my windows account even if I specifically set it to use custom credentials. To test this, I simply created a new account on my computer and ran the package with correct custom credentials and found that it didn't work. If I do the same on the account that has access to Sharepoint, it works.

I would like to know if there is a fix or a workaround for this ? 

Thank you,

 

Yan

Jul 25, 2012 at 9:24 PM

Just to clarify, my packages all use the EncryptSensitive security level.

Sep 20, 2012 at 2:46 PM

Look at the SQL Server Agent (MSSQLSERVER) user in the Sql Server Configuration Manager. Make sure that user has been added to the sharepoint site you are trying to write to.

Sep 20, 2012 at 3:23 PM

This was impossible in the context. The goal of providing the sharepoint credentials to the SP Source/Destination plugin was to avoid this issue. But obviously, that feature is bugged.

I ended up scripting the whole thing by hand without using the plugin and it worked.

Sep 27, 2012 at 9:10 PM

Can someone clarify the true intent of the Custom Credentials? Is this expected to work with AD domain accounts? What trusts must be in place for the credentials to work?

Feb 6, 2013 at 10:46 AM
I am facing the same issue, can some body please help me in this

Below are the limitations which I cannot consider
1) I cannot use PROXY account as security policy is not allowing
2) We cannot provide access to the SQL Agent at the sharepoint as per security policy

How to use custom credential which is not working?
Feb 6, 2013 at 5:08 PM
sisirpatnaik wrote:
I am facing the same issue, can some body please help me in this

Below are the limitations which I cannot consider
1) I cannot use PROXY account as security policy is not allowing
2) We cannot provide access to the SQL Agent at the sharepoint as per security policy

How to use custom credential which is not working?
After many days trying different workarounds and tests, the only solution I have found was to make my own scripts (so manually program some code to pull data and push it) within the package. It was using the correct credentials and was working perfectly in the end.