Fort SQL

SQL Server 2012 Virtual Labs

Quantum John — Tue, 23 Oct 2012 13:57:44 GMT

Want to try SQL Server 2012 without even installing it? Easy... use SQL Server 2012 Virtual Labs at http://technet.microsoft.com/en-us/video/Hh913731!

Hiding SQL Server from External Crackers

Quantum John — Mon, 15 Oct 2012 14:24:33 GMT

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 valuable tactic is to prevent bad guys from finding the servers with SQL Server on them, and we can help guard against that by disabling NetBIOS and Small Message Blocks (SMB) on Internet-connected servers that don't need them.

The Database STIG makes clear that any unnecessary network protocols should be disabled on the server hosting an instance of SQL Sever, but what I'm pointing out in this post is that network protocols on other servers may also need to be secured to provide maximum security for SQL Server.

If you have a web server or DNS server exposed to the Internet, as is very common, they normally don't need NetBIOS or SMB. If they're enabled and a cracker compromises one of them, they may be able to use them to find instances of SQL Server. The MSDN article "Security Considerations for a SQL Server Installation" (http://msdn.microsoft.com/en-us/library/ms144228(v=sql.105).aspx) covers this issue, among others.

If you want to harden your web servers and DNS servers, here are links to articles descibing how to disable NetBIOS and SMB. Note that in Device Manager, you may have an entry of "NETBT" instead of "NetBios over TCP/IP" (both represent netbt.sys).

How to Disable NetBIOS
(Netbt.sys)
http://msdn.microsoft.com/en-us/library/ms143696(v=SQL.90).aspx

How to Disable SMB
http://msdn.microsoft.com/en-US/library/ms143455(v=sql.90).aspx

Managed Service Accounts

Quantum John — Tue, 04 Sep 2012 16:40:00 GMT

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 able to work with SQL Server sooner or later.

====================================================================================

I've recently learned there's such as a thing as Managed Service Accounts (MSAs) in Active Directory if your domain controller is using Window Server 2008 R2 or Windows 7, and these accounts can significantly improve security. Why didn't I hear about this before? Probably because I'm a SQL Server specialist, not a Windows or Active Directory specialist. At any rate, I thought I'd pass this along in case I'm not the last person on the planet to get clued in.

For an MSA, Active Directory will assign a 120-random-character password, change the password every 30 days, and manage the Service Principal Names (SPNs). The account can't be locked out, and system administrators don't have to maintain it.

Security is improved because the passwords don't have to be maintained by humans and because an MSA can only be used by a service on one computer. It does take a little extra work in the beginning, though, as seen in this 4-step process:

Create an MSA in Active Directory.
Associate the MSA with a single computer.
Install the MSA on its computer.
Configure the service (e.g. SQL Server) to use the MSA.

This isn't in the Database STIG yet, but I expect it will show up there as a security best practice sooner or later.

For more information on MSAs, start here http://technet.microsoft.com/en-us/library/ff641729(v=ws.10) or here http://technet.microsoft.com/en-us/library/dd548356(v=WS.10).aspx.

Alert On Low Disk Space, Including Mount Points

Quantum John — Wed, 01 Aug 2012 12:11:00 GMT

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 most didn't report on the free space in mount point based disks. The easiest way to check free space on mount points is via Windows Management Instrumentation (WMI), and the easiest way to use WMI and create an alert is with PowerShell.

Attached to this article is a WMI-PowerShell script that checks disk free space, including mount points. It's written to be used in a SQL Server Agent job, and send an email alert via SQL Server's Database Mail. You can run it against any number of servers from one script, and you'll need to replace several items in the script: Server name(s), the Database Mail profile name, the recipient email address(es), and optionally change the warning threshold. It works like any other Agent job except you choose a job step Type of "PowerShell". Detailed notes on how to tweak and use the script are included as in-line notes in the script file.

If you're not familiar with PowerShell and you'd like to view the code in color-coded formating, use the PowerShell ISE (Start menu/Accessories/Windows PowerShell/Windows PowerShell ISE). (ISE stands for Integrated Scripting Environment.)

Here's a security-related wrinkle to WMI calls via PowerShell that's run by SQL Server Agent... If the service account that SQL Server Agent runs under has local admin permissions/privileges and you only run the script against the local server, you probably won't have any access-denied problems. Under any other scenarios, such as the Agent having limited local permissions or you target the script against remote servers and the Agent service account doesn't have admin permissions on those servers, you'll have to grant a WMI "Remote Execute" persmission to the Agent account on each server. To assign Remote Execute, run wmimgmt.msc from the Start menu's Run box or from a command prompt, then right-click on Properties, then select the Security tab, then expand the Root node, then select the CIMV2 node, then click the Security button, then add the Agent account and scroll down to find and check the box for the "Remote Enable" permission. Simple, right? You were probably going to guess that anyway. ;)

Enabling SSL on SQL Server Connections on Failover Clusters

Quantum John — Thu, 05 Jul 2012 16:18:49 GMT

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 easily find this article: http://msdn.microsoft.com/en-us/library/ms191192.aspx.

I'll point out a critical factor in the article is that you must be logged into the server with the SQL Server service account when you open the SQL Server Configuration Manager to select the certificate, if the SQL Server service account is a domain account (and it should be if you're complying with the DoD Database STIG).

Also, a community comment contains another critical element, that you have to edit the registry on each node. See the note for more info and links to the details.

SQL Server Ports

Quantum John — Tue, 03 Jul 2012 13:59:00 GMT

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 Debugger (RPC used for multiple purposes including SSIS and clustering.)
137	UDP	File & Print Sharing (replication) and Cluster Admin
138	UDP	File & Print Sharing (replication)
139	TCP	FileStream and NetBIOS Session Service (clustering)
443	TCP	HTTPS endpoints and Reporting Services
445	TCP & UDP	FileStream, SMB (clustering), and File & Print Sharing
500	UDP	IPSec
860	TCP	iSCSI
1024-5000	TCP	Original dynamic ports for named instances. (WinSock standard.)(See ports 49152-65535.) The DoD Database STIG requires static ports.
1433	TCP	SQL Server database engine
1434	TCP & UDP	SQL Server database engine, DAC, and SQL Server's "Browse" button.
2382	UDP	Analysis Serviceswhen using dynamic ports with named instances
2383	TCP	Analysis Services
2393-2394	TCP	Analysis Services version 7
2725	TCP	Analysis Services
3260	TCP	iSCSI
3343	UDP	Cluster network driver
3389	TCP	Remote Desktop Protocol (RDP)
3882	TCP	DTS/SSIS
4022	TCP	Conventional port for the SQL Broker service
4500	UDP	IPSec
5000-5099	UDP	Clustering
5022	TCP	AlwaysOn's default port for primary and secondary replicas
7022	TCP	Conventional port for Database Mirroring
8011-8031	UDP	Clustering internode RPC
49152-65535	TCP	Latest dynamic ports for named instances. (WinSock standard.)(See ports 1024-5000.) The DoD Database STIG requires static ports.

Microsoft recommends non-default ports for maximum security.

Check ports in use: SELECT ServerProperty("ProcessID")
At a command prompt: "netstat -ano"

AlwaysOn Ports

Each instance w an Availability Group (AG) must have a database mirroring endpoint, and they endpoints bust be started (query sys.database_mirroring_endpoints and sys.tcp_endpoints).
Logins from a remote server must have CONNECT permission. Each instance must have access to ports on all partners.

Resources: Windows Firewall & SQL Server, Ports that DBAs Need to Know, and KB968872

Capture Custom Events in Profiler for Troubleshooting

Quantum John — Thu, 21 Jun 2012 17:05:35 GMT

User configurable events have been available in SQL Server since at least version 2000, but aren't often used, I suspect just because DBA's aren't familiar with them. Here's a screen shot of the Events Selection page of the Trace Properties for a SQL Server Profiler trace:

Despite having all of the user configurable events selected, nothing will show up in the trace if you don't create custom events to feed the trace. The easiest way is to use sp_trace_generateevent in your T-SQL code, such as inside a trigger. Here's an example from inside a trigger:

EXEC sp_trace_generateevent 82, N'Trigger fired.'

You can use 82-91 as the first argument, which corresponds to UserConfigurable events 0-9, and then a unicode message (just put an N right before your single-quote delimited message).

When you use custom events like this, you'll often want to capture only the UserConfigurable events, thereby cutting out all the events you don't care about.

Note to self on AlwaysOn...

Quantum John — Wed, 09 May 2012 14:57:00 GMT

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 2-node cluster with a stand-alone instance of SQL Server 2012 on each node and 1 availability group with 1 database in it, and we could connect to the database by it's VNN or by connecting to the current instance name, as expected. However, when we turned off the cluster service on both nodes to simulate a cluster service failure, we could no longer connect to the database by it's virtual name, as expected, but we could also not connect to the database through the current instance name. In SQL Server Management Studio (SSMS), the database icon listed "Recovery pending" after the name of the database, even though this was the primary replica. The secondary replica didn't even show up in SSMS with the cluster service off.

Even though the "recovery pending" status didn't make sense to us, we tried "RESTORE DATABASE <dbname> WITH RECOVERY" and got a very strange error message: "Msg 3148, Level 16, State 3; This restore statement is invalid in the current context. The 'Recover Data Only' option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline state filegroups cannot be specified." This is strange because this database only had the default primary filegroup and it was online. We checked SSMS to confirm the database was online, and the "Bring Online" option was not available in SSMS, but the "Take Offline" option was available, confirming the database was still online despite the error message.

Moral of the story: When using AlwaysOn, keep at least half your cluster nodes/witness healthy, because if the cluster goes down completely, AlwaysOn goes down.

Other notes:

With this test configuration (2-node cluster, each with Non-FCI, 1 availability group, all functioning correctly): When we evicted the primay replica node from the cluster and restarted the node, we expected the availability group to be disabled, but instead it completely ceased to exist (no trace of it remained to be displayed in SSMS). The database was then a normal database without an availability group.
When creating or altering an Availability Group, SQL Server interacts with the Windows cluster service to automatically create the Virtual Network Name for the AG in the cluster service.

Installing SQL Server in a High-Security Domain, Part II

Quantum John — Wed, 04 Apr 2012 19:13:29 GMT

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 various accounts.

Recently some colleagues had failures while attempting to install SQL Server 2008 R2 on a VMware virtual Windows cluster, even though they had ensured their installation account had the 3 privileges I covered in the previous article. One of the difficulties with these types of failures is that you don't get an error saying something clear like "Sorry, you don't have the Debug Privileges permission," so it can be a little troublesome to figure out exactly which permissions are missing, if any. In this case, my colleagues were able to successfully install SQL Server after adding the "Act as part of the operating system" and "Logon as a service" permissions to the installation account. I'm going to repeat myself to emphasize they added these permissions to the installation account (the account they were logged in as while running the installer). That difference allowed their install to succeed, but if you use this remedy, remember to remove those permissions from the installation account after you finish the install.

Did they need both of those are just one of them? We don't know, they didn't uninstall and reinstall with just one of them to narrow it down. If you gain any insight on what security settings cause this problem, and whether or not only one of these permissions is needed to succeed, please let me know.

SQL Server Installation Center 2012

Quantum John — Wed, 07 Mar 2012 17:35:00 GMT

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 likes such things. (You can click on each image to get a full-size version.)

Planning Page:

First, 2012 adds “How to Get SQL Server Data Tools” which is a link to a web article. I think they should have called it “SQL Server Data Modeling Tools” since that’s what these Visual Studio tools are for.

Next, they’ve removed a link to “Setup Documentation”, which puzzles me, since it would seem to be very appropriate. I hope they’ll put it back, but in the meantime, here’s the link to the 2012 version of this article: http://msdn.microsoft.com/en-us/library/bb500469(v=sql.110).aspx.

The addition of three new articles make up the remaining differences: “How to Get started with Reporting Services SharePoint Integration on a Standalone Server”; “Install SQL Server Migration Assistant (SSMA)”; and “How to apply SQL Server updates”.

Installation Page:

Here we've removed the “Search for product updates”, but as you’ll see, they’ve simply moved it to the Maintenance page, which is a more appropriate place for it than here on the Installation page.

Maintenance Page:

Same as the previous version except this is the new home of the search-for-updates item.

Tools Page:

There are three changes here:

The “Upgrade Integration Services packages” link has been dropped.
A link to an article on “Microsoft Assessment and Planning (MAP) Toolkit for SQ Server” has been added. MAP is a separate download, but it will let you scan your network for instances of SQL Server, MySQL, Oracle, and Sybase in addition to numerous other features.
A link to “PowerPivot Configuration Tool” has been added, but is greyed out until PowerPivot has been installed first.

Resources Page:

No changes on this page.

Advanced Page:

No changes on this page.

Options Page:

The only difference to the items on this page is that we've dropped support for ia64 (Itanium processors). And yes, the dark black line is where I obfuscated the username in the install path.

Does the DoD STIG require Transparent Database Encryption (TDE)?

Quantum John — Thu, 23 Feb 2012 21:47:52 GMT

Does the DoD STIG require Transparent Database Encryption (TDE)?

The short answer is: It depends on whether or not the Data Owner says the data must be encrypted.

The current version of the DoD Database STIG is v8r1. Here are two relevant sections from that document:

3.1.4.3
Unique security requirements (encryption of sensitive data)
Access to sensitive data may not always be sufficiently protected by authorizations and requires encryption. In some cases, the required encryption may be provided by the application accessing the database. In others, the DBMS may be configured to provide the data encryption. When the DBMS provides the encryption, the requirement must be implemented as identified by the Information Owner to prevent unauthorized disclosure or access.
• (DG0106: CAT II) The DBA will ensure security requirements specific to the use of the database are configured as identified in the System Security Plan.

3.3.5
Encryption for Confidentiality - Data at Rest (ECCR)
Where access controls do not provide complete protection of sensitive data, encryption can help to close the gap. Where privileged users do not have a need-to-know, where files are stored externally to the database, where application user roles cannot be restricted by privileges to single rows and columns of data to those they need to access, encryption can provide the required level of protection.
• (DG0068: CAT II) The DBA will ensure applications that access the database are not used with options that display the database account password on the command line.
• (DG0090: CAT II) The IAO/DBA will ensure sensitive data is encrypted within the database where required by the Information Owner.
• (DG0092: CAT II) The DBA will ensure database data files are encrypted where encryption of sensitive data within the DBMS is not available.

While the Database STIG is a generic document, Security Readiness Review (SRR) documents are brand and version specific. The latest SRR for SQL Server is v8r1-2, and here are a couple of relevant sections:

4.53 DG0090: DBMS sensitive data identification and encryption
Description: Sensitive data stored in unencrypted format within the database is vulnerable to unauthorized viewing.
Check: If no data is identified as being sensitive or classified by the Information Owner, in the System Security Plan or in the AIS Functional Architecture documentation, this check is Not a Finding.

4.54 DG0092: DBMS data file encryption
Description: Where access controls do not provide complete protection of sensitive or classified data, encryption can help to close the gap. Encryption of sensitive data helps protect disclosure to privileged users who do not have a need-to-know requirement to view the data that is stored in files outside of the database. Data encryption also provides a level of protection where database controls cannot restrict access to single rows and columns of data.
Check: Review the System Security Plan and/or the AIS Functional Architecture documentation to discover sensitive or classified data identified by the Information Owner that requires encryption. If no sensitive or classified data is identified as requiring encryption by the Information Owner, this check is Not a Finding.

So, if a data owner asks you if their data should be encrypted, what should you tell them? I discuss some of the issues you should consider in this article.

I'll also mention that the SharePoint STIG includes a requirement that seems to say that the DoD requires every SharePoint database to be encrypted with TDE. Fortunately, that is not the case, and their requirement is consistent with the Database STIG: It depends on the decision of the Data Owner.

DISA Clarification on SharePoint STIG

Action Description: Customer has a question regarding the intent of the check V-28066 SRG-APP-000188-COL-000134 from the SharePoint STIG.
Status or Resolution Summary: The data only has be encrypted in the SQL database if it is required by the Data Owner. This is rare. There is another requirement that covers protection of data removed from the database, so that's not applicable here. So no, you wouldn't encrypte unless there is a mission requirement.

SQL Server 2012 release date: March 7, 2012

Quantum John — Tue, 24 Jan 2012 16:05:00 GMT

It's official, SQL Server 2012 goes on the market on Wednesday, March 7th, 2012!

Update on Feb 27, 2012: Rats. I'm always careful not to divulge any confidential information, and I'm sure I got this date from an official source that did NOT state that it was confidential, and therefore I shared the news. Just a few minutes ago, I received an email that says that the date I posted may be wrong and the official dates are still confidential. As if it's not bad enough that I posted wrong info, and that the info is supposed to be confidential, I can't remember where I originally got the date I posted so I can't really defend myself. Rats.

Update on March 6, 2012: Okay, this time I'm including the link to my info source: http://www.microsoft.com/Presspass/press/2012/mar12/03-06SQLServer12PR.mspx. Today, SQL Server 2012 has been released to manufacturing (RTM). At the moment of this update, it is still not available on MSDN. 2nd Update on March 6: Register now for the virtual launch on March 7th: http://www.sqlserverlaunch.com/ww/Home.

Last Update: SQL Server 2012 hit "general availability" on April 2, 2012. http://blogs.technet.com/b/dataplatforminsider/archive/2012/04/02/sql-server-2012-is-generally-available.aspx?prod=zSQLz&tech=zCLz_zBIz&type=zDLz_zBLz

SQL Server Accounts

Quantum John — Mon, 09 Jan 2012 18:40:00 GMT

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. The biggest problem with a single account for everything is that it's a major violation of the security principle of least-privileges. Instead, DBA's should think in terms of four different types of accounts, so I hope this list will clear up this issue:

Installation Account: This is the Windows account the person is logged in as when they are installing SQL Server, and it needs maximum privileges (permissions) for the installation process to be able to accomplish everything it needs to do. Because of these maximum privileges, this installation account should not be used for normal DBA work, nor for service accounts, nor for user or application accounts. In high-security environments, you may need to double check that your installation account has all the privileges needed before you start the install (http://tinyurl.com/7f8czcs).

Service Accounts (aka 'startup' account): A service account is a Windows account that stores and controls the privileges for a service-oriented application, such as the SQL Server database engine, SQL Server Agent, Reporting Services, Analysis Services, Integration Services, or the Full-text Service. When a service starts, it essentially logs into Windows using the credentials of its service account. These service accounts must exist before you install SQL Server services, but you do NOT need to assign all the privileges they'll need because SQL Server's install process will do that, assuming your installation account has all the privileges it needs. Each instance of each service on each server should have its own unique service account, so if you have the SQL Server database engine, SQL Server Agent, and the Full-text service all installed on 50 servers, you need 150 service accounts (see http://msdn.microsoft.com/en-us/library/cc281953.aspx or the DoD Database STIG). DBA's who are not held to best practices often compromise on this issue, and here are the two most common compromises I've seen: #1, Using one service account per server (e.g. the database engine, Agent, SSRS, etc. all use the same service account, but only on one server); #2, Use one service account per type of service (e.g. every instance of the database engine uses the same service account, every instance of Reporting Services uses the same service account which is different from the engine's service account) . These compromises may be common, but they reduce security and are NOT best practices.

Administrator Accounts: The principle of role-based security requires a Windows domain group for DBA's, where each DBA should have their normal Windows account as a member of that group, and the SQL Server Sysadmin server role should only be assigned to that group. No individual DBA accounts should have SQL Server system administrator rights; such rights should only come through membership in the DBA group. SQL Server system administrative privileges should be restricted to only those DBAs authorized by the system's Information Assurance Officer. I'll also mention here that high-security best practices require that the built-in "sa" account be both renamed and disabled, and on version 2005 and earlier, the "Builtin\Administrators" group should be removed to ensure that operating system administrators do not get SQL Server admin privileges.

User/Application Accounts: Users and applications use Windows accounts to connect to SQL Server, and to comply with the least-privileges principle, each user and application must have its own account, which should normally not have sufficient privileges to function as an installer account, a service account, or an admin account. Furthermore, permissions should not be assigned directly to user/application accounts, but to Windows groups that the user/application accounts are members of. Stating this another way, each unique set of privileges (permissions) should be assigned to a Windows group only, and each user or application account should be a member of the group that carries the privileges that account needs. For example, if the Contoso\Joe and Contoso\Jane user accounts need the db_owner role for the Sales database, create a Windows group such as Contoso_Sales_Data, assign the db_owner role to the Contoso_Sales_Data group, and make the Contoso\Joe and Contoso\Jane accounts members of that group. This role-based security method should be used even if some groups end up with only one member. It may seem like role-based access controls are a lot of extra trouble, but the benefits it provides are worth it for enterprise-class organizations.

Here are the most relevant sections of the DoD Database STIG (V8R1):

2.3 Database Authorizations
3.3.11 Least Privilege
List of requirements in 3.3.11.1 and 3.3.11.2: DG0005, DG0008, DG0040, DG0041, DG0063, DG0077, DG0080, DG0085, DG0086, DG0116, DG0119, DG0120, DG0121, DG0124

For any SQL Server security newbies, I'll also point out here that a best practice is to only use Windows authentication (mixed mode authentication), as opposed to SQL Server logins. I think the main reason for this is that SQL Server logins have password hashes stored in SQL Server, and obviously, Windows password hashes are stored elsewhere. The only time a SQL Server login should be used is when you have a poorly written 3rd-party application that isn't capable of connecting with a Windows account.

Get Rid of Deadlocks

Quantum John — Mon, 12 Dec 2011 19:23:00 GMT

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, or table to become available, but the one it's waiting on is locked by another process that's waiting for what the first process has locked. When that happens, SQL Server will detect it and roll back one of them (the one that requires the least work to redo will be the one that gets rolled back).

Occasional deadlocks are common, but it's uncommon for them to occur so often that they become a problem. Deadlocking does become a problem sometimes, though, and when my customers run into it, they usually ask for my assistance. While there are many articles about how to detect deadlocks, I haven't found much info about how to eliminate them, reduce them, or minimize their impact, so I'm going to list all the alternatives I'm aware of here, both for future reference for myself and in case it might help someone else:

SQL Server-based changes (DBA tasks):

Change indexes. After you enable the 1204 and 1222 trace flags and determine which indexes are getting deadlocked, you can often eliminate deadlocks by adding an index, changing an index, or every once in a while by deleting an index. (See http://msdn.microsoft.com/en-us/library/ms178104.aspx.)
Enable page-level and row-level locking. Page and row locking are allowed by default, but if someone's turned them off, you might need to consider turning them back on. (See http://technet.microsoft.com/en-us/library/ms189076.aspx and http://technet.microsoft.com/en-us/library/ms188388.aspx.)
Turn on row-versioning. (See http://msdn.microsoft.com/en-us/library/ms177404.aspx.)
Turn off parallel operations. If parallel operations are possible (the server has multiple processors/cores) and allowed, it's possible that turning it off (by setting MaxDOP to 1) will reduce or eliminate deadlocking. You should avoid this if something else will work, so that most workloads can benefit from parallelization. (See http://msdn.microsoft.com/en-us/library/ms181007.aspx.)

Code-based changes (Developer tasks):

Change code to trap SQL Server 1205 errors and resubmit the request. A 1205 error means the request was chosen as a deadlock victim, and a try/catch can resubmit without the user ever being aware of it.
Redesign the application to ensure that all database requests are serialized. Depending on the application, that can be easy, or it can be a major effort requiring the introduction of middleware.

If anyone else know of any other options, please let me know in the comments!

The Database STIG's System Security Plan

Quantum John — Thu, 10 Nov 2011 19:17:00 GMT

The Database STIG requires a written System Security Plan, and it's the responsibility of the Information Assurance Officer (IAO) to create it (see section 3.1.9 below). Although the DBA doesn't create it, the DBA can advise the IAO, and the DBA is required to maintain compliance with the security plan.

The following excerpts are from the Database STIG, V8R1, and include every reference to "Security Plan":

3.1.4.3 Unique security requirements (encryption of sensitive data)

Access to sensitive data may not always be sufficiently protected by authorizations and requires encryption. In some cases, the required encryption may be provided by the application accessing the database. In others, the DBMS may be configured to provide the data encryption. When the DBMS provides the encryption, the requirement must be implemented as identified by the Information Owner to prevent unauthorized disclosure or access.

(DG0106: CAT II) The DBA will ensure security requirements specific to the use of the database are configured as identified in the System Security Plan.

3.1.4.5 Restoration priority of subsystems is identified

When DBMS service is disrupted, the impact it has on the overall mission of the organization can be severe. Without the proper assignment of the priority to be placed on restoration of the DBMS and its subsystems, restoration of DBMS services may not meet mission requirements.

(DG0108: CAT III) The IAO will ensure the restoration priority of the database and its supporting subsystems are identified in the System Security Plan.

3.1.6 Partitioning the Application (DCPA)

[content skipped]

(DG0109: CAT II) The IAO will ensure the DBMS host is dedicated to support of the DBMS and is not shared with other application services including web, application, file, print, or other services unless mission or operationally required and documented in the System Security Plan.

3.1.9 IA Documentation (DCSD)

A System Security Plan defines the security procedures and policies applicable to the AIS. It includes definition of responsibilities and qualifications for those responsible for administering the security of the AIS [Automated Information System]. For databases, this includes specifically the DBA in addition to the standard SA and IAO roles. Without a security plan, unqualified personnel may be assigned responsibilities that they are incapable of meeting and the database security is prone to an inconsistent and incomplete implementation.

(DG0153: CAT III) The IAO will assign and authorize DBA responsibilities for the DBMS.

(DG0156: CAT III) The IAM will assign and authorize IAO responsibilities for the DBMS.

(DG0154: CAT III) The IAO will ensure the DBMS is included in or has defined for it a System Security Plan.

3.3.4 Changes to Data (ECCD)

The responsibility for managing the auditing configuration for data access may or may not fall to the DBA. In some cases, applications may incorporate their own auditing capability. Where the application depends on the DBMS to provide the auditing of changes to data, the responsibility for auditing for changes to data falls to the DBA. Auditing of changes to sensitive data can provide not only accountability, but also the ability to restore data to the correct value or content.

(DG0031: CAT II) The DBA will configure auditing of access or changes to data in accordance with the application requirements specified in the System Security Plan.

3.3.8 Interconnections among DoD systems and Enclaves (ECIC)

Applications that access databases and databases connecting to remote databases that differ in their assigned classification levels may expose sensitive data to unauthorized clients. Any interconnections between databases or applications and databases differing in classification levels are required to comply with interface control rules. This requirement is covered in depth in the Enclave STIG and is listed here to heighten awareness of the requirement during application and DBMS design and planning.

(DG0171: CAT II) The DBA will ensure interconnections between databases or other applications operating at different classification levels are identified and their communications configured to comply with the interface controls specified in the System Security Plan.

3.3.10 Logon (ECLO)

[content skipped]

(DG0073: CAT II) The DBA will configure the DBMS to lock database accounts after three or an IAO-specified number of consecutive unsuccessful connection attempts within a 60 minute period. The counter may be reset to 0 if a third failed logon attempt does not occur before reset. Where this requirement is not compatible with the operation of a front-end application, the unsuccessful logon count and time will be specified and the operational need documented in the System Security Plan.

[requirement skipped]

(DG0134: CAT II) The DBA will configure where supported by the DBMS a limit of concurrent connections by a single database account to the limit specified in the System Security Plan, a number determined by testing or review of logs to be appropriate for the application. The limit will not be set to unlimited except where operationally required and documented in the System Security Plan.

[requirement skipped]

(DG0160: CAT III) The DBA will ensure database connection attempts are limited to a specific number of times within a specific time period as specified in the System Security Plan. The limit will not be set to unlimited.

3.3.12 Marking and Labeling (ECML)

A database user that does not know the sensitivity level of the data being accessed cannot be expected to protect it in accordance with requirements. While normally marking and labeling of the data is handled by the application displaying the data, many applications provided with the DBMS software may not provide this capability. Use or access to any application that cannot display sensitivity labels must be restricted to protect the data from inadvertent disclosure. Where the marking and labeling of the data can be configured by the DBMS, it must be assigned in accordance with the direction of the Information Owner.

(DG0087: CAT III) The DBA will configure DBMS marking and labeling of non-public data where required in accordance with the System Security Plan.

Securing SQL Server Integration Services (SSIS)

Quantum John — Wed, 09 Nov 2011 01:42:00 GMT

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
SQL Server

Protecting the SSIS Engine

Ensure file-based Access Control List (ACL) permissions are restricted on the SSIS executable (MsDtsSrvr.exe)
Use a security tool to monitor for changes to the executable.
Limit Windows administrator accounts on the SSIS server.

Protecting SSIS Packages

Ensure file-based Access Control List (ACL) permissions are restricted on dtutil.exe.
Store packages in a hardened instance of SQL Server (SQL Server stores them in the msdb system database).
I haven’t thought of any reason why someone would have to store packages on a file system, but if you must, then set file-based Access Control List (ACL) permissions on the packages.
You can store packages in the “Package Store” which uses the file system with permissions managed by SSIS. I haven’t found any detailed documentation for this, but I expect that storing packages in SQL Server is safer, if SQL Server has been hardened.
Set the Package Protection Level (PPL) on each package, regardless of where you store them.
Limit SQL Server permissions to manage SSIS packages.

The Package Protection Level allows you to determine the package protection method and scope.

As for the method, you can choose not to save sensitive data, encrypt only the sensitive data, encrypt all data in the packages (not the data that the packages operate on), or let Windows ACLs protect the entire package. Sensitive data is connection string passwords, some tagged nodes, and some SSIS variables (see http://msdn.microsoft.com/en-us/library/ms141747.aspx). If you encrypt all data in the package, it will protect hide the logic of the operations, and the server and database sources and targets, in addition to the sensitive data.

The PPL scope allows you to encrypt with a user key or password. If you use a user key, only the user who creates or exports the package can open or run the package. When a package has passed the testing phase, the production team can re-encrypt a package with a maximum-strength password.

Package Protection Levels:

Do not save sensitive data
Encrypt sensitive data with user key
Encrypt sensitive data with password
Encrypt all data with user key
Encrypt all data with password
Rely on server storage and roles for access control

If, and only if, you store SSIS packages in SQL Server, then permissions to manage and run the packages are given only to and through these SSIS database-level roles: db_ssisadmin, db_ssisltduser, and db_ssisoperator. These roles are assigned to a package using SSMS, and they're saved to the msdb system database.

Connecting to the SSIS engine provides the ability to:

Check running packages
Store packages in MSDB or on a file system
Import packages
Execute packages
Export packages
Upgrade packages
Organize packages
Delete packages
Rename packages
Set Reader and Writer roles on packages

Protecting SQL Server against Rogue Packages

Harden SQL Server
Set BlockedSignatureStates to require valid, trusted digital signatures to run any package

The first line of defense against rogue packages is the credentials under which a package makes connections to databases. If SQL Server is hardened to a standard such as the DoD’s Database STIG, an SSIS package should be unable to read or harm any data that the connection credentials don’t have the authority for.

Create a BlockedSignatureStates registry key to prevent SSIS from running packages unless they have digital signatures from a trusted authority (see http://msdn.microsoft.com/en-us/library/ms403378.aspx ).

By default, any user who connects to SSIS via SQL Server Management Studio (SSMS) can see a list of packages, all storage locations, and which of their packages are running. This can be prevented by removing execute permissions on the dts enumeration stored procedures (e.g. sp_enum_dtspackages) from the public role (see http://msdn.microsoft.com/en-us/library/cc645944.aspx). SQL Server system administrators can see all running packages regardless of other permissions.

SQL Server Management Studio uses the SQL Server service to list running packages. Members of the Windows Administrators group can view and stop all currently running packages. Users who are not members of the Administrators group can view and stop only packages that they started.

As always, if anyone has any suggestions, I’d love to hear them!

SQL Server and PowerShell Security

Quantum John — Tue, 04 Oct 2011 17:08:00 GMT

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 whether or not requiring the use of PowerShell would introduce a new set of vulnerabilities due to PowerShell itself.

SQL Server 2008 introduced support for PowerShell, and included right-click launch shortcuts in the SQL Server Management Studio's (SSMS) Object Explorer. If you launch PowerShell from the Windows Start menu, it won't load with the SQL Server commandlets by default, although you can add them to your running environment after it's started. However, if you launch PowerShell from inside SSMS, it will include all the SQL Server commandlets in the PowerShell environment. There's a gotcha in a completely default install of Windows and SQL Server 2008, however, because PowerShell won't run any scripts at all.

PowerShell has 4 ExecutionPolicy options: Restricted, AllSigned, RemoteSigned, and Unrestricted. The Windows default for ExecutionPolicy is Restricted, which prevents it from running any scripts, although you can run any commands in interactive mode if you have sufficient permissions. You have to change the ExecutionPolicy before you can run any scripts, and that always seemed to me like the most secure default configuration, but that apparently confused more than a few people. I suspect that confusion was the reason behind this change: When you install SQL Server 2008 R2 it changes the Windows PowerShell ExecutionPolicy to AllSigned. AllSigned lets PowerShell run any script that has a digital signature from a trusted publisher.

Well, when I was at TechReady 13 in July (TechReady is the main biannual technical conference for Microsoft field personnel such as Premier Field Engineers), I went to the Ask The Experts session, found the PowerShell table, and asked how PowerShell security works. An extremely knowledgeable person explained that PowerShell neither improves nor weakens security, just as Windows Explorer (the file browser) or a command prompt do not improve or weaken security. PowerShell merely lets us automate tasks if we have sufficient permission to execute those tasks anyway. The primary aspect of security for PowerShell, just like for Windows Explorer and command prompts, is that it depends on how you're logged in and what permissions your account has.

I asked more questions, and this very friendly person answered all of them. As I was about to leave, I asked his name, and he introduced himself as Ed Wilson! The same Ed Wilson that writes "The Scripting Guys" TechNet blog and the author of numerous books, including 3 on PowerShell! So here I am in Seattle, all expenses paid by Microsoft, in the midst of an intensive training week, and I get to have a one-on-one conversation with Ed to get my PowerShell questions answered. Have I mentioned lately that I love working for Microsoft?

In summary, PowerShell does NOT introduce any additional vulnerabilities, regardless of whether it's used to manage SQL Server or not. PowerShell security relies first and foremost on the permissions of the person who is logged in and running PowerShell. PowerShell scripts can also be run by a service, which will rely on the permissions of the service account, but that's not recommended.

Separation of Duties for DBA's

Quantum John — Mon, 12 Sep 2011 17:44:00 GMT

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 a task, such as a bank vault combination having 6 numbers, and giving 3 to one employee and 3 to another.¹

Separation of duties is not designed into the SQL Server DBA role itself, as a senior SQL Server DBA will have the SQL Server sysadmin built-in server role, which gives a single DBA complete control over SQL Server. However, there are still several ways we can apply separation of duties to the management of SQL Server.

1. You can prevent operating system administrators from gaining easy access to SQL Server. For example, SQL Server 2008 no longer includes a "BUILTIN\Administrators" group that used to give every local Windows administrator full sysadmin privileges in SQL Server by default. (See DoD Database STIG, SRR DG0116.)

2. You can split data among different instances of SQL Server and not allow DBA's from one instance to have permissions on the other. Multiple instances can be on the same server or multiple servers, and multiple servers provides an option for also splitting Windows privileges between two sets of operating system administrators.

3. You can create applications that enforce separation of duties in the external management of data.

4. You can prevent SQL Server DBA's from having local administrator privileges on Windows. This option provides very little value, because if a DBA is not trustworthy they can do far more harm with their DBA privileges than with Windows privileges, and it will interfere with their ability to manage folder permissions and registry edits, which are needed for tasks such as backup file management and installing service packs. If you choose to implement this option, the DBA's will need top-priority responses from Windows administrators to make O\S level changes for them.

5. Install and implement the SQL Server Separation of Duties Framework v2.0. See http://sqlserversod.codeplex.com/. (Thanks to SQLDragon for pointing this out!)

Footnotes:
1. I'll mention here that two other security principles may limit the effectiveness of separating duties, namely, job rotation and mandatory vacation. Both of these ideas will proliferate permissions if they aren't managed very carefully, and proliferating permissions can overcome the goal sought by separating duties.

SQL Server, the DoD, and Common Criteria

Quantum John — Fri, 12 Aug 2011 16:02:34 GMT

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 sp_configure ("common criteria compliance enabled") or by using SQL Server Management Studio (server properties, security page, options, "Enable Common Criteria compliance" checkbox).

Enabling SQL Server's Common Criteria switch will enable 3 functions:

Residual Information Protection
The ability to view login statistics
Prevention of a column-level GRANT from overriding a table-level DENY

For more details about these functions, see the SQL Server Books Online article here.

If you want to know about Common Criteria evaluations of different SQL Server versions and service pack levels, just go to this page and click on the tabs across the top.

Free SQL Ranger Training

Quantum John — Wed, 20 Jul 2011 17:02:00 GMT

When I first heard about "SQL Rangers" I wasn't sure what it was, but I definitely wanted to be one. Just 'cause it sounded so cool. Basically, it was an early name for what later became Microsoft Certified Masters, which was the highest level of certification. Over time, the Master certification program expanded and evolved, and now there's a higher level certification, that of Microsoft Certified Architect. And if you haven't paid attention since the old Microsoft Certified Database Administrator (MCDBA) days, here's the current premier SQL Server certifications, as of July 2011:

Microsoft Certified Technical Specialist (MCTS), which requires one exam.
Microsoft Certified Information Technology Professional (MCITP), which requires MCTS plus one exam.
Microsoft Certified Master (MCM) (a.k.a. Ranger), which requires MCITP plus one standard exam and one hands-on lab exam.
Microsoft Certified Architect (MCA), which requires MCM plus a detailed dossier and Review Board interview.

If you're not familiar with these certifications and want to find out more about them, start here for the SQL Server specific versions: http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx.

Study options for SQL Server MCTS and MCITP include a wide variety of boot camps and other classes, books, practice exams, and articles. The options for preparing for the SQL Ranger exam and lab are more limited, but there are now some great videos and other material, available online for free, from the folks at SqlSkills.com. SqlSkills has some classroom training available, but if you're interested in advanced self-study in one-hour bites, you can really increase your expertise with these videos: http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx, and this complete set of resources: http://sqlskills.com/mcm.asp.

If you're wondering how these certs relate to working for Microsoft Services, we expect our Premier Field Engineers (PFEs) and Consultants to attain and maintain the MCITP. It helps if you already have MCITP when you're going through the hiring process, but it's not required. If you become a SQL Ranger while working for us, it helps you build your cred with your peers. The only study option used to be a very expensive 6-week in-person boot camp, and the fact that you wouldn't be burning customer-contract hours for those weeks was a major disincentive for managers to support it. Now that I've found this great training online, I'm going to pursue it myself.

Since this is a security-themed blog, I'll tie in the security aspect of certifications here. A person doesn't have to have a cert to have knowledge. And a cert is not proof that a person is competent. However, for a large organization, a policy of requiring job-related I.T. certs increases the likelihood that a person will be competent in their technical role, and competent technicians is a critical requirement for being able to establish, maintain, and improve I.T. security. For that reason, more and more organizations are requiring I.T. certs for their technical staff. For example, the U.S. Department of Defense's (DoD) Directive 8570 requires both job-related I.T. certifications and security-related certifications. Which ones depends on your job role. A SQL Server MCTS will meet the I.T. certification requirement for some SQL Server DBAs working for the DoD, but others may have to have SQL Server MCITP.

Conditionally Updating Statistics

Quantum John — Tue, 19 Jul 2011 17:49:00 GMT

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 your queries running as efficiently as possible. The main options are the UPDATE STATISTICS command and the sp_updatestats system stored procedure.

You may be aware that you can choose an Update Statistics task in SQL Server Management Studio's (SSMS) Maintenance Plan Wizard, but that task is dumb, in the sense that it will recompute stats on all indexes in a targeted database, whether they need it or not. This means that sp_updatestats is generally a better alternative, to avoid unnecessary CPU load, because sp_updatestats will only update statistics for indexes that need it. However, sp_updatestats executes per-database, and it does not provide fine-level control (e.g. sampling rate, all/column/index, etc.). In my experience, most systems don't need fine-level control, so the best option in most cases is to use sp_msforeachdb to loop through all databases and execute sp_updatestats, which will only update stats on those databases that need it:

EXEC sp_msforeachdb 'USE [?] EXEC sp_updatestats'

Put the command above in a SQL Server Agent job, schedule it, and you're done.

Advanced Options

1. If you happen to manage an instance of SQL Server that needs manual control over updating statistics, you can use the UPDATE STATISTICS command and specify options such as the sampling rate and whether to compute stats for all/column/index. Simply run whatever specific UPDATE STATISTICS commands you need, whenever you need them, and leave your scheduled sp_updatestats job alone. When the sp_updatestats job runs, if the targets of your specific UPDATE STATISTICS commands don't need updating, they'll be skipped.

2. Don't run sp_updatestats on a schedule and just run UPDATE STATISTICS after specific events, such as right after large inserts, updates, or deletes.

Notes

1. Rebuilding an index will cause SQL Server to recompute statistics, but reorganizing an index won't. If you want to experiment with rebuilds, reorgs, and updating stats, or just view a query that shows you index fragmentation and the date/time of the last statistics update of each index, see the attached script.

2. Why not just open sp_updatestats, copy the code, and tweak it to do whatever you want? Mainly because it uses a system function (stats_ver_current) that can only be run from inside a system procedure.

PFE vs. Consultant

Quantum John — Tue, 28 Jun 2011 19:04:00 GMT

Well, it's almost the end of June and none of my draft posts are close enough to finishing to meet my self-imposed standard of at least one post per month. So, here's a pathetic little human-interest post.

Microsoft has a Services group which provides a variety of support personnel to customers. Customers pay for this support, and as we're rather expensive, it's usually only large organizations that hire us. We're expensive because we're generally the leading experts in Microsoft technologies that are available for on-site assistance. Microsoft, like any of our competitors, has to pay competitive salaries in order to get the most qualified people to perform the work. Add a profit on top of the salaries, and that's why the per hour price may be higher than most other consulting companies supporting our products.

By the way, there are many less expensive support alternatives, including free. Such as this blog. Some huge company contracts with Microsoft Services and gets some of my time. Sometimes when I've encountered the same question more than once, I'll write a blog post here about the issue and what to do about it. Then everyone in the world can get my expertise for free. (Okay, everyone in the world with Internet access, but I'm guessing there aren't too many organizations using SQL Server that don't have Internet access.)

So, on to the topic in the title of this post. Microsoft Services is divided into Microsoft Consulting Services (MCS) and Microsoft Premier Services (Premier). What's the difference? Here's the best summary I've heard (sorry I can't remember who came up with it):

MCS consultants design and build things;
Premier Field Engineers (PFEs) help fix things if they break and when they aren't busy fixing broken things, they advise about how to prevent them from breaking in the first place.

That's it, short & sweet. Now for another tangent...

I've worked for a bunch of different consulting companies, and Microsoft is the best I've ever worked for, by every measure I can think of. Here's a very important example: Every consulting company I ever worked for verbally stressed, repeatedly, how important it was for their consultants to maintain their skills. Sometimes they put it in writing. But what they didn't do was actually support consultants spending time or money to study, experiment, or earn certifications. Will they pay for a week of training? Sorry, we can't spare you away from the customer contract you're on. How about if you take vacation time, will they at least cover the cost of a training program? Sorry, that's not in the budget right now. Microsoft, on the other hand, is extremely supportive, in both time and money. Microsoft consultants and PFEs both spend a lot of time studying to maintain and improve our knowledge and skills, and it's my perception that PFEs get to spend more time studying and experimenting than our consultants. As a PFE, I'm required to get at least 4 weeks per year of training. That's right, 4 weeks, minimum. That can be accounted for by formal training, such as workshops, semi-formal training, such as TechReady or technical industry conventions within our specialities, or informal training, such as reading or experimenting on a home computer lab. Those who know me well know I love learning... is it any wonder I love being a PFE?

Test SharePoint Mirror Without Down-Time

Quantum John — Thu, 19 May 2011 15:40:00 GMT

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 a copy of the data at a remote location. And 'remote location' often means there's a slow WAN (low-bandwidth) connection between the primary site and the secondary (mirror) site.

Well, an I.T. manager at one customer was concerned about how to test the disaster recovery plan. I'll mention that one reason mirroring is being used is because the slow WAN link made it impractical to regularly copy huge backup files. Simply relying on the mirroring monitor was not acceptable, because he wanted a full test, meaning he wanted test users to be able to see and interact with the remote instance of SharePoint. SharePoint was already installed on the remote site with only a default content database, but switching the mirror's primary and secondary roles in SQL Server wasn't good enough because that would require a few minutes of down-time for the production users while mounting and testing the content databases on the remote site, and this was a 24/7 production system.

This almost sounds like a scenario where you could create a Database Snapshot of the mirror, and run your test based on the snapshot. That would work for any test where it's okay for the data to be read-only, and my customer was okay with a read-only test. Unfortunately, SharePoint can't mount a content database without writing to it. So... here's the basic solution I came up with:

Mirror the SharePoint content database to a remote site.
Restore a full backup of the content database to a test server on the primary site (so you don't have to copy a huge backup file over the WAN).
Delete all the tables from the restored database.
Make a full backup of the table-less database. This is a shell SharePoint content database with all the users, stored procedures, etc, and it's backup file is tiny.
Copy the shell backup to the remote site and restore it.
Create a Database Snapshot of the mirror.
Create an SSIS package to copy the tables from the snapshot to the shell database, making it a fully functional, read-write test database.
Connect the remote-site instance of SharePoint to the test database, and let the test users confirm that everything works.

This solution works well, but beware of a couple of things that might trip you up:

Different SharePoint content databases may be associated with different application pools, which means they have different database users. A shell with the wrong users will cause failures. Just make sure your shell database has all the users necessary to support the app pools for all the content databases you're going to test.
An SSIS Data Flow task should work for every table in a SharePoint content database, but we had a failure in one system on the AllDocStreams table. We got around this problem by using an Execute SQL task with a SELECT -- INTO statement instead of a Data Flow task for that one table.

When I described this solution to a colleague, they said that while it might work, it's not a configuration supported by Microsoft. After thinking about that awhile, I disagree. Even though you may not see this solution fully described elsewhere in Microsoft documentation, each component of the solution is supported, therefore the entire solution is.

Pretty fun, huh? It's hard to believe I get paid for playing around like this...

Changing Domains on a SQL Server

Quantum John — Sat, 02 Apr 2011 15:29:00 GMT

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 via domain accounts/groups, which is normal if STIG'd. In this case you should temporarily enable the sa account and ensure you know the password, until you get the new domain sysadmin accounts/groups working.

2. Service Account. The new domain service account(s) will need the same privileges as the old domain service accounts. If there are no custom permissions, just use the SQL Server Configuration Manager to change the service account while being logged in as a domain administrator. If it's in a very high-security environment, you might have to make sure that your domain admin account has all the standard permissions.

3. SQL Server Domain-based Logins. Logins that are based on domain accounts or domain groups will need to be recreated. The old ones can be scripted out to recreate all their permissions.

4. Changing IP Addresses. If required by the domain change, and if it's clustered, then the virtual server IP address must change in addition to the individual nodes.

5. Service Principal Names (SPNs). These are always used by clustered instances, and sometimes used by stand alone instances. If the IP addresses are changing, the old SPNs need to be dropped and new ones created. If the SQL Server service account is used to manage its own SPNs (not recommended) then the new domain service account will need to be granted the "Write servicePrincipalName" privilege.

If the servers are STIG'd then you should have test servers, and you should change their domain first, to see if any other problems arise.

Do You Need MSDTC?

Quantum John — Thu, 10 Mar 2011 02:52:00 GMT

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 associated with SQL Server. Yet most DBA's don't interact with it, and may not know if it's being used. And even if you don't have any apps using SQL Server for distributed transactions, it may still be used by other services. So, if it's running but it's not being used, you need to disable it, but how do you determine if it's being used or not? There are several ways, but here's the easiest:

Log into the server as a Windows administrator.

Go to Start/Run (or the Start search box on later versions of Windows), enter dcomcnfg and hit Enter. Wait for the Component Services window to open (be patient), then drill into Component Services/Computers/My Computer/Distributed Transaction Coordinator/Local DTC/Transation Statistics.

So, if your server has been running for a long time and its MSDTC stats look like this:

...then your MSDTC is not being used, and you should disable it in Services (Start/Run/services.msc).

In case you're wondering where I stole this info, go to http://technet.microsoft.com/en-us/library/cc771686(WS.10).aspx and drill down. You'll find a wealth of info on managing MSDTC.

In addition, Microsoft provides a DTC Tester (http://support.microsoft.com/kb/293799) and a DTC Ping tool (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5e325025-4dcd-4658-a549-1d549ac17644&DisplayLang=en).