When not to use the sa password in SQL Server applications

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

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

Related Tips: More | Become a paid author

Last Update: 12/15/2006

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! Write, edit, and explore SQL effortlessly with SQL Prompt. SQL Servers keeping you up at night? Contact expert SQL Server consultants for a Health Check. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Join the over million SQL Server Professionals who get their issues resolved daily. Demystify TempDB Performance and Manageability