Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

When not to use the sa password in SQL Server applications


By:   |   Last Updated: 2006-12-15   |   Comments   |   Related Tips: More > 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


Last Updated: 2006-12-15


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools