When logged in as administrator I can test the connection and all is well.
When I log in as a different user I get: The OLE db provider "Microsoft.ACE.OLEDB.12.0 for linked server "PriceDatabase" reported an error. Authentication failed. Cannot initialiize the data source object of OLE db PROVIDER "Microsoft.ACE.OLEDB.12.0" for linked server 'PriceDataBase'. OLE DB provider Microsoft.ACE.OLEDB.12.0 for linked server"PriceDatabase" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error 7399
I have tried all kinds of different settings on the Security tab of the linked server and nothing seems to work. logging in as sa still cannot test the connection successfully.
When you log on with other user, it is clear that user do not have enough privileges to access the database. Make sure the user has all access to the database you are trying to access via linked server.
TG: I went through those steps and get the same error.
srimami I'm linking to an excel spreadsheet. User has full rights to the folder that it is in. This was originally created on Windows server 2003 SQL 2005. Stopped working when we updated to Server 2008 SQL 2008.
Error after doing steps in TG's link: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PriceDataBase" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 10 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PriceDataBase".
looks like you've made progress - 7303 is a different error than the first one you reported (7399). did you try googling "error 7303"? When I did some links suggest you didn't set up your linked server with the correct parameters.
According to this you should be using a different provider for linking to Excel: Microsoft Jet 4.0 OLE DB Provider