Establishing a connection between Acumatica and on Premise legacy ERP SQL Server

This may be an obvious question but is your Acumatica instance on-prem as well?

Sorry, unfortunately it is not. If possible, we don’t want to use anything like CData.

It's not going to be possible to connect a Saas instance to an on-prem SQL server. They would need to be on the same network.

Isn’t that what the Data Provider, MS SQL Provider is for or is there another way to establish a connection via OData? 

It’s for on-prem installs or private cloud where there would be another database or SQL instance running on the same network.

How you would go about this, I’m not exactly sure. Maybe @KurtBauer would have some insight.

As Daryl mentioned this is typically used in a scenario where the servers are on the same network and can talk to each other.  In the image above I see a server name listed and a SAAS instance wouldn’t know how to resolve it.  In theory you could put in a DNS name or public IP and the port.  If your firewall was setup to allow traffic through and forward it to the internal SQL server this might work.

As a disclaimer, I would not recommend doing this as you are exposing your internal server to a huge security risk.  There are ways to mitigate the risk by doing some IP filtering on the firewall, but I still wouldn’t recommend it.

Kurt

I meant maybe you would know what the recommended way to accomplish accessing SSRS reports in this situation would be 🙂

I’m not aware of a great way to do this, but if the standard export to Excel and create an import scenario isn’t desirable here is a thought.

1. Setup a local instance of Acumatica on the internal server and restore a full snapshot of the live system
2. Create the SQL data provider and import scenarios to migrate the data from Macola into the local Acumatica instance
3. Create a full snapshot of the local system and restore it in the SAAS instance.

Definitely not idea, but a possibility.

Kurt

wmatthews1877 wrote:

We are attempting to use Acumatica Data Provider to establish a connection to our on Premise SQL Server (MSSQLSERVER) 2019. The Server is SPCSQLSRVR19 (default instance is MSSQLSERVER)

database is a test database from our production database, logon is sa with appropriate password and the authentication is SQL  The Name is IMPORT SSRS Views, Provider Type is MS SQL Provider, Active is checked. I’ve attempted to sign on as myself (I do have domain and database admin privileges), tried administrator. I’ve set everything up myself and the service is running (SPCSQLSRVR19\MSSQLSERVER) is the instance. When I click the “FILL SCHEMA OBJECTS” it times out with an error message. Ports 1443, which is standard are open, and remote connections are allowed.

Anybody else out there been successful in setting this up or know of some tools that may be helpful? We have a ton of standard SSRS reports that need to be brought over from Macola. 

 

@wmatthews1877 - I agree and disagree with others that have had experienced here…  While connected to a SaaS setup before going PCS, we connected to a time program on an On Prem SQL server using this connection.  You just need to make sure that your connection string is exposed to the outside world and use a name that DNS can resolve to from your Acumatica server. 

You may need to ask Acumatica Support to open up your firewall to allow access - May even be better to simply whitelist an IP address for security reasons.  

Your connection string, while it might work for a local instance, will not for traversing the internet as it doesn’t know how to resolve the host name.  You’ll need a web address or an IP address to your SQL Server.  If you get this far, you should have no problem connecting.  

This is a screenshot from one of our production environments with the exact setup from when we were on SaaS and now PCS and have no issues updating a time program database with workorder job codes stored in Acumatica.  
 

As Kurt said, this is an extreme security risk. I’m no expert, but if you have any kind of cyber insurance, you would almost certainly jeopardize it if they were to find out you exposed a SQL server to the web.

I want to thank everyone for their answer. Our ARP people may also have some additional input on this, and the security aspect of it would certainly be a critical part of whether we would want the risk or not.

Check out SkyVia.com

https://docs.skyvia.com/connect/sql-endpoints/

You can install an on-premise Agent, that will expose your sql server instance to the SkyVia cloud.

Then you can create a SkyVia Connect Endpoint via Odata endpoint or Sql Endpoint… This will allow you to interact with the sqlserver instance via the cloud.

Also - SkyVia has a native Acumatica connector as well - and you can build integrations between sqlserver and acumatica using their Data Flows functionality.

-Chad