This was originally posted in a different group but moved by the moderator for not being relevant so I hope it's relevant here:
I have been experiencing great difficulty in getting any Linked Server to work on my SQL Server 2008 Express Edition SP1 installation.
EXEC sp_addlinkedserver 'TEST',
@srvproduct = '',
@provider = 'Microsoft.JET.OLEDB.4.0',
@datasrc = 'C:\test\odbc.mdb',
@provstr = ''
@rmtsrvname = N'TEST',
@useself = N'FALSE',
@rmtuser = N'Admin',
@rmtpassword = ''
I then test with:
EXEC sp_testlinkedserver TEST
EXEC sp_tables_ex TEST
The sp_testlinkedserver is successful but the sp_tables_ex fails with:
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "Microsoft.JET.OLEDB.4.0" for linked server "TEST" reported an error. Access denied.
Msg 7301, Level 16, State 2, Procedure sp_tables_ex, Line 41
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.JET.OLEDB.4.0" for linked server "TEST".
The 'Access Denied' makes me think this is a permissions issue.
I have given 'Everyone' account full rights on 'C:\test' and the SQL Server is running under 'Network Service' account.
OS is Vista SP1, Office 2007 is installed - though the Access database file I am trying to connect to is an Access 2000 version.
I've been going round in circles with this for a number of days now - I've tried moving the Access file to the SQL Server's DATA directory, tried connecting to Excel, Access 2007 files etc. Always I end up with the above error.
If anyone can provide any suggestions or solutions I'd be very grateful.
I have changed the SQL Service account from 'Network Service' to 'LocalSystem' and now get the following error from the OLE DB provider when I do:
The OLE DB provider "Microsoft.JET.OLEDB.4.0" for linked server "TEST" reported an error. The provider did not give any information about the error.
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.JET.OLEDB.4.0" for linked server "TEST". The provider supports the interface, but returns a failure code when it is used. (.Net SqlClient Data Provider)
Surely connecting to an Access database file should be as simple as you can get. There must be something else at play here......maybe my OLE DB driver for Jet is screwed? I have absolutely no problems accessing this same database from Excel for example.
Please, any suggestions?
Success - by checking the 'Allow InProcess' check against the provider - everything is finally working.
However, I am far from happy - I had tried that while the SQL Service was configured to run under 'Network Service' and I still had issues. So, if anyone would like to hazard a guess what went wrong here I'd be interested.
I made sure 'Network Service' and 'Everyone' had full access rights to the file.
I shouldn't have to run under 'LocalSystem' right?
The error messages were pretty poor. Especially: 'The provider did not give any information about the error.'