Common Database Connection Issues - How to resolve them

:warning: This was written by @pauln I am just moving it here so it can help other people.

Just in case you are still finding issues I will add the process I used as it highlights all the hotspots where roadblocks can occur. As a “freshman” I had to burn the midnight oil with this as well!

Platforms
Windows 8.1 - fully updated, Workgroups
PC1 - Server ASUS I7 3ghz 16G Ram 27" Touch
PC2 - Laptop ASUS I7 2ghc 4G Ram 15.4 Touch

Check 1 - Services
After install SQL Exp 14 as per tutorials highlighted bring up SQL Server Configuration Manager (using Tile created or shortcut) and check Services are operational as depicted below:

Check 2 - TCPIP
Under the same Tool above - now this is the kicker! make sure the TCPIP service is running for the SambaPOS database. On default install for mine it was not. Until I got this running there was no life in the connection:

Check 3 - Windows Authentication Vs SQL
Well using “Windows Athentication” I feel can return different results from different installations like Domains, Workgroups, Logins & Accounts. In a word it sucked for me so SQL Athentication has been much more dependable and takes out the guess work when adding new systems. So using SQL Server Management Studio turn on mixed Authentication and you will need to right click on the node top where I have the red arrow to bring up the property editor:

Once you have changed the Login Authentication type you should get a choice when you log in to Studio, see below how Authentication is not greyed out:

Check 4 - Enable “sa” account
Again by defaut after fresh install of SQL Expr the "sa’ account is disabled and if you run with “SQL Authentication” you will need an SQL User Account operating. So bring up the properties as show below and enable the account:

Check 5 - Check Firewall opened
This will depend on if you are using windows firewall or an antivirus firewall but the concept will be the same for both. Below I am using the product that was shipped with the PC and by default there is no enabled port open!. Open up the port as required as depicted below. Make sure you check the properties in the SQL Server Configuration Manager TCPIP Property Editor to see what port number is being used:

Test 1 - DB Connection String SambaPOS
Ok once everthing above has been crossed off your list then its time to rock with SambaPOS and test your DB connection under Local Settings as shown below:

Notice I have used User Name & Password as I am using SQL Authentication. I have also changed the sa password to “sp” just to keep it simple for now.

Test 2 - UDL text file
@emre provided this test and I found it really cool :sunglasses: as it made testing easy! There are plenty of links regarding using a UDL file but in short create a TEXT file using say notepad anywhere and rename it to “somename”.UDL and double click to bring up the screen below:

Tips
I found I had to incorporate the Database Instance into the Server Name (1) above.
I also found if I could not drop down the “Select Database on Server” option (3) above then your connection will fail.

These are my thoughts and hopefully it will save other users some time :joy:
Regards Paul.

6 Likes

Have bookmarked to find quick for the first response to connection issue questions. :smile:

effective i had hell too one time i forgot to enable the named pipes… next sql browser wasnt running on server 2012.

Thanks man no one should have now.

1 Like

I’m not sure whether to revive the post but since this is a tutorial guide and I found a youtube guide to allow SQL intance( e.g. of SambaPOS) to work with default windows firewall (Check-5 of this tutorial) which might prove useful for others following this tutorial :slightly_smiling:

4 Likes

Do we have to install sql express on windows tablet?

im using windows tablet as terminal

on pc - windows 7. sql express 2012 & sambapos
on tablet - windows 8.1 & sambapos

i’ve install sambapos on tablet, and set the data connection string.
problem “cant connect to database with current setting”

@ordinary_person please don post the same question on multiple topics trying to get quicker answer.

NO, SQL Express is only required on the ‘Server’/machine hosting the centeral database.

Please show your connection sting on the server and on the tablet first of all.
99% of the time the problem will be connection string or firewall but lets check connection string first.

I’m sorry but i’ve tried everthing. Dont know where i miss.

This is data connection string from pc.
I turn off firewall on pc & tablet.

So i use the same data connection string on tablet. is that right?
pc & tablet are connected to the same wifi router

this is what happened on the tablet when i use the same data connection string

now i try with sql express 2014

protocol for sambapos - all enabled (shared memory, named pipes & tcp/ip)
and then restart

still cannot use my windows tablet as terminal

There is no database value in the connection string firstly.

Here is my string;

Data Source=JTR-PC\SAMBAPOS; User Id=sa; Password=sambapos; Database=RAH;

You can have multiple databases on a single SQL instance so you need to specify the database name…
Although you say its working on the server?

Go through this tutorial in this very discussion thoroughly and check every idea mentioned. There are a number of reasons it is not working. One of the most common is people forget to set Mixed Authentication mode and try to use SA user. Another is they do set it but forget to enable SA user. Instructions for both of those are listed in this tutorial.

2 Likes

I will try tomorrow. Thanks a lot

I will try tomorrow. Thanks a lot.
Yeah it’s working on server.

Already tried with database name. Still can’t.

But when i try at my restaurant it’s working

Are you trying to access it remotely?

Nope. When i try at my shop, i just change the data string connection using ip address.

Like this
Data source=192.168.1.45\sambapos4;…

So it works with IP address but not hostname?

And it’s working. But printer can’t print to kitchen

I didn’t try yet using hostname

So when isnt it working? this whole thing has been about connection string not working?

You can use IP address… I have in the past…
Suggests you have network issues and the router/dhcp is not doing name resolution like it should.

This is a separate issue. and the connection string should not effect the kitchen printer… well not that I can see.
Think we need to discuss your setup on a more general perspective.

So to clarify tablet works fine connecting to server with IP address rather than hostname?

When I’m using at home pc it didn’t working. Maybe i do have missed something… Now need to print to kitchen from tablet.

Just setup the printer into network printer right