September 20, 2010 at 11:57 am
Basically my end-users are getting Cannot initialize the data souce object of OLE DB Provider "Microsoft.ACE.OLEDB.12.0" for linked server "ICD9_EXCLUSIONS"/ (Microsoft SQL Server, Error:7303. when opening up the linked server in SSMS.
I am running Sql Server 2005 sp2 on a windows 2003 r2 x64 platform in a clustered environment.
I have downloaded the x64 office driver as you see above. When I log into SSMS and open the linked server I can see everything no problem, run queries, no issue when I set the linked server to use login's current security context.. I can create a local sql server account give it SysAdmin, it can access the excel file when I set it to be made without using a security context
However when I take away SysAdmin from the local user account, it can no longer access the excel file, For other domain users, regaurdless if I grant sysadmin or not they can not access the linked server at all.
I set the permissions for the directory and the file to everyone full control. to try and take that out of the equation. I have tried making the .xls file into an .xlsx file, and changing the provider string from Excel 8.0 to Excel 12.0 as needed. I have moved the file from a network share to a local drive on the server. I am sure its something I am just overlooking but its driving me absolutely batty.
September 20, 2010 at 12:07 pm
Did it work before?
Have the permissions on the Share folder, where the file resides, changed?
September 20, 2010 at 12:14 pm
No it has not worked before. I have shared out the folder as SQLLINK opened up the share permissions to to Everyrone Full Control, then opened up the NTFS permissions to Everyone Full Control. Sitting with the end users they can access and modify the file no problem going through explorer/excel.. but still can't access when going through the linked server. I do currently also have a linked server to an oracle database which end-users have no problem accessing, its only this excel file.. or maybe the ace driver or something that I am not assignging security for properly or something
September 20, 2010 at 12:29 pm
The error message complains about an OLE DB error. I didn't think Excel used OLE DB to connect. But you're getting the queries to run when you're sysadmin?
What's the security on your Linked Server?
September 20, 2010 at 1:01 pm
Well I am creating the linked server like this
EXEC master.dbo.sp_addlinkedserver @server = N'ICD9_EXCLUSIONS', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\ICD9_EXCLUSIONS.xlsx', @provstr=N'Excel 12.0'
( I have also created them using the .xls and Excel 8.0 with similar results)
as far as security goes, I have tried a couple of configurations both with using logged in credenatials and allow all connections..
September 21, 2010 at 6:08 am
Is the Linked Server in the SSMS GUI? Check the security tab on it and let me know what the set up is.
September 21, 2010 at 6:52 am
in the security tab I have no mappings in the local to remote user as I am trying to read an excel file, and I have tried both options of "Be made without using a security context" and "Be made using the login's current security context" both of which allows my logon (which is an admin on the box" and the local sql account I have created when given Sysadmin role access to the linked server.
September 21, 2010 at 12:37 pm
What version of SQL Server are you utilizing? 32-bit or 64-bit, I see your OS is 64-bit. If you are running SQL Server 64-bit the ACE drivers were only designed to work in a 32-bit environment.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
September 21, 2010 at 12:46 pm
I am using 64 bit SQL Server .. and microsoft has released the x64 version of this driver.. Does that driver to your knowledge not work properly or something?
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D
September 21, 2010 at 12:53 pm
From the link you provided, are the domain users attempting to access from a Windows XP OS? If so, Only the 32-bit Access Database Engine may be used on Windows XP Service Pack 3
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
September 21, 2010 at 1:00 pm
I might be confused but I was figuring since I was using 64 bit sql server on a 64 bit os, that driver had to be the 64 bit version. I didn't think the clients os had any bearing on this. Me personally I am using windows xp sp3 and I can access the linked server no problem with that configuration .. but I am also an administrator on the sql server.. It almost seems to me that there is some sort of permission issue on the server and their limited rights aren't allowing them to use the provider to access the excel file somehow..
September 21, 2010 at 1:05 pm
One last shot, have you looked at the following post in SQL Server Central?
http://www.sqlservercentral.com/Forums/Topic347032-20-2.aspx
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
September 21, 2010 at 2:30 pm
check your cluster configuration. The security on the file share needs to be set up using the cluster administration tool. If you set up your local user again and give it sysadmin rights, then fail over the cluster, you should still not be able to open that datasource. If that's the case, then it's part of the cluster. Did you give domain users access to the clustered file share?
September 22, 2010 at 5:35 am
fenwicc (9/21/2010)
check your cluster configuration. The security on the file share needs to be set up using the cluster administration tool.
If this is NOT what you checked when you checked Shared permissions (I just assumed that it was) you need to go back and do this. Believe me, it makes a difference.
September 22, 2010 at 5:36 am
jspatz (9/21/2010)
I might be confused but I was figuring since I was using 64 bit sql server on a 64 bit os, that driver had to be the 64 bit version. I didn't think the clients os had any bearing on this. Me personally I am using windows xp sp3 and I can access the linked server no problem with that configuration .. but I am also an administrator on the sql server..
Didn't you say you weren't able to access it when you weren't using an admin login, though? Which, I agree, points to a permissions issue of some flavor.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply