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

Question

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

Marcio Matos 115

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,220 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 115 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 115 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

Your answer

MikeyQiaoMSFT-0444 3,220 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 115 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 115 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.
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.

Answer 1

MJ 30

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.

Marcio Matos 115 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

Answer 2

Carl BRUBAKER 25

Hooray! It seems to me the issue is solved in the latest release of SSMS version 20.2.30.0.

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)

Answer 3

MikeyQiaoMSFT-0444 3,220

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.

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.

Answer 4

na 146

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
ssis conn

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.

Answer 5

Brenda Woods 5

Same issue here. You can log in with SSMS 2.0 with the cert trust or with minimal but it doesn't matter. Jobs steps clearly cannot connect to ssis using this version of SSMS. Does Microsoft ever test any of their software they send to us???? new ssms versions ALWAYS have issues.

Thanks for the information here, saved me a TON of headaches.

Tim Hampton (SSI) 0 Reputation points

2024-06-25T15:59:13.4066667+00:00

We are also experiencing this problem. It also occurs when clicking the Edit button for an existing step on an existing job, where the step is for executing an SSIS package. (I had noticed that previous commenters mentioned this error occurring when creating a new job, and wanted to make sure it was clear that it's happening for existing jobs as well.) It also occurs when adding a new step to an existing job, and after having selected "SQL Server Integration Services Package" in the 'Type' drop-down and entered the server name in the 'Server' textbox, you click the '...' button next to the 'Package' textbox in order to select the package you wish to run for the step.

I have tried the workarounds mentioned by previous commenters re: TrustServerCertificate=True and Encrypt=False and it doesn't fix the issue for me.

The problem does not occur in SSMS 19 (version 18.12.1).

Share via

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

10 answers

Your answer