Spot the Five Differences and Win a Summit Ticket (2023-08-25)

SQLServerCentral Newsletter for August 25, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Spot the Five Differences and Win a Summit Ticket]( - [The PostgreSQL Role Part 1]( - [Screen Scraping with Python and BeautifulSoup Code Examples]( - [Managing the challenge of migrating to the cloud]( - [From the SQL Server Central Blogs - T-SQL Tuesday #165 Job Titles – What do you need?]( - [From the SQL Server Central Blogs - Finding Identity Columns–#SQLNewBlogger]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [What is .GitKeep?]( The Voice of the DBA  Removing Weak Security from SQL Server I was checking some arguments in the [RESTORE command]( for SQL Server and saw that the MEDIAPASSWORD option was deprecated and marked as being removed at some point. That made sense, and I assumed that PASSWORD was the option to be used moving forward. However, that option is also marked as deprecated. What should we do? Well, we need to better secure our backup files for sure. Disk encryption and limits on AD/directory permissions ought to be set. Of course, we need to use care when handling or moving these files, especially when they cross the secure boundary in production systems to other dev/test/etc. networks. The [security section]( of the document explains the reasoning here. The protection provided is weak and isn't intended to protect data. You can still read data in the backup file. This is mainly to prevent an incorrect restore when using tools, meaning the human picking the wrong file. This isn't to protect your data. I suspect most people dealing with SQL Server backups that use either of these options don't know this. They think the password secures their data. I know because I've seen people use this to send a backup file through email or file transfer to another party. However, if you've ever opened a backup file in [XVI32]( or another editor, you will see that your data is in plain text. If you've never done that, give it a try today and search for strings that you know are stored in the database. Some security is better than no security and layers of security that build on each other are useful. However, depending on weak security isn't good. It leads people to count on something that doesn't work and ignore more serious issues. I'm glad that Microsoft is (supposedly) removing these options. I understand that backwards compatibility and preventing existing scripts from failing are important. At the same time, we need to move forward. I'd like these password items to become a no-op, and not cause errors, but I would hope that their use in scripts would also generate a message to the user that these options don't work and need to remove removed. Perhaps with a direct message and a note in the error log, we'd start to see people embracing other security practices that will provide more protection. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Spot the Five Differences and Win a Summit Ticket]( Press Release from SQLServerCentral Spot all five differences below and enter our prize draw competition to be in with a chance of winning a 3-day pass to PASS Data Community Summit 2023. [SQLServerCentral Article]( [The PostgreSQL Role Part 1]( Shivayan Mukherjee from SQLServerCentral Learn the basics of PostgreSQL security with roles in this article. [External Article]( [Screen Scraping with Python and BeautifulSoup Code Examples]( Additional Articles from MSSQLTips.com BeautifulSoup (sometimes referred to as Beautiful Soup library) is one of several widely used screen scraping packages for a web page. It is highly regarded for its ease of use and power. Screen scraping enables developers to create solutions that permit comparative analyses from all over the internet, such as web applications that look for the best price on an item from many different online stores. [External Article]( [Managing the challenge of migrating to the cloud]( Additional Articles from Redgate Migrating to the cloud has many advantages … until you’re the one tasked with making it happen. For many data professionals, this will be a first step from the traditional, predictable challenges of managing on-premises servers to the many and varied databases and platforms in the cloud. [Blog Post]( From the SQL Server Central Blogs - [T-SQL Tuesday #165 Job Titles – What do you need?]( Diligentdba 46159 from Mala's Data Blog My dear friend Josephine Bush a.k.a HelloSQLKitty hosts this month’s T-SQL Tuesday. Josephine’s call to us is to share our understanding of the multitude of job titles available out... [Blog Post]( From the SQL Server Central Blogs - [Finding Identity Columns–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA I had to find a set of identity columns recently and through this would make a good blog post. Another post for me that is simple and hopefully serves... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  What is .GitKeep? I cloned a git repository from my company and there is a folder in the repository with a file named .gitkeep. What is this used for? 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) An Interrupted Restore I was restoring a database to my development server from a backup on a network share. This was a large, 500GB database and the process was taking a long time. There was a network interruption to the process and I got a failure message in SSMS. However, the database was shown as "restoring" in SSMS. The network is back now. What should I do to most easily complete the restore? Answer: rerun the restore command with the RESTART option Explanation: The RESTART parameter is designed for this situation. If the restore can be restarted, SQL Server will do this. Ref: - RESTORE - [ - Restart an Interrupted Restore Operation - [ [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 [Space is not getting freed up when deleting data from a log table.]( - Hi All,  We are trying to delete data from a large 5TB log table. We are able to delete the data but that is not shown up in the free space nor released to OS. The table has ntext columns as well. Application stores some xml documents in this Log table. Want to know […] SQL 2012 - General [ID increment problem (Sql Server 2012)]( - Good morning, I don't understand what's going on in my database all tables that have an auto-increment ID will stop auto-incrementing. and I have a message that the code exists. and to overcome this problem I have to delete the ID of the table and I recreate it so that it works  Cordially SQL Server 2019 - Administration [Error 500 when accessing SQL Server 2019 database - Need help!]( - I've recently set up a SQL Server 2019 database for a project I'm working on. The issue I'm encountering is that whenever I try to access the database, whether it's through my application or directly using SQL Server Management Studio, I'm getting an "Error 500" message. This error appears to be quite generic and doesn't […] [Event ID 17806]( - We have had SSPI errors across a dozen servers in an hour span. SQL error 17806 : Error code 0x8009030c – How to fix Per the above link : Stuck at the very first point... Cause of SQL error 17806 1.SQL Server engine account running with a Domain service account, and that account locked at […] [disable seeding mode]( - hello , On one of our servers with 4 Replicas 2 synchronous and 2 asynchronous we see too many waits with the "VDI_CLIENT_WORKER" type almost 93% of the wait statst do you have to deactivate the seeding mode to minimize these times ? What do you think of this recommendation? [SQLPackage Import Error]( - Hi After 1h the import fails with the error: Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 155, Level 15, State 1, Line 3 'AUTO_DROP' is not a recognized CREATE STATISTICS option. SQL Server 2019: 15.0.4316.3 sqlpackage.exe: 162.0.52.1 May the sqlpackage is to new but I didn't find the one who fits exactly. Thanks for […] [CPU Pressure.]( - Good morning! I'm seeing CPU pressure on my SQL VMs - 4 node cluster, 20 CPU Intel Xeon 2.2GHz, 128 GB RAM (100 max SQL server mem). SQL 2019 & Win 2016. The cluster is for Sharepoint 2019. I started with 95+ CPU & have stopped a large regular batch copy && some additional poorly […] SQL Server 2019 - Development [Odd columns datatype precisions showing in SSMS View...]( - Okay... haven't posted in a long time and was hoping I'd have a better topic to share but... we're seeing issues when expanding the view columns in SSMS and it's showing the wrong precision. The view is pulling from underlying tables using DECIMAL(11,2) but the view shows the columns as DECIMAL(12,2) - what would cause […] [SQL code help to get one row for each sequential entry]( - Hi, I am looking for a sql help to generate one row per each sequental door_area, for a given employee, based on the sample data that looks like this: Current Output: Desired Output: Here is my code to test for sample data. CREATE TABLE dbo.emp_seq ( emp_id int, product_code int, clock_in datetime, clock_out datetime, door_area […] [Extract value before one symbol and stop after another one in SQL]( - Hello, I am trying to extract Event Name from a long string that has some information not relevant for what I am doing at the very moment. I basically need to extract everything after 2nd '_' and before 3rd '_'. So technically I need to get Bestseller and Promotion for the examples that I am […] Reporting Services [Error Uploading RDL]( - I have reporting services 2016 and change url protocol from http to https, but if upload the rdl file I have error notification "Something went wrong. Please try again later." and cannot create a new data source with the same error message. Anyone can help? Thanks. Design Ideas and Questions [Is it a good option to store monthly total in database?]( - I want to ask if it is a good idea to store monthly total values into SQL database for my application. I'm sorry but I have to ask again for the same project I asked some months ago, but for a different question. let me first explain the context : I have a legacy application […] SQL Server 2022 - Administration [Having problems with SQL Server 2022 CU5 (docker ubuntu image) - is it ready?]( - Ubuntu image came from here - DBCC is failing - "can not create database snapshots" syspolicy_purge_history is failing due to a missing PowerShell installation master.sys.dm_server_services - is not listing the engine at all while listing agent running? Can't enable SQL Server Agent Alerts - most likely due to master.dbo.sp_MSsetalertinfo and msdb.dbo.sp_set_sqlagent_properties usage of registry SQL Server 2022 - Development [Searching in date field provides inconsistent results]( - I have been working with SQL Server for too many years to count and this is baffling; perhaps someone has a clue on the cause. I am running this on SQL Server 2022. I put together a simple function to return a primary key integer for a lookup table that has mileage rates that are […] [How to get max date for each column in SQL Server]( - Hello All, I have a table where I am having data like below. Here my requirement is I need to get the device names which has the max sync date. So my output would be like below. Could any one please help how to write query for this  Here is my data : create […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -