Thursday, January 03, 2013 12:22 AMHi,
I have Windows Server 2012 with SQL 2012 Standard SP1. I am using linked server, and Access Database Engine 2010 Redistributable to access my database file made in Microsoft Access (.mdb) from network file server.
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'MyLinkedServer', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'\\myfileserver.mydomain.com\files\mydatabase.mdb'
My SQL service is running with domain service account MYDOMAIN\SQL1$ , i have added Full control for file share and NTFS permission on my file server folder (C:\Files).
When I open (as domain admin with UAC elevated permissions) on my DB server SQL Management studio, I can browse tables and everything works.
The problem is, if I open SQL management studio (as domain admin with UAC elevated permissions) on my File server or any other computer, when trying to browse my linked server i got error:
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Synesis_3PRO2013". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.00.3000&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
When I try to place simlpe Select SQL query I got error:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MyLinkedServer" returned message "The Microsoft Access database engine cannot open or write to the file '\\myfileserver.mydomain.com\files\mydatabase.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".
I do not have any other program using my access database, and user has full control. I am trying to use in security mode - "For a login not defined in the list above, connections will be made without using a security context", i have also tried all four options. I am confused becouse it works from SQL server but from any SQL client domain member computer/server it does not work.
I have same problem in another environment where I have Windows Server 2008 R2 and SQL 2008 R2 SP2.
-- Hrvoje Kusulja
Thursday, January 03, 2013 11:50 AMThe only account important for the NTFS permissions is the account of your SQL Server Windows service. The only additional problem I can think of is - but untested in this configuration - that the ACE engine here also checks the Trusted Locations, which Access normally does. Take a look at this registry tweaks.
Thursday, January 03, 2013 12:57 PM
NTFS must be fine since it works from same server using same accounts.
As I understand, adding my access file to Access trusted location could be a problem. I have tried now to add my access database file location to trusted locations for user which is my SQL service user (Windows Service - AD managed service account MYDOMAIN\SQL1$) and my test user which I use to connect to sql server as a client from sql management studio. (Account is Domain Admins and have full permissions on SQL server also)
I have added this .reg:Windows Registry Editor Version 5.00
"Description"="My file server"
I have done this for SQL service account user and my personal test account as I said. I have tried to logoff and restart sql service and all servers also.
The same problem still persists.
Anyway, thank you for giving me a hint.
- Edited by Hrvoje Kusulja Thursday, January 10, 2013 11:53 PM
Thursday, January 10, 2013 11:54 PMAny new proposition or solution for my problem ? I have still same error.
-- Hrvoje Kusulja