Running the following using ADO in Delphi from MSDE SP4 to SQL05 Express SP3 over a VPN connection.
insert into OPENDATASOURCE('SQLNCLI','Server=10.0.0.21\SQLEXPRESS;ASYNC=TRUE;User ID=tsuser;Password=tsuser;Initial Catalog=TSDEV').TSDEV.TSADMIN.TRANSACTIONMAIN
(TRANSNO, VEHICLEID, ACCOUNTNO)
select TRANSNO, VEHICLEID, ACCOUNTNO from tsadmin.transactionmain
where TRANSNO in (1073078,1073079)
If I run this using sa credentials in the statement, it will complete no problem. That is still logging in to the local as a non admin user.
However as any user with less privilege, I get: Ad hoc access to OLE DB provider 'SQLNCLI' has been denied. You must access this provider through a linked server.
The same happens going direct from Qry Analyser on the MSDE end.
In the Surface Area Config I have enabled OpenDataSource and OpenRowSet Support. I have been through the providers in registry and manually adding DisallowAdHocAccess set to 0. I have trolled the web looking for an answer to no avail.
Any insight gratefully received.
View Complete Post