I'm working on a desktop app that was developed about 5 years ago using C# and mssql server 2000. This app uses OPENROWSET to load some xls files, and it worked just fine til the institution in where I work upgraded databases (to something like msserver 2019 or sort of), and OPENTOWSET stop working.
Despite knowing there's plentiful of procedures and recipes to fix this issue (which I cannot apply coz database runs on server), my company's DBA is telling me the only way to fix such problem is to give admin-privileges to the app-user, and he cannot do that because of safety rules - dropping the problem to me. Now I have to replace OPENROWSET with something else.
So the question is: is that true? There's no way for a skilled DBA to make OPENROWSET work on a remote DB with regular/non-admin user, fixing those errors about permissions and linked servers? Is OPENROWSET "mature-or-safe-enough" to make it run on nowadays production servers?
It looks like you are attempting to open excel files from within your database. In which case, the default setup would not allow this:
OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access.
When accessing remote OLE DB data sources, the login identity of trusted connections is not automatically delegated from the server on which the client is connected to the server that is being queried. Authentication delegation must be configured.
It sounds like this may possibly have been configured way back in the past for previous databases, but the DBA who now sees this regards it as a bit of a security problem. Whether or not he is right depends very much on how you use this, but it does sound like something that would be better avoided.
It may be better to load the excel files in your C# application and then send the BULK data to sql server to just save.