Layton ServiceDesk - FAQ - Manual SQL Connection
Contents |
Overview
Layton ServiceDesk uses Microsoft SQL Server for data storage. Layton ServiceDesk can connect to SQL Server by either NT Authentication or SQL Server Authentication. This technical note is a detailed explanation of what needs to be done to establish a database connection by either method.
Database Backup Method
It is advised that you make a back up of the entire LaytonHelpBox program folder by Right clicking on the C:\LaytonServiceDesk folder and select “Copy”, then “Paste” it to a new folder on the production server (e.g. ‘C:\LaytonServiceDesk backup’).
To make a back up of the HelpBox SQL database you can use a SQL DB manager such as “Enterprise Manager” or “MS Management Studio” as this is the most reliable method of backing up a SQL database. Under the “Databases” directory, locate the LaytonServiceDesk database and right click on it. Under “All Tasks” select the “Backup” option on the “LaytonServiceDesk’ Database. You can give the back up a file name such as “LaytonServiceDesk.bak’, then once the backup is completed, save this in a safe location.
To restore a backup copy of the “Layton ServiceDesk” database use the same method to locate the options mention above, and under “All Tasks” choose the “Restore” option and select the previous “LaytonServiceDesk.bak” file to restore.
A manual backup of the Layton ServiceDesk database can also be run from within Layton ServiceDesk by going to Administration | System Settings | Database Connection | Back-up Path. Specify the backup path and then click the backup icon and this will create the backup file (LaytonServiceDesk.BAK) in the specified location.
Note - It is recommended to make regular backups of both the Layton Layton ServiceDesk program folder and Layton ServiceDesk database.
Manual SQL Connection
Overview
The SQL connection can be configured manually. This is useful in the event where a change in the Application Database Connection settings has caused Layton ServiceDesk to lose its connection.
There are two options: NT or SQL authentication.
Connecting Using NT Authentication (Trusted Connection)
The NT Authentication can be set up by editing the config.xml file in the LaytonServiceDesk/HB_Data/configXML subfolder. The user can edit the file using any text editor such as notepad. The sample below shows the four lines that need to be configured to allow connection to SQL server using NT Authentication.
<appodbc label="Connection">(local)</appodbc> <appdb label="Database">LaytonServiceDesk</appdb> <appdbuid label="User ID (Blank = NT Trusted)"></appdbuid> <appdbpwd label="Password"></appdbpwd>
appodbc
The server name of the SQL Server you are connecting to. This can be a server name or an IP address. If an instance is used, it should be specified, e.g. SERVER\SQLEXPRESS. The value of (local) is a default value that tells LaytonServiceDesk to connect to the same server as it is installed on.
appdb
The database within the SQL Server that LaytonServiceDesk is to connect to. The default name as installed is LaytonServiceDesk, unless you have installed the LaytonServiceDesk database using a different name.
appdbuid and appdbpwd
Should be blank as shown, or LaytonServiceDesk will try to connect using SQL Server Authentication.
Connecting Using SQL Authentication
SQL authentication can be configured manually by editing the config.xml file. This is located here:
C:\Program Files (x86)\LaytonServiceDesk\HB_Data\configXML\
Open the file in Notepad. These are the four lines that need to be configured to allow connection to SQL server using SQL Authentication.
<appodbc label="Connection">(local)</appodbc> <appdb label="Database">LaytonServiceDesk</appdb> <appdbuid label="User ID (Blank = NT Trusted)">SQLACCOUNTNAME</appdbuid> <appdbpwd label="Password">PASSWORD</appdbpwd>
appodbc
The server name of the SQL Server you are connecting to. This can be a server name or an IP address. If an instance is used, it should be specified, e.g. SERVER\SQLEXPRESS. The value of (local) is a default value that tells LaytonServiceDesk to connect to the same server as it is installed on.
appdb
The database within the SQL Server that LaytonServiceDesk is to connect to. The default name as installed is LaytonServiceDesk, unless you have installed the LaytonServiceDesk database using a different name.
appdbuid
The SQL account name to be used. By default, LaytonUser1 is configured on database creation. You can use this, or another account.
appdbpwd
The SQL account's password. This is entered in plain text.
SQL User Configuration
The NT Authentication method is the method that is used by default by the LaytonServiceDesk SQL installation routine. For the NT authentication to work the IIS username must be added to the SQL server logins section or the user will get an error message. The IIS username is usually IUSR_machinename where machinename is the name of the IIS server.
Figure 59 above shows the IUSR_JHAL_SERVER1 username added to the Windows NT Security Logins
IMPORTANT !
When added to the logins, the IIS username must be given full owner access to the Layton ServiceDesk database.
Connecting Using SQL Server Authentication
The SQL Server Authentication can be set up by editing the config.xml file in the LaytonServiceDesk/HB_Data/configXML subfolder. The user can edit the file using any text editor such as notepad.
Layton ServiceDesk can be configured to connect using SQL Server Authentication by inserting a SQL server User Id between the “appdbuid” tags, and the password between the “appdbpwd” tags as in the sample below.
<appodbc label="Connection">(local)</appodbc> <appdb label="Database">LaytonServiceDesk</appdb> <appdbuid label="User ID (Blank = NT Trusted)">LaytonUser1</appdbuid> <appdbpwd label="Password">LaytonSDUser1</appdbpwd>
sa is the default system admin for SQL server and as such has full access to all databases on the SQL Server. It is recommended that you create a separate login other than sa with owner access to just the Layton ServiceDesk Database and specify this username between the appdbuid tags. By default, Layton ServiceDesk will create an account for you called LaytonUser1 with a password of LaytonSDUser1. This was done to simplify the installation; however, you may wish to change the password to increase security.
If you clear the contents of the appdbuid tag, the system will attempt to connect using NT Authentication (see above).
