The real question is, does Epicor apply special security to the production database for cloud customers which prevents access to an EntityFrameworkCore read only query?
We recently moved from Cloud to an On Prem model. Not because we didn’t like the cloud model, but more due to the fact we are a medical device company and have some special requirements we simply could not get with cloud.
The transition was almost a non event, except for the issue I’m about to describe.
We created an eDHR, which is an electronic device history record to help automate and ensure we were capturing critical information needed for the DHR. As a medical device mfg, we must maintain certain information for the life of the patient as long as our device is in said human being. We pull information from 4 different systems to create the record. We also had the replicated read only database option for a cloud customer.
The easiest way to pull the data was to use C# entity framework core to read directly from the read only database. We’re small enough, I didn’t think this it was necessary to have a replicated database in our new on prem environment, especially as this application pulls off data in off hours. Even if it didn’t, it only pulls incremental information. Thus, I tried pointing to the ‘restored’ database with no joy. Generic Access Violation with no real error information being returned. I do this through a SQL user only used for custom code and they only have read only access to the database.
Frustrated, I created a brand new database on the same server. Everything worked as expected. Thought it might have been a certificate issue, but I’m not familiar with a database specific certificate. Just for testing purposes, I even used TrustServerCertificate in the connection string. Absolutely nothing is moving the needle.
Totally guessing, when Epicor creates a cloud database, they are setting a security setting in the db that prevents outside reads. Since I never tried to access this db, in fact, I don’t think I could even get to it. I’m wondering if this security setting came over in the cloud db and I just need to know what it is and how to reverse.
The Db is on prem now, can you connect with it using SQL Studio and do what you need or is that also restricted? I couldn’t decipher from your text above if that worked or not.
I suspect that SQL only user you are using doesn’t have enough permissions. Can you try running your C# code using SA and see if you get the same results?
Anything in the SQL Sever logs I don’t think Epicor did anything like that… it seems it might be a permissions issue on the SQL DB
We’ve disabled our sa account. The read only user can read from the new database. When I granted permissions for the new database, I just added that database to his ‘User Mapping’, included public and db_datareader. I just gave the user everything except db_securityadmin for our Epicor Dev database with same result - access violation.
So joy from the database we created and no joy when trying to read from any Epicor database.
Just for sanity purposes, in SSMS, I disconnected everything, connected with read only user - joy against any database.
It’s just the Epicor databases when trying to reach from entityframeworkcore using standard datacontext. I even hard coded the connection string to make sure there is nothing in between.
Just thought of this. I can reverse engineer the tables using visual studio and the scaffolding features… but read from the reverse engineer results - no og.