This document provides instructions for connecting Journyx version 8 and higher to an external Microsoft SQL Server 2005 (or higher) instance.
External SQL Server instructions
1. Make sure you have read the general instructions here.(https://journyx.freshdesk.com/support/solutions/articles/9000110224-journyx-8-x-and-newer-external-database-guide)
2. Install Microsoft SQL Server 2005 (or higher) if not already installed.
* SQL Server 2000 is not supported for Journyx 8. You must use SQL Server 2005 or SQL Server 2008 or higher. The "MSDE" package (based on SQL Server 2000) is no longer supported either. However you may use the free "Express" editions of Microsoft SQL Server 2005 or higher.
* If installing for the first time, you may wish to change the default collation (sorting order) to "case sensitive" (SQL_Latin1_General_Cp1_CS_AS). If you have already installed SQL Server, you may change the collation for individual databases when you create them. (See below.)
3. Create the Journyx Database
* Open the Microsoft SQL Server Enterprise Manager program.
* Connect to the database. If the database is installed on the local machine but doesn't show up in the list of databases, try the name "(local)" (including parenthesis.)
* Find the server in the left-hand pane ("Object Explorer") to display sub-tree for this DB server. Find the database instance you wish to use and expand its icon.
* Select "Databases" and right-click on it.
* Select "New Database".
* Choose a name for the Journyx database such as journyx.
* Important: If your users are in the Americas or Western Europe, select SQL_Latin1_General_Cp1_CS_AS for the "Collation name" option. You must choose the correct Collation name or the Journyx database setup script will fail with a message like: "Your current database does not seem to support case-sensitive sorting order." If your users require a different character set other than Latin-1, make sure to pick the case-sensitive version of your desired character set. The Accent-sensitive (AS) variety is optional but you may choose if it your users wish for accents to be distinguished in sort orderings.
* In the second tab (Data Files), make the initial size of the main database file at least 50 mb. The exact initial size is not important as long as the database is set to "autogrow." Initial size can be estimated by multiplying 1.2MB by the number of users who will be keeping Time that you will have over the course of the year and adding 10MB to that number. This does not take into account the size of expense or mileage records, so if your paradigm is to have 1:1 time to expense or mileage records, for example, you will want to double the size.
* If you wish, you may wish to change the options on the third tab ("Transaction Log"). Typically an administrator will place the transaction log file on a different disk and controller than the actual database, to improve performance. This is completely optional.
* Default values are preferred for the rest of the options on this screen.
* Click OK to create the database.
4. Create Database User Please Note: Journyx strongly recommends that you do not use the default "sa" account that is built in to SQL Server.
* Expand server icon to display sub-tree for this DB server.
* Expand the SECURITY folder
* Select "Logins"
* Right-click and select "NEW LOGIN".
* Fill in the desired login name. Usually you will want to use journyx, or the same name as the database space you created above.
* Under Authentication, be sure that GRANT ACCESS is selected
* Change the method to SQL Server Authentication and fill in the desired password. NT Authentication is also supported but read the note below. Please Note: Special characters, such as ~!@#$%^&*(){}_+-={}|[]\:";'?,./ are not supported within the SQL DB user account. If you use one of these characters in the password then you will see an error message that your database collation is incorrect, even if your database collation is correct. Please only use letters (uppercase or lowercase) and numbers in your password. Later, you will give this password to the Journyx database setup program.
* Authentication options: Both SQL Authentication and NT Authentication are supported.
However in order to use NT Authentication you must do these things:
1. You must create an DSN (Data Source Name) for the connection in the ODBC control panel. In other words, you cannot use the "DSN-less" style connection.
2. You must add the web server user (typically "IUSR" or "NT AUTHORITY\IUSR") to the list of authorized users for the database. The IUSR must be given the "db_owner" Database Role membership.
3. The "Install User" (your Administrator-enabled Windows login account) must also have the "db_owner" role. Typically this happens by default anyway if the user is an Administrator.
* Change the Default Database for the user to be journyx or whatever you named the database space that you created above.
* Click the Database Access tab
* Select the journyx database (or whatever you named it) by checking the box to the left of the name
* Grant the user db_owner right to this database
* Click OK to add this user. You will be prompted to confirm the password.
* At this point the database is setup and ready to be used by Journyx. You can exit the enterprise manager program.
5. Create the ODBC System DSN for Journyx. Open Control Panel and search for "ODBC" or find the "Setup Data Sources (ODBC)" option under Administrative Tools.
6. Click on "System DSN" and click "Add..."
7. Select the "SQL Server Native Client 10.0" option if available, or just "SQL Native Client" if you only see that, then click Finish. If you don't see either of those options, and the SQL Server is on a different host, you may need to download and install Microsoft's SQL Server Native Client directly from Microsoft. Most recent editions of Windows include the Native Client driver though.
8. Give the DSN a name such as "Journyx".
9. For Server put "(local)" (with parenthesis, without quotes) if it's the local server, otherwise put the SQL Server hostname, then click Next.
10. You can use the "Integrated Windows Authentication" option if so desired, but read the notes on that above. Otherwise select "SQL Server Authentication" and put in the name and password you created above. Keep "Connect to SQL Server for default settings" checked, then select Next.
11. Important: Check "Change the default database to:" and change the selection to the database you created earlier such as journyx. Leave the other options set to the defaults, then click Next. Leave all the options at the default on the following screen then click Finish.
12. Click "Test Data Source..." and if that works, click OK, then OK again at accept all the changes. You have now created a SQL Server system DSN.
13. Run the Journyx installation program. Be sure and read all the information in the "Read Me First" file that it shows you during installation. Allow it to reboot your computer at the end and the "Setup Web Server and Database" program will run.
14. Choose the webserver such as IIS and other options as prompted.
15. You will be asked if you want to use the internal PostgreSQL connection or if you want an external database connection. Choose "Connect to an external database system."
16. Select the SQL Server 2005+ option.
17. Select the DSN you created earlier. If using SQL Authentication, put in the account name and password here. If using NT Authentication, put your Windows NT login information here instead.
18. Fill in this info and click OK. If a connection cannot be established, for instance due to a wrong password or hostname, you will be given the opportunity to either try again with the same info (for instance, if the SQL Server service was temporarily offline) or else to create a new connection with different info.
19. At this point it will create all the tables and views for Journyx and will eventually send you to a login screen. It will pop up an alert message with the initial username and password.
20. If necessary, you can run a "restoredb" command from the command line to populate your Journyx data from a .jx backup file.