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.