none
I have to reset the password of sa every day.

    Question

  • hi

    I have sql 2012 working OK. from last few days my 'sa' password is getting
    re-set automatically. i know it is not possible but it is happening to me.


    then i have to login with window authn. and reset password almost every
    day.
    Even i have formatted a server and scanned with anti-virus too. Even
    though problem is there.

    please help me as soon as possible.thanks

    Monday, February 17, 2014 7:50 AM

Answers

  • I dont know how it is getting changed. But you can definitely TRACK those changes easily.

    you may execute the below and see when it has got changed?

    SELECT [name], sid, create_date, modify_date
    FROM sys.sql_logins
    WHERE [name] = 'sa'

    If you want to track who is changed and when(more details) please use the below link:

    http://social.msdn.microsoft.com/Forums/en-US/aca19142-3d92-450e-b4e6-c45b25c2154c/email-notification-for-created-and-modified-logins-in-sql-server-2008?forum=sqlsecurity

    Note: For security purposes, I always tend to suggest to turn off your SA account and give the SA permission to your WINDOWS Login. However, this is up to your security policies.

    Monday, February 17, 2014 8:27 AM
  • Have you ruled out that the 'sa' login is not being locked out by failed login attempts?

    Check the SQL Server Error Log for information regarding failed Login attempts. The following blog post provides a more detailed explanation of how to do this.

    If you're still unable to identify the root cause, then it's time to use either SQL Server Profiler or Extended Events in order to capture security events for inspection. In particular you looking to identify the source IP address and Application Name, of the Login attempts/password reset


    John Sansom | SQL Server MCM

    Blog | Twitter | LinkedIn | SQL Consulting



    Monday, February 17, 2014 8:29 AM
  • John

    M. Mahfoud EL HOUDAIGUI is right. Sa should be disabled for the servers that work with web application  as it exposure over an internet. There is no such document that states we need to disable that login but if you worked with web application you probably know that hackers try to capture the SA password first..... 

    This from SQL Server 2012 best sequrity practice document

    Best practices for authentication mode and logins

    • Always use Windows Authentication mode if possible.
    • Use Mixed Mode Authentication only for legacy applications, non-Windows users, and users from untrusted domains.
    • Use the standard login DDL statements instead of the compatibility system procedures.
    • It the sa account is not going to be used, you should disable it. Change the sa account password to a known value if you might ever need to use it. Always use a strong password for the sa account and change the sa account password periodically.
    • Do not manage SQL Server by using the sa login account; assign sysadmin privilege to a knows user or group.
    • Rename the sa account to a different account name to prevent attacks on the sa account by name.
    • Do not delete internal built-in logins
    • Use Windows Logins rather than Windows Group to control access to SQL Server and use care when using Windows Group logins to prevent group overlap for a particular user.
    Use login triggers for more granular control of the login process


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 18, 2014 8:45 AM

All replies

  • I dont know how it is getting changed. But you can definitely TRACK those changes easily.

    you may execute the below and see when it has got changed?

    SELECT [name], sid, create_date, modify_date
    FROM sys.sql_logins
    WHERE [name] = 'sa'

    If you want to track who is changed and when(more details) please use the below link:

    http://social.msdn.microsoft.com/Forums/en-US/aca19142-3d92-450e-b4e6-c45b25c2154c/email-notification-for-created-and-modified-logins-in-sql-server-2008?forum=sqlsecurity

    Note: For security purposes, I always tend to suggest to turn off your SA account and give the SA permission to your WINDOWS Login. However, this is up to your security policies.

    Monday, February 17, 2014 8:27 AM
  • Have you ruled out that the 'sa' login is not being locked out by failed login attempts?

    Check the SQL Server Error Log for information regarding failed Login attempts. The following blog post provides a more detailed explanation of how to do this.

    If you're still unable to identify the root cause, then it's time to use either SQL Server Profiler or Extended Events in order to capture security events for inspection. In particular you looking to identify the source IP address and Application Name, of the Login attempts/password reset


    John Sansom | SQL Server MCM

    Blog | Twitter | LinkedIn | SQL Consulting



    Monday, February 17, 2014 8:29 AM
  • Is that possible somebody from your company changes the password? Is that possible somebody hijacked the server?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 18, 2014 6:38 AM
  • Hey Mahbabeh_t

    Can you check the policy expiration settings for account [sa]. You need to uncheck the highlighted option:

    

    Tuesday, February 18, 2014 7:08 AM
  • For the Best Practices, you must desable the (sa) account, beacause we can't delete it, and create a new SQL Login with sysadmin role.

    You can verify if you have any strategies under Manager node in SQL Server Management Studio, that reset the password.

    Tuesday, February 18, 2014 7:11 AM
  • For the Best Practices, you must desable the (sa) account, beacause we can't delete it, and create a new SQL Login with sysadmin role.

    Perhaps you can share some references documenting this as a Best Practice to support your claim?

    The 'sa' account exists for a reason.

    Just because you consider something to be "good practice" for your use case does not make it Best Practice. Help future readers of your content by backing up your argument with references and reasoning.


    John Sansom | SQL Server MCM

    Blog | Twitter | LinkedIn | SQL Consulting


    • Edited by John Sansom Tuesday, February 18, 2014 7:46 AM
    Tuesday, February 18, 2014 7:44 AM
  • John

    M. Mahfoud EL HOUDAIGUI is right. Sa should be disabled for the servers that work with web application  as it exposure over an internet. There is no such document that states we need to disable that login but if you worked with web application you probably know that hackers try to capture the SA password first..... 

    This from SQL Server 2012 best sequrity practice document

    Best practices for authentication mode and logins

    • Always use Windows Authentication mode if possible.
    • Use Mixed Mode Authentication only for legacy applications, non-Windows users, and users from untrusted domains.
    • Use the standard login DDL statements instead of the compatibility system procedures.
    • It the sa account is not going to be used, you should disable it. Change the sa account password to a known value if you might ever need to use it. Always use a strong password for the sa account and change the sa account password periodically.
    • Do not manage SQL Server by using the sa login account; assign sysadmin privilege to a knows user or group.
    • Rename the sa account to a different account name to prevent attacks on the sa account by name.
    • Do not delete internal built-in logins
    • Use Windows Logins rather than Windows Group to control access to SQL Server and use care when using Windows Group logins to prevent group overlap for a particular user.
    Use login triggers for more granular control of the login process


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 18, 2014 8:45 AM
  •  if you worked with web application you probably know that

    I'm quite familiar with implementing robust security practices thank you ;-)

    Seriously though, thanks for the clarification through provision of additional information. 

    My point was that it's important for forum contributors to provide reasoning and evidence to support their posts, in order to provide higher quality content for all to benefit from.

    The specific approach for each environment to security is dependant on the individual use case circumstances. Best Practice should be considered as "general guidelines" NOT the hard and fast rule IMHO. That's why it's important to share reasoning, so that people can understand the decision making process used and then make the most informed decision for their own unique environment.

    Thanks again for the additional details. It might also benefit readers if the link to the document you mention is included in the thread.


    John Sansom | SQL Server MCM

    Blog | Twitter | LinkedIn | SQL Consulting


    • Edited by John Sansom Tuesday, February 18, 2014 9:05 AM
    Tuesday, February 18, 2014 9:04 AM