In Sharepoint 2010, I am trying to create an External Content Type, and use a connection to a SQL Server. But, in Sharepoint Designer, when I am in the wizard to create an ECT, I'm having trouble creating the connection. I can easily get connected to SQL Server from SQL Server Management Studio. I am logged into SharePoint Designer as the same domain user that I use to log into SQL Server. I click on "Click here to discover external data sources and define operations", then I click "Add Connection". I click on "SQL Server" on the Data Source Type, then fill in Database Server and Database Name, and select "Connect with User's Identity", and OK. I get "Login failed for user ". The user is not associated with a trusted SQL Server connection."
Is there a configuration somewhere that I'm missing? I have this user set up in the BDC Metadata Store with all permissions, and clicked on "Propogate all permissions to all..." I saw some other questions in here that spoke about BDC not being set up correctly.
Also, this user is a farm admin(just to give as much info as possible). Has anyone seen this? We are thinking of just using SQL Server authentication instead of Windows Auth. Is that the solution (can't test today because I need a DBA)?