Creating a new SQL Server database connection

You can create a SQL Server database connection using a JDBC driver.

To create a SQL Server database connection using JDBC drivers

1. Navigate to CD Location\Reports\Util\DatabaseDrivers\sqlsrv2008 folder and copy the sqljdbc4.jar file to the C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\sqlsrv2008\ and C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\ locations.
    

   If the sqlsrv2008 folder is not available, create the sqlsrv2008 folder in C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\

2. Add the Transaction Isolation Level to sqlsrv.sbo file located at  C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\ . For more information, see the following example:
   
   

   DataBase Active="Yes" Name="MS SQL Server 2008">
              <JDBCDriver>
                  <!--  Uncomment and edit the following lines
                       to define java classes required by JDBC driver
                  &lt;ClassPath&gt;
                      &lt;Path&gt;your jar or class files directory&lt;/Path&gt;
                  &lt;/ClassPath&gt;
                   -->
                  <!-- Configuration is done for SQL Server 2008 JDBC Driver (2.0)  -->
                  <Parameter Name="JDBC Class">com.microsoft.sqlserver.jdbc.SQLServerDriver</Parameter>
                  <Parameter Name="URL Format">jdbc:sqlserver://$DATASOURCE$;DatabaseName=$DATABASE$</Parameter>
              </JDBCDriver>
              <Parameter Name="Extensions">sqlsrv2008,sqlsrv,jdbc</Parameter>
              <Parameter Name="Force Execute">Always</Parameter>
              <Parameter Name="Transaction Isolation Level">READ UNCOMMITTED</Parameter>
          </DataBase>

    

    

   Add the Transaction Isolation Level for all the versions of SQL Server, in the sqlsrv.sbo file.

3. Add the Transaction Isolation Level to jdbc.sbo file located at C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\ For more information, see the following example:
   
   

   DataBase Active="Yes" Name="MS SQL Server 2008">
              <JDBCDriver>
                  <!--  Uncomment and edit the following lines
                       to define java classes required by JDBC driver
                  &lt;ClassPath&gt;
                      &lt;Path&gt;your jar or class files directory&lt;/Path&gt;
                  &lt;/ClassPath&gt;
                   -->
                  <!-- Configuration is done for SQL Server 2008 JDBC Driver (2.0)  -->
                  <Parameter Name="JDBC Class">com.microsoft.sqlserver.jdbc.SQLServerDriver</Parameter>
                  <Parameter Name="URL Format">jdbc:sqlserver://$DATASOURCE$;DatabaseName=$DATABASE$</Parameter>
              </JDBCDriver>
              <Parameter Name="Extensions">sqlsrv2008,sqlsrv,jdbc</Parameter>
              <Parameter Name="Force Execute">Always</Parameter>
              <Parameter Name="Transaction Isolation Level">READ UNCOMMITTED</Parameter>
          </DataBase>

    

    

   Add the Transaction Isolation Level for all the versions of SQL Server, in the sqlsrv.sbo file.

4. Save the file.
5. Perform either of the following steps depoending on the authentication:
   1. For SQL Server authentication:
      1. For SQL Server authentication: Skip to Step 6.
      2. For Windows authentication:
         • Copy the x86\sqljdbc_auth.dll file located at CD Location\Reports\Util\DatabaseDrivers\ to the following directory:
           SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\jre\bin

         • Copy the x64\sqljdbc_auth.dll file located at CD Location\Reports\Util\DatabaseDrivers\ to the following directories:
           
           

           •  SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\sapjvm\bin

           •  SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\sapjvm\jre\bin

         • Logon to the Business Objects Server by using the login created by create_sql_server_BPPMR_user_win.sql script.
         • Navigate to Services and modify the This account details in the Log On tab, Secondary Logon Properties (Local Computer) screen, by using the login created by create_sql_server_BPPMR_user_win.sql script..

1. 1. 1. • Restart the Server Intelligent Service (SIA) service.
2. After importing the BPPM DataMart universe, in the Repository Resources tab, click Insert Session.
3. In the Open Session dialog box, specify the System, User Name, and Password and click OK
4. Right -click Connections > Insert Relational Connection.
5. In the New Relational Connection dialog box, enter the Resource Name and Description (optional).
6. Click Next.
7. In the Database Middleware Driver Selection screen, expand MS SQL Server 2008, select JDBC Drivers. Click Next.

8. In the Parameters for MSSQL 2008 Connection screen, enter the following details:

   1. In the Authentication Mode list, select Use specified user name and password.
   2. Enter the following details depending on the authentication:
      • For SQL Server authentication:
        
        1. User Name — type the Report Engine database user name that you have created using the create_sql_server_BPPMR_user.sql script.
        2. Password — type the password for the Report Engine database user.
        3. Servers — type the database host name and the port in the following format. The default port number is 1433.
           SQLServerhostName:SQLServerInstancePort
           For example, foo.zz.com:1433
        4. Database — type the SQL Server database name.
      • (Windows only)For Windows authentication:
        1. User Name — leave blank.
        2. Password — leave blank.
        3. Servers — type the SQL Server host name and the SQL Server port in the following format. The default port number is 1433.
           SQLServerhostName:SQLServerInstancePort;integratedSecurity=true
           For example, clm-pun-000735.bmc.com:1433;integratedSecurity=true
        4. Database — type the SQL Server database name.
   3. Click Test Connection, to validate the connection.
   4. Click Finish.