What are the challenges you face when working across database platforms? Take the survey
Options

Build Issue using SCA 4.6 and SQL Server 2022

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.
Tagged:

Answers

  • Options
    daviepdaviep Posts: 7 New member
    PS: Please note that I can connect via SSMS to SQL Server 2022
  • Options
    DanCDanC Posts: 603 Gold 4
    edited April 18, 2023 10:00AM
    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




    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    eamonmahereamonmaher Posts: 2 New member
    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


  • Options
    daviepdaviep Posts: 7 New member
    @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)

  • Options
    daviepdaviep Posts: 7 New member
    @DanC - do we have an ETA for the fix?
  • Options
    DavidVerDavidVer Posts: 13 Bronze 3
    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.
  • Options
    Hi,

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

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    DavidVerDavidVer Posts: 13 Bronze 3
    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.
  • Options
    Hi,

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

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    DavidVerDavidVer Posts: 13 Bronze 3
    Sweet, I can confirm that it is working for me. Thanks @DanC!

Sign In or Register to comment.