Bad habits: Giving out the sa password
sa password should be a well-guarded secret, held in the same regard as the domain administrator password. Unfortunately, I come across too many environments where
sa is used everywhere - people log in manually because it makes it easier to get things done, and people put it in their connection strings because it makes it much easier to manage the security for their applications.
This needs to stop.
The security holes this introduces are immeasurable. And I don't really think it matters if you've disabled or renamed the
sa account and are using a "more secure" (read: just slightly more obscure) account name. Once the
sa password is out there - say, in a critical 24x7 application or in your CFO's spreadsheet - the password is impossible to change, and the account is impossible to disable.
Your developers need sa privileges?
They probably don't. I'll confess, back in the day, it was far easier for me to use a privileged account than to granularly apply the right permissions to all the objects we were creating (especially during rapid early development), and all the objects those objects touched. These days, options for procedures like
EXECUTE AS OWNER and signing modules with certificates make it much easier to grant rights to the top-level objects, and that's it. The developers themselves should have Windows auth logins (so that they can't share credentials, making auditing much easier) and the application(s) they're developing should have a separate login.
A web site or application (maybe a vendor application) needs to connect as sa?
Well, I would fight even the sysadmin role in general, never mind
sa explicitly, depending on the nature of the application. That said, I do understand why certain types of applications may require elevated privileges. If they really do, there is no reason to give them
sa explicitly; just add their dedicated login to the sysadmin role. If they don't, then give them a dedicated login that has precisely the rights that they do need. In either case, when the application goes away, so can the login; and if the login needs to be changed for whatever reason, it only affects that application.
You need to have someone else do some admin work while you're out on vacation?
Great! Create a dedicated login for them that you enable for that time period, and add it to the sysadmin role. Or better yet, add their Windows login to the sysadmin role temporarily. Better still, only grant their Windows login privileges for the specific tasks they need to accomplish. On SQL Server 2012 and newer, for example, you can use custom server roles for more granular control (as described here by Mike Walsh).
Dedicated per-app logins and having users perform their work via Windows logins is the way to go, IMHO, both for security and auditing purposes. If anyone outside the core DBAs have the
sa password, it may be time to revisit that policy. Oh, and please make the password - and all SQL auth passwords, for that matter - strong.
QWERTY are not good options.
Aaron (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange. Aaron's blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.