SQL Server – Can’t Connect Using ‘sa’ Account, What Am I Missing?

sql server

I'm trying to log in to a SQL Server 2005 Express instance using the following command:

osql -S .\INSTANCE -U sa -P password

I get the error: Login failed for user 'sa'.

I can connect just fine using the -E switch. I must add that it's been working for months. I think someone changed something but I cannot figure out what is it.

This is what I tried:

  • Login using Windows authentication and changing the sa password:

    sp_password NULL, newpassword, 'sa'

  • Enabling 'sa' login:

    ALTER LOGIN sa ENABLE ;
    GO
    ALTER LOGIN sa WITH PASSWORD = 'newpassword' ;
    GO

  • Checked the Windows Registry to ensure that mixed authentication is enabled. The value was correct: LoginMode=2

What else should I check? Thanks in advance.

ADDITIONAL INFO:

This is a Windows 2003 Server. They have some password policies enabled, I remember that I needed to change the default 'sa' password my application uses when it installs SQL Server to another one more complex.

I'm connecting using VNC, so I can't really use SSMS

My application can connect using another SQL Server login, but no 'sa'

Finally, if we don't find a solution I will remove this instance and install it again, but I'd really like to find out what's the problem. Just in case it happens again and just for plain curiosity.

Best Answer

Like @SpaceManSpiff said, don't forget to check if Mixed mode is enabled. Someone changed that setting for me and I had the same problem. Here is how to solve it:

  1. Login to the MSSQL Server Management Studio with Windows Authentication.
  2. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  3. Under the Server Properties, select a page of "Security".
  4. Select the Server authentication as "SQL Server and Windows Authentication mode" and click Ok. MSSQL Server Management Database properties
  5. Restart the SQL Services and then try to login with 'sa' details.

Source: http://forums.eukhost.com/f15/login-failed-user-sa-microsoft-sql-server-error-18456-a-12544/

Related Topic