Build Issue using SCA 4.6 and SQL Server 2022

daviep

I'm getting the error below after I upgraded SCA to 4.6 and SQL Server to 2022. Am I missing any installation details?

VS 2022 Version - 17.5.4
SQL Server 2022
SQL Change Automation  - 4.6.23102.1176

66>------ Rebuild All started: Project:  TW.DB, Configuration: Debug Any CPU ------

66>MSBUILD: Warning:  : Unable to connect to [ TW.DB_DAVIE405_SHADOW] in order to perform shadow clean: 

66>Error connecting to database: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: HTTP Provider, error: 0 - ): Error connecting to database: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: HTTP Provider, error: 0 - )

66>C:\Program Files\Microsoft Visual Studio\2022\Professional\MSBuild\SqlChangeAutomation\SqlChangeAutomation.Data.Schema.Ssdt.targets(455,5): Error:  MSB4181: The "DBCleanTask" task returned false but did not log an error.

66>Done building project " TW.DB.sqlproj" -- FAILED.

daviep

PS: Please note that I can connect via SSMS to SQL Server 2022

DanC

Hi @daviep

This is a known issue with the latest versions of Visual Studio 2022 when creating or using a project it, it's not able to set the trust server certificate through the UI. The developers are currently looking into it

You should be able to workaround this for now by modifying the connecting to the shadow database and adding the needed connection parameters

For example here's mine:

Data Source=(local)\DC_SQLSERVER2019;Initial Catalog=CertificateTestSCA;Integrated Security=True;Pooling=False;Encrypt=False;Trust Server Certificate=True;Multi Subnet Failover=False

https://documentation.red-gate.com/sca/developing-databases/concepts/shadow-database

eamonmaher

I am experiencing the same problem but with Visual Studio 2019. This is with projects that used to work fine with an earlier version of SQL Change Automation.

Here's the "About" details:

Microsoft Visual Studio Enterprise 2019

Version 16.11.21

VisualStudio.16.Release/16.11.21+33027.164

Microsoft .NET Framework

Version 4.8.04084

Installed Version: Enterprise

 -- snip --

Redgate SQL Change Automation   4.6.23102.1176

Extend DevOps processes to your SQL Server databases and safely automate database deployments.

Visit https://www.red-gate.com/sca/productpage for more information.

Copyright (C) Red Gate Software Ltd 2011-2023. All rights reserved.

This software contains components from Component Owl.

SQL Server is a registered trademark of Microsoft Corporation.

Visual Studio is a registered trademark of Microsoft Corporation.

SQL Change Automation contains code from the following open source software:

Redgate SQL Prompt   1.0.0.0

Write, format, and refactor SQL effortlessly

SQL Server Data Tools   16.0.62205.05200

Microsoft SQL Server Data Tools

SQL Server Integration Services   16.0.845.0

Microsoft SQL Server Integration Services Designer

Version 16.0.845.0

daviep

@DanC - I'm using SQL Server 2022 if that helps. Another update is now the VS2022 crashes after you open SCA. The eventvwr logged the error below.

Faulting application name: devenv.exe, version: 17.5.33530.505, time stamp: 0x64262540

Faulting module name: Microsoft.Data.SqlClient.SNI.x64.dll, version: 3.0.0.0, time stamp: 0x60b95c4c

Exception code: 0xc0000005

Fault offset: 0x000000000000dcf9

Faulting process id: 0xa8c

Faulting application start time: 0x01d9734ad0dff692

Faulting application path: C:\Program Files\Microsoft Visual Studio\2022\Professional\Common7\IDE\devenv.exe

Faulting module path: C:\Program Files\Microsoft Visual Studio\2022\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\Microsoft.Data.SqlClient.SNI.x64.dll

Report Id: 16a3938f-57f4-4d72-873c-0cd6a4a4b29e

Faulting package full name: 

Faulting package-relative application ID: 

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

Application: devenv.exe

Framework Version: v4.0.30319

Description: The process was terminated due to an unhandled exception.

Exception Info: System.AccessViolationException

   at Microsoft.Data.SqlClient.SNINativeManagedWrapperX64.SNIOpenSyncExWrapper(SNI_CLIENT_CONSUMER_INFO ByRef, IntPtr ByRef)

   at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIOpenSyncEx(ConsumerInfo, System.String, IntPtr ByRef, Byte[], Byte[], Boolean, Boolean, Int32, Boolean, Int32, Int32, Boolean, Microsoft.Data.SqlClient.SqlConnectionIPAddressPreference, Microsoft.Data.SqlClient.SQLDNSInfo, System.String)

   at Microsoft.Data.SqlClient.SNIHandle..ctor(ConsumerInfo, System.String, Byte[], Boolean, Int32, Byte[] ByRef, Boolean, Boolean, Boolean, Microsoft.Data.SqlClient.TransparentNetworkResolutionState, Int32, Microsoft.Data.SqlClient.SqlConnectionIPAddressPreference, Microsoft.Data.SqlClient.SQLDNSInfo, Boolean, System.String)

   at Microsoft.Data.SqlClient.TdsParserStateObject.CreatePhysicalSNIHandle(System.String, Boolean, Int64, Byte[] ByRef, Byte[], Boolean, Boolean, Boolean, Microsoft.Data.SqlClient.TransparentNetworkResolutionState, Int32, Microsoft.Data.SqlClient.SqlConnectionIPAddressPreference, System.String, Boolean, System.String)

   at Microsoft.Data.SqlClient.TdsParser.Connect(Microsoft.Data.SqlClient.ServerInfo, Microsoft.Data.SqlClient.SqlInternalConnectionTds, Boolean, Int64, Microsoft.Data.SqlClient.SqlConnectionString, Boolean, Boolean, Microsoft.Data.SqlClient.ServerCertificateValidationCallback, Microsoft.Data.SqlClient.ClientCertificateRetrievalCallback, Boolean, Boolean)

   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(Microsoft.Data.SqlClient.ServerInfo, System.String, System.Security.SecureString, Boolean, Microsoft.Data.ProviderBase.TimeoutTimer, Boolean, Boolean, Boolean)

   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(Microsoft.Data.SqlClient.ServerInfo, System.String, System.Security.SecureString, Boolean, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, Microsoft.Data.ProviderBase.TimeoutTimer)

   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(Microsoft.Data.ProviderBase.TimeoutTimer, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, System.String, System.Security.SecureString, Boolean)

daviep

@DanC - do we have an ETA for the fix?

DavidVer

Same issue here for me. Got a new laptop clean install of VS 2022 Enterprise and SCA keeps on crashing VS. The work around does not seem to work, and also in SSMS I am not able to set the data source. So as of right now SCA is not working at all.

DanC

Hi,

The issue is still being investigated by the team and the ideal workaround is to switch to VS 2019 for now

DavidVer

I would argue this being the ideal solution. It is a tool that we pay a lot of money for, and none of our developers are using VS2019 any more or has it installed.

DanC

Hi,

If you upgrade to "SCA 4.6.23171.1653" this should now be fixed

DavidVer

Sweet, I can confirm that it is working for me. Thanks @DanC!