Installing and Configuring Microsoft SQL Server 2005 Express Edition for SCM

July 1st, 2009

Kapuwa

https://support.ca.com/irj/portal/anonymous/kbtech?searchID=TEC394780&docid=394780&bypass=yes&fromscreen=kbresults

Document ID: TEC394780

Tech Document

Title: Installing and Configuring Microsoft SQL Server 2005 Express Edition

The eTrust SCM Quarantine Manager and Reporter can use either MS-SQL or Microsoft SQL Server 2005 Express Edition as the database layer.

According to your needs, you can install a dedicated SQL 2005 Express Server on a remote machine, or install SQL Express 2005 on the same machine as the Quarantine Manager and/or Reporter.

Please read further about MS SQL Express 2005 server at:

http://msdn.microsoft.com/vstudio/express/sql/default.aspx

eTrust SCM r8 Prerequisites

Perform the following installations and checks before installing eTrust SCM:

Install Microsoft SQL Express 2005 edition according to the product’s documentation. Please read further detail in this document.
Important! Microsoft SQL Express Server must be installed before you install eTrust SCM.
Confirm that SQL Express 2005 edition and Windows authentication is enabled in the SQL Express 2005 SQL Server Management Studio Express. To confirm this setting, do the following:
- SQL Server Management Studio Express.
- Right click on the Database root and choose the “properties” option.
- Select the “Security” page at the left pane of the “server properties dialog”, as illustrated in the screenshot at the next page.
- Confirm that at the “Server Authentication” option the “SQL Server and Windows Authentication radio button is selected (see screenshot).
Create both the Quarantine and Reports Databases at the SQL level as detailed further down this document.

Install eTrust SCM after you have confirmed that the above prerequisites have been met.

Microsoft SQL Server 2005 Express Edition System Requirements

Please read the MS SQL 2005 Express Edition pre installation requirements carefully at:

http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/RequirementsSQLEXP2005.htm

Uninstall beta versions

Before you install any members of the SQL Server 2005 Express Edition family, you must uninstall any beta or CTP versions of SQL Server 2005, Visual Studio 2005, and the .NET Framework 2.0.

The following is a summery of the installation prerequisites and we recommend reading the complete requirements from the above line since installation instruction may be modified by Microsoft from time to time.

Supported Operating Systems: Windows 2000 Service Pack 4; Windows Server 2003 Service Pack 1; Windows XP Service Pack 2.
Computer with Intel or compatible Pentium III 600 MHz or faster processor (1 GHz or faster is recommended).
Minimum of 192 MB of RAM (512 MB or more is recommended).
525 MB of available hard disk space.

You must have administrative rights on the computer to install SQL Server Express.

Step 1: Download and install Windows Installer 3.0.
Note: If you are running Windows Server 2003 Service Pack 1 (SP1) or Windows XP SP2, you already have Windows Installer 3.0.

Step 2: For 32-bit platforms, download and install the 32-bit version of Microsoft .NET Framework 2.0.

Microsoft Internet Explorer 6.0 Service Pack 1 (SP1) or later (prerequisite for .NET Framework).

Step 3: Download Microsoft SQL Server 2005 Express Edition http://msdn.microsoft.com/vstudio/express/sql/default.aspx.

Direct link:
http://go.microsoft.com/fwlink/?LinkId=65212

Step 4: Follow the installation instructions provided in the Readme.
http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQLEXP2005.htm

Creating the Quarantine and Reports Databases

Download and install the SQL Server Management Studio Express (separate download below), so you will be able to manage your SQL Server Express.

Download SQL Server Management Studio Express:
http://msdn.microsoft.com/vstudio/express/sql/default.aspx

Direct link:
http://go.microsoft.com/fwlink/?LinkId=65110

To create the quarantine and reports databases, follow these steps:

Create the Quarantine Database

Perform the following steps to create the Quarantine database:

Open the “SQL Server Management Studio Express”.
Connect to the Database Server you have created during the SQL installation. Browse to the Database level.
Choose Database. The default database appears in the right pane:
Right-click the right pane and select New Database. The “New Database” dialog appears:
Enter a name for the container on the General tab, for example: eSCM_Quarantine_DB.
Within this dialog you may adjust the Data Files properties for the Database, adjust the default parameters, if this is a heavily-used database we recommend to use an initial size of more then 2MB to decrease DB fragmentation.
For Example an initial size of 100MB will be fine.
The defaults parameters are fine for testing purposes.
Adjust the parameters for the DB log file as well.

Adjust the Autograph of the DB either by size or by percentage by clicking the button next to the Autogrowth option:

Click O.K to create the database.

Create the Reports Database To create the Reports database, repeat the same steps used to create the Quarantine Database, with the exception of the database name:

On the New Database dialog, enter a unique name for the container on the General tab, for example: eSCM_Reporter_DB.
Within this dialog you may adjust the Data Files properties for the Database, adjust the default parameters, if this is a heavily-used database we recommend to use an initial size of more then 2MB to decrease DB fragmentation.
For Example an initial size of 100MB will be fine.
The defaults parameters are fine for testing purposes.
Adjust the parameters for the DB log file as well.

Creating an SQL User and Associating It with the Databases

To create an SQL user and associate it with the databases, follow these steps:

Open the “SQL Server Management Studio Express”.
Connect to the Database Server you have created during the SQL installation.
Browse to the Security folder, and right click on logins folder.
Choose the “New login” option.
The SQL Server Login Properties – New Login dialog appears:

Create a new user using the SQL Server Authentication option.
This user does not require administrative privileges anywhere except than for the Quarantine and Reports databases.

Enter the password and password confirmation for the new login you have created.

Click the User mapping option, and select the databases to be accessed by this login as follows:

Select the eSCM_Quarantine_DB and specify the roles for the new database.
In the Database roles, both public and db_owner should be selected, as this user must be the db_owner to create the tables properly.

Select the eSCM_Reports_DB and specify the roles for the new database. In the Database roles, both public and db_owner should be selected, as this user must be the db_owner to create the tables properly.

Verify that the login and the connection to the DB are enabled and granted.

Click OK.

Notes:
There is no need to further tune additional database parameters, or create any tables, or set any ODBC settings. The eTrust SCM installer will do so during the installation. If you change the SQL database credentials after the eTrust SCM installation, use the Manager Console to configure the new credentials. To do so, from the Manager Console’s menu select Settings, Engine settings, Microsoft SQL Tab.

Troubleshooting

Connecting to the DB with “Named Instance”.In most cases the connection issue might be related to the DB instance name.
When installing the SQL server and trying to connect on to the ‘localshost’ auto detected SQL DB, SCM installation utility and the SCM DB migration utility might fail to connect to ‘localhost’ DB.

Instead of ‘localhost’ at the server you’ll need to specified the named instance of the DB.

Please manually specify:

MY_SQL_EXPRESS_SERVER_HOTS\MY_SQL_EXPRESS

The named instance of this copy of the database can be view when logged via the “Microsoft SQL sever Management Studio”.

While trying to login: MYSQLSERVER\SQLSERVER

And at the following example the ‘Named instance’ is “DEV8VM200\SQLEXPRESS”
Database Login user password expiration.The eTrust SCM installation utility must have a valid connection to SQL server, in order to retrieve information regarding the available SQL servers and DB tables, and display them during the installation procedure.
In case “User must change password at next login” is checked (see screenshot below), the instillation utility, as well as the eTrust SCM Database migration tool, may face difficulties connecting to the DB interface.

Either disable this option at the “Creating an SQL User and Associating It with the Databases” stage, (see step 6 page 10), or re-login to the “SQL Server Management Studio Express” at the DB creation phase and change the password for the user (‘scm’ in this example).
1. Connect to the SQL Database using the “Microsoft Server Manager studio Express”.
2. Login with the initial password – you’ll be prompted to change the password for the login user.
Connecting to a remote SQL Express Database. If you intend to remotely connect to the SQL Express database e.g. using terminal services to the SCM host with the SQL Express database you should enable the TCP/IP protocol of the SQL Express server.
Logon to the ‘SQL Server Configuration Manager’ – Start ->Programs ->Microsoft SQL Server 2005 -> configuration Tools

Right click on the TCP/IP protocol configuration and enable to protocol:

The SQL Server service must be restarted in order to load new TCP/IP settings: