Creating an MS SQL Server Database
Notes on Installing Datasources on MS SQL Databases

To install a Datasource on a Microsoft SQL server database, follow the steps below:

1. Create the Chromeleon database on the MS SQL Server

When you create a Chromeleon database on the MS SQL server, the analysis data is not stored in the MS SQL database. Thus, it is only necessary to reserve storage space for the sample and sequence data. The database is created via the corresponding administration programs of the MS SQL server, including granting access rights to users.

At least two users are created: one administrator with all rights and one user with standard rights.

If the Chromeleon database has already been created, a standard user needs the following rights in order to connect to an MS SQL server 7.0 database:

1. • The Create Table and View (Properties of the master DB, Authorization tab page) rights must be available in the master database the first time a user connects.

   • The user must have the "database roles" db_datareader and db_datawriter assigned (DB Users).

2. Specify where to store analysis data

Analysis data is usually stored on the MS SQL server PC. Note that analysis data requires considerable storage space, especially when recording 3D fields. To enable access, all relevant Chromeleon clients and/or servers must share the directory where the analysis data is stored.

3. Install the ODBC driver on the Chromeleon client PC

Use the Microsoft Data Access Components software (for example, Mdac_typ.exe) to install the ODBC driver for the MS SQL server.

4. Create the Chromeleon datasource

Start Chromeleon. Select File > Datasources... > Connect and then select the OBDC driver called SQL Server.

5. SQL Server Login

Administrator privileges are required for the first login to a new database.

Enter the name of the MS SQL server PC, your user name, and the password. Select the database previously created on the MS SQL server via Options. Click OK to establish a connection between the Chromeleon client and the MS SQL database.

A standard user who logs in to the database for the first time must have already been assigned the Create Table and Create View privileges in the Master database of the MS SQL server. These privileges are selected in the Properties field on the Authorization tab page. In addition, the db_datareader and db_datawriter "database roles" must be available in the Chromeleon database (on the MS SQL server). These database roles are also required for all subsequent logins. These are all MS SQL privileges, and are not related to the Chromeleon privileges.

6. Enter the "Data Source Properties"

Enter a meaningful name for the data source during the first logon. This name will be used to identify the MS SQL database on the Chromeleon clients. In the Datasource Path field, set the shared path that you have previously entered for the analysis data on the MS SQL server; for example, \\MySQLServer\CM_DATA$\Year2000.

The name of the data source is disabled during the next login; the path is preset. However, it is still possible to select the path.

Also, refer to  Creating an MS SQL Server Database.