October 18, 2018 at 11:46 am
Scenario: Developers are running an OPENROWSET (BULK) query that accesses a remote file server via UNC path. Some of the connections are failing with Access Denied. I've traced the issue to the following pattern:
Case Client machine SQL Logon Credentials SQL Impersonation on Resource Result
1 Workstation SQL Authentication SQLSERVER$ machine account Success (expected behavior based on documentation)
2 SQL Server Windows Authentication (Domain User) Domain\USER of the logged on user Success (expected behavior based on documentation)
3 Workstation Windows Authentication (Domain User) ANONYMOUS LOGON Failure ( This is the one that doesn't make sense to me)
Question: So, where is the documentation that explains why in case 3 even though I'm using a Domain based logon to access my SQL Server is it not impersonating my account when it reaches out to the remote resource?
I'm sure that I've overlooked some comment somewhere, but I'm 48 hours into this one. I'm starting to just go in loops with all my searches.
Thanks,
Rob
October 22, 2018 at 1:26 pm
robr-793239 - Thursday, October 18, 2018 11:46 AMScenario: Developers are running an OPENROWSET (BULK) query that accesses a remote file server via UNC path. Some of the connections are failing with Access Denied. I've traced the issue to the following pattern:Case Client machine SQL Logon Credentials SQL Impersonation on Resource Result
1 Workstation SQL Authentication SQLSERVER$ machine account Success (expected behavior based on documentation)
2 SQL Server Windows Authentication (Domain User) Domain\USER of the logged on user Success (expected behavior based on documentation)
3 Workstation Windows Authentication (Domain User) ANONYMOUS LOGON Failure ( This is the one that doesn't make sense to me)Question: So, where is the documentation that explains why in case 3 even though I'm using a Domain based logon to access my SQL Server is it not impersonating my account when it reaches out to the remote resource?
I'm sure that I've overlooked some comment somewhere, but I'm 48 hours into this one. I'm starting to just go in loops with all my searches.Thanks,
Rob
Are you using s stored procedure utilizing execute as or a signed stored procedure? Or are the Impersonate logins you listed really just the security context used when accessing the share? It seems like that may be what that "SQL Impersonation on Resource" indicates rather than something with impersonation.
From the information posted, it looks more like an issue with a double hop and security credentials not being passed. And that would be related to kerberos.
Some general information on the double hop issue and some further links can be found in this article:
The Kerberos Double Hop Problem
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply