Share via

gMSA and Integration Services

pGwtech1234 6 Reputation points
Jul 12, 2021, 3:29 PM

Greetings,

I recently configure SQLServer and Integration Services to use a gMSA.

My maintenance plans (using CIFS address) are now failing with the following error,

Failed:(-1073548784) Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'\\<CIFS_PATH>..." failed with the following error: "xp_delete_file() returned error 2, 'The system cannot find the file specified.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:EXECUTE master.dbo.xp_delete_file 0,N''\<CIFS_PATH>\MISDEVDB21'',N''bak'',N''2021-05-10T12:50:16'',1

Already reviewed what was provided in MSDN and legacy TechNet, suggesting to change to powershell. However, I don't want to change over to a powershell script as I would like eventually use gMSA across all SQLServers and have all Integration Services jobs run successfully.

I have also attempted to execute the T-SQL manually from a domain account with sysadmin access and access to the <CIFS_PATH>, with the same above result.

Further, if I change the SQLServer and Integration Services service to the previous domain svc account, it works fine.

Any guidance would be appreciated.

Regards,
Pierson

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,643 questions
0 comments No comments
{count} vote

3 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 40,586 Reputation points
    Jul 13, 2021, 6:35 AM

    Hi @pGwtech1234 ,

    It seems the error message is related with account permission.

    Do you have a proxy account setup for SSIS subsystem which you've configured for running the sql agent job steps which executes the packages?

    If not, regardless of whether its a gMsa account or not, any changes to service account can break packages due to insufficient privileges. Otherwise you need to replicate the permissions that are given to current service account also to your gMsa account before you switch over to it.

    Not familiar with gMSA, hope it could give you some idea.

    Regards,

    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    0 comments No comments

  2. COZYROC 101 Reputation points
    Jul 13, 2021, 10:58 AM

    What is gMSA?

    0 comments No comments

  3. pGwtech1234 6 Reputation points
    Jul 13, 2021, 11:48 AM

    Hi there,

    Thanks for replies.
    Yes, I have created a credential and proxy and attempted to use it in the SSIS package, with same result.

    gMSA = Group Managed Service Account

    I now have a case open with MSFT, here are additional details to help community solve.

    Product:
    SQL Server 2019 Integration Services (SSIS) Windows
    Category:
    Issue or errors in package execution
    Title:
    gMSA restrictions on execution
    Description:
    Please describe the issue in 2-3 sentences. Include what you're trying to accomplish when issue occurs.
    Once gMSA configured on SQLENG,SSIS services, SSIS jobs fail with the error below (i.e. Maintenance Plans referencing CIFS share). We are evaluating gMSA for a FILESTREAM configuration and further to use across all SQLServers.

    When did it begin and how often does it occur? What have you tried to troubleshoot this?
    If I remove gMSA, works fine.
    Same result with a working domain account using credential,proxy configuration.

    Similarly, executing a T-SQL command to delete a backup file using xp_delete_file() fails to CIFS share, but not local.

    What errors do you see?
    Failed:(-1073548784) Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'\\<CIFS_share_address>..." failed with the following error: "xp_delete_file() returned error 2, 'The system cannot find the file specified.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Command:EXECUTE master.dbo.xp_delete_file 0,N''\<CIFS_share_address>\<BackupFolder>'',N''bak'',N''2021-05-10T12:50:16'',1

    What's the environment and are there recent changes?
    Standalone SQLServer2019 with SQLENG,SSIS,SQLAGT configured, Backups to CIFS_Share.

    The gMSA has local admin, so assuming that the gMSA being used for internal commands and not the windows authentication of the current calling account or proxy.

    --
    Please advise on what's the recommended configuration to accomodate SSIS packages executions from jobs and T-SQL commands (using the calling user account (preferred) and/or proxy).


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.