Contact

Softhard Solutions

sof1sol1@tpg.com.au

Poll

Do you like our Website?

Yes (3,640)

No (167)

Undecided (176)

Total votes: 3983

Homepage > ShopMate > Networking

Business Accounting Database System Networking

Automotive Business Accounting Database Software System for Businesses

ShopMate Automotive Business Accounting Database Networking Ideas

AzureMate - Best in Windows Azure Cloud Storage Explorer Software Products

Welcome to Softhard Solutions ShopMate Database Networking Ideas page. ShopMate Database Networking can be implemented as a LAN (Local Area Network), two or more workstations connected to one local Sql Server, or WAN (Wide Area Network), two or more different premises LANS connected to an external Sql Server.

LAN involves connecting every workstation on-premises to a Sql Server. The selected Sql Server may live on an ordinary workstation (this is described below) or on a computer running a Server operating system. As companies accumulate more and more data in modern times, keeping expensive infrastructure on-premises is becoming a concern for them so LAN is becoming less popular.

WAN, on the other hand, is gaining more and more popularity since software companies, like Microsoft, provide their Cloud computing and data storage services for a fraction of the costs involved in an on-premises business operations. You can easily establish a Microsoft Windows Azure Cloud Services account and then create Azure Cloud Sql Server and Database service account.

You can establish your account on their website:
https://azure.microsoft.com.
Then create a Sql Server and Database service account in your Azure Portal. A good thing about storing data in the Microsoft Azure Cloud Service is that it is very economical financially and your stored data is safe and available anywhere in the world via Internet Network as long as you have ShopMate, a computer to run it on and a connection to network.

ShopMate supports all the connections to a Sql Server mentioned above. Even if you do not have ShopMate installed, read on, these Sql Server Database Networking Ideas may be useful to you if you deal with a Sql Server's Databases and connections.

For some operations described here you need Microsoft Sql Server Management Studio (SSMS) tool. You can get it free at Sql Server Express Downloads page. There you can select either 32 or 64 bit version.

Important Note
If you are using LAN and an ordinary workstation for your Sql Server, you should use the Windows Professional Edition or Higher Operating System because of proper networking available in these operating systems. Home systems have only rudimentary networking available.

LAN Networking with ShopMate

Due to Microsoft® ever increasing network security, to explain Windows® operating system networking fully is beyond the scope of this help document, especially if you have any legacy workstations and or one or more domain (.com etc.) controllers, and should not be attempted without a competent IT personnel.

If you have a simple local area network comprising of a few modern home group or work group of workstations and no domains you may try to do as simply explained in the following paragraphs, with care.

The ShopMate Database can reside on any instance of SQL Server 2012 or higher installed on any machine on your network. A ShopMate installation must exist on every workstation you wish to use to run ShopMate operations.

With every installation of ShopMate on your network, an instance of SQL Server 2012, called SHOPSQLEXPRESS is installed if it does not exist. On one workstation you may install up to 16 named instances of various SQL Servers.

The ShopMate Database can reside on any instance of SQL Server 2012 any edition, or higher, that is installed on any machine on your network. A ShopMate Installation must exist on every workstation you wish to use to run ShopMate program operations.

With every installation of ShopMate on your network, an instance of SQL Server 2012 is installed, if it does not exist, and with it all the necessary server and client network protocols which can be handled by the SQL Server's Configuration Manager (see pictures below) which provides basic configuration management for SQL Server network services, server protocols, client protocols and client aliases.

Note that when the sql server is first installed by ShopMate main installation and it is not an upgrade, the networking is enabled and the SA (system server administrator) password is set to 'shopmate123' (all small letters without the apostrophes) by default to enable both types of Authentication, Windows and Sql Server. Also, the installation creates a user login called shopmate on the sql server with the same password.

You can use both accounts, SA and ShopMate, in the Sql Server Authentications. If the installation upgraded your existing sql server then the networking may be disabled by previous settings, you must check and enable the TCP/IP Network, Shared Memory and Named Pipes protocols on both, the sql server and client protocols using the SQL Server's Configuration Manager described here.

You will find this manager on your Task Bar in (see the example below):
Start\Programs\Microsoft Sql Server 2012\Configuration Tools\SQL Server Configuration Manager
installed by sql server installation. Click this shortcut to make changes to Sql Server network protocols and permissions. Click on Sql Server Services on the left then click (highlight) the Sql Server (SQLEXPRESS) record on right, right click it and select Properties. In there you can setup your server permissions.

But before you do that, you may setup the server networking first. Click Protocols for SQLEXPRESS on the left and right click each protocol on the right selecting either Enabled or Disabled choice. Do not turn on the VIA protocol, it is a legacy and not used. Match your settings in the Client Protocols on left. Then stop and restart the Sql Server in its Properties as above for the changes to take effect. On any one workstation you may install up to 16 named instances of various SQL Servers but ShopMate needs only one.

Important Note
If you are unable to Backup or Restore database, your Sql Server may be running under low permissions like NT AUTHORITY\NETWORK or \SERVICE or similar. You must increase the permissions of your Sql Server, in the Windows Services, or in the Sql Server Configuration Manager as above, to at least Local System Account or higher.

You must, therefore, decide which of the installed SQL Servers 2012 or higher on your network you are going to use as your central database repository for your data, which workstation is going to be the Host for the ShopMate Sql Server Database and connect all the other workstations to it.

Important thing is, the selected central Sql Server must have administrative permissions to access all the external workstations connected to it in case of backing up to or restoring from them. You can set its higher permissions in the Sql Server Service (in the Windows Services) or in the Sql Server Configuration Manager above. If you can't backup or restore database even on the server's own workstation, you also have to increase its permissions!

If you still can't connect remotely after you've done all the above, the reason may be that in your networking scenario the Sql Server Browser Service may not be running or the system's Firewall is Blocking the selected Sql Server's Incoming or Outgoing Network Connections. You may try the following steps but be careful. Your DBA (database administrator) should know what to do.

Next Networking Steps With ShopMate

Make sure the SQL Server Browser service is running. It is used with SQL Server 2012 for client connections. This tip covers what this is and how to use this service to improve and protect your SQL Server Environment.

The SQL Server Browser service runs as a Windows Service and it is an extension of SQL Server. It plays an important role while working with more than one SQL Server instance installed on the same machine. The basic purpose of the SQL Server Browser service is to provide instance and port information to incoming connection requests.

To configure a better and safe access mechanism for your SQL Server, your DBA (database administrator) should have proper understanding of the SQL Server Browser Service.

What if SQL Server Browser Service is stopped?

The SQL Server Browser Service is installed with installation of SQL Server 2012. It also serves earlier versions installed on the same machine if there are any. The SQL Server Browser Service has different impact in different situations, such as:

If you have just one instance installed on machine and it is running on default port 1433, then status of SQL Server Browser Service does not make any difference in your connection parameters.
If there are more than one instances running on the same machine, in that case either you have to start SQL Server Browser Service or provide the Port Number along with IP (or Workstation Name) and Instance Name, to access any other instance than default.
If SQL Server Browser service is stopped and IP along with port number is not provided then connection will be refused.
If SQL Server instance is configured using dynamic ports then browser service is required to connect to correct port number.
Also your named instances will not be published in the list of SQL Server instances on the network (which could be a good thing)

Best practices for using SQL Server Browser Service

The way you use SQL Server Browser service affects the access to your SQL Server instances, hence the security of installed instances. At one end, the most security conscious approach may be to use customized static ports (see example below) for your instances and access SQL Server with fully qualified connection parameters. It would include IP\InstanceName,PortNumber. In this configuration your instance would not be exposed to the network also more parameters are required in the connection string.

It would be a secure configuration compared to having the SQL Server Browser service running and serving the incoming connection requests with instance and port information. If you have no problem in using fully customized connection strings (IP\InstanceName,PortNumber), then you can stop the browser service permanently and access the instance through a fully qualified connection string.

In another type of configuration, if the instance is configured to use dynamic ports each time, then SQL Server Browser service should be running, otherwise there would be no way to keep track of the dynamic ports that are assigned.

If you have just a default instance installed (with default port) on your machine then SQL Server Browser service may be stopped to avoid overhead and access the instance with just WorkstationName\InstanceName in ShopMate.

As part of best practices, always run SQL Server Browser service with a minimum privileged account. According to BOL any Windows user having the following rights could be capable to run the SQL Server Browser service.

Deny access to this computer from the network
Deny log on locally
Deny log on as a batch job
Deny log on through Terminal Services
Log on as a service
Read and write the SQL Server registry keys related to network communication (ports and pipes)

You can change the default account of the browser service (local system), to any other user having the above mentioned rights on the machine.

Limitations and further considerations

SQL Server Browser service operates at the Operating System level. You can not have the browser service started for one instance and stopped for others on the same machine. If SQL Server Browser service is started, it would be serving all incoming connection requests.
Same concept may be applied in case of clustering. You have to separately install and start the browser service on each node of the cluster.
If you run the sql browser service and if you are using any type of firewall then you must include UDP port 1434 in the exception list. The UDP port 1434 is a port used by the SQL Server Browser service.
If you are running SQL Server 2000 instance side by side along with SQL Server 2005/2012, then make sure to apply service pack 3 for the SQL Server 2000 instance. SP3 is required to work correctly with the SQL Server Browser service.
For developers, it would be better if you have SQL Server Browser service with the same settings on both development and production servers. It would prevent any problems due to differences of connection string information.

Testing the configurations

In this test, a named instance with the following customized static port (1444 instead of the normal 1433) as shown in the TCP/IP properties (right click the protocol and select Properties) of the instance in the SQL Server Configuration Manager mentioned above and shown below.

If you are not going to run the SQL Server Browser service you must assign a static port 1433 (or other) to the sql server service as seen below in IP1 and IPAll on TCP Port fields and clear the TCP Dynamic Ports fields (nothing in them not even a zero). After any changes you must restart the sql service for the changes to take effect (click Sql Server Services on the left and right click the sql service record on the right selecting Properties).
Note the test is done with sql server 2008 but should be same for 2012 and higher.

Customized Static Port for SQL Server Instance

If SQL Server Browser Service is not running then start it in the Windows Services.

When the Browser Service is running, we can access the instance without specifying the port number. We can successfully login SSMS with the following connection parameters IP\InstanceName or WorkstationName\InstanceName.

Log on to SQL Server Instance without Port number

Now stop the SQL Server Browser Service in the Windows Services. Also exit SSMS and reopen it. Try to login again with the same parameters as before. This time you will get an error message like the following.

Log on to SQL Server Fails without Port number

The reason is that the browser service is not running and the customized static port is also missing in the connection parameters. To login successfully we have to provide the customized port number in the following way. The server name field would need IP\InstanceName,PortNumber or WorkstationName\InstanceName,PortNumber.

Log on to SQL Server Instance with Port number

Hopefully this has given you enough information to understand how the browser service runs and when you do not need to have this service running. It is very important to note that after making any changes to the SQL Server Browser Service make sure that the connection strings in ShopMate Business Software are changed to meet the changed configurations, otherwise you may have some connection issues.

Next Possible Networking Steps With ShopMate

If your Sql Server connection described in the above steps solved your problem and is working, nothing else has to be done. If not, you may try the following steps. If your system runs any firewall you can exempt Sql Server and its services from being blocked by doing something similar to following:

Windows 7, 8, 10 or higher:

In the Control Panel\System and Security\Windows Firewall click on Allow a program or feature Through Firewall, click Change Settings button and then Allow another program... button. In there click the Browse button and navigate to the sql server or to the sql server browser runtime as above and select it in that dialog box. Click Network location types button and select Home/Work (Private) check box, see security note below on Public networks but usually you would leave that unchecked. Click Add button to add the exception to the system.

For TCP and UDP ports:
In the Control Panel\System and Security\Windows Firewall click on Advanced settings, click Inbound Rules command and either right click Inbound Rules and select New Rule or click the New Rule... command on the right pane. In that dialog box select Port radio button and click Next button. Select TCP or UDP and Specific local ports radio buttons.

In the Specific local ports field type in 1433 for TCP port or 1434 for UDP port and click Next button. Select Allow the connection radio button and click Next button and select Domain and Private network check boxes, see security note below on Public networks but usually you would leave Public check box unchecked.

Click Next button and give the exception a name like "Inbound Sql TCP Port 1433" or "Inbound Sql UDP Port 1434" and exception description like "Opens up Sql Server TCP Port 1433" or "Opens up Sql Server Browser UDP Port 1434". Click Finish button to install the new rule into the system.
In case the required ports are blocked on the outbound, create similar rules same way on the Outbound Rules.

Important Network Security Note
Unless you have a valid reason why all the Internet users should be allowed to connect to this SQL Server, and so increase the security risk, in the Add Program or Port applet make sure the sqlservr.exe or sqlbrowser.exe or a port is selected, then click the Change scope button and in that applet select the 'My network (subnet) only' option.

This will make sure that only the workstations on your local network can connect to this and not the Internet users. However, on a wide area network which employs the tunneling through the Public Internet Networks and Domains this has to be set for public use.

Also, if you use a Wide Area Network (two or more local networks connected together) in your network setup which employs the tunneling through the Public Internet Network, the cost when you run out of your provider's maximum uploads and downloads must be considered.

All the other workstations should be re-connected to this selected central SQL Server called 'WorkstationName\ServerName' and the chosen server must have permission access to all these external workstations. Do that in ShopMate in the System\Sql Server\Connect menu or System Module 'Maintenance' button.

Optionally you can disable (in Windows Services) all the other SQL Servers on all workstations that have ShopMate installation. A good practice is to keep some of them enabled as a fail-over, in case the workstation hosting your central sql server fails.