I am attempting to create the Read Only Production Database as a Linked Server to our on premise SQL server. This would then allow us to extract data into an on premise server. Has anyone got this work? If so using which provider? Is there a better way to achieve this?
Hi Jamie, welcome to EpiUsers!
Not sure if this is exactly what you’re looking for, but it sounds similar. At my last company, we had a separate SQL Server (our “Data Warehouse”) that we used as the data source for our PowerBI dashboards. The Data Warehouse was granted READ access to our production database, and had frequent SQL jobs that would insert / update rows into data warehouse tables based on querying the production database. As long as the queries are well-designed, it sounds like it’s similar to what you’re trying to achieve.
Yes, we have done this with SSMS. Its a pain and the data retrieval is painfully slow. What are you trying to do?
1 Like
Every table reference has to be prefixed with server.epicorsaas.com,port (instead of just erp.xxx or ice.xxx) and joins pull all data back locally before joining. Plus have to specifically whitelist any ip address to access it so I can only get in via rdp to the server. Add all that together I find it quite impractical to use it for anything adhoc, writing a baq ends up being faster 
1 Like
We have a number of reports and bespoke software that need to extract data from Epicor and also from other sources into a database. We are in the process of migrating to the cloud. It does sound like what you have done is what i need for now. How did you achieve it?
1 Like
When you request your ip address to be whitelisted, cloud ops will give you the specific server and port reference, and credentials that you need. From there its just like setting up any other linked server in ssms - I just followed some random tutorial that I had googled.
1 Like
Sounds like calling BAQs with the API might be easier in this scenario.
1 Like
Hey Jamie!
Should be able to use the following settings if you are on SaaS
Linked Server = Your Read Only Server Name (12345read.epicorsaas.com,portnumber)
Server Type = SQL Server
Security → “Be Made Using this Security Context”
Your Read Only user name and password
You should then see your databases in the linked server, and be able to query them.
Hope this helps!
2 Likes
I think it really depends on exactly what they are trying to achieve.
Thank-you that appears to have worked for me.
2 Likes
Glad it works! 