SQL Server Transparent Database Encryption (TDE)

Overview of TDE with some details on major administrative issues. Many people who’ve played around with TDE seem to have had trouble with restoring a TDE database on an alternate server, and the confusion seems to stem primarily from the deep encryption heirarchy for TDE. It’s not too hard, however, once you realize that you…

1

Hiding SQL Server from External Crackers

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…


Managed Service Accounts

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…

1

Enabling SSL on SQL Server Connections on Failover Clusters

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…


SQL Server Ports

 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…

3

Note to self on AlwaysOn…

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…

3

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

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…


SQL Server Accounts

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…

2

Securing SQL Server Integration Services (SSIS)

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…

1

SQL Server and PowerShell Security

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…