Problem: I have a staging DB server on which functionality is tested before pushing it to the production environment. Now, we want to start doing VA/PT (Vulnerability Analysis and Penetration Testing) on our application, but since that can be destructive on the staging DB server, we want to make a separate Testing environment with a VAPT web+DB server. Requirements: 1. The data from the staging DB server must be replicated onto the VAPT DB server automatically so that specific new use cases, features, etc can be tested for vulnerabilities. 2. Due to VAPT activities (testing data, SQL Injection, possibly DROP TABLE exploits, etc) the VAPT DB server itself will also have its own data changes, i.e. divergence from Staging DB (Master) So, if I use simple Master-Slave replication as below I am assured of #1: Staging DB (Master) -> VAPT DB (Slave) But if I do #2, the slave will eventually diverge, which is fine for the testing environment, but, will it interrupt or mess with the Master-Slave replication as per #1? An obvious example where divergence will cause errors is a VA/PT activity that causes DROP TABLE users so that the Staging DB (Master) users table trying to INSERT/UPDATE data will cause replication errors. Some UPDATEs/DELETEs might cause errors too. In particular, If I use ROW-based replication divergence will happen quickly causing frequent errors. If I use STATEMENT-based replication, since ids will not match, it is possible that some data will break because ids are essential to link data in related tables even though we do not use foreign keys. Alternatively, instead of replication, I could **manually dump the Staging DB into the VAPT DB daily**, which would be cumbersome to automate. OR, I could make copy DBs and setup various partial copy operations, but that would complicate matters too much, given that I am not a developer and that my developers often make and revert changes of various sizes randomly. *EDIT: The data directory varies between 20-25 GB on Staging* Surely someone has come across this problem in their work so far and there might be a set of best practices for this situation i.e. maintaining a match between staging and testing environments in real-time while allowing testing freedom to play with the data. I tried googling for a while but the right phrasing for google escapes me. All I get is howtos for master slave replication, handling unwanted drift/divergence and so on. Nothing much about desired/accepted drift and divergence or partial replication. Thanks in advance.

I'm getting a SQL vulnerability error discovered by Microsoft Defender for Cloud. The error points to the following Vulnerability Assessment rule . But when I try to set a baseline (recommended by this rule), I get the following error: >The client 'tom.doe@mydomain.com' with object id 'eXXXXXX-...' does not have authorization to perform action 'Microsoft.Sql/managedInstances/databases/vulnerabilityAssessments/rules/baselines/write' over scope '/subscriptions/subscriptionid/resourceGroups/myResourceGroup/providers/Microsoft.Sql/managedInstances/mySQLManagedInstance **Question**: What permissions are required to set the baseline here? | Rule ID | Rule Title | Severity | Rule Description | |---------------------|------------------|------------|--------------------| | VA1281 | All memberships for user-defined roles should be intended| Medium | User-defined roles are security principals defined by the user to group principals to easily manage permissions. Monitoring these roles is important to avoid having excessive permissions. Create a baseline that defines expected membership for each user-defined role. This rule checks whether all memberships for user-defined roles are as defined in the baseline. |

I'm looking for ideas here. I've inherited a large SQL estate with multiple servers in multiple configs that has grown over 10-15 years with no specific DBA-skilled individual in charge. I'm the first DBA to join the organisation (and the only one, currently). As the title says there are ~700 DBs on ~70 servers with different functions, and different approaches to security within them. A mix of home-grown and vendor supplied databases. I've installed the Microsoft Monitoring Agent across the estate to build an overview in Azure of what's going on - and as it turns out there are quite a few problems. I'm not really sure where to begin here. I could tackle the high severities first for instance. There is also some mileage in identifying servers that should be decommissioned. I've already upgraded servers that are running unsupported SQL versions (2012 and prior). But a lot of the recommendations are things like "disable the SA account" which is not always as simple as it sounds, particularly if it's been used to drive certain database functionality. Where would you start? What would you do?

I'm using on-prem SQL 2022 enterprise edition. SQL vulnerability assessment option is taken out from SSMS 19.1 onwards. I'm reading that this feature is now part of defender for cloud. If anyone has tried this then is this free? And do I need to ask by Azure admin to setup defender for cloud now just to be able to do SQL vulkerabity assessment?

Actually I have silly question. If I install below update during working hours then is it occurring any interrupt ? Also I have checked release notes. But I couldn't find anything. Security Updates for Microsoft SQL Server OLE DB Driver (June 2023)

I am trying to establish whether SQL Server is affected by the recently discovered OpenSSL vulnerabilities. For example, are SQL Servers that are using TLS/Certificates to encrypt connections between client and server affected to? I believe SQL Server uses Open SSL libraries for encryption purposes. Can someone please shed light on this?

I did a Vulnerability Assessment for my database today. I found VA2108 and it suggests me to ALTER ROLE [db_owner] DROP MEMBER [dbo] But it's the default setting of any SQL instance. Should I go for it? And is there any risk? Thanks

I have an Azure Web App connected to an Azure SQL database. Let's call the app ACME. The application has it's own Role and User called ACME, and I gave that User db_owner principal roles. The authentication of the Web App is not Active Directory based, but it uses its own custom ASP.NET MVC based User and Role registry. Therefore the Web App creates Users and manages roles of those users, but those roles are technically not native SQL roles, but custom managed AspNetUserRoles. Seldom there are some schema changes and I perform the schema upgrades as ACME. Recently the [Vulnerability Assessment rules got changed](https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-database-vulnerability-assessment-rules-changelog) and V2130 got changed: it accepted dbo as db_owner by default before, but now it lists it if it's not part of the baseline. ~~Last year only ACME got added to my baseline~~. Now I need to decide: 1. Should I revoke dbo's db_owner rights? That makes me feel weird because it's such a core part of SQL that by default SQL doesn't even let you revoke dbo's db_owner rights. This is only possible if you have at least one other User who has such rights. In my case I have ACME. 2. Should I add dbo as a db_owner into my baseline besides ACME? 3. Should I add dbo as a db_owner into my baseline **and** curtail ACME's principals to not be so powerful as a db_owner. This is to adhere to the least privileges basic security rule. So should I downgrade ACME to for example to a db_ddladmin. I was thinking about DDL admin because of the occasional schema changes. More generally: what could be the most secure and best practices (regarding User privileges and principles) for this common scenario? Exhibit A: VA2130 query before 2020 December, watch the principal_name != 'dbo':

-sql
WITH UsersAndRoles (principal_name, sid, type) AS 
(
    SELECT DISTINCT prin.name, prin.sid, prin.type 
    FROM sys.database_principals prin 
        INNER JOIN ( SELECT *
                     FROM sys.database_permissions
                     WHERE type = 'CO' 
                        AND state IN ('G', 'W')
        ) perm 
            ON perm.grantee_principal_id = prin.principal_id 
        WHERE prin.type IN ('S', 'X', 'R', 'E', 'G')
    UNION ALL
    SELECT 
        user_name(rls.member_principal_id), prin.sid, prin.type
    FROM 
        UsersAndRoles cte
        INNER JOIN sys.database_role_members rls
            ON user_name(rls.role_principal_id) = cte.principal_name
        INNER JOIN sys.database_principals prin
            ON rls.member_principal_id = prin.principal_id
        WHERE cte.type = 'R'
),
Users (database_user, sid) AS
(
    SELECT principal_name, sid
    FROM UsersAndRoles
    WHERE type IN ('S', 'X', 'E', 'G')
        AND principal_name != 'dbo'
)
SELECT DISTINCT database_user AS [User], sid AS [SID]
    FROM Users
    WHERE sid != 0x01

Exhibit B: VA2130 query after the 2020 December rule changes:

-sql
WITH UsersAndRoles (principal_name, sid, type) AS 
(
    SELECT DISTINCT prin.name, prin.sid, prin.type 
    FROM sys.database_principals prin 
        INNER JOIN sys.database_permissions perm 
            ON perm.grantee_principal_id = prin.principal_id 
        WHERE prin.type in ('S', 'X', 'R')
    UNION ALL
    SELECT 
        user_name(rls.member_principal_id), prin.sid, prin.type
    FROM 
        UsersAndRoles cte
        INNER JOIN sys.database_role_members rls
            ON user_name(rls.role_principal_id) = cte.principal_name
        INNER JOIN sys.database_principals prin
            ON rls.member_principal_id = prin.principal_id
        WHERE cte.type = 'R'
),
Users (database_user, sid) AS
(
    SELECT principal_name, sid
    FROM UsersAndRoles
    WHERE type in ('S', 'X')
)
SELECT DISTINCT database_user, sid
    FROM Users
    WHERE sid != 0x01

An extra note: I'm aware of https://dba.stackexchange.com/questions/284571/should-i-drop-dbo-from-the-database-role-db-owner however I think it's kinda negligent to just add dbo as a baseline and move on without thinking about and discussing the ACME User which many developers have in case of ASP.NET MVC and other apps. --------------- Note: I was confused looking through multiple Vulnerability Scan items, and realized that ACME user thankfully does not have any kind of special database role! --------------- Related to VA2108: https://github.com/MicrosoftDocs/azure-docs/issues/70391

Every week I'm getting a failed "Vulnerability Assessment" email report from my Microsoft Azure SQL Database that is incorrect.
It complains about columns that have **is_computed** column in sys.columns set to 1.
It is not possible to add a classification for those columns. Is it possible to stop the warning?

I'm trying to disable xp_Cmdshell and rpc_out and when I run the commands on query analyzer it shows it's disabled. But after this I need to run a security scan report which provides me the following report that it's has not been disabled. Can anyone help me? > 5 Microsoft SQL Server Database Link Crawling Command Execution > > QID: > 19824 > > Category: > Database > > CVE ID: > - > > Vendor Reference > - > > Bugtraq ID: > - > > Service Modified: > 02/20/2013 > > User Modified: > - > > Edited: > No > > PCI Vuln: > Yes > > THREAT: > Microsoft SQL Server is exposed to a remote command execution vulnerability. > > Affected Versions: > Microsoft SQL Server 2005, 2008, 2008 R2, 2012 are affected. > > IMPACT: > Successful exploitation could allow attackers to obtain sensitive information and execute arbitrary code. > > SOLUTION: > There are no solutions available at this time. > Workaround: > Disable RPC_Out and xp_cmdshell for this issue. > > COMPLIANCE: > Not Applicable > > EXPLOITABILITY: > There is no exploitability information for this vulnerability. > > ASSOCIATED MALWARE: > There is no malware information for this vulnerability. > > RESULTS: > C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn\sqlservr.exe Version is 2009.100.4000.0

I want to run SQL Server Vulnerability Assessment from a SQL Server Agent Job. Currently, I am attempting a job with a PowerShell script and am running a command like the one below. Invoke-SqlVulnerabilityAssessmentScan -ServerInstance $(ESCAPE_DQUOTE(SRVR)) -Database AdventureWorks I have confirmed that Invoke-SqlVulnerabilityAssessmentScan is available on the SQL Server (I can run it from the PowerShell command prompt there), but when I run my job, I receive an error stating that > The term 'Invoke-SqlVulnerabilityAssessmentScan' is not recognized as the name of a cmdlet After looking at this Microsoft article , I am wondering if SQL Agent only has a subset of PowerShell cmdlets that it can access. How can I run the vulnerability assessment scan from a SQL Job?

Can anyone help me explain on what basis 'Data Migration Assessment' features recommendation suggests for Dynamic Data Masking and displays certain objects/tables columns for masking. On what basis DMA identifies those columns for vulnerabilities. Is there any set algorithm for identification or is it simply based on the name of the column? Please comment in case of any more details or clarification required for the question.

I am running Vulnerability Assessments against my SQL Servers and databases using the PowerShell cmdlets Invoke-SqlVulnerabilityAssessmentScan and Export-SqlVulnerabilityAssessmentScan, however, I am hitting an error on one database. I receive the error > Scan size: 50864310 is greater than 10485760 bytes. The scan JSON file is nearly 50 MB in size, whereas the other scans for databases on this server are < 10 MB in size, which seems to be a limit for the PowerShell cmdlet to export. SSMS is able to run the scan and export the file without any issues. Is there any way around this scan result size limitation using the PowerShell cmdlets?