Post reply

OpenRowSet works locally but not over networked drives

  • July 10, 2013 at 10:23 am

    #295414

    Hi,

    My company got some new stuff. I spent an hour or so on the phone with a sysadmin this morning going over permissions issues etc. in getting SQL able to import files via ORS. So we got it to work on local drives, but not over networked drives. This is the code and error:

    select * into sample.dbo.[eriktest] from

    OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',

    'Data Source=\\server\Sample\test.xlsx;

    Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1"')...[Sheet1$]

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    But going from the local C: or D: drives is fine. I also tried using xp_cmdshell (SORRY OPC) to map a drive, and then tried using ORS with the drive letter, and got the same error. Has anyone else run into this? I've spent a lot of time reading up about the errors, and tried everything that sounded like a reasonable suggestion on other message boards.

    Thanks

  • July 10, 2013 at 10:29 am

    #1631304

    It is tied to permissions and security.

    What user is SQL running as? Is this a local user? A system user? A Domain user?

    In the case of ANYTHING but a domain user you have already determined your problem. Those users don't have access to external resources.

    In the case of a domain user, does it have access to the share? To the underlying files? To THAT file in particular?

    CEWII

  • July 10, 2013 at 10:30 am

    #1631305

    most likely permissions, because the startup account of SQL server doesn't have access to the network share.

    When you touch anything outside of a database, SQL server does not use the account you intuitively think it should be using.

    Even though you, yourself, are an admin or domain admin, SQL doesn't use YOUR credentials for the network path.

    SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:

    or if the above was blank, the account in services:

    if you are a sysadmin on the SQL server, it will use the account in services no matter what.

    :

    That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.

    As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.

    Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.

    you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the external object you were trying to access/use works when SQL is run your credentials, so you'd know you need a domain account to access the resource.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 10, 2013 at 10:48 am

    #1631310

    Hi,

    We already have SQL running with a domain account called clients\SQLService, which I'm told has every permission in the book allowed to it. The server proxy account in SSMS is indeed blank, so it should be using clients\SQLService. Both the SQL service and the server itself have been rebooted in the last 12 hours, so I know it's not using any of the old values that were replaced some weeks ago. I can go back and ask my pocket sysadmin if he's sure about the permissions for that account.

    I read one post that mentioned having the account actually log in to the server once so it gets its permissions set from AD. Does that sound like a fix or a time waster?

    Thanks

  • July 10, 2013 at 11:17 am

    #1631316

    erikd (7/10/2013)

    Hi,

    We already have SQL running with a domain account called clients\SQLService, which I'm told has every permission in the book allowed to it. The server proxy account in SSMS is indeed blank, so it should be using clients\SQLService. Both the SQL service and the server itself have been rebooted in the last 12 hours, so I know it's not using any of the old values that were replaced some weeks ago. I can go back and ask my pocket sysadmin if he's sure about the permissions for that account.

    I read one post that mentioned having the account actually log in to the server once so it gets its permissions set from AD. Does that sound like a fix or a time waster?

    Thanks

    So it is a domain account, good. However, even though it might have lots of local permissions or even at the domain level you STILL have to verify that THAT user has rights to the share you are trying to reach AND the underlying files. This is the next step.

    You do not need to set the proxy in this case I believe. Keep in mind the proxy account is only used when a non-sysadmin user has been allowed to call xp_cmdshell. If you are logging into SQL with a sysadmin level user then this is not in play.

    CEWII

  • July 10, 2013 at 11:26 am

    #1631320

    Elliott Whitlow (7/10/2013)

    erikd (7/10/2013)

    Hi,

    We already have SQL running with a domain account called clients\SQLService, which I'm told has every permission in the book allowed to it. The server proxy account in SSMS is indeed blank, so it should be using clients\SQLService. Both the SQL service and the server itself have been rebooted in the last 12 hours, so I know it's not using any of the old values that were replaced some weeks ago. I can go back and ask my pocket sysadmin if he's sure about the permissions for that account.

    I read one post that mentioned having the account actually log in to the server once so it gets its permissions set from AD. Does that sound like a fix or a time waster?

    Thanks

    So it is a domain account, good. However, even though it might have lots of local permissions or even at the domain level you STILL have to verify that THAT user has rights to the share you are trying to reach AND the underlying files. This is the next step.

    You do not need to set the proxy in this case I believe. Keep in mind the proxy account is only used when a non-sysadmin user has been allowed to call xp_cmdshell. If you are logging into SQL with a sysadmin level user then this is not in play.

    CEWII

    The directory itself is everyone: full control. I'll bug someone about the rights assigned to the user again and post back. The last email I got about it said something about creating a clients\ user for me to specifically run processes as, but I'm not sure if that will help with the SQL Server Service itself logging in as clients\SQLService?

  • July 10, 2013 at 11:43 am

    #1631324

    erikd (7/10/2013)

    The directory itself is everyone: full control. I'll bug someone about the rights assigned to the user again and post back. The last email I got about it said something about creating a clients\ user for me to specifically run processes as, but I'm not sure if that will help with the SQL Server Service itself logging in as clients\SQLService?

    The directory is everyone: full control, good. If you use xp_cmdshell to do a dir of that share what do you get?

    EXEC master.dbo.xp_cmdshell 'DIR \\server\share'

    I'm gonna bet you get access denied or network name not found or something.. If you do then most likely the SQL user doesn't have access to the share or its misspelled..

    CEWII

  • July 10, 2013 at 11:45 am

    #1631327

    I checked that, actually. Not only can I use xp_cmdshell to DIR the path, but I can insert the results into a table. Weird, huh?

  • July 10, 2013 at 12:02 pm

    #1631340

    erikd (7/10/2013)

    I checked that, actually. Not only can I use xp_cmdshell to DIR the path, but I can insert the results into a table. Weird, huh?

    You might have hit on something there.. I had a similar problem and the issue was the file was being written with permissions that wouldn't allow SQL to access it. So now I guess you need to see what permissions the actual file has..

    CEWII

  • July 10, 2013 at 12:09 pm

    #1631346

    Elliott Whitlow (7/10/2013)

    erikd (7/10/2013)

    I checked that, actually. Not only can I use xp_cmdshell to DIR the path, but I can insert the results into a table. Weird, huh?

    You might have hit on something there.. I had a similar problem and the issue was the file was being written with permissions that wouldn't allow SQL to access it. So now I guess you need to see what permissions the actual file has..

    CEWII

    All users and groups, including Everyone, has all permissions allowed on the file, except Special Permissions. The only group that has Read and Read/Execute only are local users, which the clients\SQLService domain account isn't.

    So, do I need to give "Special Permissions"? These permissions don't seem special, but what do I know?

  • July 10, 2013 at 12:18 pm

    #1631349

    erikd (7/10/2013)

    Elliott Whitlow (7/10/2013)

    erikd (7/10/2013)

    I checked that, actually. Not only can I use xp_cmdshell to DIR the path, but I can insert the results into a table. Weird, huh?

    You might have hit on something there.. I had a similar problem and the issue was the file was being written with permissions that wouldn't allow SQL to access it. So now I guess you need to see what permissions the actual file has..

    CEWII

    All users and groups, including Everyone, has all permissions allowed on the file, except Special Permissions. The only group that has Read and Read/Execute only are local users, which the clients\SQLService domain account isn't.

    So, do I need to give "Special Permissions"? These permissions don't seem special, but what do I know?

    I don't really consider those special myself.. Are there other files there that CAN be read by SQL? I'm trying to get a view on what SQL actually needs. Also are there any DENY permissions set?

    CEWII

  • July 10, 2013 at 12:35 pm

    #1631355

    Elliott Whitlow (7/10/2013)

    erikd (7/10/2013)

    Elliott Whitlow (7/10/2013)

    erikd (7/10/2013)

    I checked that, actually. Not only can I use xp_cmdshell to DIR the path, but I can insert the results into a table. Weird, huh?

    You might have hit on something there.. I had a similar problem and the issue was the file was being written with permissions that wouldn't allow SQL to access it. So now I guess you need to see what permissions the actual file has..

    CEWII

    All users and groups, including Everyone, has all permissions allowed on the file, except Special Permissions. The only group that has Read and Read/Execute only are local users, which the clients\SQLService domain account isn't.

    So, do I need to give "Special Permissions"? These permissions don't seem special, but what do I know?

    I don't really consider those special myself.. Are there other files there that CAN be read by SQL? I'm trying to get a view on what SQL actually needs. Also are there any DENY permissions set?

    CEWII

    I haven't been able to either create in, or create and copy to the folder, a file that can be read.

    When the same files are copied to a local drive, they're read fine. This goes for other file types as well, not just xlsx. Also .xls, .txt, and .csv. I know that doesn't make a ton of difference.

  • July 10, 2013 at 1:01 pm

    #1631359

    erikd (7/10/2013)

    Elliott Whitlow (7/10/2013)

    I don't really consider those special myself.. Are there other files there that CAN be read by SQL? I'm trying to get a view on what SQL actually needs. Also are there any DENY permissions set?

    CEWII

    I haven't been able to either create in, or create and copy to the folder, a file that can be read.

    When the same files are copied to a local drive, they're read fine. This goes for other file types as well, not just xlsx. Also .xls, .txt, and .csv. I know that doesn't make a ton of difference.

    I'm wondering if the SQL user should be given explicit permissions to this directory. When you copy the files the permissions of the directory usually flow into it. But when you move a file on the same drive the permissions it had come with it. I'm wondering if that might be in play.. But it really feels like we are at the root of the problem, permissions on the file and/or directory. Which would probably NOT be in place on a local drive given the permissions you say this user has locally.

    CEWII

  • July 10, 2013 at 1:10 pm

    #1631365

    I've given the clients\SQLService user explicit rights to the folder. from Full Control on down. Nothing is denied. I immediately ran the query, and got the same error as my original post. Let me know if there's something additional I should do (reboot, wait for replication of some sort, etc.).

    Thanks

  • July 10, 2013 at 1:21 pm

    #1631374

    erikd (7/10/2013)

    I've given the clients\SQLService user explicit rights to the folder. from Full Control on down. Nothing is denied. I immediately ran the query, and got the same error as my original post. Let me know if there's something additional I should do (reboot, wait for replication of some sort, etc.).

    Thanks

    Did you apply the change to existing files? I'm guessing that might be it..

    CEWII

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply