SharePoint Dev Center >
> 
> 
"An error occurred while retrieving the list of Databases from The data retrieval service ..." when creating a db connection using SPD 2007

Answered "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 PM
     
     
    Sign In to Vote
    0
    New 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?

    JShesek
    • Edited by Mike Walsh FIN Monday, January 18, 2010 7:01 PM Title revised. Too much of the error message replaced by more details
    • Moved by Mike Walsh FIN Monday, January 18, 2010 7:01 PM spd questions go to Design/Customization (From:SharePoint - General Question and Answers and Discussion)
    •  
     

All Replies

  • Thursday, January 28, 2010 5:59 AM
     
     Answered
    Sign In to Vote
    0

    Hi,

     

    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

    http://office.microsoft.com/en-us/sharepointdesigner/HA101009081033.aspx

     

    Data Views and the battle of Database Connections

    http://www.u2u.info/Blogs/karine/Lists/Posts/Post.aspx?ID=4

     

    http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/acb5c276-dc06-4000-b2d8-06290df03b44

     

    Hope this helps.

     

    Rock Wang

    Rock Wang– MSFT