InfoPath, SSO and SQL Server credentials
Here’s a quick walkthrough to enable a InfoPath form to get information from a SQL Server database with credentials found in a SSO database (which you typically use with SharePoint).
1) First, you need to configure SSO if you haven’t already. Just google for SSO configuration in SharePoint and you’ll find some fine sites explaining how to do that.
2) After configuring, it’s time to add the credentials for your SQL Server. You can do that by adding a new Application Definition to your site (via Operations, Manage Single Sign-On). Set the account type to ‘Group’ and Windows Authentication.
3) Go back to the Single Sign-On settings and click ‘Manage account information for enterprise application definitions’ to add the correct credentials to the application definition.
4) In InfoPath, define a new connection to your database. Do not use Integrated Security at this point, because the resulting UDC file won’t have the correct connection string. Also, don’t save the credentials since we want to use the ones stored in the SSO database.
5) Use InfoPath to add the new connection to a Data Connection Library on your SharePoint portal. After that, delete the connection and reload it from your SharePoint site to make sure the form will use the correct connection. By the way, do this before adding the correct data connections to your form since it will delete all connections when you remove the data source.
6) Go to your SharePoint Data Connection Library and download the connection you’ve just made. Edit it with Visual Studio (or notepad if you like) and uncomment the SSO section which is commented by default. Enter the Application Name you specified for your Application Definition and use ‘Sql’ as CredentialType.
7) Upload the edited UDC file back onto the server, overwriting the previous one.
8 ) Finish your form and create a new document library based on the form (or use another method of gathering the data offcourse).
Now, when the form is used: the SSO database will be queried for the correct credentials, and those credentials will be used to logon to your SQL server. I’m not quite sure how to enable Integrated Authentication between MOSS server and SQL server, but when I’ve got that figured out I’ll let you know!
Leave a Comment