Error creating a Database Connection to SQL in SharePoint Designer 2010
So you tried to create a SQL Database Connection in SharePoint Designer 2010 and receive the message:
“Server Error: An error occurred while retrieving the list of databases from [SQL Server]: An authentication error occurred. Your logon information may be incorrectly entered, you may not have permission to access this data source, or the requested authentication method may not be supported. Contact the server administrator for more information.”
Well, join the club :)
I received this error recently and was confused for a while. What was most baffling was that I was able to create an External Content Type based on the same SQL information, using the same credentials. So why not a Database Connection? I didn’t have access to the SQL Server error logs, and there was not any detailed information in the Event Logs or ULS logs.
Judging from the number of questions and posts regarding this error I saw on the internet, many people are facing the issue. Most suggestions I saw focused on using the correct query string syntax or provider type. None of the suggestions I saw helped me though.
Then my colleague had a great idea. Try to create a Database Connection to the SharePoint Database. I did just that using the farm account and got the exact same message. That ruled out privileges being the issue. It also allowed me to see what cropped up in the SQL Error logs. Here is what I saw:
“Login Failed for user xxxxx. Reason: Attempting to use an NT account name with SQL Server Authentication”.
I wish all error messages could be that straight-forward (I’m looking at you, SharePoint)! ;)
I had the DB Admin create SQL credentials for me and I was having fun displaying SQL info in my DVWP in no time.
Obviously there can be a lot of reasons for seeing this message but hopefully this post will save someone having the same issue some time troubleshooting.