- Interact with Azure Cosmos DB with PowerShell - Tue, Sep 14 2021
- Azure health services: Track Microsoft cloud outages and maintenance - Wed, Sep 8 2021
- Powerline: Customize your PowerShell console - Tue, Aug 31 2021
If you are a Windows systems administrator, then there is a good chance that you are also an unwilling SQL Server DBA as well. After all, many organizations host line of business (LOB) applications that use SQL Server 2008 as their back-end data store.
Your organization may be subject to industry and/or governmental regulations that require you to step up the security of your IT infrastructure. On the other hand, the mere threat of data penetration or compromise may have you asking the question, “How can I secure our SQL Server databases without my having to know T-SQL and SQL Server architecture?”
The purpose of this article is to provide you with some nitty-gritty, fairly easy to implement security tweaks for SQL Server 2008. Of course, there is no such thing as a secure system. Instead, we have degrees of security, from low to high. Nevertheless, by following the tips I give you in this article, you will substantially improve the security of your SQL Server systems.
Tip #1: Change the default SQL Server port ^
By default, the first installation of a SQL Server 2008 engine (called the default instance) listens for incoming connections on TCP port 1433. This is bad news, of course, because any malicious user seeking to fingerprint your network will probe this well-known port in search of SQL Server services.
Additional installations of SQL Server on the same box (called named instances) will be dynamically assigned a TCP port number by SQL Server. However, I recommend you hard-code a personally chosen (and non-standard) port number for all of your SQL Server 2008 services.
Remember that SQL Server 2008 consists of several different services, each requiring its own TCP socket:
- SQL Server Database Engine
- SQL Server Agent
- SQL Server Analysis Services (SSAS)
- SQL Server Reporting Services (SSRS)
- SQL Server Integration Services (SSIS)
Before you change the listener port for SQL Server, please check in with your application development team beforehand. The last thing you want to do is to inadvertently break your LOB applications that may be hard-coded to connect to SQL Server by using the default port.
You’ll also want to check in with the individual(s) who manage your corporate firewall, because exceptions will doubtless need to be created for the newly assigned SQL Server port numbers.
Rather than give you the click-by-click procedures for securing SQL Server in this article, I will instead point you to hand-picked external Web sites that have already undertaken the task in excellent form.
For instance, to learn how to change the SQL Server listener port numbers, please consult the following article:
Tip #2: Harden SQL Server service accounts ^
Each SQL Server 2008 engine that you install has an associated Windows service account. Your first plan of attack is to create a separate low-privilege Active Directory user account for use by each SQL Server service.
The reason why you don’t want to use a single service account is that if someone compromises the account, they would be able to control all SQL Server services.
SQL Server will automatically grant the service account any relevant OS privileges; therefore, you should never make your SQL Server service accounts a member of any administrative group.
Here are a couple of nice tutorial links on how to specify service account identities for SQL Server 2008:
We also want to ensure that our SQL Server service accounts have strong passwords. This subject introduces a common Windows administrator conundrum: password policy. Normally, when user accounts have password expiration, this produces headaches with service accounts and potentially breaks LOB applications that contain hard-coded references to service account passwords.
If you run Windows Server 2008 R2, you can leverage the stunningly awesome managed service account (MSA) capability and let Windows itself manage the passwords for service accounts.
To create and manage MSAs, we use Windows PowerShell 2.0. See the following link for instructions:
Tip #3: Take control of authentication ^
As you may already know, SQL Server 2008 supports both Windows-integrated authentication as well as SQL Server authentication. Unless you have a compelling reason to use local SQL Server accounts, I strongly suggest that you go with Windows authentication so you can take advantage of:
- Domain password policy
- Kerberos authentication protocol
You also already know that you should change the names of your default Administrator and Guest accounts, right? Right.
Even if you choose not to enable SQL Server authentication, you should rename and potentially disable the built-in default system administrator (sa) SQL Server account. This can be accomplished either with the SQL Server Management Studio GUI tool or by running a couple simple Transact-SQL statements:
alter login sa with name = secretsa
alter login secretsa disable
Tip #4: Limit access of Public role and Guest account ^
SQL Server security is a thorny conceptual bush, indeed. The role-based security model in SQL Server includes a Public server role as well as a Public database role in every database within an instance.
The Public roles function very similarly to the Everyone and Authenticated Users Windows identities. SQL Server grants the CONNECT system privilege to the Public server role, and the Public database role has SELECT privilege against several system views.
Therefore, you want to strip as much access from the Public roles as you can get away with, and certainly not grant the roles any additional privileges.
For more information on this subject, see the following:
- Understanding SQL Server 2008 R2 Fixed Database Level Roles
- Permissions of Fixed Server Roles
- Brad’s Sure DBA Checklist
Each SQL Server database also includes a Guest database user, which can be renamed and disabled but not dropped.
The tips I provided you in this article will go a long way toward making your SQL Server 2008 instances less vulnerable to abuse/attack and more in compliance with any industry and/or governmental security mandates.
Subscribe to 4sysops newsletter!
Please feel free to share any additional easy-to-implement SQL Server security tips in the comments portion of this post.