1

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)?

Improve this question

2 Answers 2

Reset to default
3

Right, I think your error is that the user you are trying to use with BCS is unable to connect to the SQL database. You actually have two different authentication elements to deal with here:

1) Who the User connects to the BCS Service as. 2) Who the BCS service connects to the SQL database as.

You actually need to set up 2), then 1)

For point 2 you'll need to set up a Secure Store service to create an Secure Store Application ID in, and then set up an ID of the user that you want to the BCS service to connect to the database as. When creating that ID, you'll want it to be of type group.

If you're not worried about logging who's accessed your database, etc., you can set up ONE account in SQL server with the rights you want BCS to have, and use that account as your Secure Store Application ID. It's actually more efficient that way (all users can share one connection), and Windows Auth would mean a double hop, so I suspect you'd need Kerberos configured to.

Once you've done that, you can go to SharePoint Designer and add a new connection. In that dialog, there should be a 'Secure Store ID' field - use the one from above. Also, you can select how you connect to BCS too (i.e. point 1) ) - if I remember rightly, your users can connect as themselves, or as a specific account.

Finally, once that's done, and you've created your external content type, you'll need to assign rights to it in the BCS service application. E.g. If I create an external content type for 'employees', and it connects to my HR database as a single user, I might not want everyone to have access to that BCS entity and able to read all my HR details. This can be controlled in the Service app by controlling who has rights to the BCS entity. The rights are explained here: : http://blogs.msdn.com/b/bcs/archive/2009/11/24/permissions-in-business-connectivity-services.aspx

Improve this answer
1
  • Andy, thanks for the info. I actually tried this, too, without success. It reinforcees my belief that my domain user doesn't have the right access to SQL Server. It has "public".
    – Clay Acord
    Jun 16, 2011 at 13:55
0

"Connect with User's Identity" - this is pass through authentication (your win credientials). I had similar problem which was solved when I gave "my user" (which had every right in SP as yours) login privilegies and full permission(dbowner rights) to specific database (to which you wanna connect). Before that I just had right to connect to db instance but not to that specific database (it is, of course, logical but sometimes men forget). If that won't help you can try with Andy suggestion. Or you can give ReverToSelf property to BCS service to connect to DB with SP application pool account which again must have login and security permissions set for that database. That isn't very good practice, but you can, if you want.

Improve this answer
2
  • Good points. So, "connect with user's identity": does it mean 1. whoever the admin user is while setting up the ECT, or 2. each user of the site? Sounds like a stupid question, since I would assume 1, but I'm trying to draw at straws at this point.
    – Clay Acord
    Jun 16, 2011 at 14:02
  • You assume good, answer is 1.
    – trip
    Jun 16, 2011 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.