Solved

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

  • 21 February 2024
  • 13 replies
  • 162 views
W
Rank
Userlevel 4
Badge

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. 

 

icon

Best answer by jamesh 23 February 2024, 22:09

View original

13 replies

darylbowman
Rank
Badge +11

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

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
W
Rank
Userlevel 4
Badge

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

darylbowman
Rank
Badge +11

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.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
W
Rank
Userlevel 4
Badge

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

 

darylbowman
Rank
Badge +11

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.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
KurtBauer
Rank
Userlevel 4
Badge

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

darylbowman
Rank
Badge +11

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

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
KurtBauer
Rank
Userlevel 4
Badge

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

jamesh
Rank
Userlevel 6
Badge +6

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.  
 

 

James Harris - ERP Guy & Acumatica MVP - If I answered your question, please mark my response as "Best Answer" :)
darylbowman
Rank
Badge +11

You just need to make sure that your connection string is exposed to the outside world

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.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
jamesh
Rank
Userlevel 6
Badge +6

You just need to make sure that your connection string is exposed to the outside world

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.

Which is why I suggested whitelisting an IP address rather then opening it up to the entire world.  

James Harris - ERP Guy & Acumatica MVP - If I answered your question, please mark my response as "Best Answer" :)
W
Rank
Userlevel 4
Badge

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.

C
Rank
Userlevel 3
Badge

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

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved