SQL SERVER 2022 - certificate error when creating a job for integration services

Marcio Matos 100

In a test environment with SQL Server 2022 Developer Edition, I am trying to create a job to run an SSIS package.

These same packages have been successfully tested in SQL 2019.

When trying to create the job, configuring it as follows:

General / Owner : Administrator, I also tried with NT SERVICE\SQLSERVERAGENT

New Step:

type: SQL SERVER INTEGRATION SERVICES PACKAGE,

Package Source: SSIS Catalog

Source: localhost

Log on to the server: use windows authentication (only option for localhost)

When I click on the button to select the package, an error occurred:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

ADDITIONAL INFORMATION:

The certificate chain was issued by an authority that is not trusted

I am logged into the Database Engine using the Windows Authentication option.

Is it really mandatory to use a certificate when running the package on localhost ? Any tips on how to resolve this problem?

Additional Notes: After installing sql 2022, I installed "Microsoft Sql Server Native Client"

MikeyQiaoMSFT-0444 3,190 Reputation points

2024-04-19T01:32:17.9866667+00:00

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Marcio Matos 100 Reputation points

2024-04-23T13:01:03.8+00:00

The issue is that I'm not trying to connect remotely, I'm trying to connect locally on the machine where SQL Server is installed. In fact, I'm already connected. The problem occurs when creating the job, when entering localhost and trying to select the package.

It doesn't make sense to allow connecting normally to the DataBase Engine, and after connecting, when trying to create a JOB, a certificate problem occurs, considering that everything is localhost.
Marcio Matos 100 Reputation points

2024-04-23T13:01:17.8+00:00

I understand, but creating a JOB in sql agent, for executing SSIS packages, does not accept a connection string. It only accepts connection via "use windows authentication" and the field to enter the name or IP of the server. And in this server field I am reporting localhost.
4310-gfi 15 Reputation points

2024-05-07T17:07:21.5333333+00:00

I have exactly the same issue. It seems like the connection to the SQL server is established but the access of the SSIS catalog is then denied.
shadowfoxish 5 Reputation points

2024-05-13T16:06:06.6233333+00:00

Also seeing this in our systems.

Occurs when trying to create a job that uses a SSIS package from the catalog. The catalog DB is not accessible from SSMS. When the job is created via scripting it seems to work without trouble so it definitely makes sense this is a management studio bug.

The stack trace is interesting, and may be illuminating

at Microsoft.DataTransformationServices.DTSExecUI.Controls.GeneralViewCtrl.GetSSISPackage()

===================================

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Framework Microsoft SqlClient Data Provider)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver--2146893019-database-engine-error

Server Name: xxxxxxx-sql01 Error Number: -2146893019 Severity: 20 State: 0

Program Location:

at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(SNIHandle handle, SNIPacket packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate) at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate) at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, SqlConnectionEncryptOption encrypt) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword, SqlConnectionEncryptOption encrypt) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open() at Microsoft.DataTransformationServices.DTSExecUI.Controls.GeneralViewCtrl.GetSSISPackage()

===================================

The certificate chain was issued by an authority that is not trusted
Holly Cross 10 Reputation points

2024-05-30T18:46:01.4333333+00:00

I am having the exact same issue after upgrading to SSMS 20.0.70.0. I cannot add a package to a job.

I already set the connection to Trust Server Certificate.

Appears to be a bug in SSMS 20.

10 answers

MJ 30 Reputation points

2024-04-16T15:09:40.3633333+00:00

I have the same issue on an instance where no SSL certificate is installed.

I found out that it only occurs when using SSMS 20. In SSMS 19 the creation of the job for SSIS works fine.
Please sign in to rate this answer.

6 people found this answer helpful.
Marcio Matos 100 Reputation points

2024-04-23T12:58:38.6866667+00:00

Exactly. Did you manage to resolve the problem?

Brian 0 Reputation points

2024-04-23T18:14:16.3666667+00:00

This is exactly the issue I'm having. Additionally the job is using SA

Democracy Officer 0 Reputation points

2024-04-28T21:20:04.65+00:00

Also, I'm having exactly the same problem. Note that I'm running against SQL Server 2022 Microsoft SQL Server Developer (64-bit). Certificates are what MS installed.

Jacques Naude 0 Reputation points

2024-06-10T06:40:26.0666667+00:00

Also, can't create a job to run an SSIS package using SSMS 20, had no problem using v19
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Carl BRUBAKER 25 Reputation points

2024-07-12T08:27:45.2466667+00:00

Hooray! It seems to me the issue is solved in the latest release of SSMS version 20.2.30.0.
Please sign in to rate this answer.

5 people found this answer helpful.
Holly Cross 10 Reputation points

2024-07-16T18:12:25.37+00:00

Just upgraded to 20.2.30.0 and the issue has been resolved. Thank you!

Yarllysson Neves 0 Reputation points

2024-08-12T22:49:14.97+00:00

Para reforçar a mensagem do Carl, passei pelo mesmo problema no SQL Server 2017, e ao instalar a versão indicada (20.2.30.0) do SSMS, o problema foi resolvido.

Homer Lee 0 Reputation points

2024-09-06T10:16:01.66+00:00

Me too. I was having issue on version 20.1, but now is fine. (20.2.30.0)
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-05-09T06:40:33.5466667+00:00

Despite the SSMS version being updated to 20.1, issues with SSIS connectivity still persist as a known problem. The only solution remains to use the older version 19.3.

Refer to this.
Please sign in to rate this answer.

3 people found this answer helpful.
4310-gfi 15 Reputation points

2024-05-13T10:29:21.4633333+00:00

Thank you, I didn't notice this beeing listed as know issue. Lesson taken: don't update too early to a new version.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
na 141 Reputation points

2024-05-29T15:14:11.22+00:00

The issue is that we are not connecting to a database instance via SSMS for querying the db, we are connecting within an agent job step, to the SSIS catalog.
There is no option to trust connection or encrypt connection.
The only option is to use an old version of SSMS
Please sign in to rate this answer.

2 people found this answer helpful.
Goulston, Adam 0 Reputation points

2024-06-12T16:57:41.66+00:00

Thanks for the advice about SQL Server Management Studio.

I am using Version 20 which is where the problem manifested itself.

I subsequently installed Version 18.5 and created the job step with details of the SSIS Package from the SSIS Catalogue with no problem at all.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-04-11T01:47:05.4866667+00:00

Hi，Marcio Matos

SSL verification is required whenever you need to connect to a database engine instance.

This article may help you fix this issue：

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/error-message-when-you-connect?source=recommendations

Best regards

Mikey Qiao

Hope my answer may help you.
Please sign in to rate this answer.
4310-gfi 15 Reputation points

2024-05-08T07:23:31.8633333+00:00

Hi Mikey, do you see a solution to the issue for these cases when the connection is initiated by a SQL server agent job step?

MikeyQiaoMSFT-0444 3,190 Reputation points

2024-05-08T09:54:32.65+00:00

Try the CMD command

netsh winsock reset

na 141 Reputation points

2024-05-29T15:12:10.11+00:00

This is fine when connecting to a database instance, but the issue here is that when connecting to an SSIS Catalog in an Agent job step, there is no checkbox or option to trust cert or encrypt connection.

ssis conn.JPG
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL SERVER 2022 - certificate error when creating a job for integration services

10 answers

Your answer