By: Jeremy Kadlec | Comments | Related: > Security
Problem
Since SQL Server creates the sa standard login when mixed mode authentication is selected during the installation process it is common entity, making it a natural target to try to penetrate SQL Server. With many of the SQL Server installations using mixed mode authentication for custom applications, web based applications or third party application needs, managing the usage and password for the sa login is critical. It is not something to take lightly. This known login is a natural target because it has the highest privileges in SQL Server. As such, there are times that the sa login just should not be used to prevent unnecessary exposure. In this tip we will outline some of those times as the first installment of the 'sa series' from MSSQLTips.com.
Solution
The first step in identifying when not to use the sa user name and password is to identify all of the places where the login is being used. You may be surprised with the results. Another way of looking at this issue is, what hoops would you have to jump through if your sa password was compromised and you needed to change the password immediately? It probably would not be a pleasant process.
So where are the places that I should not use the sa login?
- Embedded in management tools or application development tools
- Hard coded in application connection strings
- DTS or SSIS connection objects
- UDL files
- Batch files
- SQLCMD, OSQL or ISQL T-SQL scripts
- In casual conversation
- On sticky notes on your desk or monitor
Alternatives to using the sa login
Depending on how the sa login and password is being used in your environment, dictates what steps can be taken to limit the sa usage. Once you have that information, then you can make a decision on how to migrate from using the sa credentials to other alternatives outlined below.
- Windows authentication to SQL Server with a Windows domain account
- Setup application logins that are assigned the needed rights in order for the application to operate properly
- Convert the code to execute in Jobs without having to authenticate externally to SQL Server
- Use the trusted connection switches for the connection strings or applications
Next Steps
- Assess the usage of the sa login in your environment to determine if the usage is acceptable or needs to be corrected to improve this portion of SQL Server security.
- Once you understand where the sa login is used, determine if the sa login is truly needed. See what alternatives can be put into place to limit the sa login usage and better secure the sa credentials.
- Be sure to properly test your applications to understand the needed rights and validate that the applications work properly.
- Secure the sa password to prevent this login from being used in future applications just because the password is known.
- Work with your team to understand the security needs as new applications are built.
- Stay tuned for the next installments of the 'sa series' from
MSSQLTips.com:
- Determine the last time you changed the sa password
- Determine how to find out who and what applications are using the sa user name and password
- Password management options for the sa login
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
