SQL Server (Securing SMS Site Systems)

SMS uses SQL Server as the back-end database. If the database is compromised, attackers could bypass the SMS Administrator console and access SQL Server directly to launch attacks through SMS. Attacks against the SQL Server must be considered high risk.

SMS uses SQL Server security to provide access to the SMS site database. SQL Server provides two authentication methods. Windows Authentication is available for all SQL Server network libraries, and it authenticates access based on the Windows account. SQL Server Authentication uses SQL Server-specific accounts that are maintained within SQL Server. The SQL Server account must be specified when making the connection to SQL Server. SQL Server Authentication is present for backward compatibility and for clients running Windows 95 and Windows 98. Its use is discouraged for servers in an enterprise environment.

Mixed security is not an explicit option for SQL Server. Windows Authentication is always available. SQL Server Authentication can be enabled and disabled. The effect of mixed security is achieved by enabling SQL Server Authentication.

SQL Server provides roles, which resemble Windows group accounts that have members. Permissions are assigned to roles using GRANT, REVOKE, and DENY statements. DENY explicitly denies permission on an object and takes precedence over all other permissions.

Follow the SMS SQL Hardening Checklist

The Microsoft Web site has extensive information about how to secure SQL Server. Certain best practices for SQL Server servers functioning as database servers are inappropriate for the SMS site database server. The SMS 2003 Security checklists include a checklist for hardening SQL Server when used as the SMS site database server. This checklist is based on published best practices for securing SQL Server, but includes comments about how to modify these best practices appropriately to ensure SMS functionality.

SMS 2003 SQL Server Hardening list

Apply Service Packs and Security-Related Hotfixes as They Become Available

Subscribe to the Microsoft Security Notification Service (https://go.microsoft.com/fwlink/?LinkId=28819). Watch for security bulletins related to SQL Server and apply the latest updates for Microsoft Windows operating systems and SQL Server. Run MBSA on a regular interval to check for the latest operating system and components updates.

Install SMS and SQL Server on the Same Computer

Installing SQL Server and SMS on the same computer might seem to violate the idea of role separation to increase availability, but it is not a significant security concern. If either the SMS site database or the site server go offline, the other server is virtually useless. Installing SMS and SQL Server on the same computer simplifies SQL Server configuration and thus reduces the risk of making security-related mistakes.

Use a Dedicated SQL Server for Each SMS Site

Although multiple sites can share a single computer running SQL Server to store data, this is not a recommended configuration. Using a dedicated SQL Server for each SMS site provides better compartmentalization of data. If one SMS site database is compromised on one SQL Server, the attacker has easier access to another SMS site database on the same SQL Server.

Do not use the SMS site database server to run other SQL Server applications. Increasing access to the SMS site database server increases the risk to your SMS data.

Sharing a SQL Server between sites can make it more difficult to recover your site, which could delay SMS from functioning at a critical time. If a site was sharing a SQL Server with another site and it failed, you must ensure that the recovery process of the failed site does not affect the other healthy sites. The recovery process becomes more complicated in this situation because you must isolate the recovery procedures to only the failed site.

Configure SQL Server to Use Windows Authentication

Set up SQL Server to use Windows Authentication mode, as described in the SQL Server documentation. If you use Windows Authentication mode, SQL Server verifies logons based on a Windows account's user name and password. If you follow the recommendation to have a dedicated SQL Server for each SMS site, there is no compelling reason not to use Windows Authentication. During SMS Setup, specify that you are using SQL Server in Windows Authentication mode.

Configure the SQL Server Service to Run with a Domain User Account

SMS does not require SQL Server to run with the LocalSystem account. To follow the principal of least privilege, create a domain user account and configure the SQL Server service to run with that domain user account. Do not add this account to the Domain Admins group.

Manually create the Service Principal Names (SPN) if you are using advanced security

When the SQL Server service runs under an account with Domain Admin membership, the SQL Server service automatically creates the SQL SPN that contains the fully qualified domain name (FQDN) of SQL Server. If the SQL Server service runs under a non-Domain Admin account, the SQL Server service does not create the SQL SPN that contains the NetBIOS name of the server running SQL Server. If the SQL service account is not in the Domain Admins group, you must manually create the FQDN and the NetBIOS SQL SPNs. The problem most frequently occurs in the following cases:

• The SQL Server and the site system computer are in different domains, the SQL Server service starts with a user account or a LocalSystem account of the server running SQL Server, and the site system computer tries to connect to the server running SQL Server.

• The SQL Server service runs under a user account, and the site system computer tries to use the site server's computer account to connect to the server running SQL Server.

For the procedure to create the FQDN and the NetBIOS SQL SPNs, see article 829868 in the Microsoft Knowledge Base (https://go.microsoft.com/fwlink/?LinkId=4441).