Category Archives: Security

Another great reason to enable remote admin connections

I recently came cross another great reason to enable the remote admin connections server configuration on SQL server. If you don’t know what the DAC is I’m not going to go into detail here so check out these posts here, … Continue reading

Posted in Disaster Recovery, Security, SQL Server | Leave a comment

Migrating SQL Server logins

Once upon a migration I would use sp_help_revlogin to migrate logins and remap the SQL server logins using sp_change_users_login. During some recent migration work I realised that SQL Server Management Studio could do most of the work of sp_help_revlogin. I … Continue reading

Posted in Security, SQL Server, T-SQL, Uncategorized | Tagged , | 8 Comments

Quick tip: Querying AD with xp_logininfo

We all have blind spots in our knowledge. We don’t know they are there and when we are thinking through an issue our mind quickly rejects some plans because of these blind spots. This happened to me recently when I … Continue reading

Posted in Security, SQL Server, T-SQL | 1 Comment

Quick tip: Could not load package “xxx” because of error 0xC0014062

I struck this error in one of my SSIS packages, I found lots of tips and suggestions online for the error but none of the solutions worked for me. So I want to add my solution to the mix so … Continue reading

Posted in Security, SSIS | 4 Comments

Quick tip: WMI alerts and SQL Server Agent permissions.

I came across an issue today while trying to create a WMI alert for a mirroring state change. The error was something along the lines of WMI Access denied. Error: 0x80041003. My first port of call was to verify the WMI query … Continue reading

Posted in Security, SQL Server | 3 Comments

Restore database permissions

Introduction: I came across a couple of subtle gotchas around the permissions required to restore a database in SQL Server 2008+ recently. I’m interested in situations where a DBA needs to allow certain users the ability to restore a given … Continue reading

Posted in Database options, Disaster Recovery, Security, SQL Server, T-SQL | Tagged , , | 13 Comments

Migrating SQL Server Databases that use Database Master Keys

There’s a lot of things to consider when migrating databases between SQL Server instances. I want to provide some tips for dealing with Database Master Keys, and in particular the case when those keys are encrypted by the Service Master … Continue reading

Posted in Database options, Disaster Recovery, Security, SQL Server, T-SQL | Tagged , , | 27 Comments

Quick tip: configure SQL Server authentication mode using T-SQL

SQL Server stores its authentication mode in the registry. The registry can be read and updated using the T-SQL commands xp_regread and xp_regwrite. Step 1: Construct our registry key path, run the following query and record the result: DECLARE @InstanceName … Continue reading

Posted in Security, SQL Server, T-SQL | Leave a comment

Locked out of SQL Server.

This is a very cool and useful tip if you find yourself locked out of the sysadmin role in sql server. I have a SQL Server instance with the sa account disabled and all other logins removed to simulate a … Continue reading

Posted in Disaster Recovery, Security, SQL Server | Tagged | 3 Comments

Instant File Initialization Security Risk

A while ago I blogged about instant file initialization. A colleague of mine pointed out that there is a small but non-zero risk involved with using this setting. So I decided to put a demo together to test out the … Continue reading

Posted in Security, SQL Server | Leave a comment