Layton ServiceDesk - FAQ - Manual SQL Connection

From Layton Support
(Difference between revisions)
Jump to: navigation, search
m (Jpainter moved page Layton ServiceDesk™ - SQL Connection to Layton ServiceDesk - FAQ - Manual SQL Connection: Removed the TM symbol for a cleaner URL)
(Major revision)
Line 1: Line 1:
−
<p align="right">[[File:btn_back_up.png|link=User Guide for Layton ServiceDesk™]] [[User Guide for Layton ServiceDesk™|<u>Back to Contents</u>]]</p>
+
<p align="right">[[File:btn_back_up.png|link=User Guide for Layton ServiceDesk]] [[User Guide for Layton ServiceDesk|<u>Back to Contents</u>]]</p>
 
=Overview=
 
=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.
+
Although the '''[[Layton ServiceDesk - Installation - Connect to Existing Database|DBTool application]]''' is the preferred method of configuring a SQL connection, it can be configured manually. This is useful in the event where a change in the '''[[Layton ServiceDesk - Settings - System Settings - Application Database Connection|Application Database Connection]]''' settings has caused Layton ServiceDesk to lose its connection.
  
−
=Database Backup Method=
+
There are two options: NT or SQL authentication.
  
−
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’).
+
=Location of SQL Configuration File=
 +
The SQL configuration file is
  
−
To make a back up of the HelpBox SQL database you can use a SQL DB manager such as “E'''nterprise 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.
+
    config.xml
  
−
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.
+
It is located in
  
−
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.
+
    C:\Program Files (x86)\Layton Technology\Layton ServiceDesk\HB_Data\ConfigXML\
  
−
'''Note''' - It is recommended to make regular backups of both the '''Layton Layton ServiceDesk''' program folder and '''Layton ServiceDesk''' database.
+
The file can be edited with any text editor such as Notepad.  
  
−
=Manual SQL Connection=
+
=Connecting Using NT Authentication (Trusted Connection)=
−
==Overview==
+
The sample below shows the four lines that need to be configured to allow connection to SQL server using NT Authentication.
−
The SQL connection can be configured manually. This is useful in the event where a change in the '''[[Layton ServiceDesk™ - Settings - System Settings - Application Database Connection|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>
 
     <appodbc label="Connection">'''(local)'''</appodbc>
Line 29: Line 24:
 
     <appdbpwd label="Password"></appdbpwd>
 
     <appdbpwd label="Password"></appdbpwd>
  
−
===appodbc===
+
==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.
 
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===
+
==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.
 
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===
+
==appdbuid and appdbpwd==
−
Should be blank as shown, or LaytonServiceDesk will try to connect using SQL Server Authentication.
+
Should be null as shown, or LaytonServiceDesk will try to connect using SQL Server Authentication.
  
−
==Connecting Using SQL Authentication==
+
=Connecting Using SQL Authentication=
−
SQL authentication can be configured manually by editing the ''config.xml'' file. This is located here:
+
These are the four lines that need to be configured to allow connection to SQL server using SQL Authentication. Note that the SQL user '''[[Layton ServiceDesk - FAQ - LaytonUser1 Configuration|LaytonUser1]]''' was created on installation and can be used.
−
 
+
−
    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>
 
     <appodbc label="Connection">'''(local)'''</appodbc>
Line 50: Line 41:
 
     <appdbpwd label="Password">'''PASSWORD'''</appdbpwd>
 
     <appdbpwd label="Password">'''PASSWORD'''</appdbpwd>
  
−
===appodbc===
+
==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.
 
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===
+
==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.
 
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===
+
==appdbuid==
 
The SQL account name to be used. By default, '''[[LaytonUser1 Configuration|LaytonUser1]]''' is configured on database creation. You can use this, or another account.
 
The SQL account name to be used. By default, '''[[LaytonUser1 Configuration|LaytonUser1]]''' is configured on database creation. You can use this, or another account.
  
−
===appdbpwd===
+
==appdbpwd==
 
The SQL account's password. This is entered in plain text.
 
The SQL account's password. This is entered in plain text.
−
 
+
<p align="right">[[File:btn_back_up.png|link=User Guide for Layton ServiceDesk]] [[User Guide for Layton ServiceDesk|<u>Back to Contents</u>]]</p>
−
=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.
+
−
 
+
−
 
+
−
<center>[[File:Fig59.png]]
+
−
<br/>
+
−
'''Figure 59 - MS Enterprise Manager'''</center>
+
−
<br/>
+
−
 
+
−
 
+
−
Figure 59 above shows the IUSR_JHAL_SERVER1 username added to the Windows NT Security Logins
+
−
 
+
−
 
+
−
<u>'''IMPORTANT !'''</u>
+
−
 
+
−
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).
+
−
<p align="right">[[File:btn_back_up.png|link=User Guide for Layton ServiceDesk™]] [[User Guide for Layton ServiceDesk™|<u>Back to Contents</u>]]</p>
+

Revision as of 04:52, 9 September 2016

Btn back up.png Back to Contents

Contents

Overview

Although the DBTool application is the preferred method of configuring a SQL connection, it 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.

Location of SQL Configuration File

The SQL configuration file is 

   config.xml

It is located in 

   C:\Program Files (x86)\Layton Technology\Layton ServiceDesk\HB_Data\ConfigXML\

The file can be edited with any text editor such as Notepad.

Connecting Using NT Authentication (Trusted Connection)

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 null as shown, or LaytonServiceDesk will try to connect using SQL Server Authentication.

Connecting Using SQL Authentication

These are the four lines that need to be configured to allow connection to SQL server using SQL Authentication. Note that the SQL user LaytonUser1 was created on installation and can be used. 

   <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.

Btn back up.png Back to Contents

Retrieved from "http://laytontechnology.com/support/index.php?title=Layton_ServiceDesk_-_FAQ_-_Manual_SQL_Connection&oldid=6671"
Personal tools
Namespaces

Variants
Actions
Main Page
Online User Guides
General Support
Release Notes
Toolbox