In theory, nobody should be using the sa login for anything. In reality however, I’ve seen many applications and processes configured to use it. Trying to change the way these applications and processes are setup, or convince people to change them, is a challenge to say the least… a challenge which I wont try to resolve today. For this post, lets assume your applications don’t use sa.
If you have your SQL Server instance configured to allow both Windows authentication and SQL Server authentication, you should consider securing the SQL Server sa login. This is to protect from attackers trying to login as the sa login.
Firstly, you’ll want to set a long and secure password. These days, passwords ideally should be a pass phrase, but in this case, it can be randomly generated. Save that password into your password safe. You shouldn’t ever need to retrieve it again. A strong password should make it harder for an attacker to crack the sa password. Here’s the code you can use to change the password:
ALTER LOGIN [sa] WITH PASSWORD = N'StrongPassword';
Secondly, you’ll want to rename sa to something different. Anything really, just not sa. If the login is not called sa in the first place, it’ll make it harder for the attacker to login as sa, right? Use this command:
ALTER LOGIN [sa] WITH NAME = [NewSAName];
Thirdly, you’ll want to disable the sa login. Just another barrier for an attacker to overcome when trying to login as sa. The command is:
ALTER LOGIN [NewSAName] DISABLE; -- Using the new sa name
Auditors love these settings. Do these steps, and it’s one less thing to worry about when the auditors are poking around.
When I install and configure a new SQL Server instance, I will set the password as part of the install. Then after installation is complete, I will run this script below to rename and disable sa:
SET NOCOUNT ON;
DECLARE @NewSALoginName NVARCHAR(128) = N'NewSAName';
DECLARE @CurrentSALoginName NVARCHAR(128);
DECLARE @SQLCommand NVARCHAR(128);
SELECT @CurrentSALoginName = name
FROM [master].sys.server_principals
WHERE [sid] = 0x01;
IF @CurrentSALoginName <> @NewSALoginName
BEGIN
SET @SQLCommand = N'ALTER LOGIN [' + @CurrentSALoginName + N'] WITH NAME = ' + @NewSALoginName + N';';
EXECUTE sp_executesql @SQLCommand;
PRINT N'Changed ' + @CurrentSALoginName + N' to ' + @NewSALoginName + N'.';
END
ELSE PRINT N'sa login already set to ' + @NewSALoginName;
SET @SQLCOmmand = N'ALTER LOGIN [' + @NewSALoginName + N'] DISABLE;';
EXECUTE sp_executesql @SQLCommand;
PRINT N'Disabled ' + @NewSALoginName;
GO
Then, I will run this query to check it worked:
SELECT CONVERT(NVARCHAR(128), [name]) AS sa_name, is_disabled
FROM [master].sys.server_principals
WHERE [sid] = 0x01
Which looks like this:
This is just one of the many improvements you can do to improve your SQL Server security posture.
Chad Franklin 