@tanner this has been killing me, but I think I’ve got it. I haven’t tested the process yet but it complies and validates.
I created a c:\nuget folder and added it to path; downloaded nuget.exe into that location; “installed” Microsoft.Data.SQLClient and copied the dll from here:
Then went through my BPMs and found that all the methods I had been using from System.Data.SQLClient were indeed in its replacement and same was not the object of a type forwarder.
Compiles and validates fine; I’ll let you know if it works once I’ve gotten to the processing stage.
Make a note to update those DLLs as often as the Nuget package is patched for security issues. It’s popular for attackers to “live off the land” by finding out-of-date/unpatched libraries.
Yeah. not going to argue, especially as Microsoft.Data.SQLClient appears to be open source.
However, at the moment creating an API for MS SQL Server has proved beyond me, there are just too many things I don’t know. This library is inside an environment, inside a server, behind a 3rd-party physical firewall device - I know it’s asking for trouble but I think I’ve done what I can to only ask for a little trouble. You’d have to have an admin domain user and know the one port that’s open - and if you know that much, you’re already an inside jobber and probably can get the API keys or just access the database directly. Also thanks to Jose’s input earlier we’re no longer storing the DB creds anywhere.
If anyone knows a RESTSharp equivalent for SQL Server, I’d use that.
I just did a quick one yesterday. With .NET 6 and Core, it really is simple. I’m still learning and “playing” with the generalities of the API interface, but it’s perfect to allow Epicor to communicate with our other systems in a standardized way.
The next step is to make sure we secure these APIs through some type of authentication (token over basic preferred) and authorization once identified.
Security Level Up:
Add an endpoint to your API like “admin” that is never called by any of your in-house programs, but when it is called, it gathers as much information about the caller so you can see who’s snooping around your network.
Also wrap the whole thing in a IP rate limiter that triggers based on 401 responses so that if someone tries to brute force they get put in time out. Exponential one too