Creating database and user on SQL Server

After you install Microsoft SQL Server, you must create database, filegroups, and a user with appropriate rights. Filegroup is a grouping of database objects and files. Filegroup is used for the administrative and data placement purposes.

The create_sql_user.bat script creates two users:

• Schema Owner: When prompted for the Database user name, the script creates the user with the provided name. This user is used to create schema while installing BMC TrueSight Operations Management Report Engine.  
  The create_sql_user.bat script provides following permissions to this user.
  

  Permission State	Permission Type
  GRANT	ALTER
  GRANT	CONNECT
  GRANT	CREATE FUNCTION
  GRANT	CREATE PROCEDURE
  GRANT	CREATE TABLE
  GRANT	CREATE TYPE
  GRANT	CREATE VIEW
  GRANT	EXECUTE
  GRANT	SELECT ON MSDB*

   *SELECT ON MSDB permission is required during Report Engine installation and executing of JOB (pr_analyze_configtables_once), which analyzes config data table for the first time. Installer requires this permission to verify whether an existing job is available. After successful installation and successful running of the JOB (pr_analyze_configtables_once), you can revoke this permission. If you are installing multiple Report Engines, you can revoke this permission after all the Report Engines have installed successfully. BMC recommends you to revoke the permission after 24 hours of installation.

• Read-only User: The script does not prompt for user name, however, by default, creates the user with reuniv name with the password for this user is RE#Adm1n. You can change the password after the successful installation. This user is used by BusinessObjects while creating universe connections. 

To create the database, user, and filegroups, perform the following steps:

1. Navigate to the Util folder. Copy the AdminDatabaseScripts folder to the computer where you have installed BMC TrueSight Operations Management Report Engine database. 
2. Log in to the Microsoft SQL Management Studio with the SQL admin user (default is SA).
3. In the Microsoft SQL Management Studio, navigate to Explorer.
4. Expand SQL Server > Databases. 

5. Right-click Databases > New Database.
   
   

   Note

    Ensure that you are using default collation as case insensitive collation. For example, in English, the default collation is  SQL_Latin1_General_CP1_CI_AS.

    

6. In the New Database window, enter the Database name and click OK.
7. The newly created database is added to the list of databases.
8. At the command prompt, navigate to the location where you have copied the AdminDatabaseScripts folder, as mentioned in step 1. 

1. Execute the create_sql_user.bat script only from the location where you have copied the AdminDatabaseScripts folder. 
2. Enter the required data for the script:
   
   1. 
      
      1. Enter the database server name:
         Type the Report Engine database host name.
      2. Enter the database server instance name:
         Type the Report Engine database server instance name. You can find the SQL Server instance name from services.msc. The default value is MSSQLSERVER
      3. Enter the database server port:
         Type the Report Engine database port. By default, the port number is 1433. You can also specify the port number with which you have installed SQL Server.
      4. Enter the Authentication mode for the sysadmin user. <1 -SQL 2 -Windows>
         Select 1 for SQL sysadmin user (sa) or 2 for Windows sysadmin user (hostname\administrator). The default value is SQL sysadmin (sa). If you have selected 1, go to step v. If you have selected 2, skip to step vii.
      5. Enter the sysadmin user:
         Type the sysadmin user name.
      6. Enter the sysadmin password:
         Type the sysadmin password.
      7. Enter the database name:
         Type the SQL Server database name you created in step 5.
      8. Enter the type of login you want to create <1-SQL Server Authentication 2-Windows Authentication>
         Select 1 for SQL Server login or 2 for Windows login. The default value is SQL Server login. If you have selected 1, go to the next step. If you have selected 2, skip to step xi.
      9. Enter the database login name:
         Type the login name.
      10. Enter the database login password:
          Type the  database login password.
      11. Enter the database user name:
          Type the database user name.
      12. Enter the path for file groups:
          Specify the location where you want the file groups to be created.
      13. Please enter database size..Ex. small, medium, large <Default small>:
          Select the database size as small, medium, or large. The default values is small.
          
          The system executes the script, and the user and file groups are created. 

Troubleshooting the remote connection issue

1. Run SQL Server Configuration Manager.
2. Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.
3. Make sure TCP/IP is enabled.
4. Right-click on TCP/IP and select Properties.
5. Verify that, under IP2, the IP Address is set to the computer's IP address on the local subnet.
6. Scroll down to IPAll.
7. Make sure that TCP Dynamic Ports is blank.
8. Make sure that TCP Port is set to <port_num>. By default, 1433.