Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

OpenRowSet works locally but not over networked drives Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 10:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 299, Visits: 1,205
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

Post #1472238
Posted Wednesday, July 10, 2013 10:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #1472243
Posted Wednesday, July 10, 2013 10:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 12,750, Visits: 31,117
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472244
Posted Wednesday, July 10, 2013 10:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 299, Visits: 1,205
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

Post #1472249
Posted Wednesday, July 10, 2013 11:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #1472256
Posted Wednesday, July 10, 2013 11:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 299, Visits: 1,205
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?

Post #1472261
Posted Wednesday, July 10, 2013 11:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #1472265
Posted Wednesday, July 10, 2013 11:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 299, Visits: 1,205
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?

Post #1472268
Posted Wednesday, July 10, 2013 12:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #1472282
Posted Wednesday, July 10, 2013 12:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 299, Visits: 1,205
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?
Post #1472288
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse