0

I have made a Linux Docker container performing several tasks, and finally connecting to a SQL Server 2019 (v15), and executing BULK INSERT to load a local (to the SQL Server) .csv file. The connection is made using SQL Server authentication.

As I realized, this isn't possible, as opposed to Windows authenticated users who have access to said files, SQL Server authenticated users have the permissions of the SQL Server process account, which doesn't have access to said files.

Relevant documentation by Microsoft:

https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16#security

It is advised to grant those permissions to the SQL Server process account, but I am afraid this won't be possible in a corporate production setting.

Are you aware of any workarounds?

I tried impersonating a Windows user, but that won't work since in that scenario the original login security context is taken into account (ie the SQL Server login).

I am thinking of using DATABASE SCOPED CREDENTIALS and EXTERNAL SOURCE, but I am not entirely sure that this has any connection to my problem, and the documentation is mainly about cloud, not local storage.

The final solution I think will be through Active Directory and Kerberos (?) but be it that I am using containers I think that will be the hardest one.

Any thoughts and recommendations are welcome.

Thanks in advance.

Improve this question
6
  • 1
    There's no way you're connect to SQL Server 19; it's not out yet. The latest release is version 16 (2022). Commented Mar 29, 2024 at 13:03
  • It was a typo thank you, its 15. Edited it Commented Mar 29, 2024 at 13:06
  • 1
    How does the file get onto the server hosting the SQL Server instance? Why can't it be moved or copied to a location that's accessible to the account under which the SQL Server instance executes? (Since you mention AD, that location could probably even be a file share setup for the purpose.) Commented Mar 29, 2024 at 13:07
  • And why can't you give the service account permissions if it needs them? Being in a corporate environment isn't a blocker for that; I discuss access for service accounts for our instances with our Network Administration team at the office all the time when working with migration and ETL projects. Commented Mar 29, 2024 at 13:09
  • " I am afraid this won't be possible in a corporate production setting." speak to your IT support, this is not something we can fix. I don't understand how they are happy for you to access the SQL Server remotely, but not happy for the service user to have access to that one folder. Commented Mar 29, 2024 at 15:42
Related questions
4343
How do I UPDATE from a SELECT in SQL Server?
2450
Concatenate text from multiple rows into a single text string
1076
How can I get column names from a table in SQL Server?
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.