Summary of Audit-Specific STIG ID’s

The DoD Database Security Technical Implementation Guide (STIG) has quite a few requirements in the area of auditing, but they’re scattered throughout the document. Here’s a list of all the audit-related STIG ID’s that I found. From the Security Readiness Review: “The majority of Microsoft SQL Server security auditing is provided by the trace facility….


File-Sizing

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 for all data and log…


Failover Cluster Concepts

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 — a cluster must already…


Primary DBA Responsibilities

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 they enjoy it and start spending more and more time with SQL Server….

10

DG0155: Trusted file check

The DoD Database STIG includes DG0155 (CAT II): The DBA will ensure all applicable DBMS settings are configured to use trusted files, functions, features, or other components during startup, shutdown, aborts, or other unplanned interruptions. In the SQL Server 2005 Security Readiness Review (SRR): If the DBMS does not provide a means to ensure the…

1

SharePoint/SQL Server Hierarchy

Where do SQL Server databases fit into the SharePoint architecture? Here’s my non-expert understanding: A SharePoint instance (aka a SharePoint farm) has one or more Web Apps. A Web App has one or more Content Databases, where a Content Database is a single database in SQL Server. Each Web App has its own IIS application pool. A…


Defragmenting Indexes

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, let me emphasize: index fragmentation can dramatically degrade performance. While the Maintenance Plan…


Connection Error After Removing Public Permissions

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 public role than previous versions, but it still…

3

Agent Schedules

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 in the attached file provides that…


List of SQL Server Service Names

In the tables below, services that can have multiple instances on the same server have the default instance name listed first and then a named instance – “Contoso1.” These services names are without any service packs being applied. I haven’t checked to see if there are any changes to service names after installing service packs….

15