Question:
What are the specific tasks assigned to System Administrator (SA)role in SQL Server? How can I disable or rename SA account in SQL Server 2005? How to track password changes on SA account?
Solution:
There are few caveats for the beginners in SQL Server to be aware about in managing roles and security. The System Administrator (SA) login is a special login provided for backward compatibility which cannot be revoked or deleted from SQL Server. Due to the wealth of features available for this login, users sometimes neglect the security vulnerabilities inherent in using the. Prior to SQL Server 2000 Service Pack 3a,there was no warning prompting DBA’s to change the SA password. In later release, during installation, SQL Server Setup prompts you to set an sa login password if you request Mixed Mode authentication. It is best practice to avoid using the SA login for any application, in addition the user should assign a password immediately to prevent unauthorized access. For this reason Microsoft always recommends using Windows Authentication during the installation for the sake of login security integration. SA login has all kinds of privileges on SQL Server by default which must be restricted with a strong password.
There is no documented approach or restrictions in SQL Server 2000 to require that the SA password must not be blank or must be changed periodically. DBAs must take measures to secure the SA password and eliminate usage of the SA account by any application within their environment. It is not easy to implement security measures to protect against this, but due to the nature of security vulnerability this is a major requirement in any environment. DBA’s can check the last time the SA password was changed or attempted to be changed by running
SELECT * FROM master.dbo.syslogins WHERE [name] = ‘sa’
The resulted columns sid, [name], createdate, updatedate gives the required values.
As a part of Trustworthy computing process all operating systems and products from Microsoft are a “secure on its own” product with highly customizable security features. In SQL Server 2005, the SA has been changed to a predefined role that includes tasks that are useful for a report server administrator who has overall responsibility for a report server, but not necessarily for the content within it. Due to the nature of legacy applications the Authentication modes Mixed & Windows (Integrated) are still allowed in SQL Server 2005 version.
The newest security model in SQL Server 2005 introduces users and schema separation and more granular permissions. The ALTER LOGIN statement in SQL Server 2005 allows a DBA to disable the sa account and rename it. This is a good security measure for SQL Servers running in mixed authentication. The statements to rename and disable sa account are below:
ALTERLOGINsa DISABLE; –to disable
ALTERLOGINsa WITHNAME=[Admin-Sys]; — to rename to a different name
In SQL Server 2005, it is possible to change the sa account to a different name using the ALTER LOGIN command. But be aware that changing the SA name is not supported on servers in a replication topology and could result in replication failing. Changing the sa password is a relatively easy process with little to no impact on the organization. For SQL Server 2005 installations in side-by-side configurations, the SQL Server 2005 services must use accounts in the global domains group that are used for those services. The account should not appear directly in the local Administrators group. Failure to comply with this warning will cause unexpected security behavior between side-by-side installations and installations where only SQL Server 2005 is installed.
As with SQL Server 2000, in SQL Server 2005 the column modify_date of the sys.sql_logins catalog view can be used as an indicator of when the last property (default database, default language, etc.) for the login has changed. But if the SA name has been changed or disabled this value is of no use. In SQL Server 2005, passwords are tied to the operating system. In addition, SQL Server 2005 login passwords are now case-sensitive.
]]>