SQL Server – Bulk Insert Failed from SSRS Dataset but Succeeds 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.

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.

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.
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).

Related Solutions

Sql-server – How to Resolve a Case of “The tablix ‘xyz’ contains an invalid DataSetName in SSRS

I found a solution, so am posting for others to benefit.

The tablix receiving the warning was a tablix nested inside of another tablix. I had checked and rechecked that nested tablix, and nothing in the Visual Studio GUI was amiss. It was reporting the correct dataset.

Finally I resorted to opening up an XML editor and opened the RDL file, and sure enough, the XML file had the wrong dataset in the XML.

It was in the definition of the tablix, at this point:

<DataSetName>DataSet2</DataSetName>
<Left>0.74in</Left>
<Height>0.875in</Height>
<Width>5.64866in</Width>
<Style>
  <Border>
    <Color>LightGrey</Color>
    <Style>Solid</Style>
  </Border>
</Style>

The solution was to delete the

<DataSetName>DataSet2</DataSetName>

line from the XML entirely, because a nested tablix should not specify a dataset name, because by definition a nested tablix must use the dataset from the outer tablix.

The reason why this warning had come about was because originally I created the tablix as a standalone tablix, so it did have a dataset. After some report changes, I copied and pasted the tablix into another tablix. At that point, apparently, visual studio kept the dataset name in the paste, but did not support changing it from the GUI.

Sql-server – SQL Bulk Insert Impersonation issue

OK, here is the answer to my particular problem. While the commenters above were very helpful and had some good suggestions, like manually adding the SPN, there was one final part missing. You know, one of those that has to be completed on a dark stormy third Thursday of the month, only when the moon is waxing half way...

After a few days after this posting, I basically posted the same question on the Microsoft SQL forums to get a fresh prospective. Long story short, I had to have my network admin run adsiedit.msc and have him edit the service's domain account user. The userAccountControl attribute, which is a number that represents a bitmap fields had to be updated. I had to take a single existing number, 0x10200 (NORMAL_ACCOUNT | DONT_EXPIRE_PASSWORD) and OR in 0x80000 (TRUSTED_FOR_DELEGATION) and OR in 0x1000000 (TRUSTED_TO_AUTH_FOR_DELEGATION) for a grand total of 0x1090200!

Now, isn't that user friendly! I honestly don't know why that when we setup the account in the GUI for delegation that it did not set these bits too, but it didn't.

I now have Bulk Insert running correctly for a trusted connection (Windows Authentication) and impersonating correctly on two out of three of my SQL Servers. Not sure why it is not one the one, but that is for a different day...

Best Answer

Related Solutions

Sql-server – How to Resolve a Case of “The tablix ‘xyz’ contains an invalid DataSetName in SSRS

Sql-server – SQL Bulk Insert Impersonation issue

Related Question