1

I am working with a SQL Server 2012 database. I am able to log into the server using Windows authentication. Although I have the SQL Server authentication username and password which is provided to me by the team, but authentication fails.

Is there anyway to find the password for the username : sa. Also, how to create new username and password without affecting the existing sa username.

3 Answers 3

1

New accounts with system administrator access can be created by other equivalent accounts but there is no way to reverse engineer the password for an existing account. You should also verify that the SQL Server you're connecting to allows for mixed authentication (Windows and SQL Server).

This StackOverflow question describes how to access authentication via SQL Server Management Studio (https://stackoverflow.com/questions/1393654/how-to-change-from-sql-server-windows-mode-to-mixed-modesql-server-2008)

1
  • How to verify that SQL Server has mixed authentication.
    – goofyui
    Jan 8, 2014 at 17:35
1

When it comes to accessing SQL Server, always use Principle of Least Privilege.

You dont need sa account to do your routine work. Also, if you have windows authentication, why do you even need a SQL Server login with user and password.

From Choose an Authentication Mode

When a user connects through a Windows user account, SQL Server validates the account name and password using the Windows principal token in the operating system. This means that the user identity is confirmed by Windows. SQL Server does not ask for the password, and does not perform the identity validation. Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication. Windows Authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration. A connection made using Windows Authentication is sometimes called a trusted connection, because SQL Server trusts the credentials provided by Windows.

-- check for authentication Mixed or windows

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
        WHEN 1
            THEN 'Windows Authentication'
        WHEN 0
            THEN 'Windows and SQL Server Authentication'
        END AS [Authentication Mode]

Now even if you want to use SQL Server authentication, then log-in using your windows authentication and then create a login with sql authentication and map that to the database that you want to access with ONLY required privileges.

0

Are you sure that the sa user is enabled? sa user is not usually used as a login, users with administrator levels are more common.

My 2012 sa details are set in security > logins, however the status is 'grant' connection, 'disabled' logins.

4
  • sa user is Enabled in the Login (Option Box )
    – goofyui
    Jan 8, 2014 at 17:47
  • Does the 'General' option tag offer password reset? However sa is utterly central to the system so changing the password may not be the best idea!
    – EJW
    Jan 8, 2014 at 17:51
  • I am not sure, but not interested to change the SA Password. Other application might be running on that password.
    – goofyui
    Jan 8, 2014 at 17:53
  • If you can see that the sa is enabled for login, can you use another admin user as to see the permissions settings you must have logged in. I won't mention the issue that making changes as sa could bring other permissions issues.
    – EJW
    Jan 8, 2014 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.