• 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

• What is .GitKeep?

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