DataTestUtility.cs

// Licensed to the .NET Foundation under one or more agreements.
// The .NET Foundation licenses this file to you under the MIT license.
// See the LICENSE file in the project root for more information.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;
using System.Diagnostics.Tracing;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.NetworkInformation;
using System.Net.Sockets;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Security;
using System.Security.Principal;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using Azure.Core;
using Azure.Identity;
using Microsoft.Data.SqlClient.Tests.Common.Fixtures.DatabaseObjects;
using Microsoft.Data.SqlClient.TestUtilities;
using Microsoft.Identity.Client;
using Xunit;
using Xunit.Abstractions;

namespace Microsoft.Data.SqlClient.ManualTesting.Tests
{
    public static class DataTestUtility
    {
        public static readonly string NPConnectionString = null;
        public static readonly string TCPConnectionString = null;
        public static readonly string TCPConnectionStringHGSVBS = null;
        public static readonly string TCPConnectionStringNoneVBS = null;
        public static readonly string TCPConnectionStringAASSGX = null;
        public static readonly string AADAuthorityURL = null;
        public static readonly string AADPasswordConnectionString = null;
        public static readonly string AADServicePrincipalId = null;
        public static readonly string AADServicePrincipalSecret = null;
        public static readonly string AKVOriginalUrl = null;
        public static readonly string AKVTenantId = null;
        public static readonly string LocalDbAppName = null;
        public static readonly string LocalDbSharedInstanceName = null;
        public static List<string> AEConnStrings = new List<string>();
        public static List<string> AEConnStringsSetup = new List<string>();
        public static bool EnclaveEnabled { get; private set; } = false;
        public static readonly bool TracingEnabled = false;
        public static readonly bool SupportsIntegratedSecurity = false;
        public static readonly bool UseManagedSNIOnWindows = false;

        public static Uri AKVBaseUri = null;
        public static readonly string PowerShellPath = null;
        public static string FileStreamDirectory = null;

        public static readonly string DNSCachingConnString = null;
        public static readonly string DNSCachingServerCR = null;  // this is for the control ring
        public static readonly string DNSCachingServerTR = null;  // this is for the tenant ring
        public static readonly bool IsDNSCachingSupportedCR = false;  // this is for the control ring
        public static readonly bool IsDNSCachingSupportedTR = false;  // this is for the tenant ring
        public static readonly string UserManagedIdentityClientId = null;
        public static readonly string AliasName = null;


        public static readonly string EnclaveAzureDatabaseConnString = null;
        public static bool ManagedIdentitySupported = true;
        public static string AADAccessToken = null;
        public static bool SupportsSystemAssignedManagedIdentity = false;
        public static string AADSystemIdentityAccessToken = null;
        public static string AADUserIdentityAccessToken = null;
        public const string ApplicationClientId = "2fd908ad-0664-4344-b9be-cd3e8b574c38";
        public const string UdtTestDbName = "UdtTestDb";
        public const string EventSourcePrefix = "Microsoft.Data.SqlClient";
        public const string MDSEventSourceName = "Microsoft.Data.SqlClient.EventSource";
        private const string ManagedNetworkingAppContextSwitch = "Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows";

        private static Dictionary<string, bool> AvailableDatabases;
        private static BaseEventListener TraceListener;

        public static readonly bool IsManagedInstance = false;

        //Kerberos variables
        public static readonly string KerberosDomainUser = null;
        internal static readonly string KerberosDomainPassword = null;

        // SQL server Version
        private static string s_sqlServerVersion;

        //SQL Server EngineEdition
        private static string s_sqlServerEngineEdition;

        // SQL Server capabilities
        private static bool? s_isDataClassificationSupported;
        private static bool? s_isJsonSupported;
        private static bool? s_isVectorSupported;
        private static bool? s_isVectorFloat16Supported;

        // Login permissions
        private static bool? s_isSysAdmin;
        private static bool? s_isSecurityAdmin;

        // Azure Synapse EngineEditionId == 6
        // More could be read at https://learn.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver16#propertyname
        public static bool IsAzureSynapse
        {
            get
            {
                if (!string.IsNullOrEmpty(TCPConnectionString))
                {
                    s_sqlServerEngineEdition ??= GetSqlServerProperty(TCPConnectionString, ServerProperty.EngineEdition);
                }
                _ = int.TryParse(s_sqlServerEngineEdition, out int engineEditon);
                return engineEditon == 6;
            }
        }

        public static bool TcpConnectionStringDoesNotUseAadAuth
        {
            get
            {
                SqlConnectionStringBuilder builder = new(TCPConnectionString);
                return builder.Authentication == SqlAuthenticationMethod.SqlPassword || builder.Authentication == SqlAuthenticationMethod.NotSpecified;
            }
        }

        public static string SQLServerVersion
        {
            get
            {
                if (!string.IsNullOrEmpty(TCPConnectionString))
                {
                    s_sqlServerVersion ??= GetSqlServerProperty(TCPConnectionString, ServerProperty.ProductMajorVersion);
                }
                return s_sqlServerVersion;
            }
        }

        // Is TDS8 supported
        public static bool IsTDS8Supported =>
            IsTCPConnStringSetup() &&
                GetSQLServerStatusOnTDS8(TCPConnectionString);

        /// <summary>
        /// Checks if object SYS.SENSITIVITY_CLASSIFICATIONS exists in SQL Server
        /// </summary>
        /// <returns>True, if target SQL Server supports Data Classification</returns>
        public static bool IsDataClassificationSupported =>
            s_isDataClassificationSupported ??= IsTCPConnStringSetup() &&
                IsObjectPresent("SYS.SENSITIVITY_CLASSIFICATIONS");

        /// <summary>
        /// Determines whether the SQL Server supports the 'json' data type.
        /// </summary>
        /// <remarks>
        /// This method attempts to connect to the SQL Server and check for the existence of the
        /// 'json' data type.
        /// </remarks>
        public static bool IsJsonSupported =>
            s_isJsonSupported ??= IsTCPConnStringSetup() &&
                IsTypePresent("json");

        /// <summary>
        /// Determines whether the SQL Server supports the 'vector' data type.
        /// </summary>
        /// <remarks>This method attempts to connect to the SQL Server and check for the existence of the
        /// 'vector' data type. If a connection cannot be established or an error occurs during the query, the method
        /// returns <see langword="false"/>.</remarks>
        /// <returns><see langword="true"/> if the 'vector' data type is supported; otherwise, <see langword="false"/>.</returns>
        public static bool IsSqlVectorSupported =>
            s_isVectorSupported ??= IsTCPConnStringSetup() &&
                IsTypePresent("vector");

        /// <summary>
        /// Determines whether the SQL Server supports the 'float16' base type for the 'vector' data type.
        /// </summary>
        /// <remarks>
        /// Probes the server by first executing
        /// <c>ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;</c> and then executing
        /// <c>DECLARE @v AS VECTOR(5, float16) = '[1.0, 1.0, 1.0, 1.0, 1.0]'; SELECT @v;</c>.
        /// As a side effect, this enables preview features for the current database. If the server does not
        /// recognize <c>float16</c> as a vector base type, if the server does not support the required syntax,
        /// if the current principal lacks permission to change the database scoped configuration, or if another
        /// server-side error occurs while running the probe, this method returns
        /// <see langword="false"/>. Implies <see cref="IsSqlVectorSupported"/>.
        /// </remarks>
        /// <returns><see langword="true"/> if the 'float16' vector base type is supported; otherwise, <see langword="false"/>.</returns>
        public static bool IsSqlVectorFloat16Supported =>
            s_isVectorFloat16Supported ??= IsTCPConnStringSetup() &&
                IsSqlVectorSupported &&
                CheckVectorFloat16Supported();

        private static bool CheckVectorFloat16Supported()
        {
            try
            {
                using SqlConnection connection = new(TCPConnectionString);
                // Enable preview features (required while float16 is in preview), then declare a
                // float16 vector and select it back. Without a negotiated float16 feature extension,
                // the server returns the value as a varchar(max) JSON string, which the client can
                // safely read. We use 1.0 (exactly representable in IEEE-754 binary16) so the
                // round-tripped JSON matches the input bit-for-bit. Any failure (server parse
                // error such as Msg 195 "'float16' is not a recognized vector base type.", lack of
                // permission to set PREVIEW_FEATURES, etc.) means we cannot exercise the float16
                // path.
                float[] expected = { 1.0f, 1.0f, 1.0f, 1.0f, 1.0f };
                using SqlCommand command = new(
                    "ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;" +
                    "DECLARE @v AS VECTOR(5, float16) = '[1.0, 1.0, 1.0, 1.0, 1.0]'; SELECT @v;",
                    connection);
                connection.Open();
                using SqlDataReader reader = command.ExecuteReader();
                if (!reader.Read())
                {
                    return false;
                }
                string json = reader.GetString(0);
                float[] actual = System.Text.Json.JsonSerializer.Deserialize<float[]>(json);
                return actual != null && actual.Length == expected.Length
                    && actual.AsSpan().SequenceEqual(expected);
            }
            catch (SqlException)
            {
                // Server-side failure (e.g. Msg 195 "'float16' is not a recognized vector base
                // type.", Msg 102 syntax errors on older servers, lack of permission to set
                // PREVIEW_FEATURES) — float16 path is unavailable.
                return false;
            }
            catch (System.Text.Json.JsonException)
            {
                // Server returned a payload we can't parse as a float[] JSON array.
                return false;
            }
        }

        public static bool IsSysAdmin =>
            s_isSysAdmin ??= IsTCPConnStringSetup() &&
                IsServerRoleMember("sysadmin");

        public static bool IsSecurityAdmin =>
            s_isSecurityAdmin ??= IsTCPConnStringSetup() &&
                IsServerRoleMember("securityadmin");

        public static bool CanCreateLogins =>
            IsSysAdmin || IsSecurityAdmin;

        public static bool CanUseSqlAuthentication =>
            IsSysAdmin && GetAuthenticationMode() == 2;

        static DataTestUtility()
        {
            Config c = Config.Load();
            NPConnectionString = c.NPConnectionString;
            TCPConnectionString = c.TCPConnectionString;
            TCPConnectionStringHGSVBS = c.TCPConnectionStringHGSVBS;
            TCPConnectionStringNoneVBS = c.TCPConnectionStringNoneVBS;
            TCPConnectionStringAASSGX = c.TCPConnectionStringAASSGX;
            AADAuthorityURL = c.AADAuthorityURL;
            AADPasswordConnectionString = c.AADPasswordConnectionString;
            AADServicePrincipalId = c.AADServicePrincipalId;
            AADServicePrincipalSecret = c.AADServicePrincipalSecret;
            LocalDbAppName = c.LocalDbAppName;
            LocalDbSharedInstanceName = c.LocalDbSharedInstanceName;
            SupportsIntegratedSecurity = c.SupportsIntegratedSecurity;
            FileStreamDirectory = c.FileStreamDirectory;
            EnclaveEnabled = c.EnclaveEnabled;
            TracingEnabled = c.TracingEnabled;
            UseManagedSNIOnWindows = c.UseManagedSNIOnWindows;
            DNSCachingConnString = c.DNSCachingConnString;
            DNSCachingServerCR = c.DNSCachingServerCR;
            DNSCachingServerTR = c.DNSCachingServerTR;
            IsDNSCachingSupportedCR = c.IsDNSCachingSupportedCR;
            IsDNSCachingSupportedTR = c.IsDNSCachingSupportedTR;
            EnclaveAzureDatabaseConnString = c.EnclaveAzureDatabaseConnString;
            UserManagedIdentityClientId = c.UserManagedIdentityClientId;
            PowerShellPath = c.PowerShellPath;
            KerberosDomainPassword = c.KerberosDomainPassword;
            KerberosDomainUser = c.KerberosDomainUser;
            ManagedIdentitySupported = c.ManagedIdentitySupported;
            IsManagedInstance = c.IsManagedInstance;
            AliasName = c.AliasName;

#if NETFRAMEWORK
            System.Net.ServicePointManager.SecurityProtocol |= System.Net.SecurityProtocolType.Tls12;
#endif

            if (TracingEnabled)
            {
                TraceListener = new BaseEventListener();
            }

            if (UseManagedSNIOnWindows)
            {
                AppContext.SetSwitch(ManagedNetworkingAppContextSwitch, true);
                Console.WriteLine($"App Context switch {ManagedNetworkingAppContextSwitch} enabled on {Environment.OSVersion}");
            }

            AKVOriginalUrl = c.AzureKeyVaultURL;
            if (!string.IsNullOrEmpty(AKVOriginalUrl) && Uri.TryCreate(AKVOriginalUrl, UriKind.Absolute, out AKVBaseUri))
            {
                AKVBaseUri = new Uri(AKVBaseUri, "/");
            }

            AKVTenantId = c.AzureKeyVaultTenantId;

            if (EnclaveEnabled)
            {
                if (!string.IsNullOrEmpty(TCPConnectionStringHGSVBS))
                {
                    AEConnStrings.Add(TCPConnectionStringHGSVBS);
                    AEConnStringsSetup.Add(TCPConnectionStringHGSVBS);
                }

                if (!string.IsNullOrEmpty(TCPConnectionStringNoneVBS))
                {
                    AEConnStrings.Add(TCPConnectionStringNoneVBS);
                }

                if (!string.IsNullOrEmpty(TCPConnectionStringAASSGX))
                {
                    AEConnStrings.Add(TCPConnectionStringAASSGX);
                    AEConnStringsSetup.Add(TCPConnectionStringAASSGX);
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(TCPConnectionString))
                {
                    AEConnStrings.Add(TCPConnectionString);
                    AEConnStringsSetup.Add(TCPConnectionString);
                }
            }

            // Many of our tests require a Managed Identity provider to be
            // registered.
            //
            // TODO: Figure out which ones and install on-demand rather than
            // globally.
            SqlAuthenticationProvider.SetProvider(
                SqlAuthenticationMethod.ActiveDirectoryManagedIdentity,
                new ManagedIdentityProvider());
        }

        public static IEnumerable<string> ConnectionStrings => GetConnectionStrings(withEnclave: true);

        public static IEnumerable<string> GetConnectionStrings(bool withEnclave)
        {
            if (!string.IsNullOrEmpty(TCPConnectionString))
            {
                yield return TCPConnectionString;
            }
            // Named Pipes are not supported on Unix platform and for Azure DB
            if (Environment.OSVersion.Platform != PlatformID.Unix &&
                IsNotAzureServer() &&
                !string.IsNullOrEmpty(NPConnectionString))
            {
                yield return NPConnectionString;
            }
            if (withEnclave && EnclaveEnabled)
            {
                foreach (var connStr in AEConnStrings)
                {
                    yield return connStr;
                }
            }
        }

        private static string GenerateAccessToken(string authorityURL, string aADAuthUserID, string aADAuthPassword)
        {
            return AcquireTokenAsync(authorityURL, aADAuthUserID, aADAuthPassword).GetAwaiter().GetResult();
        }

        private static Task<string> AcquireTokenAsync(string authorityURL, string userID, string password) => Task.Run(() =>
        {
            // The below properties are set specific to test configurations.
            string scope = "https://database.windows.net//.default";
            string applicationName = "Microsoft Data SqlClient Manual Tests";
            string clientVersion = "1.0.0.0";
            string adoClientId = "2fd908ad-0664-4344-b9be-cd3e8b574c38";

            IPublicClientApplication app = PublicClientApplicationBuilder.Create(adoClientId)
                .WithAuthority(authorityURL)
                .WithClientName(applicationName)
                .WithClientVersion(clientVersion)
                .Build();

            AuthenticationResult result;
            string[] scopes = new string[] { scope };

            // Note: CorrelationId, which existed in ADAL, can not be set in MSAL (yet?).
            // parameter.ConnectionId was passed as the CorrelationId in ADAL to aid support in troubleshooting.
            // If/When MSAL adds CorrelationId support, it should be passed from parameters here, too.

            SecureString securePassword = new SecureString();

            securePassword.MakeReadOnly();
            #pragma warning disable CS0618 // Type or member is obsolete
            result = app.AcquireTokenByUsernamePassword(scopes, userID, password).ExecuteAsync().Result;
            #pragma warning restore CS0618 // Type or member is obsolete

            return result.AccessToken;
        });

        public static bool IsKerberosTest => !string.IsNullOrEmpty(KerberosDomainUser) && !string.IsNullOrEmpty(KerberosDomainPassword);

        #nullable enable

        /// <summary>
        /// Returns the current test name as: ClassName.MethodName
        /// xUnit v2 doesn't provide access to a test context, so we use reflection into the
        /// ITestOutputHelper to get the test name.
        /// </summary>
        /// <exception cref="Exception">
        /// Thrown if any intermediate step of getting to the test name fails or is inaccessible.
        /// </exception>
        /// <param name="outputHelper">Output helper instance for the currently running test</param>
        /// <returns>Current test name</returns>
        public static string CurrentTestName(ITestOutputHelper outputHelper)
        {
            // Reflect our way to the ITestMethod.
            Type type = outputHelper.GetType();

            FieldInfo testField = type.GetField("test", BindingFlags.Instance | BindingFlags.NonPublic)
                ?? throw new Exception("Could not find field 'test' on ITestOutputHelper");

            ITest test = testField.GetValue(outputHelper) as ITest
                ?? throw new Exception("Field 'test' on outputHelper is null or not an ITest object.");

            ITestMethod testMethod = test.TestCase.TestMethod;

            // Class name will be fully-qualified. We only want the class name, so take the last part.
            string[] testClassNameParts = testMethod.TestClass.Class.Name.Split('.');
            string testClassName = testClassNameParts[testClassNameParts.Length - 1];

            string testMethodName = testMethod.Method.Name;

            // Reconstitute the test name as classname.methodname
            return $"{testClassName}.{testMethodName}";
        }

        /// <summary>
        /// SQL Server properties we can query.
        ///
        /// GOTCHA: The enum member names must match the property names
        /// queryable via T-SQL SERVERPROPERTY().  See:
        ///
        /// https://learn.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql
        /// </summary>
        public enum ServerProperty
        {
            ProductMajorVersion,
            EngineEdition
        }

        public static string GetSqlServerProperty(string connectionString, ServerProperty property)
        {
            using SqlConnection conn = new(connectionString);
            conn.Open();
            return GetSqlServerProperty(conn, property);
        }

        public static string GetSqlServerProperty(SqlConnection connection, ServerProperty property)
        {
            using SqlCommand command = connection.CreateCommand();
            command.CommandText = $"SELECT SERVERProperty('{property}')";
            using SqlDataReader reader = command.ExecuteReader();

            Assert.True(reader.Read());

            switch (property)
            {
                case ServerProperty.EngineEdition:
                    // EngineEdition is returned as an int.
                    return reader.GetInt32(0).ToString();
                case ServerProperty.ProductMajorVersion:
                default:
                    // ProductMajorVersion is returned as a string.
                    //
                    // Assume any unknown property is also a string.
                    return reader.GetString(0);
            }
        }

        #nullable restore

        private static bool GetSQLServerStatusOnTDS8(string connectionString)
        {
            bool isTDS8Supported = false;
            SqlConnectionStringBuilder builder = new(connectionString)
            {
                [nameof(SqlConnectionStringBuilder.Encrypt)] = SqlConnectionEncryptOption.Strict
            };
            try
            {
                SqlConnection conn = new(builder.ConnectionString);
                conn.Open();
                isTDS8Supported = true;
            }
            catch (SqlException)
            {

            }
            return isTDS8Supported;
        }

        public static bool IsNotX86Architecture => RuntimeInformation.ProcessArchitecture != Architecture.X86;

        public static bool IsDatabasePresent(string name)
        {
            AvailableDatabases = AvailableDatabases ?? new Dictionary<string, bool>();
            bool present = false;
            if (AreConnStringsSetup() && !string.IsNullOrEmpty(name) && !AvailableDatabases.TryGetValue(name, out present))
            {
                var builder = new SqlConnectionStringBuilder(TCPConnectionString);
                builder.ConnectTimeout = 2;
                using (var connection = new SqlConnection(builder.ToString()))
                using (var command = new SqlCommand("SELECT COUNT(*) FROM sys.databases WHERE name=@name", connection))
                {
                    connection.Open();
                    command.Parameters.AddWithValue("name", name);
                    present = Convert.ToInt32(command.ExecuteScalar()) == 1;
                }
                AvailableDatabases[name] = present;
            }
            return present;
        }

        public static bool IsObjectPresent(string objectName)
        {
            using SqlConnection connection = new(TCPConnectionString);
            using SqlCommand command = new("SELECT OBJECT_ID(@name)", connection);

            connection.Open();
            command.Parameters.AddWithValue("@name", objectName);

            return command.ExecuteScalar() is not DBNull;
        }

        public static bool IsTypePresent(string typeName)
        {
            using SqlConnection connection = new(TCPConnectionString);
            using SqlCommand command = new("SELECT COUNT(1) FROM SYS.TYPES WHERE [name] = @name", connection);

            connection.Open();
            command.Parameters.AddWithValue("@name", typeName);

            return (int)command.ExecuteScalar() > 0;
        }

        public static bool IsServerRoleMember(string roleName)
        {
            using SqlConnection connection = new(TCPConnectionString);
            using SqlCommand command = new("SELECT IS_SRVROLEMEMBER(@role)", connection);

            connection.Open();
            command.Parameters.AddWithValue("@role", roleName);

            // IS_SRVROLEMEMBER returns 1 if the caller is a member of the specified server role, 0 if not, and DBNull.Value if the role is not valid.
            return command.ExecuteScalar() is int result && result == 1;
        }

        public static int GetAuthenticationMode()
        {
            using SqlConnection connection = new(TCPConnectionString);

            connection.Open();
            using SqlCommand command = new("EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\\Microsoft\\MSSQLServer\\MSSQLServer', N'LoginMode'", connection);
            using SqlDataReader reader = command.ExecuteReader();

            reader.Read();
            return reader.GetInt32(1);
        }

        public static bool IsAdmin
        {
            get
            {
#if !NETFRAMEWORK
                System.Diagnostics.Debug.Assert(OperatingSystem.IsWindows());
#endif
                return new WindowsPrincipal(WindowsIdentity.GetCurrent()).IsInRole(WindowsBuiltInRole.Administrator);
            }
        }

        public static bool IsDNSCachingSetup() => !string.IsNullOrEmpty(DNSCachingConnString);

        // Synapse: Always Encrypted is not supported with Azure Synapse.
        //          Ref: https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/17858869-support-always-encrypted-in-sql-data-warehouse
        public static bool IsEnclaveAzureDatabaseSetup()
        {
            return EnclaveEnabled && !string.IsNullOrEmpty(EnclaveAzureDatabaseConnString) && IsNotAzureSynapse();
        }

        public static bool IsNotAzureSynapse() => !IsAzureSynapse;

        public static bool IsNotManagedInstance() => !IsManagedInstance;

        // Synapse: UDT Test Database not compatible with Azure Synapse.
        public static bool IsUdtTestDatabasePresent() => IsDatabasePresent(UdtTestDbName) && IsNotAzureSynapse();

        public static bool AreConnStringsSetup()
        {
            return !string.IsNullOrEmpty(NPConnectionString) && !string.IsNullOrEmpty(TCPConnectionString);
        }

        public static bool IsSQL2022() => string.Equals("16", SQLServerVersion.Trim());

        public static bool IsSQL2019() => string.Equals("15", SQLServerVersion.Trim());

        public static bool IsSQL2017() => string.Equals("14", SQLServerVersion.Trim());

        public static bool IsSQL2016() => string.Equals("13", SQLServerVersion.Trim());

        // "At least" version checks for use as ConditionalFact/ConditionalTheory conditions.
        public static bool IsAtLeastSQL2017() => int.TryParse(SQLServerVersion?.Trim(), out int major) && major >= 14;

        public static bool IsAtLeastSQL2019() => int.TryParse(SQLServerVersion?.Trim(), out int major) && major >= 15;

        public static bool IsSQLAliasSetup()
        {
            return !string.IsNullOrEmpty(AliasName);
        }
        public static bool IsTCPConnStringSetup()
        {
            return !string.IsNullOrEmpty(TCPConnectionString);
        }

        // Synapse: Always Encrypted is not supported with Azure Synapse.
        //          Ref: https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/17858869-support-always-encrypted-in-sql-data-warehouse
        public static bool AreConnStringSetupForAE()
        {
            return AEConnStrings.Count > 0 && IsNotAzureSynapse();
        }

        public static bool IsSGXEnclaveConnStringSetup() => !string.IsNullOrEmpty(TCPConnectionStringAASSGX);

        public static bool IsAADPasswordConnStrSetup()
        {
            return !string.IsNullOrEmpty(AADPasswordConnectionString);
        }

        public static bool IsAADServicePrincipalSetup()
        {
            return !string.IsNullOrEmpty(AADServicePrincipalId) && !string.IsNullOrEmpty(AADServicePrincipalSecret);
        }

        public static bool IsAADAuthorityURLSetup()
        {
            return !string.IsNullOrEmpty(AADAuthorityURL);
        }

        public static bool IsNotAzureServer()
        {
            return !AreConnStringsSetup() || !Utils.IsAzureSqlServer(new SqlConnectionStringBuilder(TCPConnectionString).DataSource);
        }

        public static bool IsAzureServer()
        {
            return AreConnStringsSetup() && Utils.IsAzureSqlServer(new SqlConnectionStringBuilder(TCPConnectionString).DataSource);
        }

        public static bool IsNotNamedInstance()
        {
            return !AreConnStringsSetup() || !new SqlConnectionStringBuilder(TCPConnectionString).DataSource.Contains(@"\");
        }

        public static bool IsLocalHost()
        {
            SqlConnectionStringBuilder builder = new(DataTestUtility.TCPConnectionString);
            return ParseDataSource(builder.DataSource, out string hostname, out _, out _) && string.Equals("localhost", hostname, StringComparison.OrdinalIgnoreCase);
        }

        // Synapse: Always Encrypted is not supported with Azure Synapse.
        //          Ref: https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/17858869-support-always-encrypted-in-sql-data-warehouse
        public static bool IsAKVSetupAvailable()
        {
            return AKVBaseUri != null && !string.IsNullOrEmpty(UserManagedIdentityClientId) && !string.IsNullOrEmpty(AKVTenantId) && IsNotAzureSynapse();
        }

        private static readonly DefaultAzureCredential s_defaultCredential = new(new DefaultAzureCredentialOptions { ManagedIdentityClientId = UserManagedIdentityClientId });

        public static TokenCredential GetTokenCredential()
        {
            return s_defaultCredential;
        }

        public static bool IsTargetReadyForAeWithKeyStore()
        {
            return DataTestUtility.AreConnStringSetupForAE()
#if !NETFRAMEWORK
                // AE tests on Windows will use the Cert Store. On non-Windows, they require AKV.
                && (OperatingSystem.IsWindows() || DataTestUtility.IsAKVSetupAvailable())
#endif
                ;
        }

        public static bool IsSupportingDistributedTransactions()
        {
#if NET8_0_OR_GREATER
            return OperatingSystem.IsWindows() && System.Runtime.InteropServices.RuntimeInformation.ProcessArchitecture != System.Runtime.InteropServices.Architecture.X86 && IsNotAzureServer();
#elif NETFRAMEWORK
            return IsNotAzureServer();
#else
            return false;
#endif
        }

        public static bool IsUsingManagedSNI() => UseManagedSNIOnWindows;

        public static bool IsNotUsingManagedSNIOnWindows() => !UseManagedSNIOnWindows;

        public static bool IsUsingNativeSNI() =>
#if !NETFRAMEWORK
            IsNotUsingManagedSNIOnWindows();
#else
            true;
#endif
        // Synapse: UTF8 collations are not supported with Azure Synapse.
        //          Ref: https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/40103791-utf-8-collations-should-be-supported-in-azure-syna
        public static bool IsUTF8Supported()
        {
            bool retval = false;
            if (AreConnStringsSetup() && IsNotAzureSynapse())
            {
                using (SqlConnection connection = new SqlConnection(TCPConnectionString))
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = "SELECT CONNECTIONPROPERTY('SUPPORT_UTF8')";
                    connection.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            // CONNECTIONPROPERTY('SUPPORT_UTF8') returns NULL in SQLServer versions that don't support UTF-8.
                            retval = !reader.IsDBNull(0);
                        }
                    }
                }
            }
            return retval;
        }

        public static bool IsTCPConnectionStringPasswordIncluded()
        {
            return RetrieveValueFromConnStr(TCPConnectionString, new string[] { "Password", "PWD" }) != string.Empty;
        }

        public static bool DoesHostAddressContainBothIPv4AndIPv6()
        {
            if (!IsDNSCachingSetup())
            {
                return false;
            }
            using (var connection = new SqlConnection(DNSCachingConnString))
            {
                List<IPAddress> ipAddresses = Dns.GetHostAddresses(connection.DataSource).ToList();
                return ipAddresses.Exists(ip => ip.AddressFamily == AddressFamily.InterNetwork) &&
                    ipAddresses.Exists(ip => ip.AddressFamily == AddressFamily.InterNetworkV6);
            }
        }

        public static string GetShortName(string prefix, bool withBracket = true) =>
            DatabaseObject.GenerateShortName(prefix, withBracket);

        public static string GetLongName(string prefix, bool withBracket = true) =>
            DatabaseObject.GenerateLongName(prefix, withBracket);

        public static void CreateTable(SqlConnection sqlConnection, string tableName, string createBody)
        {
            DropTable(sqlConnection, tableName);
            string tableCreate = "CREATE TABLE " + tableName + createBody;
            using (SqlCommand command = sqlConnection.CreateCommand())
            {
                command.CommandText = tableCreate;
                command.ExecuteNonQuery();
            }
        }

        public static void CreateSP(SqlConnection sqlConnection, string spName, string spBody)
        {
            DropStoredProcedure(sqlConnection, spName);
            string spCreate = "CREATE PROCEDURE " + spName + spBody;
            using (SqlCommand command = sqlConnection.CreateCommand())
            {
                command.CommandText = spCreate;
                command.ExecuteNonQuery();
            }
        }

        public static void DropTable(SqlConnection sqlConnection, string tableName)
        {
            ResurrectConnection(sqlConnection);
            using (SqlCommand cmd = new SqlCommand(string.Format("IF (OBJECT_ID('{0}') IS NOT NULL) \n DROP TABLE {0}", tableName), sqlConnection))
            {
                cmd.ExecuteNonQuery();
            }
        }

        public static void DropStoredProcedure(SqlConnection sqlConnection, string spName)
        {
            ResurrectConnection(sqlConnection);
            using (SqlCommand cmd = new SqlCommand(string.Format("IF (OBJECT_ID('{0}') IS NOT NULL) \n DROP PROCEDURE {0}", spName), sqlConnection))
            {
                cmd.ExecuteNonQuery();
            }
        }

        private static void ResurrectConnection(SqlConnection sqlConnection, int counter = 2)
        {
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
            while (counter-- > 0 && sqlConnection.State == ConnectionState.Connecting)
            {
                Thread.Sleep(80);
            }
        }

        /// <summary>
        /// Drops specified database on provided connection.
        /// </summary>
        /// <param name="sqlConnection">Open connection to be used.</param>
        /// <param name="dbName">Database name without brackets.</param>
        public static void DropDatabase(SqlConnection sqlConnection, string dbName)
        {
            ResurrectConnection(sqlConnection);
            using SqlCommand cmd = new(string.Format("IF (EXISTS(SELECT 1 FROM sys.databases WHERE name = '{0}')) \nBEGIN \n ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE \n DROP DATABASE [{0}] \nEND", dbName), sqlConnection);
            cmd.ExecuteNonQuery();
        }

        public static bool IsLocalDBInstalled() => !string.IsNullOrEmpty(LocalDbAppName?.Trim()) && IsIntegratedSecuritySetup();
        public static bool IsLocalDbSharedInstanceSetup() => !string.IsNullOrEmpty(LocalDbSharedInstanceName?.Trim()) && IsIntegratedSecuritySetup();
        public static bool IsIntegratedSecuritySetup() => SupportsIntegratedSecurity;

        public static string GetAccessToken()
        {
            if (AADAccessToken == null && IsAADPasswordConnStrSetup() && IsAADAuthorityURLSetup())
            {
                string username = RetrieveValueFromConnStr(AADPasswordConnectionString, new string[] { "User ID", "UID" });
                string password = RetrieveValueFromConnStr(AADPasswordConnectionString, new string[] { "Password", "PWD" });
                AADAccessToken = GenerateAccessToken(AADAuthorityURL, username, password);
            }
            // Creates a new Object Reference of Access Token - See GitHub Issue 438
            return AADAccessToken != null ? new string(AADAccessToken.ToCharArray()) : null;
        }

        public static string GetSystemIdentityAccessToken()
        {
            if (ManagedIdentitySupported && SupportsSystemAssignedManagedIdentity && AADSystemIdentityAccessToken == null && IsAADPasswordConnStrSetup())
            {
                AADSystemIdentityAccessToken = AADUtility.GetManagedIdentityToken().GetAwaiter().GetResult();
                if (AADSystemIdentityAccessToken == null)
                {
                    ManagedIdentitySupported = false;
                }
            }
            return AADSystemIdentityAccessToken != null ? new string(AADSystemIdentityAccessToken.ToCharArray()) : null;
        }

        public static string GetUserIdentityAccessToken()
        {
            if (ManagedIdentitySupported && AADUserIdentityAccessToken == null && IsAADPasswordConnStrSetup())
            {
                // Pass User Assigned Managed Identity Client Id here.
                AADUserIdentityAccessToken = AADUtility.GetManagedIdentityToken(UserManagedIdentityClientId).GetAwaiter().GetResult();
                if (AADUserIdentityAccessToken == null)
                {
                    ManagedIdentitySupported = false;
                }
            }
            return AADUserIdentityAccessToken != null ? new string(AADUserIdentityAccessToken.ToCharArray()) : null;
        }

        public static bool IsAccessTokenSetup() => !string.IsNullOrEmpty(GetAccessToken());

        public static bool IsFileStreamSetup() => !string.IsNullOrEmpty(FileStreamDirectory) && IsNotAzureServer() && IsNotAzureSynapse();

        private static bool CheckException<TException>(Exception ex, string exceptionMessage, bool innerExceptionMustBeNull) where TException : Exception
        {
            return ((ex != null) && (ex is TException) &&
                ((string.IsNullOrEmpty(exceptionMessage)) || (ex.Message.Contains(exceptionMessage))) &&
                ((!innerExceptionMustBeNull) || (ex.InnerException == null)));
        }

        public static void AssertEqualsWithDescription(object expectedValue, object actualValue, string failMessage)
        {
            if (expectedValue == null || actualValue == null)
            {
                var msg = string.Format("{0}\nExpected: {1}\nActual: {2}", failMessage, expectedValue, actualValue);
                Assert.True(expectedValue == actualValue, msg);
            }
            else
            {
                var msg = string.Format("{0}\nExpected: {1} ({2})\nActual: {3} ({4})", failMessage, expectedValue, expectedValue.GetType(), actualValue, actualValue.GetType());
                Assert.True(expectedValue.Equals(actualValue), msg);
            }
        }

        #nullable enable

        /// <summary>
        /// Asserts that <paramref name="actionThatFails"/> throws an exception of type
        /// <typeparamref name="TException"/> and optionally verifies that its message contains
        /// <paramref name="exceptionMessage"/>.
        /// </summary>
        public static TException AssertThrows<TException>(
            Action actionThatFails,
            string? exceptionMessage = null)
        where TException : Exception
        {
            TException ex = Assert.Throws<TException>(actionThatFails);

            if (exceptionMessage != null)
            {
                Assert.True(ex.Message.Contains(exceptionMessage),
                    string.Format("FAILED: Exception did not contain expected message.\nExpected: {0}\nActual: {1}", exceptionMessage, ex.Message));
            }

            return ex;
        }

        /// <summary>
        /// Asserts that <paramref name="actionThatFails"/> throws <typeparamref name="TException"/>
        /// whose <see cref="Exception.InnerException"/> is of type <typeparamref name="TInnerException"/>.
        /// Optionally verifies message text on both the outer and inner exceptions.
        /// </summary>
        public static TException AssertThrowsInner<TException, TInnerException>(
            Action actionThatFails,
            string? exceptionMessage = null,
            string? innerExceptionMessage = null)
        where TException : Exception
        where TInnerException : Exception
        {
            TException ex = AssertThrows<TException>(actionThatFails, exceptionMessage);

            Assert.NotNull(ex.InnerException);
            Assert.IsAssignableFrom<TInnerException>(ex.InnerException);

            if (innerExceptionMessage != null)
            {
                Assert.True(ex.InnerException.Message.Contains(innerExceptionMessage),
                    string.Format("FAILED: Inner Exception did not contain expected message.\nExpected: {0}\nActual: {1}", innerExceptionMessage, ex.InnerException.Message));
            }

            return ex;
        }

        /// <summary>
        /// Asserts that <paramref name="actionThatFails"/> throws <typeparamref name="TException"/>
        /// whose <see cref="Exception.InnerException"/> is either <typeparamref name="TInnerException"/>
        /// or <typeparamref name="TAlternateInnerException"/>. Use this when a race condition
        /// (e.g. disposal during an async read) may cause the inner exception type to vary
        /// between runs. The <paramref name="innerExceptionMessage"/> is only verified when the
        /// inner exception is <typeparamref name="TInnerException"/>.
        /// </summary>
        public static TException AssertThrowsInnerWithAlternate<TException, TInnerException, TAlternateInnerException>(
            Action actionThatFails,
            string? exceptionMessage = null,
            string? innerExceptionMessage = null)
        where TException : Exception
        where TInnerException : Exception
        where TAlternateInnerException : Exception
        {
            TException ex = AssertThrows<TException>(actionThatFails, exceptionMessage);

            Assert.NotNull(ex.InnerException);
            Assert.True(
                ex.InnerException is TInnerException or TAlternateInnerException,
                $"Expected {typeof(TInnerException).Name} or {typeof(TAlternateInnerException).Name}, got: {ex.InnerException?.GetType()}");

            if (innerExceptionMessage != null && ex.InnerException is TInnerException)
            {
                Assert.True(ex.InnerException.Message.Contains(innerExceptionMessage),
                    string.Format("FAILED: Inner Exception did not contain expected message.\nExpected: {0}\nActual: {1}", innerExceptionMessage, ex.InnerException.Message));
            }

            return ex;
        }

        #nullable restore

        public static TException ExpectFailure<TException>(Action actionThatFails, string[] exceptionMessages, bool innerExceptionMustBeNull = false, Func<TException, bool> customExceptionVerifier = null) where TException : Exception
        {
            try
            {
                actionThatFails();
                Assert.Fail("ERROR: Did not get expected exception");
                return null;
            }
            catch (Exception ex)
            {
                foreach (string exceptionMessage in exceptionMessages)
                {
                    if ((CheckException<TException>(ex, exceptionMessage, innerExceptionMustBeNull)) && ((customExceptionVerifier == null) || (customExceptionVerifier(ex as TException))))
                    {
                        return (ex as TException);
                    }
                }
                throw;
            }
        }

        public static string GenerateObjectName()
        {
            return string.Format("TEST_{0}{1}{2}", Environment.GetEnvironmentVariable("ComputerName"), Environment.TickCount, Guid.NewGuid()).Replace('-', '_');
        }

        // Returns randomly generated characters of length 11.
        public static string GenerateRandomCharacters(string prefix)
        {
            string path = Path.GetRandomFileName();
            path = path.Replace(".", ""); // Remove period.
            return prefix + path;
        }

        public static void RunNonQuery(string connectionString, string sql, int numberOfRetries = 0)
        {
            int retries = 0;
            while (true)
            {
                try
                {
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        connection.Open();
                        command.CommandText = sql;
                        command.ExecuteNonQuery();
                        break;
                    }
                }
                catch (Exception)
                {
                    if (retries >= numberOfRetries)
                    {
                        throw;
                    }
                    retries++;
                    Thread.Sleep(1000);
                }
            }
        }

        public static DataTable RunQuery(string connectionString, string sql)
        {
            DataTable result = null;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        result = new DataTable();
                        result.Load(reader);
                    }
                }
            }
            return result;
        }

        public static void DropFunction(SqlConnection sqlConnection, string funcName)
        {
            using (SqlCommand cmd = new SqlCommand(string.Format("IF EXISTS (SELECT * FROM sys.objects WHERE name = '{0}') \n DROP FUNCTION {0}", funcName), sqlConnection))
            {
                cmd.ExecuteNonQuery();
            }
        }

        public static string GetValueString(object paramValue)
        {
            if (paramValue.GetType() == typeof(DateTimeOffset))
            {
                return ((DateTimeOffset)paramValue).ToString("M/d/yyyy hh:mm:ss tt zzz");
            }
            else if (paramValue.GetType() == typeof(DateTime))
            {
                return ((DateTime)paramValue).ToString("M/d/yyyy hh:mm:ss tt");
            }
            else if (paramValue.GetType() == typeof(SqlDateTime))
            {
                return ((SqlDateTime)paramValue).Value.ToString("M/d/yyyy hh:mm:ss tt");
            }

            return paramValue.ToString();
        }

        public static string FetchKeyInConnStr(string connStr, string[] keys)
        {
            // tokenize connection string and find matching key
            if (connStr != null && keys != null)
            {
                string[] connProps = connStr.Split(';');
                foreach (string cp in connProps)
                {
                    if (!string.IsNullOrEmpty(cp.Trim()))
                    {
                        foreach (var key in keys)
                        {
                            if (cp.Trim().ToLower().StartsWith(key.Trim().ToLower(), StringComparison.Ordinal))
                            {
                                return cp.Substring(cp.IndexOf('=') + 1);
                            }
                        }
                    }
                }
            }
            return null;
        }

        public static string RemoveKeysInConnStr(string connStr, string[] keysToRemove)
        {
            // tokenize connection string and remove input keys.
            string res = "";
            if (connStr != null && keysToRemove != null)
            {
                string[] keys = connStr.Split(';');
                foreach (var key in keys)
                {
                    if (!string.IsNullOrEmpty(key.Trim()))
                    {
                        bool removeKey = false;
                        foreach (var keyToRemove in keysToRemove)
                        {
                            if (key.Trim().ToLower().StartsWith(keyToRemove.Trim().ToLower(), StringComparison.Ordinal))
                            {
                                removeKey = true;
                                break;
                            }
                        }
                        if (!removeKey)
                        {
                            res += key + ";";
                        }
                    }
                }
            }
            return res;
        }

        public static string RetrieveValueFromConnStr(string connStr, string[] keywords)
        {
            // tokenize connection string and retrieve value for a specific key.
            string res = "";
            if (connStr != null && keywords != null)
            {
                string[] keys = connStr.Split(';');
                foreach (var key in keys)
                {
                    foreach (var keyword in keywords)
                    {
                        if (!string.IsNullOrEmpty(key.Trim()))
                        {
                            if (key.Trim().ToLower().StartsWith(keyword.Trim().ToLower(), StringComparison.Ordinal))
                            {
                                res = key.Substring(key.IndexOf('=') + 1).Trim();
                                break;
                            }
                        }
                    }
                }
            }
            return res;
        }

        public static bool ParseDataSource(string dataSource, out string hostname, out int port, out string instanceName)
        {
            hostname = string.Empty;
            port = -1;
            instanceName = string.Empty;

            // Remove leading and trailing spaces
            dataSource = dataSource.Trim();

            if (dataSource.Contains(":"))
            {
                dataSource = dataSource.Substring(dataSource.IndexOf(":", StringComparison.Ordinal) + 1);
            }

            if (dataSource.Contains(","))
            {
                if (!Int32.TryParse(dataSource.Substring(dataSource.LastIndexOf(",", StringComparison.Ordinal) + 1), out port))
                {
                    return false;
                }
                // IndexOf is zero-based, no need to subtract one
                dataSource = dataSource.Substring(0, dataSource.IndexOf(",", StringComparison.Ordinal));
            }

            if (dataSource.Contains("\\"))
            {
                instanceName = dataSource.Substring(dataSource.LastIndexOf("\\", StringComparison.Ordinal) + 1);
                dataSource = dataSource.Substring(0, dataSource.LastIndexOf("\\", StringComparison.Ordinal));
            }

            hostname = dataSource;

            return hostname.Length > 0 && hostname.IndexOfAny(new char[] { '\\', ':', ',' }) == -1;
        }

        public class MDSEventListener : BaseEventListener
        {
            private static Type s_activityCorrelatorType = Type.GetType("Microsoft.Data.Common.ActivityCorrelator, Microsoft.Data.SqlClient");
            private static PropertyInfo s_currentActivityProperty = s_activityCorrelatorType.GetProperty("Current", BindingFlags.NonPublic | BindingFlags.Static);

            public readonly HashSet<string> ActivityIDs = new();

            public override string Name => MDSEventSourceName;

            protected override void OnMatchingEventWritten(EventWrittenEventArgs eventData)
            {
                object currentActivity = s_currentActivityProperty.GetValue(null);
                string activityId = GetActivityId(currentActivity);

                ActivityIDs.Add(activityId);
            }

            private static string GetActivityId(object currentActivity)
            {
                Type activityIdType = currentActivity.GetType();
                FieldInfo idField = activityIdType.GetField("Id", BindingFlags.NonPublic | BindingFlags.Instance);
                FieldInfo sequenceField = activityIdType.GetField("Sequence", BindingFlags.NonPublic | BindingFlags.Instance);

                Guid id = (Guid)idField.GetValue(currentActivity);
                uint sequence = (uint)sequenceField.GetValue(currentActivity);

                // This activity ID format matches the one used in the XEvents trace
                return string.Format("{0}-{1}", id, sequence).ToUpper();
            }
        }

        public class BaseEventListener : EventListener
        {
            public readonly List<int> IDs = new();
            public readonly List<EventWrittenEventArgs> EventData = new();

            public virtual string Name => EventSourcePrefix;

            protected override void OnEventSourceCreated(EventSource eventSource)
            {
                if (eventSource.Name.StartsWith(Name, StringComparison.Ordinal))
                {
                    // Collect all traces for better code coverage
                    EnableEvents(eventSource, EventLevel.LogAlways, EventKeywords.All);
                }
                else
                {
                    DisableEvents(eventSource);
                }
            }

            protected override void OnEventWritten(EventWrittenEventArgs eventData)
            {
                if (Name == eventData.EventSource.Name)
                {
                    IDs.Add(eventData.EventId);
                    EventData.Add(eventData);
                    OnMatchingEventWritten(eventData);
                }
            }

            protected virtual void OnMatchingEventWritten(EventWrittenEventArgs eventData)
            {
            }
        }

        #nullable enable

        /// <summary>
        /// Resolves the machine's fully qualified domain name if it is applicable.
        /// </summary>
        /// <returns>Returns FQDN if the client was domain joined otherwise the machine name.</returns>
        public static string GetMachineFQDN(string hostname)
        {
            IPGlobalProperties machineInfo = IPGlobalProperties.GetIPGlobalProperties();
            StringBuilder fqdn = new();
            if (hostname.Equals("localhost", StringComparison.OrdinalIgnoreCase) ||
                hostname.Equals(machineInfo.HostName, StringComparison.OrdinalIgnoreCase))
            {
                fqdn.Append(machineInfo.HostName);
                if (!string.IsNullOrEmpty(machineInfo.DomainName))
                {
                    fqdn.Append(".");
                    fqdn.Append(machineInfo.DomainName);
                }
            }
            else
            {
                IPHostEntry host = Dns.GetHostEntry(hostname);
                fqdn.Append(host.HostName);
            }
            return fqdn.ToString();
        }

        #nullable restore
    }
}