1. Home
  2. Point-Of-Sale Software
  3. Remote DB Access for Store Operations

Remote DB Access for Store Operations

I have poured over previous questions on this topic but have not found the specific setup instructions. I already understand this is an unsupported configuration and that it will run slowly through a VPN due to the volume of database communication activity. That being said...here is my question:

I need to add thousands of descriptions and images that will sync to our new web storefront from our RMS terminal. This is clearly more efficient to do on another computer other than the POS terminal. We have a secure VPN connection in place already to the POS terminal.

When we setup an ODBC setting on the remote end we are only able to see the default databases to connect to. It seems the security settings on our live database are set so that they are not visible (because we can see the other databases in the same folder i.e. demo...). What are the steps to adjust the settings so that we can see them for Store Operations at the remote end of the VPN?

Thanks, dg

Reply to
dg
Loading thread data ...

Thanks Glenn. All is working through the final step for setup (vpn/ping...). The step that continues to not function is unders SO Admin. I go to File/Configuration. Under Database tab/server name: internal IP, username: sa/password is the sql pw, database is what it is called on the pos terminal. I hit test database and get the error: #2147467259 Connot open database requested in login '###' (database name). Login Fails. sql state: 42000, NativeError: 4060.

Thoughts? dg

"Glenn Adams [MVP - Retail Mgmt]" wrote:

Reply to
dg

Windows firewall is actually not being used (turned off). We are using a hardware firewall for the VPN so do we still need to open port 1433? We can connect to the following databases through the VPN on the RMS terminal: demo, master, model, msdb, Tempdb. I even did a restore of a backup as a new database and it didn't show up. Does it matter where on the xp RMS machine the databases reside?

dg

"Glenn Adams [MVP - Retail Mgmt]" wrote:

Reply to
dg

What program are you trying to use to connect on the remote system? Are you just trying to open up SO Manager remotely? If so, you don't need an ODBC Connection.

1) Use a Static IP Address on the system hosting the database 2) Make sure that any software firewalls (Windows SP2, Symanftec Internet Security, etc...) allow connections to the system - if you can open a specific port, the default SQL Server install uses 1433. If everything else looks good and you still can't open a connection , try turning off the firewall temporarily to exclude it as the problem.

Once your VPN connection is established, try to ping the database system using the static IP Address you assigned it (this should be a non-routable address such as 10.x.x.x or 192.168.x.x)

If you get a successful ping, you should be able to connect SO Manager on the remote system by using the Static IP Address you assigned in place of the server name in SO Administrator...

Once you have an active VPN Connection, the set up is exactly the same as you would use to connect a second system on the local network, except that you will need to use the IP Address instead of a "Friendly" server name. I believe there is a Knowledge Base article that discusses configuring the Windows XP SP2 Firewall for RMS - you may want to take a look at that...

Reply to
Glenn Adams [MVP - Retail Mgmt]

I would suspect that you have a firewall issue issue on the database 'server', but I'm just guessing at this point. Have you tried connecting a second system locally? If the VPN is correctly configured there should really be no difference...

Reply to
Glenn Adams [MVP - Retail Mgmt]

No, it doesn't matter where the files reside - SQL Server (including MSDE) works as a service, not through file based access. Are you sure you don't have multiple INSTANCES of MSDE/SQL Server Running? As an example, MS POS (Not the POS component MS RMS) will install a named instance of MSDE.

I'm stumped - if you can see ANY databases, you should be able to see ALL of them. You noted earlier that you were using the "sa" account to log in ,which should prevent any rights problems. The only other thing I can think of is the databases may be in single user mode, but I don't know howu to check that without Enterprise Manager off the top of my head.

You're sure that the "Demo" database you are seeing is really the same one that's on the Register machine? All the others you listed are standard databases that exist in every instance of SQL Server....

Reply to
Glenn Adams [MVP - Retail Mgmt]

I did some testing with another computer on the RMS side of the vpn....couldn't see the databases from there either (not even the default database). It did see the sql server running on the RMS terminal though....it seems the default databases we saw through the VPN were actually on the local machine.

One thing I did see is that the database owner is not sa...it has the computer name\username as the owner....not sure how to change that or if I need to.

It is running on an XP machine and I am trying win 2k machines to connect to it...could that be the issue?

Any help is appreciated... Dg

"Glenn Adams [MVP - Retail Mgmt]" wrote:

Reply to
dg

did you open the hardware firewall port??

Reply to
root

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.