promap
asked on
OpenRowSet: Access to SQL Server Permission Denied With Integrated Security
I'm attempting to Import Access Data into SQL Server 2000 (SP3) with OpenRowSet logging into SQL Server with Integrated Security and recieving the following Error: OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\web1\FTP\Montgomery\AgW ork.mdb'.
My Query Looks Like This: SELECT * INTO [temp_AgErrorSum] FROM OpenRowSet('Microsoft.Jet. OLEDB.4.0' ,'\\web1\F TP\Montgom ery\AgWork .mdb';'adm in';'', [AgErrorSum])
My connection string looks like this: Provider=SQLOLEDB.1;Integr ated Security=SSPI;Persist Security Info=False;Initial Catalog=Montgomery;Data Source=SQL2
If I execute the query logging in as "sa" in my SQL Server Connection String, everything works just fine.
If I execute the query logging in as a non-sysadmin account, I get the lovely Ad-hoc access is denied error.
But executing with Integrated Security=SSPI, I get the error message at the top.
I've attempted to run this app under both the Administrator Account and as myself (I'm a Domain Admin) and SQL Server has the BUILTIN\Administors account set as a sys-admin role.
I've also set the folder/file permission on the directories to Everone - Full Control and the Drive Share permissions to Everyone - Full Control (just to eliminate any possible permissions issues). I have no problems at all manually opening the Access Database logged in as administrator or myself.
I'm working with SQL Server 2000 (SP3) and JET 4.0 DB sitting on a Win2K3 server and locally I'm using VB6 on a WinXP SP1 machine.
Any help would be greatly appreciated...
My Query Looks Like This: SELECT * INTO [temp_AgErrorSum] FROM OpenRowSet('Microsoft.Jet.
My connection string looks like this: Provider=SQLOLEDB.1;Integr
If I execute the query logging in as "sa" in my SQL Server Connection String, everything works just fine.
If I execute the query logging in as a non-sysadmin account, I get the lovely Ad-hoc access is denied error.
But executing with Integrated Security=SSPI, I get the error message at the top.
I've attempted to run this app under both the Administrator Account and as myself (I'm a Domain Admin) and SQL Server has the BUILTIN\Administors account set as a sys-admin role.
I've also set the folder/file permission on the directories to Everone - Full Control and the Drive Share permissions to Everyone - Full Control (just to eliminate any possible permissions issues). I have no problems at all manually opening the Access Database logged in as administrator or myself.
I'm working with SQL Server 2000 (SP3) and JET 4.0 DB sitting on a Win2K3 server and locally I'm using VB6 on a WinXP SP1 machine.
Any help would be greatly appreciated...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.