0

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?

Improve this question
4
  • Are you keeping the target file in the root folder ? Try creating a subfolder and applying the appropriate permissions there
    – Stephen Morris - Mo64
    Jan 27, 2021 at 9:51
  • @mo64, it's just for test and example. Ofcourse I don't keep it in the root. In regards to permission for share, All domain users can read and write
    – TJS
    Jan 27, 2021 at 11:09
  • 1
    This sounds like a very odd process that there's probably an easier solution for. What is your end goal that you need to BULK INSERT an SSRS dataset and a File together? Also a little unclear if you're saying you're trying to BULK INSERT into an SSRS dataset, in conjunction with one, or from one, as I see no mention of the SSRS report or dataset at all?
    – J.D.
    Jan 27, 2021 at 12:32
  • @J.D., My end goal is to use a secret part of data in conjunction with MDS databese and DWH, but our customer wants to to hide his secret data from not only most of employes but from developers too. MDS and likewise SSRS does not support encryption, as far as I know. Therefore I can't see another way except shared folder containing file with a part of a secret data. This way allows to develop and to debug with any shared folder containing a sample file and use a report with another path (to real file) in production environment.
    – TJS
    Jan 27, 2021 at 15:43

2 Answers 2

Reset to default
0

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

Improve this answer
11
  • Thank you for your reply. But I can't take advantage of your suggestion, because of MDS does not support DDM and encryption. There is no another GUI to manage data except MDS. Of course there is a few business systems in a customer infrastructure, but this report (and others) goes beyond the purposes of that systems. Developers and DBAs have admin permissions to MDS.
    – TJS
    Jan 27, 2021 at 21:59
  • I understand this extended permissions is not the best practices, but I can't affect it and therefore I can't guarantee data safity to my customer. And only file storage server have one person with admin permissions and who signed a secrecy agreement. This fact led me to use "duct tape" as a shared file.
    – TJS
    Jan 27, 2021 at 21:59
  • @TJS While you think you're solving the problem, even duct tape falls apart over time. Unless you're the highest level IT admin at the company, and you don't have a boss above you (technical or not) your solution has a lot of risk, e.g. what stops the IT Admin from undoing the file share permissions you put in place? All the solutions I mentioned above apply because MDS uses a regular SQL Server database under the hood: "Master Data Services uses a Microsoft SQL Server database as the physical data store".
    – J.D.
    Jan 27, 2021 at 22:16
  • I don't understand how can I bring MDS (WebApp, Excel Add-in) to decrypt a column with the Always encrypted option
    – TJS
    Jan 28, 2021 at 12:28
  • @TJS MDS has to store that centralized data somewhere, which is a SQL Server database. You'll have to talk to your DBA, the person who setup MDS / the database, or research into where that database lives in your organization. Then you should be able to connect to it like any other SQL Server database and apply the same features as well. Hopefully this RedGate article helps explain the details to you. Without working at your organization, I couldn't tell you the location of your databases unfortunately.
    – J.D.
    Jan 28, 2021 at 14:02
0

Chrome browser does not request a kerberos ticket with delegation flag by default. Therefore when report was viewing via Chrome I get

Operating system error 5(Access is denied.)

exception, because it's as if anonymous user for endpoint, while IE request a kerberos ticket with delegation flag. And a report works fine in Chrome after it was open in IE previously.

See AuthNegotiateDelegateWhitelist for Chrome

I have found the issue with klist utility

PS. Double or single slashes in paths has no any relevance to this issue

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.