Microsofts recommendation on TempDB on Azure VM’s is to place it on the D-drive that every Azure server is created with.  In most cases this is going to be the fastest storage on the virtual machine, and given that tempDB files do not need to be persisted this can give you access to a high performance TempDB configuration without the need to provision additional storage.

  The only issue is that the Ephemeral drive is temporary.  Everything is wiped from it whenever a machine is deallocated(Note – contents can survive a reboot, just not a shutdown-deallocation event).  Because I keep forgetting how to set it up I thought I might as well store it here so next time I forget I don’t have to log into the last VM I set up and create it.

Step 1.  Create a folder called scripts and create a file in it called SQL-Startup.ps1.  Edit that file and enter the following code.

Start-Transcript -Path "C:\Scripts\Transcript$(((get-date).ToUniversalTime()).ToString("yyyyMMdd_hhmmss")).txt" -Append
$SQLService="SQL Server (MSSQLSERVER)”
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQLTEMPDB"
if (!(test-path -path $tempfolder)) {
   New-Item -ItemType directory -Path $tempfolder
}
Start-Service $SQLService
Start-Service $SQLAgentService

$runtime= Get-Date
echo “{$runtime}: I just ran” >> C:\scripts\outputfile.txt

Step 2.  Create a Windows Scheduler Task which will run that script each time the Virtual Machine Starts.

1. Click on Windows button and type ‘Scheduler’ – select and open task scheduler.
2. Click “Create Task”
   1. On General Tab:
      1. Set name to “SQL Startup”
      2. Change user to run as a service account with permissions to create a folder and Start SQL services.
      3. Select “Run whether the user is logged in or not.
   2. On Triggers Tab:
      1. Select “New”
      2. Select “At Startup” from the dropbox menu.
      3. Ensure “Enabled” is checked and click Okay.
   3. On the Actions Tab:
      1. Select “New”
      2. Select Action as “Start a program”
      3. For the program type(or browse to) powershell.exe
      4. For Add Arguments type Powershell.exe -ExecutionPolicy Unrestricted -File “C:\Scripts\SQL-Startup.ps1”
      5. Click Okay.
   4. On the Conditions Tab:
      1. No Changes
   5. On the Settings Tab:
      1. No Changes
3. Click Okay – You will be prompted for the credentials for the service account you selected to run the job under.

Step 3.  Set SQL to auto start(delayed)

1. Click Windows Start button and type “Services.msc”
2. Right click on the SQL Service and change Startup Type to “Automatic (Delayed Start)”
3. Click Okay.

Step 4.  Ensure Service Account has correct rights

1. Ensure your service account has sufficient permissions to start SQL and create a folder.
2. Ensure your service account has “Log on as a batch job” rights.

Now you can stop SQL, delete the tempdb folder and restart the virtual machine.  On bootup the script should recreate the folder and add a record of the restart to C:\scripts\outputfile.txt

Another audit picks up “Scan for Startup Procs” and flags it as a problem. The recommendation is to turn it off. I disagree, and here’s why…

Like my previous post about xp_cmdshell, I have a stake here. We use this stored procedure in our monitoring so whenever SQL starts we can check the error logs and calculate downtime etc. It’s useful, and this is exactly the scenario that I believe the configuration is made for.

Unlike xp_cmdshell though I can see a likely configuration where this can be a problem. So let’s talk about what it does.

If you enable this option SQL will scan for stored procedures marked to run on startup next time it restarts(As a side note you need to restart the server to enable it). You can mark a stored procedure to run on startup by using sp_procoption. And when I say you, I mean YOU, the sysadmin.

Yes the only user who has the ability to mark a stored procedure to run on startup is a sysadmin, so like xp_cmdshell this is a vulnerability that can only be exploited by a sysadmin. And if they are already a sysadmin they can turn it on anyway. Also…you know…they are a sysadmin. If they are malicious you have bigger problems than them running a stored procedure on startup. They can run ANY stored procedure, right now.

Of course, it’s a bit more complex than this. In this case the stored procedure is only required to mark a stored procedure to run on startup. It is possible for a user without sysadmin privileges who has the correct rights on the master database to alter the stored procedure. So yes, there is a vector here to elevate db owner on master to sysadmin, and then do whatever else you want.

This is where I have issues with some of these generic recommendations in audits. They are painted as black and white and as we all know the answer in SQL is always ‘It depends’. But when you just label something as ‘bad’ and people just accept that rather than ask why and how, perfectly safe and useful pieces of functionality are turned off.

A better approach would be to treat this finding as the start of a conversation, sit down with the server owners, understand if the functionality is needed and if so what mitigations can be put in place to ensure it is not a risk. There’s not many good reasons for giving a user elevated rights on the master database.

I’ll stop now because this post has a lot of potential to turn into a rant on the difference between compliance and security, but if you found this blog looking for a recommendation about using “Scan for Startup Procs” my advise would be this. If there’s another way of doing what you want that is equally efficient, use that. “Scan for Startup Procs” is a setting you will find yourself having to defend every time there is an audit of your server and you may not want to fight that fight. If you do want to fight it, making the setting safe is very simple. Don’t let anyone have rights to edit the stored procedure you create and you are fine. Bare in mind however that with all your good intentions, one day you may not be there, the guidance may not be followed and someone will be able to exploit this.

Well this was an odd one, and luckily on my test environment but definitely one I’ll look out for in the future. I was doing some maintenance removing old unused test databases when suddenly I got hit with this error:

Logon failed for login ‘ROBLT\RobSQL’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

This was not a fun error. Everything I tried to do in the object explorer generated this error. Basically I couldn’t create any new connections to the database, I did have an existing query window open to master database which was still working which turned out to be my saving grace, as you’ll see later.

The error is pretty clear about what is going on. There is a trigger that is failing which is preventing any new logins from being established. If this was a production server it would mean NO-ONE could connect at all, which would be raise the stakes somewhat from my play server. The thing is, as a test server I had no idea what trigger it was talking about. I vaguely remember playing with login triggers 6 months ago but have no idea which trigger I needed to stop. So from my open query window to master, I stopped them all with the below TSQL:

DISABLE TRIGGER ALL ON ALL SERVER 

This immediately allowed me to access the object explorer and go and look for the specific trigger that had caused the problem:

This trigger had of course been trying to write into one of the databases I had cleaned up. Lesson learnt – check for server scoped triggers before you hit that delete button.

There’s a number of other scenario’s that could get you into this pickle – such as creating a trigger to whitelist logins and forgetting to add yourself, or creating a trigger that references an object that doesn’t exist. If you want to be more surgical you can check for the trigger name with:

select * from sys.server_triggers

All this of course assumes you can get into the server in the first place. I was lucky enough to have an open live connection. If you don’t a failing trigger will prevent EVERYONE from connecting to the server, including sysadmins. You don’t have to hand in your resignation just yet though. Microsoft has foreseen that people will do stupid things with login triggers so there’s a few ways to address it outlined in the documentation:

1. Login using the dedicated Administrator Connection: Details of how to do that are here if you are not sure. The DAC is not affected by login triggers. If you have enabled the setting “Remote Admin Connections”(And you should) you can do this remotely, otherwise you need to be on the same server that SQL is installed on.
2. Start SQL in minimal configuration mode. You can do this by adding the -f flag when you start the server.

Either of these options will allow you to log into the server and disable or fix the trigger.

For this months TSQL-Tuesday Tom Zika asks “Which quality makes code production grade?“

Wow. I feel like I’ve just been tossed a hand grenade and I don’t have a pin. I don’t consider myself a developer by any stretch of the imagination, but there’s a bunch of code out there I have written that is running businesses. Some big businesses. And the idea that there is a grade that any code should pass before it goes into production always makes me a bit nervous, because I’m pretty sure the bar would need to be quite low for everything I’ve ever put out there to pass it.

So let’s step back a moment and talk about this place I live. It’s called reality. And in reality business problems often come to you like this:

Big Manager: The Scurbwazler is about to exceed it’s threshold, we need to offload the thingiwozly workload via the bropfizzle or the whole thing it going to fall over.

Developer: I can get a prototype done that will stop it blowing up, but once the emergency is over we need to go back and make sure it’s performant and secure.

Big Manager: Sure, sure, of course.

(Insert code producing sound effects here).

Developer: Okay it’s done. I’ll look over the security settings after lunc…

Other Big Manager: Hey, what’s going on with the BropFizzle? We’ve releasing our new product today and we need it’s full capacity.

Developer: I guess we could route that via the tertiary system…

And so on it goes.

Now I love the devops movement which makes so many of these problems go away, but I still see changes happen in exactly this way in so many environments, and I totally get the reasons.

So I’m going to try and answer this question from a slightly different angle than what the code should look like and instead talk about how it comes into being.

1. Production Grade Code should be planned.
2. Production Grade Code should be tested before it becomes production code.
3. Production Grade Code should be understood\Documented\In source control.

Let’s deal with them one by one.

Production Grade Code should be planned.

This seems pretty obvious. Before a line of code should be written someone, and probably a group of someones, should define what the code is for and what it’s meant to do. I keep getting told by my developers that the further along the dev process an error gets before being identified the more expensive it is to fix. It stands to reason then that the inception of a requirement is the place you want to focus the most attention to ensure that what you are asking your developers to build is actually the thing you want. Furthermore, time invested in explaining what you’ve just written as a requirement and allowing Q and A time is time that you prevents multiple periods of rework later in the process.

Production Grade Code should be tested before it becomes production code.

You all have a pipeline to run your code through right? Well, if you don’t you don’t need to feel like you are alone. A lot of businesses are still deploying code on a completely adhoc basis and their ‘test’ system bares very little similarity to their production system.

For code to be production grade, it needs to have passed the functionality tests defined in the planning stage, and ideally it will be stress tested against a similar load to production.

In SQL land the classic example is code that is non-SARGable. Functionally it may perform fine against your table with 1000 rows in it, but when it hits production and tries to run against a table with a few billion rows it will bring your server to it’s knees.

Production Grade Code should be understood\Documented\In source control

I’ve been in too many places where only one person understands how systems interact, or how data moves, or what relies on the system you are talking about upgrading\migrating. Inevitably that person will be unavailable. Sometimes they stick around till they retire, sometimes they leave for greener pastures, but they will inevitably leave.

I hate documentation, but I hate lack-of-documentation even more.

I think there’s a period in peoples career lifecycle where they just do heaps of stuff and never document anything and feel productive, but increasingly I don’t want me to be the only guy who understands(and therefore can fix) anything I write. I don’t want to be the bottle neck. I want any competent DBA or developer to be able to look at anything I’ve built and go…ohhh…I see what he’s done here. And then they can fix it instead of me.

And that really is the key to me for what “Production Grade” should be. It’s got to serve a clear purpose, do it’s job and be easily understood and maintainable in the future. To get there we’ve got to get over the idea that code ‘belongs’ to the person who wrote it and think of our code as part of the larger eco-system.

SQL Server Reporting Services is something I don’t play with much, but I had an interesting problem come up this week.

Several months ago we migrated a copy of SSRS from 2016 to 2019 for users to test against and be comfortable that everything works the same on the 2019 version. They did that, eventually, but also apparently some people updated reports on one version and some people updated it in the other. I needed a way of finding out which reports had been updated on which server, so we could redeploy them all to theri final home.

Luckily that’s very possible:

SELECT
c.[Path], c.Name
, c.CreationDate
, cu.UserName as [Created by]
, c.ModifiedDate
, cm.UserName as [Modified By]
FROM [Catalog] c
JOIN Users cu on cu.UserID = c.CreatedByID
JOIN Users cm on cm.UserID = c.ModifiedByID
order by ModifiedDate DESC