May 5, 2021 at 3:53 pm
I have a problem that appears to be the same problem as this one:
However, the solution in that post doesn't work for my situation since I can't use Agent to run the package/project.
I can't use Agent, because I need a user to run the project on demand. I have recently learned about the SSIS Catalog and it's ability to run packages via t-sql/on-demand. I successfully created the SSIS Catalog and the t-sql works to start the Catalog project. However, even when I'm just right-clicking and running the package manually, the project produced the following errors when it tries to access .csv files on our LAN/file server:
" Cannot open the datafile "path and filename.csv" "
" Access denied. "
" Flat File Source failed the pre-execute phase and returned error code 0xC020200E. "
FYI: The path for the file is using UNC syntax.
The project runs just fine when I run it manually through Visual Studio. This appears to be a permissions problem, however I log onto SSMS/SQL Server through Windows Authentication. In other words, I'm on SSMS with the same account that I'm Visual Studio. Previously I had understood that running an SSIS package in the legacy deployment system on SSMS would use my own account when run manually on SSMS (and a different account when run on Agent via the "Run As" property.) Since I'm running into a permissions problem, it looks like the Catalog project does not use my account. What account is it using?
My *guess* is that the Catalog is using the SSIS service account. I wanted to give the account running the SSIS service permissions to access the file server, but I’m told by our LAN Admins that that is not a secure option. The Admins want to create a GMSA (Group Managed Service Account) account that the SSIS project should run under and which has permission to access the file server.
MY QUESTION IS: How do I tell the Catalog to run the project under a different account? Agent has a "Run As" property. I don’t see anything like that in the Catalog. What am I missing?
NOTE: I did find the "Configure" property page for the project in SSMS.
> In the Configure page, there is a Parameters tab which has a Connection Managers tab.
> And in the Connection Managers tab, the .csv file Connection has a property for "ConnectByProxy".
> I can change that property to "True", but I can't find documentation on how to set the actual proxy value.
> Or maybe the "UserName" property is the value I need to change to get this to work. But again, I can't find documentation on what that property is supposed to control or what value I should put in the property.
May 5, 2021 at 4:03 pm
Sounds like a double hop issue to me: Your Host -> SQL Server Host (first hop) -> File Host (second hop). I would speak to your network administrator for assistance here.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 5, 2021 at 4:24 pm
Thanks for the reply Thom. Sadly, it is the network administrator who asked me to post on this forum since he doesn't know what to do.
I had not heard of a "double hop" before. I just looked it up and found this definition:
"Double hop issues are when you have a client connect to one SQL Server and that server needs to pull data from another SQL Server. The first server uses Windows Authentication credentials on the second server and the connection to the first SQL Server is made using Kerberos authentication." from: https://treehozz.com/what-is-double-hop-issue-in-sql-server
I'm actually trying to connect to a file server, not a second SQL Server, but maybe the same principle applies? Is that what you are thinking? I will ask my admins if we are using Kerberos anywhere.
Don't I still need to designate to the Catalog project which account to run the project under so that eventually a regular user can run the project via t-sql code/stored procedure???
Sorry, but this permissions/account stuff is outside my area of knowledge. I'm very confused. Thanks for any other insight you (or anyone) can provide.
May 5, 2021 at 4:55 pm
You can create a proxy account in SQL Server, using the credentials provided by your network folks. Use the proxy account in the SQL Agent step that executes the SSIS package. This of course will only work on scheduled jobs.
If you right-click on the package and execute it, it should execute within the context of the logged-in user. Assuming that you are logged in with Windows authentication, it should work if your account also has access to the network drive.
Could the issue be the default parameters and/or variables values? The path may not be fully set, or the file may not exist during the pre-execute phase, or the path isn't accessible. Try using the fully qualified path...I've seen lots of issues with name resolution in the past.
May 5, 2021 at 5:05 pm
I'm actually trying to connect to a file server, not a second SQL Server, but maybe the same principle applies? Is that what you are thinking? I will ask my admins if we are using Kerberos anywhere.
The same principle applies. The "double hop" issue isn't solely related to SQL Server, just that it many do meet it in SQL Server when dealing with things like linked servers, or in this case an SSIS package trying to access a file on a different host (to SQL instance you have authenticated to).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 5, 2021 at 5:13 pm
Thanks for the reply Martin. I appreciate you taking the time.
Some points to note:
re: "If you right-click on the package and execute it, it should execute within the context of the logged-in user. Assuming that you are logged in with Windows authentication, it should work if your account also has access to the network drive."
Yes, THIS! This is what I had understood too. It's why I'm so confused. That's how it's supposed to work even with the Catalog? I know that's how it worked in the old legacy system, but I wasn't sure if the Catalog was different. This is one of my questions. I thought maybe the Catalog worked differently???
If that *is* how the Catalog is supposed to work too, then I wonder why I'm running into a problem. The problem might have something to do with what Thom was saying about "double hop"? I emailed my network administrators after seeing Thom's reply to see if there's any chance that this could be the problem. I'm awaiting a reply.
May 5, 2021 at 5:15 pm
Thanks for the clarification Thom! I emailed my network administrators with your information and asked if they could look at the problem as a 'double hop' issue. I will let you know what happens.
May 5, 2021 at 5:22 pm
You should see the user context in the Catalog reports, and if that doesn't work it's most likely Kerberos delegation.
One other thing to try: In stored procedures you can force the execution to a specific user context, by using "execute as". Try that and see what happens.
Also, have you tried to create a local shared drive as a possible solution?
May 5, 2021 at 8:03 pm
I know what you are saying about creating a proxy. I know how to do that and how to assign the proxy in Agent. The problem is that I can't use Agent for this job. I need the users to be able to run the project on-demand through t-sql. That's why I'm using the Catalog. (However, also note that when I load the SSIS package the old way into MSDB, the same error occurs when I run the package manually on SQL server in the legacy deployment method. It's just that I really need the job to run outside of Agent and I'm not seeing how to use a proxy for that. And I don't see why my account doesn't work unless Thom's answer was a clue.) I don't see how to assign a proxy when running a Catalog project through t-sql. Let me know if I'm missing something.
Can you explain the process and why it must be user initiated? Could you setup an agent job to run every xx minutes - and put a check in the package for a specific condition? If that condition is met - execute the rest of the package, else exit?
The problem here is going to be permissions - and the only way around those permissions is using a proxy account from the agent. You won't be able to use EXECUTE AS or let the user run it directly from the catalog. The only thing that will work is to setup an agent job with no schedule - and a stored procedure the user can execute that starts the agent job.
re: "If you right-click on the package and execute it, it should execute within the context of the logged-in user. Assuming that you are logged in with Windows authentication, it should work if your account also has access to the network drive."
Yes, THIS! This is what I had understood too. It's why I'm so confused. That's how it's supposed to work even with the Catalog? I know that's how it worked in the old legacy system, but I wasn't sure if the Catalog was different. This is one of my questions. I thought maybe the Catalog worked differently???
This is the double-hop issue - the user is authenticated on their workstation, connecting to SQL Server through SSMS on a server (first hop). Then - they start a process that is being run from the server (right-click - execute) that needs to authenticate to the UNC (second hop).
If that *is* how the Catalog is supposed to work too, then I wonder why I'm running into a problem. The problem might have something to do with what Thom was saying about "double hop"? I emailed my network administrators after seeing Thom's reply to see if there's any chance that this could be the problem. I'm awaiting a reply.
If you have Kerberos setup correctly - with the appropriate SPN's configured - then the 'second' hop should work and authenticate using the user ticket generated from Windows. However - this may not work because the action of right-clicking and executing the package in SSMS may not be using the user credentials that logged into SSMS, and instead is using the context of the account running SQL Server (not the agent - and not the integration services service).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 5, 2021 at 9:06 pm
Also, have you tried to create a local shared drive as a possible solution?
Do you mean the following? : Copy the .csv files to the local hard drive on the SQL Server. Then the SQL Server shouldn't have any trouble accessing the .csv files.
If that's what you are thinking, I thought of the idea, but I was so sure that there was an easier answer/that it wouldn't be this complicated. However, I certainly could! do that if I could get the following to work without an double hop (sounds like a beer problem) problem: If I could create a batch file that the SSIS job can run which copies the files from our file server to the hard drive of the SQL Server, then that may be the work-around. I could test and see if the SSIS package would run the batch file (which would also have to be stored on the SQL Server hard drive I presume) without a permissions issue. Maybe I should give that a try and stop trying to get the double hop issue figured out?
May 5, 2021 at 9:11 pm
Jeffrey: Thanks for the reply and added information/explanation.
You asked why my user needs to run the job on demand. It's a fair question. Here's the situation:
In the past, when we got a data download from a State's database, we were able to either directly connect to the database to pull the data we need or we got an extract that was run nightly and SFTPed. Users did not have to be part of the data transfer process at all. The new State's model of "cooperation" is to make us manually run "reports" from the State's application. A support staff person will run the reports and save the exported reports/.csv files to our file system. The staff person then needs the SSIS job to import the data into our database. She then will need to immediately look at our "Import Errors" front-end application and deal with any issues with merging or validating the data.
This process happens once a day and needs to happen only once. While I could technically set up the job to run at say 7:00 am every morning, it would be terrible service/not user-friendly. It would require the staff person to make sure she does part one of her task every morning before 7:00. What if she's running late? Too bad for that day? That's not acceptable. If she runs the job early, then then the staff person has to wait until after 7:00 to resolve the import errors, breaking up her work-flow. Bottom line: At best, this set up would not be efficient/not nice for the staff person. At worse, a day might be missed on getting the data uploaded into our database. That's not acceptable.
The goal is to allow the user to do all steps one after another without having to wait for Agent to do anything. The user would: 1) download the data from the State's database, 2) run the SSIS to bring the data into our database, 3) process Import Errors.
---------------------------------------------------
re: "The problem here is going to be permissions - and the only way around those permissions is using a proxy account from the agent. You won't be able to use EXECUTE AS or let the user run it directly from the catalog. The only thing that will work is to setup an agent job with no schedule - and a stored procedure the user can execute that starts the agent job."
It's good to know that my choices are limited/that I can't actually run the job from the server. It's disappointing news, but better to know it is not possible than to keep trying to make it work.
I didn't know that I could run an agent job via a stored procedure. I will try to find an example for that.
What do you think of Martin's idea of first trying to move the data files from our file server to a local drive on the SQL Server? SSIS should be able to access files on it's own hard drive? Maybe I could set up a batch file that is stored locally on the SQL Server and which moves the data files. The SSIS project could then run the batch file first thing?
------------------------------
re: "If you have Kerberos setup correctly - with the appropriate SPN's configured - then the 'second' hop should work and authenticate using the user ticket generated from Windows. However - this may not work because the action of right-clicking and executing the package in SSMS may not be using the user credentials that logged into SSMS, and instead is using the context of the account running SQL Server (not the agent - and not the integration services service)."
Is this saying that there is a solution that just involves magically fixing SPNs or somethign? If I share this paragraph with my network admins, should this be enough information for them to know how to fix the problem? Or is this just explaining to me why I'm stuck and need a work-around? (Sorry for my denseness. You definitely helped me get a better grasp on the issue. I just don't have the full grasp yet.)
May 5, 2021 at 9:30 pm
Martin Schoombee wrote:Also, have you tried to create a local shared drive as a possible solution?
Do you mean the following? : Copy the .csv files to the local hard drive on the SQL Server. Then the SQL Server shouldn't have any trouble accessing the .csv files.
If that's what you are thinking, I thought of the idea, but I was so sure that there was an easier answer/that it wouldn't be this complicated. However, I certainly could! do that if I could get the following to work without an double hop (sounds like a beer problem) problem: If I could create a batch file that the SSIS job can run which copies the files from our file server to the hard drive of the SQL Server, then that may be the work-around. I could test and see if the SSIS package would run the batch file (which would also have to be stored on the SQL Server hard drive I presume) without a permissions issue. Maybe I should give that a try and stop trying to get the double hop issue figured out?
You are still looking at this from the point of view of the user starting the process - why?
It seems this is a process that loads data into SQL Server from defined CSV files - which can be scheduled to run every xx minutes. The user places the file(s) into the folder to be processed and the next scheduled run picks the files up and loads them.
No need to have the user involved in starting the job.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 5, 2021 at 9:32 pm
re: "However - this may not work because the action of right-clicking and executing the package in SSMS may not be using the user credentials that logged into SSMS, and instead is using the context of the account running SQL Server (not the agent - and not the integration services service)."
I just absorbed what this sentence is saying. I understand it. And that makes perfect sense to me.
So, that brings us back to my network admins saying that giving permissions to the account running SQL Server would be a security problem. Does that sound right to you? Do people normally give the SQL Server service account permissions to access file servers? Or is that considered a bad practice?
May 5, 2021 at 9:38 pm
re: "It seems this is a process that loads data into SQL Server from defined CSV files - which can be scheduled to run every xx minutes."
I don't think I can do that. When the package is run, the old import data is first cleared (thereby clearing all the match and validation errors), then the new data is loaded, validated, matched and merged (for those records with no errors). I can't keep deleting all the match and validation errors every xx minutes since that could remove the data while the user is working on it. Plus, it wouldn't be very efficient to merge the same data into the main database more than once a day.
I maybe missing something, but it doesn't seem possible nor practical to run the same import job more than once a day.
May 5, 2021 at 10:05 pm
Yes, you are missing something.
You could process once a day before business starts. The user works the errors from yesterday and downloads the new file and places it in the folder to be processed.
Or, you schedule it more frequently.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply