"An error occurred while retrieving the list of Databases from The data retrieval service ..." when creating a db connection using SPD 2007
Monday, January 18, 2010 6:35 PMNew at this:
Using SPD 2007 sp2 and trying to create database connection to a sql server. On the Configure Database Connection screen for
Server Name: \\SQLServerName
Provider Name: Use .Net SQL or OLE DB
Authentication: Use SQL Login & Password name
Then I get the error:
Sharepoint Server Error: An error occurred while retrieving the list of Databases from The data retrieval service encountered an error during connection to the data source.
I also tried using a custom string - no luck there either.
One last question - If I use a custom string can I connect to an Access db?
Thursday, January 28, 2010 5:59 AM
Please check the following settings on your SQL server, make sure they are all correct.
1. Ensure your SQL server is configured properly, and that you are using a local SQL account.
3. Try connecting with the SA account, and check the effect.
4. If SA account works, if explains to you that the issue is probably permission related.
5. Go to the SQL server, change the problematic SQL user account's password, and ensure it had mappings to some databases.
6. Try the account again in SharePoint Designer and check the effect.
7. Also make sure that you have set the SQL server authentication mode to mixed mode: "SQL Server and Windows Server Authentication" and Enable SQL Server login. For detailed steps, please check the following steps:
SQL Server and Windows Server Authentication:
a) Open SQL server management studio,
b) Right-click on the server node and select 'Properties'.
c) Select 'Security' from the left menu under 'Select a page'.
d) Under 'Server Authentication', select the 'SQL Server and Windows Authentication mode option'.
e) Click 'OK' to close the dialog.
f) Right click on the server node and choose 'Restart' for the changes to take effect.
Enable SQL Server login:
a) In the server node expand 'Security' and 'Logins'.
b) Right click on the login name and select 'Properties'.
c) Enter a password and confirm the password for the login.
d) Select 'Status' from the left menu under 'Select a page'.
e) Set the 'Login' option to 'Enabled'.
f) Click 'OK' to close the dialog.
For more information about how to add a database as a data source, please refer to the following articles:
Add a database as a data source
Data Views and the battle of Database Connections
Hope this helps.
Rock Wang– MSFT
- Marked As Answer by Rock Wang– MSFT Friday, January 29, 2010 7:34 AM