MAR-10382580-r2.v1 – RAT

MAR-10382580-r2.v1 – RAT

by | Jul 18, 2022 | Security, Technology

This article is contributed. See the original author and article here.

This report is provided “as is” for informational purposes only. The Department of Homeland Security (DHS) does not provide any warranties of any kind regarding any information contained herein. The DHS does not endorse any commercial product or service referenced in this bulletin or otherwise.

This document is marked TLP:WHITE–Disclosure is not limited. Sources may use TLP:WHITE when information carries minimal or no foreseeable risk of misuse, in accordance with applicable rules and procedures for public release. Subject to standard copyright rules, TLP:WHITE information may be distributed without restriction. For more information on the Traffic Light Protocol (TLP), see http://www.cisa.gov/tlp.

Description

This CISA submission included one unique file. This file is a malicious loader that contains an embedded executable. This embedded executable is a Remote Access Tool (RAT) that provides a vast array of Command and Control (C2) capabilities. These C2 capabilities include the ability to remotely monitor a system’s desktop, gain reverse shell access, exfiltrate data, and upload and execute additional payloads. The malware can also function as a proxy, allowing a remote operator to pivot to other systems.

For a downloadable copy of IOCs, see: MAR-10382580.r2.v1.WHITE_stix

Submitted Files (1)

4cd7efdb1a7ac8c4387c515a7b1925931beb212b95c4f9d8b716dbe18f54624f (ilasvc.exe)

IPs (1)

151.106.30.120

4cd7efdb1a7ac8c4387c515a7b1925931beb212b95c4f9d8b716dbe18f54624f

Tags

remote-access-trojantrojan

Details
Name ilasvc.exe
Size 1056768 bytes
Type PE32+ executable (GUI) x86-64, for MS Windows
MD5 05d38bc82d362dd57190e3cb397f807d
SHA1 52b04d348adf7e42e7c7d6c2ec9aabbcaba07188
SHA256 4cd7efdb1a7ac8c4387c515a7b1925931beb212b95c4f9d8b716dbe18f54624f
SHA512 d03894ad9ce7a5f0e58a5e6385926263507f2571e3cbe60fce1ed5463a77152a7779d8b494ee7a6ff4986de19c0a92cbcc8dae5697d69dc196c474723ee553ef
ssdeep 24576:mStdBO8/kIH46+jHd3JURkxXH3rg9fNJa9y5xmDYzgLu8b7oCK:mST2+qXHbg91Ja9y5MOgL3K
Entropy 7.599564
Antivirus
ESET a variant of Win64/Injector.HA.gen trojan
IKARUS Trojan.Win64.Injector
YARA Rules
  • rule CISA_10382580_03 : loader
    {
       meta:
           Author = “CISA Code & Media Analysis”
           Incident = “10382580”
           Date = “2022-05-02”
           Last_Modified = “20220602_1200”
           Actor = “n/a”
           Category = “Loader”
           Family = “n/a”
           Description = “Detects loader samples”
           MD5_1 = “3764a0f1762a294f662f3bf86bac776f”
           SHA256_1 = “f7f7b059b6a7dbd75b30b685b148025a0d4ceceab405e553ca28cacdeae43fab”
           MD5_2 = “21fa1a043460c14709ef425ce24da4fd”
           SHA256_2 = “66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16”
           MD5_3 = “e9c2b8bd1583baf3493824bf7b3ec51e”
           SHA256_3 = “7ea294d30903c0ab690bc02b64b20af0cfe66a168d4622e55dee4d6233783751”
           MD5_4 = “de0d57bdc10fee1e1e16e225788bb8de”
           SHA256_4 = “33b89b8915aaa59a3c9db23343e8c249b2db260b9b10e88593b6ff2fb5f71d2b”
           MD5_5 = “9b071311ecd1a72bfd715e34dbd1bd77”
           SHA256_5 = “3c2c835042a05f8d974d9b35b994bcf8d5a0ce19128ebb362804c2d0f3eb42c0”
           MD5_6 = “05d38bc82d362dd57190e3cb397f807d”
           SHA256_6 = “4cd7efdb1a7ac8c4387c515a7b1925931beb212b95c4f9d8b716dbe18f54624f”
       strings:
           $s0 = { B8 01 00 00 00 48 6B C0 00 C6 44 04 20 A8 B8 01 }
           $s1 = { 00 00 48 6B C0 01 C6 44 04 20 9A B8 01 00 00 }
           $s2 = { 48 6B C0 02 C6 44 04 20 93 B8 01 00 00 00 48 }
           $s3 = { C0 03 C6 44 04 20 9B B8 01 00 00 00 48 6B C0 }
       condition:
           all of them
    }
ssdeep Matches

No matches found.

PE Metadata
Compile Date 2020-04-30 19:43:57-04:00
Import Hash 99197f3296550481a848ea8d4e097487
Company Name Sysinternals – www.sysinternals.com
File Description Flush cached data to disk.
Internal Name Sync
Legal Copyright Copyright (C) 2016 Mark Russinovich
Original Filename Sync.exe
Product Name Sysinternals Sync
Product Version 2.2
PE Sections
MD5 Name Raw Size Entropy
a917582fc3e796bb1d43bfce05c0cfb3 header 1024 3.105665
5fbd29958a5484173910cb06dcfc4e9e .text 310784 6.453454
34b6e6a847957ef90ef9460e0f8dd3d0 .rdata 98304 5.168254
e32c1166142d325350f6e6443db43144 .data 3584 2.609738
ffc4ab2046acad015eba98898e975ad5 .pdata 18432 5.804487
502485fa11633b4eb9eaef15fcb482a5 .rsrc 622080 7.975998
69687e4a3ffbefbe782d13637ce8605a .reloc 2560 4.913641
Relationships
4cd7efdb1a… Connected_To 151.106.30.120
Description

This malware is a 64-bit Windows loader that contains an embedded encrypted malicious executable. During runtime, this embedded executable is decrypted and loaded into memory, never touching the system’s hard disk. The encrypted executable is similar in functionality to the file 66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16, described in report MAR-10382580.r1.v1. The malware embedded within this loader attempts to communicate with the hard-coded C2 Internet Protocol (IP) address 151[.]106[.]30[.]120. This malware provides a vast array of C2 capabilities including the ability to log keystrokes, upload and execute additional payloads, function as a proxy, and have graphical user interface (GUI) access over a target Windows system’s desktop. Many of the structures utilized to implement the C2 capabilities in this malware appear to be derived from the same source code as 66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16, however this malware utilizes much more complex obfuscation to hinder the analysis of its code structures. This malware also utilizes a more complex encryption algorithm to secure its network communications.

The malware embedded within this binary utilizes a secure strings scheme based on a rotating XOR cipher (Figure 7). The strings are partially decrypted and listed below with their corresponding approximate memory address locations during runtime — assuming a base address of 0x260000.

–Begin Decoded Strings–

(‘0x264e32’, ‘RegQueryValueExl’)
(‘0x264f58’, ‘RegQueryValueEx’)
(‘0x265325’, ‘GetCurrentProcessId’)
(‘0x265bc9’, ‘GetEnvironmentVariableW’)
(‘0x265cc1’, ‘ShellExecuteExW’)
(‘0x268b20’, ‘GetAdaptersInfo’)
(‘0x268c49’, ‘GetAdaptersInfo’)
(‘0x26a77c’, ‘EnumDependentServicesW’)
(‘0x26a98b’, ‘EnumDependentServi’)
(‘0x26abb9’, ‘ControlService’)
(‘0x26ad5b’, ‘QueryServiceStatus’)
(‘0x26af62’, ‘CloseServiceHandle’)
(‘0x26c3ed’, ‘GetComputerNameW’)
(‘0x277621’, ‘GetEnvironmentVariableW’)
(‘0x27856f’, ‘GetLogicalDriveStringsW’)
(‘0x2788e5’, ‘GetVolumeInformationW’)
(‘0x278f87’, ‘FindFirstFileW’)
(‘0x27a3f3’, ‘GetSystemDirectoryW’)
(‘0x27bf04’, ‘SetFilePointerEx’)
(‘0x27d125’, ‘RemoveDirectoryW’)
(‘0x27daa7’, ‘FindFirstFileW’)
(‘0x284074’, ‘GetClipboardData’)
(‘0x2850d4’, ‘GetForegroundWindow’)
(‘0x28513d’, ‘GetDesktopWindow’)
(‘0x28b443’, ‘GetProcessHeap’)
(‘0x28b533’, ‘CoInitializeEx’)
(‘0x28b655’, ‘StartServiceCtrlDispatch’)
(‘0x28cd63’, ‘GetModuleFileNameW’)
(‘0x2636f3’, ‘UnkownError’)
(‘0x2649f3’, “Display”””)
(‘0x264ab0’, ‘RegOpenKeyExW’)
(‘0x264af0’, ‘ADVAPI32.dll’)
(‘0x264ca0’, ‘RegEnumKeyExW’)
(‘0x264ce0’, ‘ADVAPI32.dll’)
(‘0x264d80’, ‘RegOpenKeyExW’)
(‘0x264dc0’, ‘ADVAPI32.dll’)
(‘0x264e90’, ‘ADVAPI32.dll’)
(‘0x264fb0’, ‘ADVAPI32.dll’)
(‘0x265160’, ‘RegCloseKey’)
(‘0x2651b0’, ‘ADVAPI32.dll’)
(‘0x265390’, ‘KERNEL32.dll’)
(‘0x265c30’, ‘KERNEL32.dll’)
(‘0x265d20’, ‘SHELL32.dll’)
(‘0x266950’, ‘GetVersionExW’)
(‘0x266990’, ‘KERNEL32.dll’)
(‘0x266b63’, ‘CurrentMajorVersionNum’)
(‘0x266c33’, ‘CurrentMajorVersionNum’)
(‘0x268b80’, ‘IPHLPAPI.dll’)
(‘0x268c03’, ‘KERNEL32.dll’)
(‘0x268ca0’, ‘IPHLPAPI.dll’)
(‘0x26a710’, ‘GetTickCount’)
(‘0x26a750’, ‘KERNEL32.dll’)
(‘0x26a7b8’, ‘EnumDepende’)
(‘0x26a7f3’, ‘Advapi32.dll’)
(‘0x26a872’, ‘GetLastError’)
(‘0x26a8b0’, ‘KERNEL32.dll’)
(‘0x26a940’, ‘KERNEL32.dll’)
(‘0x26aa17’, ‘Advapi32.dll’)
(‘0x26aafb’, ‘OpenServiceW’)
(‘0x26ab4b’, ‘Advapi32.dll’)
(‘0x26ac33’, ‘Advapi32.dll’)
(‘0x26acd4’, ‘Sleep’)
(‘0x26ad24’, ‘KERNEL32.dll’)
(‘0x26adea’, ‘Advapi32.dll’)
(‘0x26aeaa’, ‘GetTickCount’)
(‘0x26af03’, ‘KERNEL32.dll’)
(‘0x26afdb’, ‘Advapi32.dll’)
(‘0x26c2e0’, ‘GetUserNameW’)
(‘0x26c320’, ‘Advapi32.dll’)
(‘0x26c450’, ‘KERNEL32.dll’)
(‘0x26cad0’, ‘KERNEL32.dll’)
(‘0x273220’, ‘closesocket’)
(‘0x274a90’, ‘getsockname’)
(‘0x275280’, ‘getsockname’)
(‘0x276583’, ‘Erroroccurswhiles’)
(‘0x276714’, ‘NoTabsinclient.’)
(‘0x2769e3’, ‘NoTabsinclient.’)
(‘0x276b60’, ‘KERNEL32.dll’)
(‘0x277690’, ‘KERNEL32.dll’)
(‘0x2785e0’, ‘KERNEL32.dll’)
(‘0x2786d3’, ‘ErroroccursinGetL’)
(‘0x278950’, ‘KERNEL32.dll’)
(‘0x2789e0’, ‘GetDriveTypeW’)
(‘0x278a20’, ‘KERNEL3’)
(‘0x278f10’, ‘PathCombineW’)
(‘0x278f50’, ‘SHLWAPI.dll’)
(‘0x278fa4’, ‘FindFirstFile’)
(‘0x278fe0’, ‘KERNEL32.dll’)
(‘0x279120’, ‘PathCombineW’)
(‘0x279160’, ‘SHLWAPI.dll’)
(‘0x2791c1’, ‘CreateFileW’)
(‘0x279200’, ‘KERNEL32.dll’)
(‘0x279280’, ‘GetFileTime’)
(‘0x2792c0’, ‘KERNEL32.dll’)
(‘0x279320’, ‘CloseHandle’)
(‘0x279360’, ‘KERNEL32.dll’)
(‘0x2796a0’, ‘FindNextFileW’)
(‘0x2796e0’, ‘KERNEL32.dll’)
(‘0x2797b3’, ‘Cannotaccesstofold’)
(‘0x27a460’, ‘KERNEL32.dll’)
(‘0x27a4e3’, ‘kernel32.dll’)
(‘0x27a540’, ‘PathCombineW’)
(‘0x27a580’, ‘SHLWAPI.dll’)
(‘0x27a5e0’, ‘CreateFileW’)
(‘0x27a620’, ‘KERNEL32.dll’)
(‘0x27a692’, ‘GetFileTime’)
(‘0x27a6d0’, ‘KERNEL32.dll’)
(‘0x27a730’, ‘CloseHandle’)
(‘0x27a770’, ‘KERNEL32.dll’)
(‘0x27acf0’, ‘CreateFileW’)
(‘0x27ad30’, ‘KERNEL32.dll’)
(‘0x27ade0’, ‘GetFileTime’)
(‘0x27ae20’, ‘KERNEL32.dll’)
(‘0x27af80’, ‘GetLastError’)
(‘0x27afc0’, ‘KERNEL32.dll’)
(‘0x27b430’, ‘GetLastError’)
(‘0x27b470’, ‘KERNEL32.dll’)
(‘0x27b932’, ‘CreateFileW’)
(‘0x27b970’, ‘KERNEL32.dll’)
(‘0x27b9f0’, ‘GetLastError’)
(‘0x27ba30’, ‘KERNEL32.dll’)
(‘0x27bf60’, ‘KERNEL32.dll’)
(‘0x27c000’, ‘KERNEL32.dll’)
(‘0x27c080’, ‘KERNEL32.dll’)
(‘0x27c1b0’, ‘CloseHandle’)
(‘0x27c1f0’, ‘KERNEL32.dll’)
(‘0x27c270’, ‘GetLastError’)
(‘0x27c2b0’, ‘KERNEL32.dll’)
(‘0x27c3c3’, ‘Nodescriptorfound.’)
(‘0x27c860’, ‘KERNEL32.dll’)
(‘0x27c950’, ‘CloseHandle’)
(‘0x27c990’, ‘KERNEL32.dll’)
(‘0x27c9f0’, ‘GetLastError’)
(‘0x27ca30’, ‘KERNEL32.dll’)
(‘0x27cb00’, ‘CloseHandle’)
(‘0x27cb40’, ‘KERNEL32.dll’)
(‘0x27cdc0’, ‘CloseHandle’)
(‘0x27ce00’, ‘KERNEL32.dll’)
(‘0x27d180’, ‘KERNEL32.dll’)
(‘0x27d1f0’, ‘DeleteFileW’)
(‘0x27d230’, ‘KERNEL32.dll’)
(‘0x27d290’, ‘GetLastError’)
(‘0x27d2d0’, ‘KERNEL32.dll’)
(‘0x27d3e3’, ‘Deletesuccessed.’)
(‘0x2c3743’, ‘Deletepayloadcorrupt’)
(‘0x27da30’, ‘PathCombineW’)
(‘0x27da70’, ‘SHLWAPI.dll’)
(‘0x27dac4’, ‘FindFirstFile’)
(‘0x27db00’, ‘KERNEL32.dll’)
(‘0x27dc20’, ‘PathCombineW’)
(‘0x27dc60’, ‘SHLWAPI.dll’)
(‘0x27ded1’, ‘FindNex2@x04@%@’)
(‘0x27df10’, ‘KERNEL32.dll’)
(‘0x284030’, ‘OpenClipboard’)
(‘0x284110’, ‘Kernel32.dll’)
(‘0x2841b3’, ‘<CTRL+V>’)
(‘0x284253’, ‘</CTRL+V>’)
(‘0x284fe3’, ‘Composition’)
(‘0x285073’, ‘Sfwrirsfi’)
(‘0x28507c’, ‘otaeMcootW’)
(‘0x285484’, ‘Monitor%d[%d*%d]’)
(‘0x28b280’, ‘DeleteObject’)
(‘0x28b400’, ‘KERNEL32.dll’)
(‘0x28b4a0’, ‘KERNEL32.dll’)
(‘0x28b6d0’, ‘Advapi32.dll’)
(‘0x28cdc0’, ‘KERNEL32.dll’)
(‘0x28d230’, ‘ExitProcess’)
(‘0x28d270’, ‘KERNEL32.dll’)
(‘0x28d3b0’, ‘GetTempPathW’)
(‘0x28d3f0’, ‘KERNEL32.dll’)
(‘0x28d4a0’, ‘PathCombineW’)
(‘0x28d4e0’, ‘SHLWAPI.dll’)

–End Decoded Strings–

Screenshots

Figure 1 - This screenshot illustrates the malware sending an initial block of data to its hard-coded C2 server. As with the malware sample 66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16 in MAR-10382580.r1.v1, this malware's initial outbound block contains a chunk of random data and the unicode string "hello".

Figure 1 – This screenshot illustrates the malware sending an initial block of data to its hard-coded C2 server. As with the malware sample 66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16 in MAR-10382580.r1.v1, this malware’s initial outbound block contains a chunk of random data and the unicode string “hello”.

Figure 2 - This screenshot illustrates the malware's hard-coded cryptographic key it utilizes to encrypt and decrypt its network communications traffic via the algorithm in Figure 4.

Figure 2 – This screenshot illustrates the malware’s hard-coded cryptographic key it utilizes to encrypt and decrypt its network communications traffic via the algorithm in Figure 4.

Figure 3 - This screenshot illustrates the data returned to the remote operator if they simply reply to the malware's initial "hello" packet with their own "hello" packet. This data block contains the compromised system's MAC address, IP address, OS version, processor type, as well as other system specific information. The cryptographic algorithm illustrated in Figure 4 will be utilized to encrypt this data before it is sent to the remote C2 server.

Figure 3 – This screenshot illustrates the data returned to the remote operator if they simply reply to the malware’s initial “hello” packet with their own “hello” packet. This data block contains the compromised system’s MAC address, IP address, OS version, processor type, as well as other system specific information. The cryptographic algorithm illustrated in Figure 4 will be utilized to encrypt this data before it is sent to the remote C2 server.

Figure 4 - This screenshot illustrates code extracted from this malware's primary cryptographic function. This algorithm will be utilized to encrypt and decrypt all network traffic exchanged between this implant and its remote operator. Although the malware does communicate over port 443, this algorithm is utilized rather than SSL. Static analysis indicates a hard-coded 16 byte key is utilized to encrypt and decrypt network traffic. That key can be observed in Figure 2.

Figure 4 – This screenshot illustrates code extracted from this malware’s primary cryptographic function. This algorithm will be utilized to encrypt and decrypt all network traffic exchanged between this implant and its remote operator. Although the malware does communicate over port 443, this algorithm is utilized rather than SSL. Static analysis indicates a hard-coded 16 byte key is utilized to encrypt and decrypt network traffic. That key can be observed in Figure 2.

Figure 5 - This screenshot illustrates the names of various classes utilized by this implant. The class VK1AlgorithmEngine contains the function which is utilized to encrypt and decrypt this malware's network traffic (Figure 4). Notably, the previously analyzed sample 66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16 utilizes a different class to implement its network traffic encryption. That sample's cryptographic class name is VSimpleXorAlgorithmEngine. This explains why the samples, while structurally and functionally very similar, utilize a different algorithm to secure their network communications.

Figure 5 – This screenshot illustrates the names of various classes utilized by this implant. The class VK1AlgorithmEngine contains the function which is utilized to encrypt and decrypt this malware’s network traffic (Figure 4). Notably, the previously analyzed sample 66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16 utilizes a different class to implement its network traffic encryption. That sample’s cryptographic class name is VSimpleXorAlgorithmEngine. This explains why the samples, while structurally and functionally very similar, utilize a different algorithm to secure their network communications.

Figure 6 - This screenshot illustrates several malicious classes this malware utilizes. The class VFeatureCmd provides the function which implements the malware's reverse shell capability -- providing the remote hacker direct access to a Windows command shell. The class VFeatureKeylogger provides advanced key logging capabilities. Static analysis indicates the VFeatureSocks and VFeatureTunnel classes implement the malware's TCP proxying capability. And the VFeatureScreen class provides functions which allow the remote operator to monitor victim user's desktop / GUI sessions. These same classes are utilized in malware sample 66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16 in MAR-10382580.r1.v1.

Figure 6 – This screenshot illustrates several malicious classes this malware utilizes. The class VFeatureCmd provides the function which implements the malware’s reverse shell capability — providing the remote hacker direct access to a Windows command shell. The class VFeatureKeylogger provides advanced key logging capabilities. Static analysis indicates the VFeatureSocks and VFeatureTunnel classes implement the malware’s TCP proxying capability. And the VFeatureScreen class provides functions which allow the remote operator to monitor victim user’s desktop / GUI sessions. These same classes are utilized in malware sample 66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16 in MAR-10382580.r1.v1.

Figure 7 - This malware sample contains many encoded strings. As illustrated in this screenshot, many of the strings are encoded using an XOR cipher utilizing a single-byte key. A different XOR single-byte key will be used to decode each string.

Figure 7 – This malware sample contains many encoded strings. As illustrated in this screenshot, many of the strings are encoded using an XOR cipher utilizing a single-byte key. A different XOR single-byte key will be used to decode each string.

Figure 8 - his screenshot illustrates a misspelling with the word "modifing" in the malware's source code. This same misspelling can be observed in the plugin embedded within malware sample 66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16 in MAR-10382580.r1.v1. This piece of information may be useful for attribution purposes.

Figure 8 – his screenshot illustrates a misspelling with the word “modifing” in the malware’s source code. This same misspelling can be observed in the plugin embedded within malware sample 66966ceae7e3a8aace6c27183067d861f9d7267aed30473a95168c3fe19f2c16 in MAR-10382580.r1.v1. This piece of information may be useful for attribution purposes.

Lesson Learned #225: Has an unresolved reference to object using Linked Server or External Table

by | Jul 16, 2022 | Technology

This article is contributed. See the original author and article here.

We used to have cases when our customers are trying to export their database using SQLPackage or Export service in the Portal having, for example, the following error message: Error SQL71562: Error validating element Synonym: [dbo].[MyView] has an unresolved reference to object [linkedserver].[databasename].[schemaname].[tablename]. External references are not supported when creating a package from this platform. In this post I would like to suggest an alternative to export this data.


 


The first thing that we need to know that SQLPackage validates that we don’t have any of the following points in our database that we want to export:


 



  • Reference using 4 parts, for example, databasename.schemaname.tablename

  • Using an external table.

  • Calling a table using a linked server, for example, select * from mylinkedserver.databasename.schema.table

  • Calling a synonyms that uses a linked server, for example, CREATE SYNONYM [dbo].[ExternalTable1] FOR [linkedserver].[databasename].[schemaname].[tablename]


If we have any of these scenarios we are going to have the following error message exporting the data:


 



  • One or more unsupported elements were found in the schema used as part of a data package.
    Error SQL71562: Error validating element Synonym: [dbo].[MyView] has an unresolved reference to object [linkedserver].[databasename].[schemaname].[tablename]. External references are not supported when creating a package from this platform.



  • One or more unsupported elements were found in the schema used as part of a data package.
    Error SQL71562: Error validating element [dbo].[ExternalTable1]: Synonym: [dbo].[ExternalTable1] has an unresolved reference to object [linkedservername].[databasename].[schemaname].[tablename]. External references are not supported when creating a package from this platform.




 


In order to be able to export the data, I developed the following stored procedure that basically, runs the following code. The goal is to create dynamically the external table and/or view or synonym that is calling external tables,


 



  • Parameters:

    • @TableName = will be the name of the synonym or external table to create. 

    • @Action = will be the operation to perform, CREATE or DROP the synonym




 


 

CREATE PROCEDURE dbo.DeployMyExternalTable(@TableName varchar(400),@Action VARCHAR(10))
AS

 IF @TableName = 'ExternalTable1' 
 BEGIN
  IF @Action = 'CREATE' 
    BEGIN 
      IF NOT EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable1' )
	  	 EXEC SP_EXECUTESQL N'CREATE SYNONYM [dbo].[ExternalTable1] FOR [LinkedServerName].[DatabaseName].[SchemaName].[ExternalData1]' 
    END
  IF @Action = 'DROP' 
    BEGIN 
	 IF EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable1' )
	    EXEC SP_EXECUTESQL N'DROP SYNONYM [dbo].[ExternalTable1]' 
    END
 END
 
 IF @TableName = 'ExternalTable2' 
 BEGIN
  IF @Action = 'CREATE' 
    BEGIN 
     IF NOT EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable2' )
    	 EXEC SP_EXECUTESQL N'CREATE SYNONYM [dbo].[ExternalTable2] CREATE SYNONYM [dbo].[ExternalTable2] FOR [LinkedServerName].[DatabaseName].[SchemaName].[ExternalData2]' 
    END
  IF @Action = 'DROP' 
    BEGIN 
	 IF EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable2' )
	    EXEC SP_EXECUTESQL N'DROP SYNONYM [dbo].[ExternalTable2]' 
    END
 END

 


 


The idea is if you need to run any query against this synonym or external table is to run the following code: 


 


 

exec dbo.DeployMyExternalTable 'ExternalTable1', 'CREATE'

SELECT * FROM [dbo].[ExternalTable1]

 


 


Or if you need to delete it


 


 

exec dbo.DeployMyExternalTable 'ExternalTable1', 'DROP'

 


 


Basically, if you need to export the data, you could call these stored procedure for all synonyms or external tables, export the data and after it your application/database will create again them without modifying your code. 


 


Enjoy!

Lesson Learned #224:Hands-On-Labs: Checking the performance with Perf Collector Analyzer

Lesson Learned #224:Hands-On-Labs: Checking the performance with Perf Collector Analyzer

by | Jul 16, 2022 | Technology

This article is contributed. See the original author and article here.

One of the main problem that we have when we are working with multiple databases in an Elastic Databases Pool or Azure SQL Managed Instace (that are running on same SQL Instance) is how to consolidate all the information in a single place. 


 


For example, if we use Query Data Store we need to review one by one the database finding the performance problem. Trying to resolve these insights we would like to suggest to use PerfCollector and PerfCollector Analyzer to allows you to capture and query the information collected for all the databases that you have in an Elastic Database Pool and SQL Managed Instance and even, all the databases associated in an Azure SQL Server or On-Premises. 


 


How to collect the data?



Videos how to use? 



Where download the PerfCollector Analyzer



 


Advantages to use PerfCollector Analyzer:



 


Some Example queries to obtain the information from Query Data Store for all the databases:


 



  • Highest wait durations at instance level


 

SELECT TOP 10
    sum(total_query_wait_time_ms) AS sum_total_wait_ms, 
	 ws.[wait_category_desc]
FROM [_xTotalxAcummulatedx_xQDSx_query_store_wait_stats] ws
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_plan] p ON ws.plan_id = p.plan_id and ws.dbname = p.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query] q ON p.query_id = q.query_id and p.dbname = q.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query_text] qt ON q.query_text_id = qt.query_text_id and q.dbname = qt.dbname
GROUP BY ws.[wait_category_desc]
ORDER BY sum_total_wait_ms DESC;

 


 



  • Highest wait durations per database at instance level/elastic database pool level


 

SELECT TOP 100
    sum(total_query_wait_time_ms) AS sum_total_wait_ms, 
	 ws.[wait_category_desc], 
	 q.dbname
FROM [_xTotalxAcummulatedx_xQDSx_query_store_wait_stats] ws
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_plan] p ON ws.plan_id = p.plan_id and ws.dbname = p.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query] q ON p.query_id = q.query_id and p.dbname = q.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query_text] qt ON q.query_text_id = qt.query_text_id and q.dbname = qt.dbname
GROUP BY q.dbname , ws.[wait_category_desc]
ORDER BY q.dbname, sum_total_wait_ms DESC;

 



  •  


 



  • Highest wait durations per instance level/elastic database pool level and query


 


 

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
    rs.last_execution_time, p.dbname
FROM [_xTotalxAcummulatedx_xQDSx_query_store_query_text] AS qt
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query] AS q
    ON qt.query_text_id = q.query_text_id and qt.dbname = q.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_plan] AS p
    ON q.query_id = p.query_id and q.dbname = p.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_runtime_stats] AS rs
    ON p.plan_id = rs.plan_id and p.dbname = rs.dbname
WHERE rs.last_execution_time > DATEADD(DAY, -10, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

 


 


Jose_Manuel_Jurado_0-1657975634494.png


 


Video how to use it


 


 


 


Enjoy!

Introducing Azure Well-Architected Framework Assessments for Azure Stack Hub (Preview)

Introducing Azure Well-Architected Framework Assessments for Azure Stack Hub (Preview)

by | Jul 15, 2022 | Technology

This article is contributed. See the original author and article here.

 


Azure Stack Hub1.png


Azure Stack Hub extends the capabilities of Azure to on-premises and edge locations, enabling you to deploy, manage, and operate a subset of Azure services from your own datacenter or remote locations. The local control plane and Azure consistent developer experience provided by Azure Stack Hub can be used to address challenging technical or regulatory requirements, such as low latency, data sovereignty and security or compliance requirements. For more information, please see Why use Azure Stack Hub?


 


The Azure Well-Architected Framework (WAF) provides prescriptive guidance and recommendations for architects to use when creating or reviewing cloud solutions. The WAF guidance is organized into five pillars, Reliability, Security, Cost Optimization, Operational Excellence, and Performance Efficiency. Incorporating the recommendations into workload designs helps to ensure reliable, scalable, and performant architecture patterns are implemented for cloud solutions.


 


Today we are announcing two pillars of the Well-Architected Framework are available in Preview for Azure Stack Hub on the Microsoft Assessment Platform. These are the Reliability and Operational Excellence pillars. If you are using Azure Stack Hub to deploy and operate workloads for key business systems, it is now possible to answers questions for these pillars within the assessments platform. After completing the assessments, you will be provided with a maturity or risk score, together with prescriptive guidance and knowledge links that suggest possible improvements you could make to your architecture design and score.


 


 Azure Stack Hub2.png


 


Reliability


The Reliability pillar of the WAF for Azure Stack Hub focuses on User workload solutions that have been deployed on an Azure Stack Hub scale unit, with questions probing architecture design areas such as high availability, disaster recovery, app and data resiliency capabilities, error handling, performance, and monitoring.


 


 


Azure Stack Hub3.png


 


When designing applications using the guidance in the Well-Architected Framework Reliability pillar, the objective is not to avoid any and all failures – it is to respond to failure in a way that avoids downtime and data loss. This is achieved by increasing the resiliency of your applications using patterns that include data replication and application failover capabilities. These design principles are used to provide high availability and increase system uptime, which is a key success-criteria for business-critical systems.


 


Operational Excellence


The questions in the Excellence pillar for Azure Stack Hub, focus on the Cloud Operator Persona. As a Cloud Operator, you use the administrator portal and PowerShell for day-to-day management and operations of Azure Stack Hub, such as populating the Marketplace, defining quotas, plans and offers, monitoring infrastructure health, and applying platform updates.


 


 


Azure Stack Hub4.png


 


The questions in the Operational Excellence review focus on identifying what processes and procedures Cloud Operators are using to effectively manage and operate the Azure Stack Hub scale unit. Question topics include, identity and access management, network integration, monitoring, and alerting, offering services, capacity management, updates, business continuity and disaster recovery (BC/DR) and effective support and administration.


 


Cloud Operators can use the Azure Well-Architected Review to complete a set of easy-to- questions in order to obtain their maturity score, together with a list of curated / individual recommendations and guidance for how they can improve the processes and procedures they use for operating and managing Azure Stack Hub. An example of the assessment in action is shown below:


 


 


 WAF_ASH_Demo.gif


 


 


We are working to bring the three remaining pillars of the Azure Well-Architected Framework (WAF) to the Assessments Platform for Azure Stack Hub in the coming months. If you have any feedback or comments in relation to the Azure Stack Hub WAF Assessments, please send an email to AzS-WAF-Feedback@microsoft.com.


 


To get started with the Azure Stack Hub Well-Architected Review Assessments today, please visit this link: https://aka.ms/architecture/review


 


About the Author 


Neil Bird is a Senior Program Manager in the Azure Edge & Platform Engineering team at Microsoft. His background is in Azure and Hybrid Cloud infrastructure, operational excellence, and automation. He is passionate about helping customers deploy and manage cloud solutions effectively using Azure and Azure Edge technologies.


 


 

Microsoft Defender for Cloud PoC Series – Multi-cloud with AWS

Microsoft Defender for Cloud PoC Series – Multi-cloud with AWS

by | Jul 14, 2022 | Technology

This article is contributed. See the original author and article here.

Introduction:


The purpose of this article is to provide specific guidelines on how to perform a Proof of Concept (PoC) for Microsoft Defender for Cloud’s native Amazon Web Services (AWS) support. This article is part of a series of articles called , each providing specific guidelines on how to perform a PoC for a specific Microsoft Defender for Cloud plan. For a more holistic approach and where you need to validate Microsoft Defender for Cloud’s Cloud Security Posture Management (CSPM) and Cloud Workload Protection (CWP) capabilities all up, see the How to Effectively Perform an Microsoft Defender for Cloud PoC article.


 


Planning:


This section highlights important considerations and availability information that you should be aware of when planning for the PoC. Let’s start with outlining how to go about planning for a PoC of Microsoft Defender for Cloud native AWS support.


NOTE: At the time of writing this article, Microsoft Defender for Cloud native AWS support isn’t available for national clouds (such as Azure Government and Azure China 21Vianet). For most actual information, see Feature support in government and national clouds.


The first step begins with a clear understanding of the benefits that enabling the native AWS support in Microsoft Defender for Cloud brings to your organization. Microsoft Defender for Cloud’s native AWS support provides:



  • Agentless CSPM for AWS resources

  • CWP support for Amazon EKS clusters

  • CWP support for AWS EC2 instances

  • CWP support for SQL servers running on AWS EC2, RDS Custom for SQL Server


The CSPM for AWS resources is completely agentless and at the time of writing this article, supports the data types in AWS as mentioned towards the end of Providing you with recommendations on how to best harden your AWS resources and remediate misconfigurations.


Keep in mind that the CSPM plan for AWS resources is available for Free. Refer this for more information.


The CWP support for Amazon EKS clusters offers a wide set of capabilities including discovery of unprotected clusters, advanced threat detection for the control plane and workload level, Kubernetes data plane recommendations (through the Azure Policy extension) and more.


The CWP support for AWS EC2 instances offers a wide set of capabilities, including automatic provisioning of pre-requisites on existing and new machines, vulnerability assessment, integrated license for Microsoft Defender for Endpoint (MDE), file integrity monitoring and more.


The CWP support for SQL servers running on AWS EC2, AWS RDS Custom for SQL Servers offers a wide set of capabilities, including advanced threat protection, vulnerability assessment scanning, and more.


Now that we’ve touched briefly on the benefits that Microsoft Defender for Cloud’s native AWS support provides, let’s move onto the next step. Next up is identifying which use cases the PoC should cover. A common use case might be that Management ports of EC2 instances should be protected with just-in-time network access control, or blocking public access on S3 buckets.


 


Preparation and Implementation:


This section highlights the requirements that you should be aware of before starting the PoC. For the complete list of pre-requirements, see the Prerequisites section.


There are three main steps when preparing to enable Microsoft Defender for Cloud’s native AWS support.



  1. Determining which capabilities are in the scope of the PoC


At the time of writing this article, Defender for Cloud supports the following AWS capabilities: (see Figure 1):



  • Agentless CSPM for AWS resources

  • CWP support for Amazon EKS clusters

  • CWP support for AWS EC2 instances

  • CWP support for SQL servers running on AWS EC2, RDS Custom for SQL Server


Figure 1: Native CSPM and CWP capabilities for AWS in Microsoft Defender for CloudFigure 1: Native CSPM and CWP capabilities for AWS in Microsoft Defender for Cloud


 


 



  1. Selecting the AWS accounts on which you’d like to perform the PoC


For the purposes of this PoC, it’s important that you identify which AWS account(s) are going to be used to perform the PoC of Defender for Cloud’s native AWS support. You can choose a single AWS account or optionally, you can choose a management account, which will include each member account discovered under the provisioned Management account.



  1. Connecting AWS accounts Microsoft Defender for Cloud


You can connect AWS accounts to Microsoft Defender for Cloud with a few clicks in Azure and AWS. For detailed technical guidance see Microsoft Docs. For a video of step-by-step guidance on how this process looks like end-to-end in Azure and AWS, see this short video.  


Figure 2: Connecting AWS accounts to Microsoft Defender for CloudFigure 2: Connecting AWS accounts to Microsoft Defender for Cloud


 


NOTE: Our service performs API calls to discover resources and refresh their state. If you’ve enabled a trail for read events in CloudTrail and are exporting data out of AWS (i.e. to an external SIEM), the increased volume of calls might also increase ingestion costs and we recommend filtering out the read-only calls from the Defender for (as stated here, under ”Important”).


 


Validation


Once you’ve created the connector, you can validate it by analyzing the data relevant to the use cases that your PoC covers.


When validating recommendations for AWS resource, you can consult reference list of AWS recommendations.


When validating alerts for EC2 instances, you can consult reference list of alerts for machines.


When validating alerts for EKS clusters, you can consult reference list of alerts for containers – Kubernetes clusters.


When validating alerts for SQL servers running on AWS EC2 and AWS RDS Custom for SQL Server, you can consult reference list of alerts.



 


Closing Considerations:


By the end of this PoC, you should be able to determine the value of the native AWS support in Microsoft Defender for Cloud and the importance of having it enabled for your AWS resources. Stay tuned for more Microsoft Defender for Cloud PoC Series here.


 


P.S. To stay up to date on helpful tips and new release, subscribe to our Microsoft Defender for Cloud Newsletter and join our Tech Community where you can be one of the first to hear the latest Defender for Cloud news, announcements and get your questions answered by Azure Security experts.


 


Reviewers:


Or Serok Jeppa, Senior Program Manager

Juniper Networks Releases Security Updates for Multiple Products

by | Jul 14, 2022 | Security, Technology

This article is contributed. See the original author and article here.

Juniper Networks has released security updates to address vulnerabilities affecting multiple products. An attacker could exploit some of these vulnerabilities to take control of an affected system. 

CISA encourages users and administrators to review the Juniper Networks security advisories page and apply the necessary updates.