September 22, 2010 at 8:08 am
If you're wondering where this is set, open the cluster admin. Go to the group that has the file share where the data source is located. Click on the file share folder properties, then the parameters tab, click the permissions button on the bottom left. --This is for win 2k3 / sql 2005 cluster. Things are a little different for win2k8 clustering but the permissions will be the same. Brandie is right, this is definitely cluster related permissions. After you add the permissions to the share verify your access too. I do this by putting text file on the share, updating it, saving it, then delete it. - if you need that level of access to that share. Then fail it over and do it again. Hope this helps.
-chris
September 22, 2010 at 8:15 am
Thanks! I will be trying this today, I did not user cluster administrator when creating the share .. I will let you all know how it goes .. thank you again for all your input
September 22, 2010 at 8:22 am
I might need to read a little more carefully...was the local user you added a local user on the machine or a sql account, and did it have local admin privileges or sql sys admin privileges?
thanks
September 22, 2010 at 8:29 am
It was a sql account which worked accessing the file when sysadmin role was applied .. but did not work when sysadmin role was revoked.
September 22, 2010 at 8:32 am
are you a domain admin or local admin on both boxes?
September 22, 2010 at 9:14 am
Alright .. so now I created the share through cluster admin opened it up for EVERYONE full control, checked the ntfs permissions on the folder, everyone full control, recreated my linked servers referencing the new share. My network account again has no problem accessing the linker server excel spreadsheet. I go to my enduser who is part of a network group that has the sysadmin role. No go still getting that same error message, I create his network account as a security user and remove the group security logon to rule out it being group membership .. still no go, I grant his new sql server logon sysadmin role. Still no dice on accessing the linked server excel spreadsheet (he did verify he could get to the file from windows and modify it) Now I added his user account to the Local Administrators group and BAM .. he now has access to the linked server.. So it is my guess there is somefile or process that is used to initiate the connection to the spreadsheet that standard end-users are not getting access to.. Again thank you guys for all your direction
September 22, 2010 at 9:18 am
Local Admin I am not a domain admin
September 22, 2010 at 9:41 am
when you dropped the user into the local admin role, they get sa privileges in SQL 2005 by the built in \admin group(unless you removed it).
Do you have the domain users group in the local users group? --check this
-If you are a local admin and can access the data source, and the user can access the data source ONLY when they are a local admin, it sounds like machine - level permissions, probably not SQL level.
I say this because you set the user up as an sa in SQL and they still did not have the ability to connect.
-or it could very well be both, i bet that helps...
Cluster permissions are tricky for file shares. I think you have to fail it over and reapply the permission to verify they propagated over.
If you fail the cluster over, does your testing user still have access to that linked server? If he does, I would be surprised. Is this a production server or can you not fail it over?
Can you post some screen shots?
September 22, 2010 at 11:40 am
jspatz (9/22/2010)
Thanks! I will be trying this today, I did not user cluster administrator when creating the share .. I will let you all know how it goes .. thank you again for all your input
Yeah, that causes all SORTS of problems. Glad to see you created it through the cluster administrator tool later.
Regarding the continuing problem... What are the SQL Agent and SQL Server Service accounts set up with? Local account? AD user account?
September 23, 2010 at 11:36 am
Ok .. I have tried just about everything and still no go. I was starting to think maybe it was just a 64 bit thing and that provider was hosed or something. I went to a standalone server running windows 2003 32bit with sql server 2005 sp2. I popped the excel file in a directory on the local c drive, granted permissions to said end user on the file. He verified he could browse to the file and open it and save it no problem. Created a database gave the enduser access to the database, he verified he could connect to the database no issue... checked providers, I had both the jet 4 and the ace12 providers, both were checked off for allow inprocess. Created a linked server using the jet4 driver. I go in to the link serveropen it up and I can see contents fine and dandy. I get my enduser to login tries to open up the linked server and WHAM same error message... so obviously there is something horribly wrong with the way I am installing sqlserver or something 🙂 because I can't get this to work anywhere and this should be a terribly easy process.. I went into surface area configuration thinking something there might not be set.. turned on the OLE Automation for no other reason the the word OLE was in the title.. but again same exact scenario you need to be an administrator on the machine for you to be able to access this spreadsheet through a linked server connection.
September 23, 2010 at 11:36 am
and the agent accounts are AD users we use for each instance.
September 23, 2010 at 1:45 pm
can you drop the domain user you have been testing with into the local user group then go into sql and give SA rights then go into the cluster admin and give that account full control? It has to be one of the three places. The weird thing is that you say this works when you are in the local admin group, but that OLE driver is for access so I'm surprised it works at all.
September 23, 2010 at 1:46 pm
Ok just an addendum .. on my standalone machine I changed the account that runs the sqlserver service from being a domain user who had admin rights on the server (myself) to Local System account with allow service to interact with desktop. Making this change and restarting sql server allowed my end users the ability to get at the linked server. So while this is nice, on our production servers we use AD accounts specifically for service accounts of our database instances. So still I have a problem with getting a service accounts to behave the same way as the local system account interacting with desktop. Any ideas?
September 23, 2010 at 2:12 pm
ooooh, I'm thinking local security policy then. local admin gets everything including sa in sql.
There is might be a local security right that you need to grant to the users to allow the interaction.
if you go to start--> run--> type gpedit.msc [enter]
and traverse down Computer Configuration --> Windows setting --> Local Policies --> User Rights Assignment.
It's probably a single permission you need to assign to your sql server service account(the domain one).
I think local service is equivalent to a local user, but logs on anonymously and has a NULL session, but the fact you let it interact with the desktop makes me think that a local security tweak on your domain account may do the trick.
What was the security group the sql service belonged to?
September 24, 2010 at 6:00 am
This whole situation makes me think the AD user account doesn't have all the proper GPO rights it needs.
Check this article out, about 1/3 of the way down.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply