Stairway to SQL Server Security Level 11: Auditing (2023-03-08)

SQLServerCentral Newsletter for March 8, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to SQL Server Security Level 11: Auditing]( - [Complex Production Database Deployments and Flyway]( - [AWS Lambdas with C#]( - [From the SQL Server Central Blogs - Documentation vs Automation]( - [From the SQL Server Central Blogs - 30 Interview Questions for a Database Administrator and Developer]( - [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Question of the Day - [Deterministic Encryption Operations]( The Voice of the DBA  Daily Coping Tip Ask someone how their day is going and really listen to them I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Has Everyone Had a Data Breach? Almost every time that I attend an event, I'll end up meeting someone that has had security issues at their company. I'm always surprised how many people have had ransomware or other security problem that didn't get well publicized. It's not like everyone has had one, but out of 100 people, it seems there is at least one issue. Many of us work with third-party companies for either products or services. It's become standard to use other firms for specific things your organization needs. However, since that's the practice, many of those firms you partner with have their own partners. After all, they're using other companies for specialized work just like you are. These third- and fourth-party relationships have changed our security and risk profiles for the worse. As the numbers of data breaches and security issues grow, it's likely that someone in your partner network has had an issue, which might mean that you have an issue. This depends on what you have contracted with partners for, but it seems more and more often this is some sort of service provided, often with your data being shared with the partner. Which could mean your data is shared with their partners. [An article recently noted]( that the number of partners are going up and many organizations are not aware of the risk this creates for them and their customers. There are more and more third- and fourth-party partners who have suffered data breaches, and if they have shared our data, we may have liability. The weakest link in a supply chain is the problem, and many of us have lengthened our supply chains quite a bit without paying attention. I don't know that there are good solutions here, but I am seeing more and more companies demanding that suppliers of services prove they have strong security practices and protocols in place. It's not perfect, but it does help us remember that security is everyone's business, or at least everyone with whom we share our data. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to SQL Server Security]( [Stairway to SQL Server Security Level 11: Auditing]( Don Kiely from SQLServerCentral.com By defining server- and database-level audits, you can record just about any kind of event that occurs in SQL Server, which can be an invaluable source of security troubleshooting and forensic information when security breaches occur. In this stairway level you’ll learn how to define the various audit specification objects, how to capture audit data, and how to explore and use the data. [External Article]( [Complex Production Database Deployments and Flyway]( Additional Articles from Redgate This article explains how, by use of schemas and stub interfaces, we can use Flyway to manage the main development work smoothly alongside any changes or additions required to maintain production-only code. [External Article]( [AWS Lambdas with C#]( Additional Articles from SimpleTalk In this article, I will take you through the development process of building an API on the serverless cloud with C#. This API will be built to serve pizzas, with two endpoints, one for making pizza and the other for tasting fresh pizzas. I expect some general familiarity with AWS and serverless computing. Any previous experience with building APIs in .NET will also come in handy. [Blog Post]( From the SQL Server Central Blogs - [Documentation vs Automation]( hellosqlkitty from SQLKitty Originally, I was going to write a post on troubleshooting SQL Server. This is because I’m writing up and reviewing a lot of documentation at my current job. I’ll... [Blog Post]( From the SQL Server Central Blogs - [30 Interview Questions for a Database Administrator and Developer]( Will Assaf from SQL Tact This blog post summarizes the type of technical questions I would ask candidates for a Microsoft SQL Server data platform administrator and database developer role.Hopefully this helps both candidates... [Pro Encryption in SQL Server 2022]( [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Additional Articles from SQLServerCentral This in-depth look at the encryption tools available in SQL Server shows you how to protect data by encrypting it at rest with Transparent Data Encryption (TDE) and in transit with Transport Level Security (TLS). You will know how to add the highest levels of protection for sensitive data using Always Encrypted to encrypt data also in memory and be protected even from users with the highest levels of access to the database. The book demonstrates actions you can take today to start protecting your data without changing any code in your applications, and the steps you can subsequently take to modify your applications to support implementing a gold standard in data protection.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Deterministic Encryption Operations In Always Encrypted, which of these operations are allowed with deterministic encryption? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Dropping One Table I have this DDL trigger on SQL Server 2019: CREATE TRIGGER NO_DROP_ImportantTable ON DATABASE FOR DROP_TABLE AS DECLARE @Table SYSNAME; SELECT @Table = EVENTDATA ().value ('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'); IF @Table = 'ReallyImportantTable' ROLLBACK; GO A user (with DROP TABLE rights) runs this in SSMS on the database with default settings: DROP TABLE dbo.MyNewTable DROP TABLE dbo.ReallyImportantTable Both dbo.MyNewTable and dbo.ReallyImportantTable exist. What is the result of this code? Answer: MyNewTable is deleted, but ReallyImportantTable is not with an error message returned Explanation: This DDL trigger prevents the deletion of ReallyImportantTable. Even though both DROP TABLE statements are in one batch, they are separate transactions, so MyNewTable is deleted, and the ReallyImportantTable deletion is rolled back. The rollback generates an error message. Ref - DDL Triggers - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [waittypes in sql server]( - Hi All,  We have a UAT sql instance running on SQL 2017 EE CU23. 16cpu's - 256GBRAM max server memory set to 230GB max degree of parallelism set to default 0. At database level MAXDOP is set to 4. Tempdb configuration : 8 files and 1 logfile.  When load testing is done, we […] SQL Server 2016 - Administration [SQL 2016 updates]( - Hello, so Microsoft has two Feb 2023 SQL updates listed under the same CVE. 5021128 Security update for SQL Server 2016 SP3 Azure Connect Feature Pack+GDR, and 5021129 Security update for SQL Server 2016 SP3+GDR. I support three 2016 SQL VMs within VMWare. Question; why two updates for 2016 SQL, as they both address the […] SQL Server 2016 - Development and T-SQL [Additional records needed with Using MAX function on Most Recent Record]( - Hello - I would like to show a record for a patients last visit regardless of what doctor they saw. If patient saw a doctor who is not their primary doctor on their last visit by date, show the record and then show the record of the last time they saw their primary doctor. If […] Development - SQL Server 2014 [Update row using different steptype within same order]( - Hi All, been a while since I posted. Facing weird calculation, I cant wrap my head around on how to google it. I have a data that looks like this (simplified): Need to calculate Lead Time, rules as follow: For StepType 1: If Actual is empty: Today minus Steptype 10 "schedule" date Actual is not […] SQL Server 2019 - Administration [Semaphore Errors in Variety of Applications]( - Hello - about 2 months ago our users started to receive semaphore errors in a variety of applications we run with SQL Server backends, including SSMS. All of our servers are hosted in Azure and our SQL servers are traditional server setups, we are not using Azure SQL managed instances or databases. Resources on the […] [Download Sql Server 2019]( - Hi, I am trying to download Sql Server 2019 enterprise edition, but I am not able to find a link to download the .exe file.  Can someone help me with it?  Thanks. [Right way and SSIS sql server integration services projects instalation]( - Hi there, i ´m new in this roads, and i don´t see the right topic to wrote this . I'm connected to an instance on a standard SQL 2019 server. I installed SQL Dev 2019 on my pc and visual studio 2022 community, but I can't install ssis because during the installation it doesn't recognize […] SQL Server 2019 - Development [Join 4 Columns of Date/Time together]( - Hi Forumers, I have a file with 4 Columns pertaining to Date & Timestamp Values. I want to join them to show the 4 Columns as one Column with forward slashes between the Year/Month/Day and then a Colon between the Year/Month/Day & the Timestamp field. An example is attached, thanks for your help.  [SQL - Grouping SQL output in 5 minute intervals]( - I have a dataset that has a date column every 1 minute. I wanted to group the information to have the data "organized" every 5 minutes and average the SellCount column. For example, I have 5 records, 1 for minute 1, another for minute 2, another for minute 3, another for minute 4 and finally […] Reporting Services [how to test https for SSRS reports in SSRS web portal]( - We have configured to use https on the SSRS server with port 443. so for the web portal URL is  How can I test the https is working or not from SSRS web portal? Thanks [PL/SQL to SSRS: how to migrate report events----- From ORB to SSRS]( - Hi everyone, I was wondering if there is an equivalent of the "after report" and "before report" events that existe in Oracle Report Builder. In ORB, we can use these events to handle errors or update parameters and to much thing before or after report execution in PL/SQL. Can anyone tell me if SSRS has […] Design Ideas and Questions [Help with database design]( - Hi all, I'vebeen using an excel spreadsheet to capure info on oranistions and vehcile related data and would like to tranfer this into the Microsoft dataverse environment. As you can probably tell, my database skills aren't as good as my excel skills. I'd be grateful if someone could cast there eyes over the design below […] SQL Server 2022 - Administration [DBCC error with sys.Syscolpars table]( - Consistency error in DB. 1 page from sys.syscolpars table is missing. There is no any impact but the thing is can't able to upgrade the DB from 2008 to higher version. Backup restore fails. Old good backups are not available. As this is a sys schema table so needed DAC connection to query it. But […] SQL Server 2022 - Development [Random Number Generating]( - Hi All, I need to generate random unique numbers for 10 million records. Here is my requirement The numbers should begin with 11. The minimum length of the number should be 14 The maximum length of the number should be 25 Need to generate the random unique numbers for 10 million records. Thanks Sathish Kumar […] [Please help on SQL Code]( - You are given a table of people’s openions about places in a city: Create table opinions ( Id int not null primary key, Place varchar(255) not null, Opinion varchar (255) not null ); Each row contains place’s name and an opinion about it, which is eaither recommended or not recommended. Your task is to generate […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -