January 14, 2003 at 1:33 am
I've posted this in both Security and T-SQL.
Here are the system details
SQL Server 2K running on its own box
Citrix running on its own box (all connections below made via citrix)
All users are members of a group that has execute permissions on the sp. I also went so far as to add the user in question to the database with execute permissions on the sp.
Problem
Users who are not Admins of the domain cannot run a stored procedure that uses Linked Server (Access Database with no security) (Admin with no password). I’m not changing the data in the stored proc…it is used to feed a report.
Testing
I created a stored proc that runs great when I’m the one running it. My account is an admin of the domain. (using Query Analyzer) the stored proc uses servername…tablename syntax.
I have another account that is setup just like a regular user. When I attempt to run the same stored proc (using Query Analyzer) I get the following message. I also tried using the openrowset, but still got the same error.
MSG 7399
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ reported an error.
[OLE/DB provider returned message: Unspecified error]
I have searched the web hi and low and found a few people with similar problems, but I have yet to find a solution.
I have verified that the TEMP and TMP variables on the SQL Server Box allow access by all.
I’ve read the MS white paper that talks about making sure the systemDB key in the registry is set to the correct workgroup file…the access database is not secure. The fact that I can run the stored proc works under at least one user name tells me that it can’t be the systemDB setting.
I tried to run regmon and see if there were some “Access Denied” areas, but I didn’t see any. It could be a registry permission problem, but I’m not sure which keys are used by SQL Server when using a linked server, but on the other hand, since the SQL Server is on its own box, the Citrix created Registry for each user shouldn’t interfere with SQL Server.
Remember, there is no security on the Access Database.
I attempted to open another unsecure database using openrowset and I get the same results as with the above sp.
I’ve only been working in SQL Server for about a year and a half and I know I have a lot more to learn, but this seems like it should be simple to solve, yet the solution remains out of my grasp.
Any help would be greatly appreciated…I’ve been working on this problem off an on for about a week and I’m starting to feel like I lost the battle.
Thanks,
Mike
January 17, 2003 at 8:00 am
This was removed by the editor as SPAM
January 21, 2003 at 9:04 am
A few thoughts...
Have you locked down Provider access?
That is, is "DisableAdhocAccess" set to 1 under the key HKLM\SW\MS\MSSQLServer\Providers\<provider>
If so, set it to 0
Do the ordinary users have permissions to the Access DB?
You say you are running as the Domain Admin.
What if you run in a normal domain account, but with sysadmin in the server itself? This will isolate the problem to either the network or the SQL server.
Also, SP3 changes mean more information is reported for distributed queries
That may help
Cheers
January 22, 2003 at 9:24 am
Don't know about 2k but with 7 I did the following for Access 2000 db.
Created the linked server with the data source being the file name (e.g. c:\temp\db1.mdb) and set security to be mapped to Admin and no password. Could then access the tables thus
SELECT * FROM linkname...tablename
Don't know if this helps or not.
p.s. Access db's do have security set, they are set to the user admin by default and you must use the admin user to access the db.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply