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

    Question

  • 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]
    GO 
    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE WITH OverRide
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE WITH OverRide
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    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

Answers

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.

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL\DisallowAdhocAccess

    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
    http://www.microsoft.com/en-us/download/details.aspx?id=14839

    How to import a DBF file in SQL Server
    http://stackoverflow.com/questions/52822/how-to-import-a-dbf-file-in-sql-server


    Allen Li
    TechNet Community Support

    Tuesday, August 06, 2013 9:06 AM
    Moderator