question

roppierson avatar image
0 Votes"
roppierson asked ZoeHui-MSFT commented

gMSA and Integration Services

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @roppierson,

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

COZYROC avatar image
0 Votes"
COZYROC Suspended answered

What is gMSA?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

roppierson avatar image
0 Votes"
roppierson answered ZoeHui-MSFT commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your sharing, if there is any workaround or resolution, you may also share with us. :😁

0 Votes 0 ·