Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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 (http://stackoverflow.com/questions/1393654/how-to-change-from-sql-server-windows-mode-to-mixed-modesql-server-2008)

share|improve this answer
    
How to verify that SQL Server has mixed authentication. –  goofyui Jan 8 at 17:35
    
thanks . Will check that –  goofyui Jan 8 at 17:54

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.

share|improve this answer

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.

share|improve this answer
    
sa user is Enabled in the Login (Option Box ) –  goofyui Jan 8 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 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 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 at 17:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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