[SharePoint Designer 2010] Connecting to an external data source

Question

I'm using SPD 2010 to connect to an external SQL database hosted on a different server. External data source type selection is SQL Server. I then filled up the connection properties , choose "Connect with Impersonated Windows Identity" and Secure Store Application Id (which I created using this link). But SPD 2010 return this error: Cannot logon with credentials obtained from Secure Store Provider

I don't know how to fix this. Can somebody help please?!

Thanks, SPNewbie

Answer 1

You need to set up your Secure Store service application on the server, and add a Target Application. This is an arbitrary name you assign to the set of credential mappings that you will set up in the Secure Store.

To be honest, this is a non-trivial administration task and you probably need to understand a little about how the Secure Store works and is configured. The article you link is good; your next port of call will probably be TechNet:

http://technet.microsoft.com/en-us/library/ee806889.aspx

http://technet.microsoft.com/en-us/library/ee806866.aspx

Another good hands-on how-to article

Things to watch out for: Use a group mapping if you want a single set of credentials rather than give each user individual access to the back-end system. Make sure permissions to the ECT configured using the BCS service application. Make sure the account for which you create the credentials in the Secure Store actually has access to the back-end database.

Don't forget to check the ULS logs and the SQL logs and the event log on the machine hosting your service application.