Connection to external data source using windows authentication (NTLM)

Question

I'm trying to connect to an external SQL server DB programmatically from a SharePoint WebPart. The problem is that I'm not able to authenticate to the DB although I gave access to application pool identity on it.

After investigation, I knew that the reason is that we are using Windows Authentication with NTLM, which doesn't have the ability to pass the credentials to the external DB. I also knew that kerberos can be a solution. But, other than kerberos, do we have any other options?

I've heard about something called SharePoint Secure Stores where I can declare a specific target data source and SharePoint can handle the authentication to it. But I'm not sure if this solution fits my problem

Answer 1

I answered a similar question recently.

SharePoint 2010 you should use the Secure Store Service (SSS) for these kind of settings (credentials, connectionstrings etc). That provides a user interface for selected users, you can then in your Web Part Property point to the SSS application ID that you want it to use (you could also get fancy and make the property a dropdown of available application ID's).

Create a library class with a GetCredentials method that fetches SecureStoreProvider using SPServiceContext and use the GetCredentials(appId) method to fetch your SecureStoreServiceApplicationCollection.

Read full answer here

Answer 2

You can also use connection string with SQL server authentication instead of windows.

Data Source=ServerName; Initial Catalog=DatabaseName; User Id=UserName; Password=UserPassword;