Evopos Help - 2.09.078

Problems Connecting to the database

Problems Connecting to the database

Previous topic Next topic  

Problems Connecting to the database

Previous topic Next topic JavaScript is required for the print function  

See Connecting to the database first for the normal instructions

To diagnose the problem it is normally best to work in the following order

Run through the General Issues

Ensure you can connect on the same computer as where the SQL Server is running. If not see section on 'Issues connecting on the same computer as the SQL Server' below.

If you can connect on the SQL computer, but not on another local workstation, then run through 'Issues connecting on a different computer to the SQL Server' (Normally a Connection details / Network / Firewall issue)

If you cannot access across the Internet, see the 'Issues Connecting over the Internet' section  

General Connection Issues

Make sure the connection details are correct - Ensure the Server Name is set to the Computer Name or local IP Address of the computer running the SQL Database Server. Note: You can see the Computer name on the SQL server by right clicking My Computer and select Properties. If you have another computer already connecting to the data, open Evopos, select Settings and note the details to the right of the 'Change Database' button. If it says: \\Server1\Sometimes the settings can revert to the New Defaults for the Instance name (Evopos) and Database name (EvoposMain), if your data base uses the earlier defaults eg: Instance name (bbs) and Database name (BBSMain) you will need to manually change these field. Note: The SQL User Name is: sa and the passcode is: bbs1955 by default.

Make sure certain VPN / Network Services are not running - Some network services such as VPN (eg: Yamaha VPN) will disconnect the SQL network connection. You can normally get around this by disconnecting the other network service temporally, then connecting Evopos, and then the other service can normally be connected again

If you cannot get the Connection screen - Normally the Connection screen will show if Evopos cannot connect to the data or if you select Settings / System / Change Database. An alternative way to get Evopos to prompt for the connection details when starting is to delete the registry entry manually (Please take care and backup registry first). The registry key to delete is normally: HKEY_Local_Machine\Software\Evopos

 

Issues connecting on the same computer as the SQL Server

Make sure the SQL Server and Browser services are running on the server computer - Try stopping and re-starting the SQL Server service by selecting Control Panel > Administration Tools > Services, select the SQL Server service for the instance, select 'Stop' and then select 'Start' (as even if the service says it is running, it may not be). Note: Sometimes the SQL Server service does not start up correctly after a Windows update. If it will not start try disabling the TCP/IP protocol and re-try (at least to get a backup). You may have to re-run the SQL installation to repair, or sometimes you will need to remove and re-install the instance.  If you are not specifying specific ports in the SQL Configuration and the connection details (eg: with the server IP), then you will also need to make sure the SQL Browser service is also running. Note if this is not enabled you can only enable it in Windows Services and not SQL Configuration.  When specifying ports you normally only need to set IPALL.

Ensure the SQL user has not be locked out - If open to the internet, it is common to get SQL Login attacks. This is where an automated program tries to login to your SQL Server over the internet, after so many attempts SQL will automatically lock that user (Normally: sa) out for a period of time. You can reset this by stopping and re-starting the SQL Server (select Control Panel. You can also login on SQL Management Studio (see link below) using Windows Authourisation and view / change login details (A good security measure is to use a different user name and then disable the default 'sa' user. Also set a non-standard Port (not 1433) for SQL Server)

Ensure the database is not marked as suspect - See Problems with SQL Server / Fixing a Suspect Database

 

Issues connecting on a different computer to the SQL Server

Ensure the SQL Server has it's Firewall configured to allow SQL Server and Browser as Exceptions - On the computer running SQL Server you have to configure the firewall to allow access (exceptions) to the SQL Server and SQL Browser. See Connecting to the database If using a different firewall please see their instructions. We especially have had issues with Synamtec (Norton).  

Ensure the Local IP Addresses are in the same range - On the Server go to the CMD prompt (select Run and enter CMD, or press Windows key + R), then in the black Command Window and enter IPCONFIG and make a note of the current Local IP(V4) address and the Gateway IP Address (It is normally something like 10.0.0.? or 192.168.0.?). Then repeat on the computer that will not connect, the first 3 number sets should be the same for the range to match. If it does not, first try a full re-boot (all computers off, then Modem/Router off for 30 seconds, then Modem/Router on, then Sql Server on, then other computers on). If that does not correct the IP address you can set manually by selecting: Network / Properties / Network Adapter / Properties / Internet Protocol Version 4 (TCP/IPv4). Then set 'Use the following IP Address' and set the Local IP address as above, the Subnet mask as 255.255.255.0, set the Gateway and Preferred DNS as the default gateway IP.

Some configurations can only use a local IP address to connect – Some Network configurations will only work if the local IP address of the server is used rather than the computer name. If this is the case you will need to set the local address to be static by selecting the local network card and setting the local IP address in properties. You can see the local IP Address by running CMD and enter the command: IPCONFIG. Note if the Local Area Connection is set to Obtain IP Address Automatically then the Local IP may change the next time the computer is started (Control Panel / Network / Right click Local Area Connection and select Properties, highlight Internet Protocol, select Properties. If not using SQL Browser and not using the default instance you will have to specify the port after the IP address eg: 192.168.0.2, 1433

Use SQL Browser or Specify Ports - Because we normally use a non default instance (bbs or evopos), we need SQL Browser to be running and accessible (normally on port 1434) so that SQL Browser can browse through all instances and select the appropriate one. Alternatively we need the port (usually 1433) to be specified for the Instance in SQL Configuration and the port specified in the server name eg: 192.168.0.2, 1433

Allow Outbound connections - Some firewalls / security software restrict outbound connections so in some cases we may need to configure the computer to allow "Outbound" connections on the connecting computer as well as the server. See Connecting to the database

Ensure SQL Server is configured to allow remote connections - On the server:

Select Start / All Program / Microsoft SQL Server 2??? / Configuration tools /  SQL Server Surface Area Configuration.

Select Surface Area Configuration for Services and Connections

Select the instance, select Remote Connections and ensure Local and Remote Connections is enabled using TCP/IP and Named pipes.

Try connecting to the database using SQL Management Studio - You can download this free utility from Microsoft at: http://www.microsoft.com/en-gb/download/details.aspx?id=8961. If running on the SQL Server computer you can normally logon using Windows authourisation which does not require the user name or passcode

 

Issues Connecting over the Internet

With Evopos you can connect directly to the data over the Internet. This has advantages over remote control software as it does not take over a computer. It is also good for multiple stores or accessing from home while still allowing users to use it.

Ensure you have a static Global IP address for the internet connection where the Data Server is located. If you do not have a static address the IP can change intermittently. Contact you ISP if you do not yet have a static address. You can check your Global IP Address by going to a site such as www.myipaddress.com There is also Dynamic DNS services which can give you a static name for a Dynamic IP

Also the Local IP address on the computer that is running SQL Server needs to be set statically (so it is always the same). You can ensure it is static by selecting by selecting Local Area Connection, select Properties, select Internet Protocol and select properties. You can see what the local IP Address of both the server and the Router / Modem (Gateway) by selecting: Start, Run, enter CMD (to get to the command prompt) and entering: IPCONFIG

Setup your Internet Router to forward port 1433 (Default port for SQL server) to the Local IP address of the computer running the applicable SQL Database Server. See the instructions for that model of Router for more details (Normally under NAT / Port Forwarding).

You will also need to configure SQL Server so that the instance (normally BBS or Evopos) uses port 1433 as a static port. To configure Connecting to the database

Note: When we set IPALL we normally have to leave the instance blank on the connection detail when connecting over the internet.

If you are having problems try connecting to the Database using a utility such as Microsoft SQL Management Studio. This helps show if the problem is with Evopos or the security settings.

 

What Evopos does in relation to connecting to the database

When Evopos starts it checks the registry details for the connection details. If it cannot find any Registry details or the SQl user name is blank it will bring up the Connect to Database screen

If it finds the Registry details it will attempt to find the instance specified. If it cannot find it then it will bring up the Connect to Database screen

If it finds the Instance but cannon connect to the database then it will bring up the Connect to Database screen

If you select the 'Connect' option it will try and connect to the database using the specified settings

If you select 'New Database' then Evopos will look for the Instance specified. If found it will create the database. If not found it will look for the SQL Server setup file. If it does not find it it will download it, create an INI file with the Instance, Database and User details. It will then run SQL setup to create the Instance and the database