Troubleshooting Search Server Connection Errors using ODBC

  • Updated

When sharepoint is installed or updated on a Microsoft Search Server 2010 installation the following error may occur. 

Cannot connect to database master at SQL server at server146. The database might not exist, or the current user does not have permission to connect to it.

2017-11-06_12_40_23-EPiServer_Ektron_Support_-_Agent.png

In cases like this you can test connectivity from the search server to the SQL Server by creating an ODBC connection between the two servers. 

  1. Before creating the ODBC connection make sure you are running the wizard with the search user. If Search Server was previously installed the search user can be identified by looking at the log on ask property of the SharePoint Server Search 14 service. If Search is not fully installed yet, you should have created the Search user based on the following requirements.
    • The user is a domain user
    • The user has "log-on as a service" role.
    • The user is an administrator on the box.
    • The user is added to the SQL server with the db_creator and securityadmin SQL roles. 
      More on setting up search server
  2. Create an ODBC connection on the search server by doing the following steps.
    • Remote onto the search server.
    • Click Start > Administrative Tools > Data Sources (ODBC)
    • Click Add..
      2017-11-06_12_47_57-svm-zachpoint_-_Remote_Desktop_Connection_Manager_v2.7.png
    • Click SQL Server.
    • Give the new connection a name and enter the SQL server you are connecting to. 
      2017-11-06_12_50_25-svm-zachpoint_-_Remote_Desktop_Connection_Manager_v2.7.png
    • Click Next.
    • On the next screen enter the credentials of the SQL user being used for the Sharepoint Configuration database. 
      2017-11-06_12_56_41-svm-zachpoint_-_Remote_Desktop_Connection_Manager_v2.7.png
    • If the Sharepoint Configuration database has been created already click Change the default database to: and switch the database to Sharepoint Configuration database. If it has not been created connect to the master database. Keep all other settings the same and click next. 
      2017-11-06_12_59_51-svm-zachpoint_-_Remote_Desktop_Connection_Manager_v2.7.png
    • Click Finish.
      2017-11-06_13_01_31-svm-zachpoint_-_Remote_Desktop_Connection_Manager_v2.7.png
    • Click test data source.
      2017-11-06_13_02_32-svm-zachpoint_-_Remote_Desktop_Connection_Manager_v2.7.png
    • If communication and permissions allow it, you should get the following message.
      TESTS COMPLETED SUCCESSFULLY!
      2017-11-06_13_02_45-svm-zachpoint_-_Remote_Desktop_Connection_Manager_v2.7.png
    • If it fails check with your Network and SQL Admins to identify the cause.