SQL Server sa account and RiskFabric

Products

• Information Centric Analytics

Issue/Introduction

Your organization's security policies might require you to disable Microsoft SQL Server's built-in sa account. Does Information Centric Analytics (ICA) require the use of the sa server-level security principal in SQL Server?

NOTE: The sa account is distinct from the sysadmin fixed server-level role. For information about the sysadmin role and ICA, refer to the following sections of the Symantec ICA Administrator Guide:

• Required Installation Privileges and Credentials
• Permission Settings for the ICA Service Account Outside of Using the SQL Server sysadmin Role

Environment

Version : 6.x

Component : Microsoft SQL Server

Resolution

NOTE: If Mixed Mode Authentication for SQL Server is enabled in your environment and you plan to disable the sa account, ensure you have specified at least one local or domain user as a system administrator in SQL Server before disabling the sa account. Refer to the Additional Information section of this article for more information.

In a standard deployment of ICA, the server-level security principal sa is not used by ICA and can be safely disabled without any adverse effects on ICA's database operations.

In a non-standard deployment of ICA, the following queries may be used to confirm the sa account has not been granted ownership of ICA-related database objects:

SELECT [name] FROM msdb.sys.syslogins WHERE dbname = N'RiskFabric';

SELECT     p.[name] AS "proxy_name",
           c.[name] AS "credential_name",
           c.[credential_identity]
FROM       msdb.dbo.sysproxies AS p
INNER JOIN msdb.sys.credentials AS c
        ON p.credential_id = c.credential_id;

 

SELECT [name], [type_desc] FROM msdb.sys.server_principals WHERE default_database_name = N'RiskFabric';

 

SELECT     d.[name] AS "Database",
           p.[name] AS "Principal"
FROM       msdb.sys.databases AS d
INNER JOIN msdb.sys.server_principals AS p
        ON d.owner_sid = p.[sid]
WHERE      d.[name] = N'RiskFabric';

 

ICA's database is named 'RiskFabric' by default. If you provided a different name when you installed ICA, replace 'RiskFabric' in these queries with the name you provided in your environment.

Additional Information

The sa account can only be used as a login to SQL Server when Mixed Mode Authentication (Windows Authentication and SQL Server authentication) is enabled. Per Microsoft:

If you select Windows Authentication during setup, the setup creates the sa account for SQL Server Authentication but it's disabled. If you later change to Mixed Mode Authentication and you want to use the sa account, you must enable the account. Any Windows or SQL Server account can be configured as a system administrator. Because the sa account is well known and often targeted by malicious users, don't enable the sa account unless your application requires it. Never set a blank or weak password for the sa account.

For more information about authentication modes in SQL Server, refer to the Authentication Mode section of Microsoft's SQL Server technical documentation online.