Steps to configure Portal v 6.1.x with SQLServer 2005 property extension database 

WebSphere Portal Detailed System Requirements

Install SQL Server 2005 and the JDBC Driver

In this section we will install SQL Server 2005 and the Microsoft JDBC 1.2 Driver. All steps will be completed on Windows.

1. Install SQL Server 2005 following Microsoft documentation. Ensure you install these components:

• SQL Server Database Services
• Integration Services

2. Ensure that TCP/IP connectivity is enabled in the SQL Server Configuration Manager.

3. Download the Microsoft SQL Server JDBC drivers to a temporary directory on the SQL Server server. In this guide we will be using the JDBC 1.2 drivers.

NOTE: These drivers can be retrieved from the Microsoft support website.
NOTE: The JDBC 2.0 driver is only supported with WebSphere Portal version 6.1.0.3 or higher. If you try to use the JDBC 2.0 driver with WebSphere Portal v6.1.0.0, 6.1.0.1, or 6.1.0.2, the configuration will fail.

4. Extract the JDBC driver download into some directory on the server. For example, C:\SQLJDBCDriver\.

5. Locate the sqljdbc_xa.dll file within the JDBC Driver directory. For example, C:\SQLJDBCDriver\sqljdbc_1.2\enu\xa\x86.

6. Copy sqljdbc_xa.dll to the following directory on your SQL Server:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\

8. Start the Microsoft SQL Server Management Studio and login as the system administrator.

9. Select File -> Open -> File and select the xa_install.sql file from the Driver directory you created in Step 4. For example, C:\SQLJDBCDriver\sqljdbc_1.2\enu\xa\xa_install.sql.

NOTE: Ignore any warnings you see that say stored procedures cannot be found. This is expected.

11. Launch the Windows Registry Editor by clicking Start -> Run, then by typing regedit and clicking OK.

12. Navigate to the element HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL

13. From the menu, select Edit -> New -> String Value.

14. Create a new parameter called: sqljdbc_xa.dll

16. Ensure XA Transactions are enabled in the Windows environment. Click Start -> Run, type dcomcnfg, and click OK to open Component Services.

17. Expand Component Services -> Computers. Right-click on My Computer and select Properties.

18. Click the MSDTC tab, and then click Security Configuration.

19. Ensure the Enable XA Transactions checkbox is checked.

20. Click the OK button and restart the SQL Server to ensure the changes are set.

21. Copy the database driver jar files from the SQL Server server to the WebSphere Portal server in some directory. For example,

C:\SQLJDBCDriver\sqljdbc_1.2\enu\sqljdbc.jar

Modifying the ConfigEngine properties

In this section, you will prepare the wkplc.properties and wkplc_dbtype.properties files with the property extension database information. Several of these properties, such as the Database Names, Users, and Schemas, do not exist yet in the SQL Server database. They will be created in the 'Creating Databases' and 'Setting up Databases' sections.


You can organize this data in however many databases you would like. The recommendation is to use six separate databases.

In this guide, we will be database name WP_LADB for our database name.

Complete the following steps:

1. Backup the following file from the <wp_profile>/ConfigEngine/properties directory:

wkplc.properties

2. Edit the <wp_profile>/ConfigEngine/properties/wkplc.properties and update the following properties:

la.DbType - The type of database you are configuring. This value should be 'sqlserver2005'.
la.DbName - The name of the database for this domain. This database will be created in the 'Creating Databases' section.
NOTE: This value should be present in the DbUrl.
la.DbSchema - The schema identifier for objects within the database for this domain. The schema will be created in the 'Setting Up Databases' section.
NOTE: This value should equal the la.DbUser value.
la.DataSourceName - The name of the datasource that will be created in the WebSphere Application Server configuration.
NOTE: Do NOT use any of these values: releaseDS, communityDS, customizationDS, jcrDS, likemindsDS, feedbackDS
la.DbUrl - The url used to access your database.
NOTE: The URL must use this exact syntax for SQL Server: jdbc:sqlserver://<sqlserver_hostname>:<port>;SelectMethod=cursor;DatabaseName=<DbName>
Any other syntax will cause the database transfer to fail.
NOTE: The value you set for DbName must be used in this url.
la.DbUser - The name of the user that will access this database. This user does not exist yet and will be created in the 'Setting Up Databases' section.
NOTE: This value should equal the la.DbSchema.
la.DbPassword - The password of the DbUser.

4. Save the wkplc.properties file.

5. If needed, edit the wkplc_dbtype.properties file and modify the following property to point to the location that you copied the jar files from Step 21 in the 'Installing SQL Server 2005' section:

sqlserver2005.DbLibrary=C:\SQLJDBCDriver\sqljdbc_1.2\enu\sqljdbc.jar

6. Save the wkplc_dbtype.properties file.

Creating Databases

In this section, you will create the database in the SQL Server environment to be used by WebSphere Portal. For this guide, just one database will be created:

WP_LADB

1. Start the Microsoft SQL Server Management Studio and login as the system administrator.

2. Expand the tree in the Object Explorer window to see Databases.

4. Enter the database name. This should match the name you provided for la.DbName in the 'Modifying the ConfigEngine properties' section.

5. On the Options section, set the Collation value so that it matches your environment. For English systems, select SQL_Latin1_General_CP1_CS_AS:

NOTE: Ensure this is set to "Case-sensitive" (CS).

6. Click OK to save the database changes.

Setting up the databases

In this section we will prepare the databases for configuration with WebSphere Portal.


1. Start the Microsoft SQL Server Management Studio and login as the system administrator.
2. Expand the tree in the Object Explorer window to see Databases -> WP_LADB -> Security -> Schemas
3. Right-click on your Schemas and select "New Schema..."

4. Create a new schema with the value you provided for la.DbSchema in wkplc_comp.properties (from the 'Modifying the ConfigEngine Properties' section).

5. Expand the tree in the Object Explorer window to see Databases -> System Databases -> master -> Security -> Schemas

6. Right click on Schemas and select "New Schema..."

7. Create a schema here for the la domain. Use the same value you provided for release.DbSchema in wkplc.properties (from the 'Modifying the ConfigEngine Properties' section).

8. Expand the tree in the Object Explorer window to see Security -> Logins.

9. Right-click on Logins and select "New Login..."

10. Provide the name of the la user. Use the same value you provided for la.DbUser in wkplc.properties (from the 'Modifying the ConfigEngine Properties' section).

11. If you are using SQL Server Authentication, select the radio button for this and enter the password. This should be the same password you set for la.DbPassword.

12. Set the "default database" to the WP_LADB database.

15. Click the link on the left hand side for "Server Roles". Ensure that NO server roles are checked:

16. Click the link on the left hand side for "User Mapping".

17. Check the box for the look-aside database and set the schema to the look-aside schema name.

18. For Database Role Membership, check the boxes for "db_owner" and "public".

19. Still on the 'User Mapping' window, check the box next to 'master' and set the schema name to the look-aside schema.

20. For Database Role Membership, check the boxes for 'public' and 'SqlJDBCXAUser'

21. Click 'OK' to create the user.

When you are finished, the user should be the db_owner of the database AND have SqlJDBCXAUser access to the 'master' database.
This guide will detail how to configure WebSphere Portal v6.1.0.x and 6.1.5.x with SQL Server 2005 databases. For more details on supported databases for WebSphere Portal, please review the detailed system requirements for WebSphere Portal:

WebSphere Portal Detailed System Requirements