none
Cannot initialize datasource for linked server implementation RRS feed

  • Question

  • Greetings,

     

     

    I am implementing an update to an application that needs to run some SQL Server Stored Procedures queries against a legacy Access database.  I implemented a linked server for this purpose.  If I run the queries as an Administrative user, all is well.  But for limited rights users, I get the following error if I try to access any of the Access data:

    "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XYZ"."

     

     

    I've looked at any permissions related items I could find in SQL Server Management Studio 2005 (Express).  I can't find anything different in permissions between the Users and Administrators group.  I confirmed that the query would run if I simply added the limited rights user to the Windows Administrators group.  I opened all the permissions on the directories and files where the Access .mdb file and the SQL MDF/LDF files are located with no increase in joy.

     

    After some ‘research’ (i.e., googling), I replaced the linked server with the openrowset command, and changed the provider to MSDASQL (to get more verbose error messages).  From that arrangement, I received the following messages:

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Microsoft Access Driver] Disk or network error.".

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x6a4 Thread 0xc3c DBC 0x453b3fd4 Jet'.".

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

     

    Any ideas?  I am quite stumped.

     

     

    Here's the initial setup I had for the linked server 

     

    EXEC sp_grantlogin [BUILTIN\Administrators]

    EXEC sp_addsrvrolemember @loginame = [BUILTIN\Administrators], @rolename = 'sysadmin'

     

    -- Create linked server so we can use SQL Server to query the DAFIF

    -- Edition 7 Access database prepared by PFPS

    exec sp_addlinkedserver

         @server     = 'XYZ',

         @srvproduct = 'Access 97',

         @provider   = 'Microsoft.Jet.OLEDB.4.0',

         @datasrc    = 'C:\XYZ\DBase\<Access_Filename>.mdb',

         @location   = 'localhost',

         @catalog    = 'XYZ'

     

     

     

    ---The following command will fail with the "Cannot initialize ..." error message

    SELECT * FROM ACOM  --ACOM is a SQL view for one of the tables in the Access DB

    Tuesday, February 19, 2008 10:54 PM

Answers

  • Thanks for the reply.  I called MS Tech Support before the weekend and we were able to find the permissions problem.  It turns out that by default, the SQLEXPRESS service is set up to log on using the "NT AUTHORITY\NetworkService" Account.  The permissions problem (with the original linked server) occurs because the .JET. engine creates a few temporary files in the Network Service temp folder (\Documents and Settings\NetworkService\Local Settings\Temp).  We used the SysInternals FileMon tool to track this down (a great tool, by the way).

     

    So, the fix is to do one of the following:

     - Change the SQL Server's service Log On to "Local System account" OR

     - Provide full access to the "\Documents and Settings\NetworkService\Local Settings\Temp" for any account that needs to run the queries.

     

    Thanks for you help,

        Mike

    Monday, February 25, 2008 2:45 PM

All replies

  • Hi Napierm,

     

    I think its good that you are using the MSDASQL provider instead. Lets look more closely at the error that you are getting :

     

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x6a4 Thread 0xc3c DBC 0x453b3fd4 Jet'.".

     

    => As you can see it is pointing to some registry permissions issue.

     

    So you can do this :-

     

    1. Start Registry Editor (Regedt32.exe).
    2. Select the following key in the registry: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC
    3. Right-click on it and click Permissions .
    4. Type the limited rights user here and give him the necesary permission.
    5. Quit Registry Editor.

     

    => Clear the contents in the TEMP directory.

    => Now try re-running the linked server query from this limited user.

     

    Let me know if this doesnt solve it and i can help you further..

     

    --

    Sudarshan

    Sunday, February 24, 2008 6:12 AM
  • Thanks for the reply.  I called MS Tech Support before the weekend and we were able to find the permissions problem.  It turns out that by default, the SQLEXPRESS service is set up to log on using the "NT AUTHORITY\NetworkService" Account.  The permissions problem (with the original linked server) occurs because the .JET. engine creates a few temporary files in the Network Service temp folder (\Documents and Settings\NetworkService\Local Settings\Temp).  We used the SysInternals FileMon tool to track this down (a great tool, by the way).

     

    So, the fix is to do one of the following:

     - Change the SQL Server's service Log On to "Local System account" OR

     - Provide full access to the "\Documents and Settings\NetworkService\Local Settings\Temp" for any account that needs to run the queries.

     

    Thanks for you help,

        Mike

    Monday, February 25, 2008 2:45 PM
  • Hi,

     

    I trying to connect to visual froxpro database by SQLEXPRESS with the follwing sql query :-

     

    select * from OPENROWSET

    ('MSDASQL',

    'Driver={Microsoft Visual FoxPRO Driver};SourceDB=C:\Inetpub\wwwroot\MK\;SourceType=DBF',

    'SELECT * FROM CUSTOMER')

     

    I receive the following message when i execute the sql :-

     

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Driver does not support this function".

    Msg 7303, Level 16, State 1, Line 2

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

     

    I did follow the step provided above but i still get the same error. Any ideas to solve this problem?

     

     

    Cherrie

    Tuesday, November 18, 2008 6:34 AM
  • Thanks for your help with this one. I had exactly the same trouble, but with Windows Server 2008 x64.

    After installing the Microsoft Access Database Engine 2010 Redistributable (enables legacy drivers in x64 applications), applying a bunch of tweaks to allow Ad Hoc Queries (both in SQL Server and the registry), I found that my ASP.NET application still couldn't run SQL scripts that used MSDASQL with OPENROWSET etc.

    However, the same queries would work find whee I used SQL Management Studio. The difference of course was permissions. My account could write to the NetworkService Temp directory, but the worker account associated with the IIS app pool could not.

    So, to query CSV data sources (or XLS, or MDB etc.) in SQL Server 2008 on Windows Server 2008 x64, the process was:

    1. Install Microsoft Access Database Engine 2010 Redistributable.
    2. Allow ad hoc queries in SQL Server using sp_configure.
    3. Add the "DisallowAdhocAccess" key in the registry for MSDASQL, and set its value to zero.
    4. Use Process Monitor to work out where the temp directory is for NetworkService on Windows Server 2008. In my case it was "C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp".
    5. Give the "IIS_IUSRS" group (to which all ASP.NET worker accounts should be a member) modify permission to that folder.

    Success! Hope that helps others.

    Wednesday, January 12, 2011 11:16 PM
  • Another thing to note on this solution, is that if you're not using SQLExpress, but you're using a full blown version SQL Server you'll need to change the permissions on whatever account the SQL Server service is running under.  I ran into the problem because I had separate accounts running the SQL Server service and the SQL Server Agent service.  The agent account was trying to create temp files under the SQL Server account rather than it's own and didn't have permission.
    Monday, April 25, 2011 8:03 PM
  • Hello,


    I also lost lots of hours looking for a solution and I found it. 

    I post the solution no my Blog, here: http://blog.danielcosta.pt/?p=492

    Have Fun,

     

    Daniel Costa

    Wednesday, November 16, 2011 11:25 PM