Cannot connect to SQL database in SDP 2010 - authentication problem? Why? Works in SQL Management Studio

Question

I am having a problem similar to This thread

I am unable to follow correctly the steps in SQL Management Studio to get authentication to work.

I can:

• Open any of the SQL databases on the server with Management Studio using SQL or Windows authentication
• I cannot get SPD to give me a list of databases nor connect to an individual one.
• When I try configuring database connection, I enter the server name and use windows configuration (It works in Studio).
• When I try adding an external content type, i do the same but get the same error

The error starts off as Server Error: An error occurred while retrieving the list of Databases from <servername>.  An authentication error occurred....

Can someone provide me SPECIFIC settings for the databases on the server (I have three so far, one for SharePoint, the default one and one for application as yet not tabled out).  Do I have to change roles?  I think I have loon permissions OK as I can log on and make changes via Management Studio.

---

Larry Heimendinger TechEvents.com

Answer 1

I am having a problem similar to This thread

I am unable to follow correctly the steps in SQL Management Studio to get authentication to work.

I can:

• Open any of the SQL databases on the server with Management Studio using SQL or Windows authentication
• I cannot get SPD to give me a list of databases nor connect to an individual one.
• When I try configuring database connection, I enter the server name and use windows configuration (It works in Studio).
• When I try adding an external content type, i do the same but get the same error

The error starts off as Server Error: An error occurred while retrieving the list of Databases from <servername>.  An authentication error occurred....

Can someone provide me SPECIFIC settings for the databases on the server (I have three so far, one for SharePoint, the default one and one for application as yet not tabled out).  Do I have to change roles?  I think I have loon permissions OK as I can log on and make changes via Management Studio.

---

Larry Heimendinger TechEvents.com

Answer 2

Hi you have asked your question in a pre-SharePoint 2010 forum.

Please ask your question in the
SharePoint 2010 forum and not in this pre-SharePoint 2010 forum.<o:p></o:p>

SharePoint 2010 - General Questions and
Answers

http://social.technet.microsoft.com/Forums/en-US/sharepoint2010general/threads<o:p></o:p>

SharePoint 2010 - Setup, Upgrade,
Administration and Operation

http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/threads<o:p></o:p>

SharePoint 2010 - Using SharePoint
Designer, Infopath, and other customization

http://social.technet.microsoft.com/Forums/en-US/sharepoint2010customization/threads

SharePoint 2010 - Using Visual Studio with SharePoint and other programming

http://social.technet.microsoft.com/Forums/en-US/sharepoint2010programming/threads<o:p></o:p>

---

Dirk Van den Berghe

Answer 3

http://blogs.msdn.com/b/mvpawardprogram/archive/2012/04/12/sharepoint-2010-external-content-type-to-read-data-from-sql-server-using-sql-authentication-and-secure-store-service.aspx

Try the above link with SSS.Let us know the results

---

Destin -MCPD: SharePoint Developer 2010, MCTS:SharePoint 2007 Application Development

Answer 4

Hi LMHeimendinger,

Since external content type is a new feature in SharePoint 2010 and you already mentioned that you are using SharePoint Designer 2010, it should be a SharePoint 2010 problem, and allow me to move it to SharePoint 2010 Designer forum.

Thanks & Regards,
Emir Liu
TechNet Subscriber Support in forum
If you have any feedback on our support, please contact [e-mail].

---

Emir Liu

TechNet Community Support

Answer 5

No it idn't work - final step trying to create the external connection - failed with an authentication error.  I am going to have dozens of tables anyway, and having to do this for each table would be a nightmare!

---

Larry Heimendinger TechEvents.com

Answer 6

Can you try giving DB Server IP instead of Server Name

---

Destin -MCPD: SharePoint Developer 2010, MCTS:SharePoint 2007 Application Development

Answer 7

Also check if you logged in with the account has permission to access DB. Check it by clicking the people icon on the left bottom of SharePoint Designer.

Best regards,
Emir

---

Emir Liu

TechNet Community Support

Answer 8

Neither of these proposed issues made any difference so I have unmarked them as answers.

---

Larry Heimendinger TechEvents.com