Sql-server – Delegation: Bulk Insert failed when executed from SSRS dataset but success from SSMS

bulk-insertimpersonationsql serverssrs

I've configured delegation for SQL server engine and SSRS and it works fine.

SQL Server (2017) engine and SSRS installd on the same machine. Let's call it SqlSrv1.

What we should attain to:
User opens a report with a dataset which joins a database data and an external shared file (with sensitive data) via impersonation. If user has no access to a shared file, report should display him an empty sensitive columns.

  1. When I execute BULK INSERT via SSMS, something like
BULK INSERT #t1 FROM '\\FS1\common\anyfile.txt'

it works fine. Computer Management shows my name (instead of sqlengine service account) in "Shared folders\Sessions" as User and SqlSrv1 as Computer.

  1. Now when I execute BULK INSERT from SSRS report dataset against a local file (stored on SqlSrv1 C:\anyfile.txt) it works and ProcMon displays my name in Detail column, instead of ssrs service account.

  2. At last I execute BULK INSERT from SSRS report dataset against a shared file \\FS1\common\anyfile.txt and I get error Operating system error 5(Access is denied.), but ProcMon displays my name in Detail column as Impersonating.

There is a something strange I've located:
When I change Data Source in connection string of dataset to FQDN, BULK INSERT begones to work from SSRS. After that I change back Data Source to NetBIOS but BULK INSERT continues to work. What is going on?

All fine and dandy, but after restart SSRS service I get error Operating system error 5(Access is denied.) again.

When I execute the query from the step 1, ProcMon shows me the path
\\FS1\common\\anyfile.txt (with double slash after shared folder name! What?!!)
instead of
\\FS1\common\anyfile.txt

When I get error 5, ProcMon shows
\\FS1\common\anyfile.txt (without double slash)

But when the step 3 begones to work (after FQDN manipulations), OMG! The ProcMon shows me
\\FS1\common\\anyfile.txt (with double slash)
and after SSRS service restart I see in ProcMon
\\FS1\common\anyfile.txt (without double slash)
I've tryed to add double slash to BULK INSERT, but with no luck, SQL engine drops it (as expected).

Have you any ideas?

Best Answer

I'm glad I asked what your end goal was. I was going to reply in a comment but it turned into too long of a response.

Your best bet is honestly to close this question and ask a new one on "best ways to implement data encryption and obfuscation across multiple databases?" with details on each type of data system you're using. There's a multitude of ways to accomplish what your customer wants with out-of-the-box features of SQL Server without rigging together a roundabout solution.

One of them is Encryption another is Dynamic Data Masking, and finally understanding proper Permissioning in SQL Server are all standard and potential ways to hide your customer's data, even from developers.

All three of those previously mentioned plus many other features, are applicable to the databases that are utilized by the systems you mentioned (SQL Server version dependent).