How to prepare MS SQL Server for MonTel and other SQL Server issues

This topic is NOT intended as a topic to describe how to install MS SQL Server, rather this topic discusses some aspects of MS SQL Server that affect the use of MonTel, provides detail on the settings that need to be made in the PABX.INI file

For a blow by blow account on setting up MonTel once SQL Server is running and the parameters below are correct see : How to update or set up MonTel to use SQL Client Server databases rather than Jet

MonTel and SQL Connections

Version 2.10 and earlier needed to set UseSQL = 1. If UseSQL = 1 in the [ClientDB] section of the PABX.INI then MonTel will use the ODBC Driver to connect to an SQL database, rather than directly to the much slower and less reliable Jet Engined Master Client Database on the network file server. Version 2.11 onwards uses ODBC Driver, so this setting is no longer required.

MonTel connects to the MS SQL Server using the ODBC driver supplied in MDAC. MDAC is installed on computers, with the MonTel client set up, which do not have at least version 2.1 of MDAC, providing the additional MDAC installation files have been downloaded and placed in the setup32/MDAC folder in the MonTel Application Directory by InstMon. (Windows 2000 already has MDAC 2.6). Since MonTel uses a DSN less connection, it doesn't require any additional set up on the local client computer if MDAC is working correctly. In other words, generally on Microsoft networks, you should not need to change any of the ODBC settings in control panel.

This is not always true for non-Microsoft networks. In some Netware installations, you may need to change the SQL Server Net-Libraries. We are still researching an efficient way to do this, however it appears that creating or modifying any DSN on the client computer with the correct Network Protocol Stack seems to give all DSNs or DSNless connections using the same driver access to the correct protocol. Please contact Netwiz support for more information.

For MS SQL Server the correct ODBC driver name is "SQL Server".

ODBCDriver = "SQL Server"

The database name is an arbitrary name to describe the database inside the SQL Server. It should be limited to 8 characters and be unique. On the test system it is called "Client32". You may well wish to called it "MONTELDB" to avoid confusion with other systems and names!

DatabaseName = "CLIENT32"

You will also need to specify the computer name of the SQL Server. For example FS1 or DBSERVER, or in the case of the Netwiz test network, NETWIZ1.

DatabaseServerName = "NETWIZ1"

MonTel and SQL Security settings

MonTel users can access the MS SQL Database by specifying ONE of two methods of Authentication: Windows NT Authentication Mode (also known as integrated) and SQL Server Authentication Mode (also known as standard). MS SQL Server 7.0 and 2000 support standard mode only in combination with integrated security, known as mixed security. However, MonTel will support one or the other but not both at the same time. For ease of administration, if MS SQL Server 7.0 (or 2000) is using mixed security then MonTel should use standard or SQL Server Authentication Mode OR if security is an issue then use Windows NT Authentication mode, and if MS SQL Server 7.0 or 2000 are using integrated (only) then MonTel must use Windows NT Authentication Mode.

In MonTel the type of security is determined by the setting WindowsNTAuthenticationMode. If it is not present (blank) or is 0, then standard (SQL Server Authentication) Mode is assumed. Standard mode is easy to use, but much less secure. See the discussion below for more information on this. Naturally, if WindowsNTAuthenticationMode = 1 then Windows NT Authentication is used.

For more information on security see the MS Web site: http://support.microsoft.com/default.aspx?scid=kb;en-us;325022

Windows NT Authentication Mode (Integrated)

WindowsNTAuthenticationMode = 1

MonTelAdminGroup = "Power Users"

MonTelUsersGroup = "Everyone"

According to Microsoft, the Windows NT Authentication Mode uses the security mechanisms within Windows NT when validating login connections, and relies on a user's Windows NT security credentials. Users do not need to enter login IDs or passwords for SQL Server—their login information is taken directly from the network connection. When this occurs, an entry is written to the syslogins table and is verified between Windows NT and SQL Server. This is known as a trusted connection and works like a trust relationship between two Windows NT servers.

To avoid having a complex mechanism to add each MonTel user to the syslogins table in the SQL Server (master database), two Windows NT security groups (of users) are given permission to use the database. These are a "MonTel Admin Group" and a "MonTel Users Group". In a basic set-up these groups might well be "Power Users" and "Everyone", although in larger networks security and control requirements would require the creation of specific groups and include the Domain name to be able to access MonTel. For example:

MonTelAdminGroup = "{domain}\MonTelAdmin"

MonTelUsersGroup = "{domain}\MonTelUsers"

In either case users need to be assigned to these groups by the Network Administrator. MonTel has no control over this process.

Moreover, the very first time the Master Client database is run (that is, when it is created) the Windows NT security of the user needs to be the equivalent of Administrator or the SQL Administrator on the computer on which the SQL Server software is being run (not necessarily on the client computer on which MonTel is being run).

SQL Server Authentication Mode (standard).

WindowsNTAuthenticationMode = 0

Standard (SQL Server Authentication) Mode is easier to manage from MonTel, as the groups and users are maintained inside the Master Client Database, and MonTel looks after the security arrangements. However, since MonTel uses a Jet Engine local client database (that is, an MS Access file), which stores a login name and password to be able to attach to the SQL database, security could easily be breached, as an attacker need only to look at the link parameters of a linked table in the local client database to recover the login name and password. In environments where sensitive data, or other programs are being run on the same SQL Server, Windows NT Authentication Mode (integrated) should be used.

There is a special user in Standard (SQL Server Authentication) Mode called 'sa' (SYS Admin) which has control over every aspect of the SQL Server. This user should always be secured by password, even if only integrated mode is used, since mixed and integrated mode settings can be changed by a single entry in the Windows registry. By default SQL Server installs 'sa' with a blank password. This should be changed using Enterprise Manager or by running OSQL and using the sp_password command.

EXEC sp_password NULL, 'new_password', 'sa'

The sa account exists in all authentication modes, not just standard, so it is vital that the password word is set on all installs. While is cannot be accessed in Windows NT Authentication Mode, the mode can be changed in the Registry with one setting, and therefore, un secured, is a considerable security risk and virus vector.

When the Master Client database is used for the first time MonTel will need to logon as 'sa' to be able to create he database, and establish the users and groups inside the SQL database. Note that neither MonTelAdminGroup or MonTelUsersGroup need to be specified (they are used only in the integrated security mode).

For more information on how to verify and change the system administrator password in MSDE or SQL Server 2005 Express Edition see http://support.microsoft.com/kb/322336.

SQL Server Backup.

Since SQL Server is running (probably) 24 hours a day, many backup systems will fail to copy any open files. You may need to use the OSQL command line tool to run the BACKUP command to copy a backup file to a part of the network that is backed up, on a regular basis. To do this you will need to attach to the SQL Server as a member of the db_owner fixed database role (This will be 'sa' for Standard security mode), or use Enterprise Manager. For an example OSQL script see: How to set up MSDE to run MonTel on your network.

Alternatively, you can create a backup each time you run Monex or Monimp32 (2.9.53 onwards) by specifying AutoImportSQLBackUpMasterClientDB or AutoExportSQLBackUpMasterClientDB and optionally SQLBackupPath for the location. Note that, SQLBackupPath is relative to the SQL server, and not the monex/monimp PC.

It is important to understand the recovery mode that the SQL database is using. On large sites a full SQL server (not express) may be in use, in which case the recovery mode is likely to be FULL. In this case is is essential that a regular log file backups are made, and the database administrator has a recovery/backup plan in place. For this reason from version 2.12.30 MonTel no longer performs backups on SQL servers that don't have a recovery model set to SIMPLE, as this may break or obfuscate the backup chain.

On SQL express system, the recovery mode by default is simple. On these systems or on system where the DBA has explicitly set SIMPLE mode, a MonTel administrator can perform backups and other system maintenance functions from the menu of MtAdmin. In SIMPLE mode from MonTel version 2.12.30 onwards MonTel will run a checkpoint when performing and update (On SQL version 7 or MSDE 1.0 a log file truncation is also performed), followed by a shrink log file command down to 16MB. This should include plenty of growth space, but should catch runaway situations before they evolve. To reduce any ensuing internal fragmentation, a de-fragmentation of all the data indexes is performed as well. On the database system menu of MtAdmin the 'Truncation and shrink of transaction log files' is performed using the same steps.

SQL Server File growth.

The Master Client Database is stored on the SQL Server in the SQL data directory with the name specified in the PABX.INI file (DatabaseName) with the data file appended with ".mdf" and the log file appended with ".ldf". Internally, the database names will have "_dat" and "_log" appended to the DatabaseName. So if the DatabaseName is "CLIENT32" then the SQL data and log files will be: "CLIENT32.mdf" and "CLIENT32.ldf". (On databases created prior to 2.9.53 the log file will be "CLIENT32_log.ldf").

The Data file is set to unlimited growth, though the real growth is likely to be very small, and the log file to a max size of 1024 MB. Prior to version 2.12.30 databases were set to limit growth to 2048MB for the main data file and 1024MB for the log file, though the real figures were closer to 30MN and 5MB respectively in a medium sized site. Some much older systems may have had growth limited to smaller sized.

The data file is initially created to be 20 MB in size, growing in 4MB increments. The log file is created to be 5 MB in size, growing in 4MB increments.