A Few Best Practices for Strong SQL Server Security (2024-03-01)

SQLServerCentral Newsletter for March 1, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Live webinar: How to excel at managing multiple database platforms]( - [A Few Best Practices for Strong SQL Server Security]( - [From the SQL Server Central Blogs - EightKB 2024]( - [From the SQL Server Central Blogs - Friday Flyway Tips–Capture the Filegroup for Tables]( - [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Question of the Day - [Writing to Clones]( The Voice of the DBA  Using AI for Security AI (Artificial Intelligence) systems and technology has been all over our industry for the past year or so, ever since ChatGPT released the initial public version in late 2022. It seems that there is a lot of hype around the possibilities, with plenty of excitement and skepticism, depending on who is talking about the tech. However, there do seem to be some places where the technology is working well, and security is one of them. There is [an article about how Microsoft is using AI to help spot ransomware]( which seemed to have run rampant a few years ago. It's still around, though it seems fewer exploits are being publicized. That might be because systems are better protected, perhaps there are fewer attacks (unlikely), or maybe more organizations are getting better at covering up their issues. They might be better prepared to restore backups or quicker to pay a ransom. In any case, Microsoft is exploring machine learning (ML, a subset of AI) to detect patterns and behaviors that can indicate a ransomware campaign is starting on a system. Looking through logs of activity for unusual behavior is something ML might be much better at, or faster at, than humans. I certainly know that if I were running queries that might look at my activity on systems, taking a guess about whether or not the activity this week is "regular" and matches patterns from last week is hard. Often exact matches of activity patterns cause lots of false positives if they are too tightly written. If we loosen the parameters too much, we miss potential attacks. A fuzzy view of the pattern is needed, something ML is good at detecting. After all, we need to look at all activity from all users, and determine if Steve's activity this week is different than last week, and at the same time, is Grant's activity unusual and a sign that his account is compromised? Some humans are very good at spotting patterns in activity, but only at a limited scale. We get tired, our minds wander, and we can't only focus on looking for patterns in log files. We'll get bored, distracted, and start to make mistakes. AIs don't get tired, and while they might miss some anomalous activity, and certainly will report plenty of false positives, humans can focus on this subset of reports and perhaps partner with AIs to do a better job helping secure our systems. I lean towards the idea that AI technology will help us better spot malicious activity in the tremendous amount of data we capture about our networked systems when humans are attempting to hack us. What I'm not sure about is how well criminal actors will use AI tech to further disguise their activity. I can certainly see a future where lots of AI bots battle each other at blinding speed while humans watch and hope the defenders manage to outwit their attacking AI opponents. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Live webinar: How to excel at managing multiple database platforms]( Press Release from SQLServerCentral Learn about how to handle the requirements of multitple platforms in this webinar. [SQLServerCentral Article]( [A Few Best Practices for Strong SQL Server Security]( Smit Dagli from SQLServerCentral Explore key strategies for securing SQL databases in this comprehensive guide. Learn about robust authentication, data encryption, SQL injection prevention, and more, with practical code examples. Enhance your database's security and performance by following tips from this article. [Blog Post]( From the SQL Server Central Blogs - [EightKB 2024]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold EightKB is back! The biggest online SQL Server internals conference is back in 2024…it’s all happening on August the 8th! We’ve open our call for speakers, you can submit... [Blog Post]( From the SQL Server Central Blogs - [Friday Flyway Tips–Capture the Filegroup for Tables]( Steve Jones - SSC Editor from The Voice of the DBA A customer asked recently why Flyway doesn’t detect the filegroup for some changes. I showed them it does and decided to write a post on this. I’ve been working... [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Site Owners from SQLServerCentral Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Writing to Clones I run this code on SQL Server 2019: DBCC CLONEDATABASE(imdb, imdb_dev) I then change to the cloned database, imdb_dev, and run some queries. I then run this code while testing: INSERT dbo.Title (TitleID, Title, DateReleased) VALUES (3234, 'Maestro', '2023') GO What happens? 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) Query Execution Modes What are the two distinct processing modes that the SQL Server Database Engine can use to process T-SQL statements? Answer: Row mode and Batch mode Explanation: The two ways that queries are processed at row mode and batch mode. Ref: Query Processing Architecture - [ [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 [Estimated rollback completion: 0%. Estimated time remaining: 0 seconds]( - Greetings! I have a process that has been running for over a day (normally it runs about 30 minutes), tried "Kill SPID", it shows "Commands completed successfully." but it is still running. tried "Kill Spid with StatusOnly", it shows "transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds."  The sql […] [Troubleshooting sql issue where multiple sessions are involved]( - Hi All, We have an application that spawns multiple spid's, runs multiple multiple batches , multiple txns. While troubleshooting a BLOCKING, initially thought to trace one spid and collect the sql statements for that session. But eventually, dev team and we saw multiple spid's are spawned as part of that process and even multiple databases […] SQL Server 2019 - Administration [SSMS - Consistency in Scripting Options Across Team]( - Hi, We use SSMS' "generate scripts" feature to script out all DB objects for source control. We have our scripting options dialed in the way that we like them under Tools -> Options -> SQL Server Object Explorer -> Scripting. Historically it had been a small handful of people that go through the process of […] [Filestream Filegroups on state RESTORING]( - Hello All, I have a question. My SQL Servers are running SQL Server 2019 CU23. I have a setup where i have a logship copy of a production database available on a secondary server. This copy is Standby/Read-Only. This database uses FileStream to store BLOB data. There are several filegroups for the FileStream Data. So […] SQL Server 2019 - Development [TSQL Pivot Crosstab]( - Hi, I am doing a crosstab but I am unsure if its a pivot or is there a simpler way of doing it. Create Table TblPerson (ID Int, Name Varchar(50), Child Varchar(50)); SELECT * FROM TblPerson ID, Name, Child  The output should look like this, not everyone has equal number of items so some […] [Find dependencies on a table]( - I am in the process of doing a re-write for my project so I need to find all SP that use a particular table. SS has a tool that lets me do this. How reliable is it? I tried sp_depends and compared the output from it and the above approach...they don't give me the same […] [what does this error mean?]( - I was running a SP and I got this. This is the first time I have ever received this. What does it mean? How do I fix it so it doesn't happen again? Msg 9002, Level 17, State 4, Procedure dbo.Query, Line 223 [Batch Start Line 2] The transaction log for database 'myDB' is full […] [how to properly index?]( - I am not sure how to decide on how I should index my tables so the query is able to run as fast as possible. I am fairly new to SS. My queries are all SELECT. Some use GROUP BY but others do not. There are no DELETE/UPDATE. How do I decide which type of […] [Need help read JSON file]( - Hello, I've JSON file format as following, { "acc_div": [ { "level1_code" : 1104, "level2_code" : "01", "gl_code" : "0900", "description" : "Finance Department - Estate Account ", "short_name" : "FINANCE " }, { "level1_code" : 1106, "level2_code" : "01", "gl_code" : "0933", "description" : "Marketing Department - SGRK ", "short_name" : "MKTG-KKSR " }, […] [when peers maintain a job rdp'd in they corrupt the job]( - Hi, one of my peers prefers to maintain one specific sql agent job after rdp'ing into that server. I think its probably because he usually wants to then restart the job knowing it will continue running even if he has to shut down his own pc. I'm not sure but i think if you restart […] [import json file]( - Any idea on how to import a json file into sql server? I've never done one before. I saw some example like below but the file I have had columns report for different sections.  SELECT import_data.* FROM OPENROWSET (BULK 'C:\Files\import_data\small_json.json', SINGLE_CLOB) as j CROSS APPLY OPENJSON(BulkColumn) WITH( name varchar (255), object_id varchar (255) , […] Reporting Services [Upgrading the SQL version]( - Hi we have an old sql server 2012 box that (among other things) hosts SSRS (front and back end). I need to upgrade the version of SQL. When I do this will SSRS take care of itself or are there other things I need to be mindful of? I have vague memories of doing this […] General [Career Advice]( - hello folks, I am posting this question with heavy heart. I have been a SQL server developer through out my career. I love it from all my heart. I love designing efficient database designs based on the business requirement of an existing/new application. I love playing with the data. Problem solving, bug fixing, performance tuning […] SQL Server 2022 - Administration [Sql 2022 Alway ON]( - Hi i need to setup a sql server 2022 onprem replicated with sql always on availability group on azure. On prem do I need to setup a cluster or can it be a single vm? then replicate it to azure? canI use ASR to orchestrate recovery? I found some articlesthat says SQL AwaysOn (Recovery Plan […] [Best Practice for full backup and transaction log truncation]( - I am new to SQL Server DB. Could someone please let me know what would be the best practice to do a full backup and truncate the transaction log. If you could please let me know the strategy you are using in your company, that would give me some idea and help me learn from […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -