November 24, 2016 at 5:50 am
On our old server these style statements executed as sa from a website.
execute as user = 'NewUser'
select *
from OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\PathDocs\test.xls;IMEX=1;HRD=Yes', 'Select * FROM [Sheet1$]')
Msg 15274, Level 16, State 1, Line 23
Access to the remote server is denied because the current security context is not trusted.
On the new server this works correctly as sa. I have created a new SQL Server login, and a new user for the database the code above executes as.
It fails with Access to the remote server is denied because the current security context is not trusted.
User has Execute, read and write for all objects in db.
Adhoc has been enabled. Microsoft.ACE.OLEDB.12.0 is installed. DB has been set up as trusted.
Sever register entries of DisallowAdHocAccess have been entered against provider and Microsoft.ACE.OLEDB.12.0.
Sa confers many rights some of which the new user is obviously missing. I'm pretty new at SQL Server security, and the above code is definitely required.
I most definitly don't want to go back to the website application signing in with sa.
I would appreciate help with either the steps required to get the user to successfully open the spreadsheet, or a URL that explains in pretty basic terms what needs to be done.
November 24, 2016 at 10:02 am
Done the following:-
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
Changed inline process of [Microsoft.ACE.OLEDB.12.0] to yes
Added registry values for [Microsoft.ACE.OLEDB.12.0] to inlineprocess=1 and denyadhocprocess=0
In the end I related, and added sysadmin to the login. It now works :unsure:
Not a lot better than just giving them sa, really.
November 24, 2016 at 10:37 am
Did move on from untrusted error to this one:-
Msg 7415, Level 16, State 1, Line 19
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.
Which is what the allowadhocprocess settings were supposed to cure?
November 24, 2016 at 10:40 am
Asked a colleague to look through things, and he spotted I'd failed to add
Value name: DisallowAdHocAccess
Data type: REG_DWORD
Radix: Hex
Value data: 0
In the registry for Microsoft.ACE.OLEDB.12.0
The Inprocess=1 was already there.
Ahh - problem 2 foot from screen syndrome.
November 27, 2016 at 8:46 pm
This was removed by the editor as SPAM
November 28, 2016 at 6:16 am
JasonClark (11/27/2016)
When we are running SQL Server Agent job that is not owned by a system admin account, SQL Server Agent runs SETUSER as a owner of the job. And if the job uses linked servers, job will fail.So may change to owner ship of job to system admin or change the linked server link server mapping.
Just to add to this, I agree with Jason's post above. The database should be owned by the "SA" user (which should also be disabled) and the job should be owned by "dbo", which translates to "SA". You might also want to add EXECUTE AS OWNER to the code if someone needs to run it without giving them "sysadmin" privs.
Also, you've misspelled "HDR" and "HRD" in your originally posted code. "Must look eye". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply