Database issues Multi-Terminal Issues

Ok I got everything properly installed and can connect to database though SQL Server 2014 Management Studio. Though I can not get any terminal to connect to the database. This is my Data string as it is, DATASOURCE=localhost\SAMBAPOS; User Id=Lewis
though have used
DATASOURCE=localhost\SAMBAPOS;
DATASOURCE=localhost\SAMBAPOS; DATABASE=SAMABPOS;
and a few others.
Though nothing is connecting and cant seem to get any terminal to connect to the database.
I have also tried with MySQL login and no luck. Though I turned it back to windows login only.

What is DATASOURCE? There is no such keyword.
Instead of hand typing it click […] button and fill details.

Read more about it here

Problem is it locks me out out of the pos now and pops up the error message saying it can not connect to database.

You can change the string from inside that error box. Look closely at that tutorial. DATASOURCE= should be Data Source=

If yours is not working then you did not follow tutorial correctly.

PS: MySQL is a different software package. I know you probably didn’t mean it but to prevent confusion in future you should know that we do not use MySQL here, refer to it as MS SQL or just SQL Express

PS2: is SQL server on a different machine than your terminals?

I followed your guide for SQL Express 2014, the server is up and running on office machine and I can connect using windows login as you said is best in guide. Though when I go to link sambapos to it I get connection errors with those above command strings. Though have tried to connect from other terminals all on same network and no luck.

Just a commant based on what you said, localhost would only work on the machine with the SQL on it, the other terminals would need to have the server/pc name in place of localhost

1 Like

@JTRTech is correct. The Terminals must specify the Server Name or the IP address of the Server in the connection string as part of the Data Source. If you use Static IP on Server, or have a Reservation for a consistent IP for the Server in the DHCP service, you can use the Server IP, otherwise use the Server PC Name.

localhost means “this computer”, so on a Terminal (where you would not have SQL installed and running), it won’t be able to connect to a DB because there is no DB installed there (on the Terminal).

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.

4 Likes

Excellent post @pauln. Great insights and really good information for anyone having trouble with this. Nice to see!

1 Like

Seriously @QMcKay if I can get anywhere near your level competency so I contribute like the amazing stuff you and others do here I would be satisfied…

2 Likes

I am going to copy this into a tutorial if you dont mind. Very good thread.

1 Like

Honoured, thanks @kendash,
respectfully Paul.

Windows Firewall Script to Allow MSSQL to run without putting your firewall off.

Copy to Notepad and save as a .bat file. Run as Administrator

@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 “SQLServer”
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 “SQL Admin Connection”
@echo Enabling conventional SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 “SQL Service Broker”
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 “SQL Debugger/RPC”
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 “Analysis Services”
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 “SQL Browser”
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 “HTTP”
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 “SSL”
@echo Enabling port for SQL Server Browser Service’s ‘Browse’ Button
netsh firewall set portopening UDP 1434 “SQL Browser”
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

3 Likes

Would this be a one time thing?
Only ask as only time ive used .bat personally is for a few small scheduled automations

Yes it’s a one time thing. I can’t think of a reason for it not to be.

PS: you can allow the service without having to mess with ports this is the preferred way especially if you run dynamic ports.

2 Likes

Good everyone, I have followed this same procedure…still no results connecting to the client, though the server shows a green “connected” at the bottom. I used windows authentication not sql server authentication.

Do i have to share my Samba datafiles in “My Documents” over the network…?

The green connected means that the Message Server is connected. You can only run this on the server.
Check of you have not enabled this on the client.

Message Server has nothing to do with the SQL Server connection or your network.

1 Like

No @Raphael9630 you do not need to share any directories.

I would switch to SQL authentication as it will use secure routing through TCPIP and user accounts within SQL Express. So I guess in parts you have not followed as I have highlighted above as I really struggled with Windows Authentication…

1 Like

Thanks so much @Peter_Cijsouw and @pauln, Now the client can see the server, I was able to successfully switch to SQL authentication mode and it just did the magic!. thanks