Windows 2008 Server and SQL 2012 login Win Authentication using Microsoft.ACE.OLEDB.12.0 returned message "Unspecified error"


  • I have recently installed 64 bit SQL 2012 SP1 on 64 bit Server 2008 R2 Standard  under an evaluation license.
    I have created a windows user account "someone" with administrators privilege.
    I have installed 64 bit Microsoft.ACE.OLEDB.12.0.
    I have created SQL accounts with Windows authentication for "Administrator" and "someone" assigning role sysadmin.

    Configured SQL using

    USE [master]
    sp_configure 'show advanced options', 1
    sp_configure 'Ad Hoc Distributed Queries', 1
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

    I have a "dbase" file D:\WebTest\ShapeFiles\Lines.dbf created by an application E.S.R.I. ArcMap.
    When I log into Windows using account "Administrator", run SQL Server Management Studio 2012 using Windows authentication,  the following SQL returns expected data.

    select * from  OPENROWSET( 'Microsoft.ACE.OLEDB.12.0'
            ,'dbase IV;Database=D:\WebTest\ShapeFiles'
            ,'SELECT * FROM Lines')

    However, when I log into Windows using account "someone", and try SQL Studio with Windows Authentication I get:

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

    I created SQL Authentication account "isadmin" with SQL sysadmin privilege and when I run SQL Server Management Studio 2012 using SQL Authentication for "isadmin" the query returns expected  data.

    What is needed to get other than "Administrator" Windows Authentication logins to run the above?

    Thursday, August 01, 2013 7:32 PM


All replies

  • Research continues:

    I created a linked-server. (found something about inadequate security using openrowset )

    1. provider: Microsoft Ofice 12.0 Access Database Engine OLE DB Provider
    2. Product Name: Microsoft Access DB
    3. Data source: d:\WebTest\ShapeFiles
    4. Provider string: dbase IV

    Works OK for admin though it still fails when using long file names.

    I still can not get this to work without administrator privilege... more research.

    Registry item DisallowAdhocAccess does not exist - Ad hoc access will be allowed for members of the SysAdmin server role and for Windows NT security connections, and will be disallowed for normal users using standard security connections for this provider.


    Is this my problem? If so, what is the recommended way to address this?  I have no "Providers" under MSSQLServer

    Friday, August 02, 2013 7:55 PM
  • Hi abjones3,

    As far as I know, we need to install Microsoft OLE DB Provider for Visual FoxPro 9.0 to access a .DBF file. You can get the driver from the following link.

    Microsoft OLE DB Provider for Visual FoxPro 9.0

    How to import a DBF file in SQL Server

    Allen Li
    TechNet Community Support

    Tuesday, August 06, 2013 9:06 AM