Deployment of SSIS project that fails and causes the destination SQL Server to stop

Question

Deployment of SSIS project that fails and causes the destination SQL Server to stop

Yu, Xueke (JP - AB 余雪柯) 0

Hello,

I am trying to deploy an SSIS project using the wizard.

The deployment fails with the following error message.

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The handle is invalid.)
And it always seems to cause the destination SQL Server to stop.

Is there a way to solve this? Thanks in advance.

Best,

Roxie

ZoeHui-MSFT 41,491 Reputation points

2023-12-01T01:53:52.3366667+00:00

Please look in the SQL Server errorlog to see if there are any messages which seem to correlate with this error.
Yu, Xueke (JP - AB 余雪柯) 0 Reputation points

2023-12-01T02:08:34.7966667+00:00

@ZoeHui-MSFT
Thank you for the suggestion, the following errors were logged.

12/01/2023 10:01:37,spid58,不明,Audit: Server Audit: 65536<c/> State changed from: TARGET_CREATION_FAILED to: SHUTTING_DOWN

12/01/2023 10:01:37,spid58,不明,Trace ID '1' was stopped because of an error. Cause: 16534123(failed to retrieve text for this error. Reason: 317). Restart the trace after correcting the problem.

12/01/2023 10:01:37,spid58,不明,SQL Trace was stopped due to server shutdown. Trace ID = '2'. This is an informational message only; no user action is required.

12/01/2023 10:01:37,spid58,不明,SQL Server shutdown has been initiated

12/01/2023 10:01:37,spid58,不明,An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space<c/> and then restart SQL Server. If the problem persists<c/> disable auditing by starting the server at the command prompt with the "-f" switch<c/> and using SP_CONFIGURE.

Sorry for the Japanese Kanji character in the middle, it just means unknown.

Does this mean anything?

1 answer

Your answer

ZoeHui-MSFT 41,491 Reputation points

2023-12-01T01:53:52.3366667+00:00

Please look in the SQL Server errorlog to see if there are any messages which seem to correlate with this error.
Yu, Xueke (JP - AB 余雪柯) 0 Reputation points

2023-12-01T02:08:34.7966667+00:00

@ZoeHui-MSFT
Thank you for the suggestion, the following errors were logged.

12/01/2023 10:01:37,spid58,不明,Audit: Server Audit: 65536<c/> State changed from: TARGET_CREATION_FAILED to: SHUTTING_DOWN

12/01/2023 10:01:37,spid58,不明,Trace ID '1' was stopped because of an error. Cause: 16534123(failed to retrieve text for this error. Reason: 317). Restart the trace after correcting the problem.

12/01/2023 10:01:37,spid58,不明,SQL Trace was stopped due to server shutdown. Trace ID = '2'. This is an informational message only; no user action is required.

12/01/2023 10:01:37,spid58,不明,SQL Server shutdown has been initiated

12/01/2023 10:01:37,spid58,不明,An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space<c/> and then restart SQL Server. If the problem persists<c/> disable auditing by starting the server at the command prompt with the "-f" switch<c/> and using SP_CONFIGURE.

Sorry for the Japanese Kanji character in the middle, it just means unknown.

Does this mean anything?

Answer 1

ZoeHui-MSFT 41,491

Hi @Yu, Xueke (JP - AB 余雪柯),

You may select @@version to check your SQL Server version.

There is a known issue when running Microsoft SQL Server 2016 and 2017 and have c2 audit mode enabled. After you install Service Pack 2 (SP2) on the server, the SQL Service shuts down, and you receive an error message that resembles the following:

An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the "-f" switch, and using SP_CONFIGURE.

Resolution

A fix for this issue is included in the following cumulative updates for SQL Server:

Cumulative Update 11 for SQL Server 2017

Cumulative Update 3 for SQL Server 2016 Service Pack 2.

You may also check An Error Occurred While Writing an Audit Trace. SQL Server is Shutting Downto see if it is helpful.

Regards，

Zoe Hui

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

Yu, Xueke (JP - AB 余雪柯) 0 Reputation points

2023-12-01T02:53:35.6233333+00:00

@ZoeHui-MSFT Thank you for the suggestion.

However, it seems that I cannot simply update the version of Microsoft SQL Server. Is it possible to resolve this issue by for example changing the version of the SSIS package that I am creating? Any advice is highly appreciated, thanks in advance!
ZoeHui-MSFT 41,491 Reputation points

2023-12-01T03:30:17.2266667+00:00

Hi @Yu, Xueke (JP - AB 余雪柯),

What about disable the C2 audit mode?

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. The C2 security standard has been superseded by Common Criteria Certification. See the common criteria compliance enabled Server Configuration Option.

c2 audit mode (server configuration option)
Yu, Xueke (JP - AB 余雪柯) 0 Reputation points

2023-12-01T07:26:42.15+00:00

@ZoeHui-MSFT
Thank you for the alternative suggestion. While I do have to check if this change in settings can be done, there's an update to my attempts in deployment and I wonder if it may give a fuller picture of the problem.

I tried to deploy not the entire project, but the package alone.

This time the SQL Server did not stop and a different error message was given that says "Object Reference not set to an instance of an object".

I looked it up and it seems to suggest that the .Net Framework version of the Script Task in my package is not the same as that for the SQL Server?

I wonder if this is caused by the same reason stated above that the versions of the Visual Studio and the SQL Server are not compatible?

Share via

Deployment of SSIS project that fails and causes the destination SQL Server to stop

1 answer

Your answer