January 14, 2003 at 1:32 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 17, 2003 at 10:07 am
Please dont cross post - did you get an answer on the other post?
Andy
January 17, 2003 at 10:10 am
sorry for cross posting.....no, I didn't get an answer on either post. Thanks for the inquiry.
Mike
January 20, 2003 at 2:47 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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply