Database Link Connection Test Error

Problem

On Microsoft SQL Server, the configuration process can fail with the following error message:

The configuration process uses the credentials of the reports data warehouse user to create the linked server between a BMC Server Automation database (site) and the reports data warehouse. This user must be assigned to the sysadmin role. If this user is not assigned the sysadmin role, the configuration process fails. 

When the error occurs, the postinstallation configuration wizard prompts you to create a linked server in the following format: BSA_<SiteID>_<buildNo>. 

Solution

Create a linked server between the BMC Server Automation database and the reports data warehouse: 

1. If you have not already done so, copy the installation scripts from the bdssa89<servicePackVersion>external-files.zip file into a directory. 

   If you extract the entire contents of bdssa89<servicePackVersion>external-files.zip, the scripts are copied into the /Sqlserver directory below the directory that you specify.

2. From the reports data warehouse location, log on to SQL Server Management Studio as sa.
3. Navigate to the directory in which you extracted the installation scripts.
4. Locate the create_linked_server_for_om.sql file and open it. 
   The script file contains variables related to the BMC Server Automation database, such as user name or database name.
5. Open New Query.
6. Copy the contents of the script file and paste the contents into the query window.
7. For each parameter in the file, provide the appropriate value for the BMC Server Automation database.
   • @om_sqlsvr_instance is the name of the SQL Server instance on which the BMC Server Automation database resides.
     • If this is a default instance on a computer, use the computer name (for example, Computer1).
     • If this is a named instance, use the computer name and instance, with the format as in the following example: Computer1\Instance1.
   • @om_db_name is the name of the BMC Server Automation database.
   • @om_sqlsvr_login is the SQL Server logon ID for connecting to the BMC Server Automation database.

   • @om_sqlsvr_pwd is the clear text password for the previous logon ID. 
     The following example shows how to include these parameters in the new file:

     set @om_sqlsvr_instance=N'Computer1\Instance1'
     set @om_db_name=N'bladelogic'
     set @om_sqlsvr_login=N'sa'
     set @om_sqlsvr_pwd=N'sa'

8. Replace all the instances of  'DB_SITE1_LINK ' with 'BSA_<SiteID>_<buildNo>'.

   Note

   For BMC Decision Support for Server Automation version 8.9.00, use the build number 42 in the link name. So, for the link name for the primary site use BSA_SITE1_42. 

   
   

9. Save the changes to a new file.

   Note

   Do not save your changes to the original script file. You might need that file later.

10. Run the newly created script.
11. In the left pane of SQL Server Management Studio, navigate to Server Object/Linked Server and verify that the new linked server was added.

12. In a SQL Server Management Studio query window, test the linked server by executing the following statement:

    Select * from BSA_<SiteID>_<buildNo>.<blDatabase>.dbo.agent_state

    <blDatabase> is the same database name that you used when you replaced the <Database name for OM> variable in step 7. 
    If the linked server was successfully added, a table is displayed with a description of values that indicate possible agent states. The table contents vary based on the version of BMC Server Automation used for the database.

13. Exit SQL Server Management Studio. 
14. Run the postinstallation configuration wizard again.