I have db user account & used it to create a secure store application id and a managed account. I have also given myslef full permissions on the bdc service & bdc metadata store. I have no problem connecting using a sql server management studio but any type of connection I make to the datasource on the sql server via SPD gets denied.
Try this... it's work perfectly.
Create Secure store App ID (In Central Admin)
Manage BDC Service (In Central Admin)
Configure SQL Server