We harden SQL Server to minimize the threats to SQL Server from rouges/hackers and crackers, but it may be equally important to harden systems other than SQL Server to protect our data. For example, coders and DBA's need to ensure that calls to SQL Server are protected from SQL Injection attacks. Another...

Doh! Never mind the post below. If I had read more thoroughly (or if I had tested using MSAs with SQL Server) before posting, I would have realized MSAs are NOT supported with SQL Server. At least, not according to the article in the first link below. Sorry to mislead you, and hopefully this will be...

A common task for many database administrators (DBAs) is to set up alert emails to notify themselves when free disk space falls below a certain threshold (e.g. 10%). Before SQL Server ran on Windows clusters that included mount-points, there were a number of methods for checking free disk space, but...

With high-security SQL Server configurations we usually want to encyrpt the data-in-transit between SQL Server and the application servers. It's a little more trouble with a Failover Cluster Instance (FCI) than a stand-alone instance, and this post is primarily just a link to help me make sure I can...

Quick cheat sheet for port numbers used by SQL Server services or services that SQL Server may depend on: 21 TCP FTP (replication) 80 TCP HTTP endpoints, Reporting Services, HTTP replication 135 TCP & UDP RPC, WMI, MSDTC, SQL Agent file copy, and TSQL...

I came up with the idea that perhaps we could let clients connect to a database in an AlwaysOn Availability Group (AG) by the current instance name instead of the virtual network name (VNN) if the cluster service crashed. This idea does not work. Microsoft Consultant Don Scott set up a very simple...

In this article , I pointed out some of the most common permissions failures when installing SQL Server in an environment where security has been hardened, such as the removal of the Debug Programs permission. In my experience, "hardened" usually means some default permissions have been removed from...

As a reminder for myself when I can't fire up the Installation Center, here are the screen shots. To make it slightly more interesting, I'm adding some notes about the differences from the 2008 R2 version. I'm also attaching a Word doc with the 2012 and 2008 R2 versions side by side for a friend who...

Sometimes I run into established DBA's who have a little confusion regarding the different types of accounts used with SQL Server. I suspect that kind of confusion may come from a history of installing/experimenting with SQL Server on a workstation or laptop where they do everything under a single account...

Locks are used by relational database management systems to increase user concurrency (more users) while guaranteeing data consistency. A deadlock is when two locks interfere each other, and is caused by one process locking a row, page, partition, or table while it waits for another row, page, partition...

I was recently asked about securing SQL Server Integration Services, and I knew next to nothing about it. After digging in for a while, here are my notes, mostly for myself, but shared in case they might help someone else. There are 3 areas that need to be secured: The SSIS Engine SSIS Packages...

Sometime back, I heard that Microsoft was going to start using PowerShell scripts to monitor and optionally enforce security standards in SQL Server configurations, such as in the Microsoft Security Compliance Manager (SCM). I knew little about PowerShell, but right away I had a serious concern about...

Someone recently asked me about the principle of separation of duties (aka segregation of duties) as it applies to SQL Server DBA's, and I thought that would make a good topic for this blog, so here goes... The idea of separating duties in general is to prevent a single person from being able to complete...

Common Criteria is an international standard for a set of security characteristics, and the U.S. Department of Defense (DoD) Database Security Technical Implementation Guide (STIG) (via the Security Readiness Review for SQL Server) requires it to be enabled. (See DG0084.) You can turn it on by using...

SQL Server's query optimization engine uses statistics on indexes to determine the most efficient execution plans. By default, SQL Server automatically updates statistics, but sometimes the automatic processes don't update them soon enough, so there are multiple ways to force them to update to help keep...

Here's a SQL Server PFE war-story to give one example of one of the kind of work we might do. Most of the customers I support run SharePoint services, and they use a variety of methods to provide disaster recovery capabilities. One method involves SQL Server's Database Mirroring capability to maintain...

Someone recently asked me what issues might arise when changing a server's domain and the server is running SQL Server. Here are the possible issues I'm aware of as far as SQL Server is concerned: 1. SQL Server SysAdmin (SA) Access. The most important issue is if you only have SQL Server sysadmin...

STIG requirement DG0016 specifies that you should not install any service you do not need, and if one is automatically installed and cannot be removed, it should be disabled. The Microsoft Distributed Transaction Coordinator (MSDTC) is a Windows service, not a SQL Server service, but it's closely...

If your SQL Server master database becomes corrupt, such as from its disks losing power while SQL Server was running, the conventional advice is to rebuild the master database, start SQL Server, then restore the backup of the master database. That's because SQL Server won't start with a corrupt master...

One of the primary areas of responsibility for DBA's is maintenance, and one of the primary maintenance tasks is file-size management. First I'll present a list of the file-sizing tasks with short explanations for each one, then some background information, and then some T-SQL to give you the file sizes...

This post is primarily an online note for myself, which I'll make public in case someone else might find it helpful, but here's one issue I've seen cause more confusion among my customers than any other regarding SQL Server clustering. The SQL Server installation process does not create a cluster --...

SQL Server is so well-behaved it's often installed by 3rd party applications in an organization or department without a professional Database administrator (DBA). When such implementations need attention (e.g. backups), system administrators often get involved as acting-DBAs, and some of them discover...

Last update to script 1Nov2011. Maintaining indexes is a primary duty of Database Administrators, because appropriate indexes are critical to maintaining high performance in any relational database, and proper maintenance is required to avoid excessive fragmentation. In case that's not clear enough...

One of the requirements for STIGging an instance of SQL Server (or applying other enterprise security standards) is to remove all permissions from SQL Server's public role and the CONNECT permission from the database guest accounts. Incidentally, SQL Server 2008 has far fewer permissions assigned to...

The SQL Server Management Studio has a "Job Activity Monitor" which provides a lot of useful information about scheduled jobs. However, it does not show the schedule type or a schedule description, nor does it clearly distinguish between the job being enabled vs. the schedule being enabled. The query...