SQL Server login and user accounts

The SQL Server installation must be set up and configured for iBase users.

The main activities in managing security for iBase in an SQL Server installation are to:
  • Set up login accounts and user accounts for iBase users.
  • Deny access to the iBase databases from applications other than iBase.
  • Allow access to other applications that must access the database such as the backup tool or the Microsoft Search service that creates the index for Full-Text Search.
Note: Access from iBase to an SQL Server database is managed through an application role. Application roles are an SQL Server mechanism that allows external applications to assume control of a database in a secure manner. Database access in Analyst's Notebook through the iBase API.

In an SQL Server installation of iBase, each user must gain access to an instance of Microsoft SQL Server through a login that establishes the user's ability to connect (authentication). This login is then mapped to an SQL Server user account, which is used to control activities performed in the database (permissions validation). Therefore, a single login is mapped to one user account that is created in each database the login is accessing. If no user account exists in a database, the user cannot access the database even though the user may be able to connect to an instance of SQL Server.

Almost all users that work with iBase need only have permissions to access the SQL Server database. They do not need any other permissions. To configure this, the SQL Server administrator should use the public database role. Every user of a database will be given the public database role.

In an SQL Server installation, the default permissions for this role allow a user to access system tables and run predefined stored procedures. It does not allow any access to any of the user tables that hold the iBase data. All access to user tables is handled by iBase. If a user only has the public database role they cannot use other applications to access the data in the database. This will prevent them from using tools such as Microsoft Query, Enterprise Manager or Management Studio to see data in the database.

In addition, the SQL Server login that is used to connect to the SQL Server database must have execute permission on the sp_help_jobschedule stored procedure in the msdb system database. SQL Server administrators can grant this permission by editing the properties of the Public database role defined in the msdb database.

SQL Server administrators can further reduce access to the database by removing access to all of the system tables within the database from the public role. This can be done by accessing the roles for the database and changing the permissions to not allow access. This measure prevents an iBase user with valid access from using the public role to open the database from another tool and reading the contents of the system tables which would, for instance, allow them to determine the names of tables and fields.

iBase users require access to these SQL Server databases:

Database Description
iBase security All users of an iBase database must have access to the database (security file) which secures the main iBase database.

The name of the database is provided by the iBase security administrator at the time of creation. The security database on the SQL Server machine will have the SQL Server equivalent name of the Microsoft Access component with the suffix _ sec. For example, a security file that has been given the name Vehicle Crimes will have a connection file named Vehicle Crimes.ids and an SQL Server database name of Vehicle_Crimes_sec.
iBase database iBase manages the way that users can access the data within the database. Users can access only the data for which they have access permissions, which are set using the optional iBase Extended Access Control (EAC) module.

The name of the database file is provided by the iBase administrator at the time of creation. The database on the SQL Server machine will have the SQL Server equivalent name of the Microsoft Access connection file. The SQL Server database does not have a suffix. For example, a database file that has been given the name Vehicle Crimes will have a connection file named Vehicle Crimes.idb and an SQL Server database name of Vehicle_Crimes.
Audit log database In an SQL Server installation, iBase creates an audit log database alongside the main database. The name of the database is the same as the main database name with the suffix _log. For instance, the database Vehicle_Crimes has an audit log database Vehicle_Crimes_log.

You must also ensure that iBase users can access this audit log database. If you do not provide access to the audit log database iBase attempts to create a new audit log database and fail with a message that says it could not do so successfully.

Managing data access

The model database is used by SQL Server as a template for all new databases that are created. Setting the permissions of the public database role on the model database ensures that newly created security databases, iBase databases, or audit databases copy the permissions that are set on the model database. This applies to all databases created on the server and not only those that are created through iBase.

In a Microsoft Windows environment, a simple way to manage data access is to use a Windows group for iBase users. For instance, create a windows group with the name iBase_users and add to it all users that need access to the database. In SQL Server, create a login for this group and grant it access to the iBase database (or even to the model database). Do not give this login any server roles or database roles other than public.

SQL Server login for creating databases in iBase Designer

When you create SQL Server databases using iBase Designer, users must have the iBase role Database Creator. These users must also have a corresponding SQL Server login that is a member of the dbcreator fixed server role.

A simple way to manage the creation of databases is to have a dedicated SQL Server login that uses either SQL Server or Windows authentication and is a member of the dbcreator fixed server role. iBase administrators use this login whenever they create new SQL Server databases or convert Microsoft Access databases to SQL Server. The databases are created with this login mapped to the dbo user.

The login used to create the database will continue to have dbo rights to the database and audit log database. It is important that either the login and password are kept secure, or the ownership of the databases is changed by an SQL Server administrator.

Note: After creating an iBase database, an iBase administrator can change the connection file so that users will connect to the database via a less powerful SQL Server login. The SQL Server administrator must grant this login access to the database on the SQL Server instance. For details of the login, see above Data access for iBase users. Alternatively, they can change the connection to use Windows authentication. For details of this step, see the Administration Center document External Access Control.

Working with BUILTIN\Administrators

Any Windows account that is an Administrator of the machine on which SQL Server is running is automatically a member of the SQL Server BUILTIN\Administrators group. In some SQL Server installations, it is a policy that this powerful group is removed so that SQL Server administration is separate from administration of the server machine on which it runs. If this group is removed or modified, it will be necessary to take additional measures to ensure that SQL Server and iBase continue to function.

Attention: If the BUILTIN\Administrators login is removed, ensure that there is at least one other login that is a member of the System Administrators fixed server role. If this login is validated using SQL Server authentication, also ensure that the SQL Server is configured to allow both SQL Server and Windows authentication.

If, during the installation of the SQL Server instance, the option was chosen to run the SQL Server service and SQL Server Agent service under the context of the localsystem account, it will be necessary to set up specific

Windows user accounts to run these services:
Service Windows user account
SQL Server This user account should only be a member of the Windows group Domain Users.
SQL Server Agent This user account usually only needs membership of the Windows group Domain Users, however to perform certain advanced tasks, for example, executing xp_cmdshell or using the AutoRestart feature of the SQL Server Agent service, it will also need to be a member of the Local Administrators group.

You may need to provide a login for backup operations. This login will need to have the db_backupoperator database role.

Access for Full-Text Search

The Microsoft Search Service must have permission to read the database. By default this service runs as localsystem and has access to the databases on the server via the BUILTIN\Administrators login.

If you remove access for BUILTIN\Administrators login you must add a new login with the name:

NT AUTHORITY\System (notice the space between NT and AUTHORITY)

This login must be a member of the SQL Server System Administrators fixed server role.