none
Cannot create a working linked server to Access or Excel

    Question

  • 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.

    e.g.

    EXEC sp_addlinkedserver 'TEST',
    @srvproduct = '',
    @provider = 'Microsoft.JET.OLEDB.4.0',
    @datasrc = 'C:\test\odbc.mdb',
    @provstr = ''

    EXEC sp_addlinkedsrvlogin
        @rmtsrvname = N'TEST',
        @useself = N'FALSE',
        @rmtuser = N'Admin',
        @rmtpassword = ''
    GO

    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.

    Thanks,

    Matt Haddon

    Thursday, May 28, 2009 10:30 AM

All replies

  • 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:

    EXEC

     

    sp_tables_ex 'TEST'


    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?

    Matt Haddon

    Thursday, May 28, 2009 2:18 PM
  • 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.

    Some points:

    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.'

    Comments?

    Matt Haddon
    Thursday, May 28, 2009 2:29 PM