When SQL Server is installed, the “sa” login is created automatically. When using Windows-only authentication, this administrative account is disabled. With mixed mode authentication, the “sa” login can be a target for attacks.
When you install SQL Server, the “sa” login is created by default. This is an administrative account that has full access to, and complete control of, the SQL Server instance and all of its databases. For new installations, it’s sensible to configure SQL Server to use only Windows authentication. This forces all connections to use Windows or Active Directory accounts, rather than SQL Server logins. This security mode disables the “sa” login and randomises its password.
Some software only connects using SQL Server logins, forcing you to use mixed mode authentication. This means that the “sa” login is potentially available and could be the target of a brute force or dictionary attack. To reduce the possibility of the password being found and the SQL Server instance being compromised, you can rename the “sa” login or disable it entirely.
Renaming the “sa” login allows you to continue using a SQL Server login with full permissions to the server and databases but lowers the risk that the login will be compromised. To rename the login, use the ALTER LOGIN command and the WITH NAME clause. The following code changes the “sa” login to the less obvious name, “richard”.
There is a known problem with changing the name of the “sa” login. If you change the name and then attempt to update a SQL Server 2005 instance to SQL Server 2008, you will receive the following error.
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
This problem is caused by the upgrade expecting the “sa” login to exist. To resolve the issue, temporarily return the name to “sa” until the upgrade is complete.
If you have no need of the “sa” login, you can disable it using the ALTER LOGIN command. This is shown below:
If you need to re-enable the login at a later time, use the same command with the ENABLE clause:
You can also enable and disable the “sa” login using SQL Server Management Studio. In the Object Explorer, expand the Security branch of the tree view and then expand Logins. Right-click the “sa” login and choose “Properties” from the context-sensitive menu. You can change the enabled status of the account in the Status section of the dialog box.