When was my SQL Server Database Last Accessed?
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
1w ago
Your boss walks up to you one morning and says, “Hey, I wanna list of all of our databases and when they were last accessed”. If you’ve got some sort of auditing switched on or a trace or xevent catching this sort of info you might be ok, but I’m betting you don’t have any of that. That’s cool, it’s not something that I tend to monitor as standard either. But if you’re not monitoring it, is there any way that you can get at that info? SQL doesn’t really give us anything obvious here, there’s no magic, ‘last_access_date’ column in sys.databases or anywhere else for that matter. There may be a ..read more
Visit website
Sp_snapshot – The easy way to take database snapshots of one of more databases – Version 2.0
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
2M ago
Presenting you with an updated version of our sp_snapshot procedure, to easily create database snapshots. This new version adds more flexibility to the procedure, allowing you to specify the snapshot’s suffix, add a timestamp and alter the path that the snapshot files are taken to. Parameters @DatabaseList  – a comma delimited string of database names, allows wildcards @Suffix – adds a suffix to the snapshot name, giving it the format <databasename>_<suffix>, DEFAULT value is ‘snapshot’ @FilePath – specify a file path where the snapshot files will be saved to. If left blank ..read more
Visit website
Write to backup block blob device failed. Device has reached its limit of allowed blocks.
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
5M ago
Picture this, you’re happily backing up your database to a Azure blob storage until suddenly it starts mysteriously failing with the error… Write to backup block blob device https://****** failed. Device has reached its limit of allowed blocks. What’s going on, nothing’s changed?! Well, it has, the database has grown and the issue that you’re bumping into here is that there’s a limit to the number of blocks we can upload to blob storage. That limit is 50,000. So what does that mean for our backups? Well, essentially this puts a limit on the size of a SQL backup file. But there are a few thing ..read more
Visit website
Penetration Testing your SQL Server, Part 1 – The Recon Mission
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
7M ago
Since witnessing a rather nasty cyber attack around a year ago, I’ve been thinking quite a bit about security. Do we really know how secure our SQL Servers are? Penetration testing is a great way to find out where our weaknesses and vulnerabilities are. Ideally you probably want to be getting regular pen tests conducted by external companies (although in my experience, some are better than others. I’ve known some who argue totally pointless issues and miss glaring holes which I know exist, but that’s a whole different story) but wouldn’t it be useful if we could conduct some of these tests ou ..read more
Visit website
Finding a History of Database Snapshots, Taken and Restored
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
8M ago
All code in this post can be found in our GitHub repo https://github.com/SQLUndercover/UndercoverToolbox This is a question that’s come up twice this morning, firstly where can we find a history of database snapshots and secondly where can we find a history of restores from snapshot? Frustratingly, SQL doesn’t make this at all easy for us and if this is something that you want to record, you’re going to have a do a little extra work. Let’s take a look at each part in turn. Getting a History of Database Snapshots Taken In my head, a database snapshot is a form of backup. At least that’s how I ..read more
Visit website
I’ve Got 99 Problems But xp_cmdshell Ain’t One
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
1y ago
xp_cmdshell is an extended SQL stored proc that allows users to run Windows command prompt commands from within SQL. Sound scary? It might, but is xp_cmdshell really a security risk? Well a lot of people think so, many DBAs and IT departments will insist that it’s always disabled and many auditors and pen testers will raise it a significant vulnerability if they see it enabled on any of your SQL Servers. But is it really that much of a security risk? Before I do go any further, I would like to say that I’m a firm believer of not switching on things unless you’re going to be using them. If you ..read more
Visit website
Using SQL Alerts to Spot Suspicious Activity in SQL
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
1y ago
SQL doesn’t really give us too many tools out of the box to allow us to spot when someone may be up to no good. We can look at the number of failed login attempts in SQL’s error log. If you start seeing multiple login attempts, especially for SA or any other suspicious looking user name that could indicate a brute force attack. If you’ve got a SQL Server that’s open to the internet (I know, you really shouldn’t be doing that but let’s face it, sometimes in the real world it’s unavoidable. Especially if you’ve inherited an application written by someone who didn’t know better.) you’ll probably ..read more
Visit website
Encryption, Comparing your Options in SQL Server
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
1y ago
The question of encryption seems to be coming up a lot recently. I’ve had a number of people asking me about how to go about encrypting SQL Server. SQL can encrypt our data at a number of different levels and gives us a quite a few options when doing so. I want to use this post to put together a matrix so you can easily see which method of encryption suits your purpose. I’ll look in to each method in more detail in a future series of posts. Transport Layer Security Transparent Data Encryption (TDE) Backup Encryption Cell Level Encryption Always Encrypted Encrypts Data at Rest Enc ..read more
Visit website
Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (80090302) 0x80090302(The function requested is not supported).
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
1y ago
Photo by Pixabay on Pexels.com Here’s a quick one for today and is an issue that had me stumped for a while. It’s not one that I’d come across before and there isn’t really all that much out there on the internet about it. The Issue as I Saw It So, what was the issue? I was setting up an AG, all pretty standard stuff using the wizard in SSMS. I went through the usual setup and when I got to the end, everything seemed to create as I’d expect it, the only issue and first hint that there was an issue was that the wizard just sat there spinning when trying to join a database to the secondary. I ev ..read more
Visit website
DELETE TOP x rows with an ORDER BY
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
1y ago
Photo by Pixabay on Pexels.com This was an interesting question that I was asked yesterday and something that I’d never really thought of before. Can you delete the top x number of rows based on an ORDER BY? Why would you want to do that? Well let’s just assume that we have a ‘people’ table and some strange bug in the application has cause the need to delete the top 10 oldest females for whatever reason (I know, it’s very contrived example and not the situation that my colleague was facing but it’ll do to illustrate the point). We have a table that looks something like this…. It’s easy enough ..read more
Visit website

Follow SQL Undercover – The Home of the Undercover DBAs on FeedSpot

Continue with Google
Continue with Apple
OR