Build Issue using SCA 4.6 and SQL Server 2022

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

  daviep April 17, 2023 00:39

  - 0 +
• 
  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
  
  
  
  
  

  DanC April 18, 2023 10:00

  - 0 +
• 
  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

  
  

  
  

  eamonmaher April 18, 2023 18:21

  - 0 +
• 
  @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 April 20, 2023 05:46

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

  daviep May 08, 2023 00:25

  - 0 +
• 
  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.

  DavidVer June 02, 2023 10:41

  - 0 +
• 
  Hi,
  
  The issue is still being investigated by the team and the ideal workaround is to switch to VS 2019 for now

  DanC June 05, 2023 11:33

  - 0 +
• 
  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.

  DavidVer June 07, 2023 06:37

  - 0 +
• 
  Hi,
  
  If you upgrade to "SCA 4.6.23171.1653" this should now be fixed
  

  DanC June 23, 2023 08:06

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

  DavidVer June 23, 2023 09:43

  - 0 +

Add comment

Please sign in to leave a comment.