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

We have a SharePoint web application with multiple site collections under that. We have built a new site which will be hosted under the same web application but in a new site collection. It has some custom web parts which reads and writes data to external sql server hosted in a separate server. I have a DAL layer (separate dll) through which it connects and access data. We were using SQL login to make the connection. But now it has to be changed to windows authentication. There will be lot many users using the app, so cannot go with adding all the users to SQL login. There is a service account created for this purpose which SharePoint has to use to connect.

If i have to use windows authentication my connection string will have Integrated Security=SSPI but this will take the current logged in user. How can i use the service account to connect to database. I checked the below SE url but couldn't come to a conclusion. accessing database through sharepoint using a service account (windows account)

We also have some SSRS reports in SharePoint running under SQL login currently which has to be changed to use service account. I tried with Integrated Security=SSPI in connection string box and credentials of service account under user name and password but couldn't make it work.

Any thoughts or directions would greatly help. Thanks.

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

This is a tough one, and I haven’t tried this at home – but still I would like to do my very best to help. As I understand it, you’re trying to use windows authentication to log in to a SQL Server which may have constrained delegation. The accepted answer says the following:

Basically what that means is that an impersonated context by default cannot be delegated to authenticate with a resource on the network. If constrained delegation would not be in place anyone could create a web site in the enterprise and expose some benign application. But underneath, once the user authenticated with the site, it could impersonate that user to do anything, like read his mail, sale stock on his behalf, give raises to site developer, *anything. This is why impersonated contexts are not trusted outside the machine that impersonated the user.

To allow an impersonated context to connect to a remote resource (like a file share, or a database server) the domain administrator has to explicitly set up Constrained Delegation, which allows the impersonated context to authenticate with one specific resource.

There are numerous articles describing the problem and the solution:

If the impersonated context doesn't work, or it is too hard to implement (I would be reluctant to try the above steps) maybe try to use Business Connectivity Services (BCS) instead. I've found the post SharePoint 2010 using BCS with SQL Server database which describes how to use BCS with SQL and Windows Authentication. You have to decide if this is a real options and it depends on how much business logic in your DAL that has to be implemented in the BCS.

share|improve this answer
    
I'm in touch with infra/network team to get this worked out as per approach suggested here. Sorry for not updating, was on vacation. –  Mahesh Jan 17 '13 at 21:24
    
We couldn't break through this approach. And converting it to BCS at this point would be a lot of change as there is a separate projects for Business and Data layer. So for now we have decided to continue with connecting through SQL account. Thanks for your help, Benny. –  Mahesh Jan 30 '13 at 21:38
add comment

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.