Can the Business Data Catalog connect to Microsoft SQL Server using a SQL account? The answer is yes!
Firstly why would you want to use a SQL account?
– it gets around the double hop issue
– a good way to connect to a remote server off your domain
Here is how to do it.
1, Open BDC Meta Man. Connect to your SQL Server database – for this step it doesn’t matter if you use Windows Authentication or a SQL account.
2, Drag and drop the tables onto the design surface you want to use.
By default BDC Meta Man will generate your application definition file using PassThrough BDC authentication type. This will attempt to authentication against the backend data source using the windows account of the user who is trying to view the data. If you’d rather the BDC uses a SQL account…
3, In the bottom left tree view, right click on the root node and choose Edit from the menu that appears
4, In the configuration box that appears, set:
Authentication Type : RevertToSelf
Username : your SQL account username
Password : your SQL account password
Click the Save button.
And that is it, generate your application definition file and import it into SharePoint and away you go…
<nick/>
