Solved

OpenRowSet:  Access to SQL Server Permission Denied With Integrated Security

Posted on 2004-10-08
4
1,776 Views
Last Modified: 2009-09-28
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\AgWork.mdb'.

My Query Looks Like This: SELECT * INTO [temp_AgErrorSum] FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0','\\web1\FTP\Montgomery\AgWork.mdb';'admin';'', [AgErrorSum])

My connection string looks like this:  Provider=SQLOLEDB.1;Integrated 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...
0
Comment
Question by:promap
4 Comments
 
LVL 3

Accepted Solution

by:
avbsql earned 500 total points
ID: 12281048
The issue is that with integrated security. U need to use trusted connection, meaning sql login to do the job. Does the table already exist in sql server or does it need to be created on the fly. Best for this purpose would be to created linked server to the access database from sql server. And then run sql query to fetch the data into sql server. Let me know if this works for you or need more details on linked server.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now