26

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.

11 Answers 11

36

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/

14

Ok, I've been able to figure out what was happening (kind of) and got a workaround.

It seems that one week ago they were playing with the Windows Security policy. They were adding/removing permissions but they can't give me exactly what they did because they really didn't know (ouch!).

Anyway, I connected using Windows authentication (-E switch) and run the following query:

ALTER LOGIN [sa] WITH PASSWORD='newpassword', CHECK_POLICY=OFF
GO
ALTER LOGIN [sa] ENABLE
GO

The key here is CHECK_POLICY=OFF. That made the trick. I hope this will make 'sa' immune to future changes in their domain setup.

Thanks for all your suggestions.

1
  • I used this while having issue with my windows docker container that hosts sql server. The SA password passed on the command to run the container just did not work even if I change is to whatever value. ( container: microsoft/mssql-server-windows-express). That worked thanks a lot JAG!
    – codea
    Feb 15, 2021 at 9:13
6

Things to check

Password on SA account

Mixed mode enabled

Is the SA account disabled?

Create another SQL account and try it (since you can get in with -E you dhould be able to do this)

Test using an ODBC connection, you can create this connection to see if the SA works

Oh and the always catch all in windows, reboot (seriously, this helps my test SQL server after I've done stuff to it)

1

I had that problem some years ago after installing a Windows ServicePack (no SQLServer Update, but Windows), the SQLServer refused connections, until the SQLServer ServicePack was installed also! I found a message in the eventlog after some hours.

I think they did this, because they knew there is a big security hole and wanted to force all admins to install the pack. However, since it is some time ago, I don't no the exact versions and so on. I would recommend, you double check you event log and install all/newest service packs.

2
  • This looks promising, I'll try it on Monday and report back. Thanks for the suggestion.
    – JAG
    Jul 3, 2009 at 18:58
  • Finally I did not try to install the latest Service Pack. See my own 'accepted answer'.
    – JAG
    Jul 6, 2009 at 9:57
1

If you are installing SQL Express 2014, you'll have to do a few things to resolve this error if you try logging in with the [sa] account.

  1. The "sa" account will become disabled. So you'll need to toggle the account from disabled to enabled under the status section of the account.

  2. The server must have windows and sql server authentication enabled. By default, only windows authentication is enabled.

  3. Reset the password. (see code below; ServerFault has a bug where code doesn't show up the same when using an ordered list as of 6/8/2016)

  4. Restart the Windows Service for SQL Server

=========

ALTER LOGIN sa WITH PASSWORD='password', CHECK_POLICY=OFF;
ALTER LOGIN [sa] ENABLE;
0

Assuming you have Management Studio or Management Studio Express installed, can you connect the Management Studio to the instance using sa?

JR

2
  • 1
    SSMS is not installed and I'd prefer not to install it if possible. Also, connecting from another computer with SSMS is not an option as this is a customer server. Only the command line tools are available.
    – JAG
    Jul 3, 2009 at 11:25
  • If you have the Management Studio on another PC, even if it's at the far end of a VPN, you should still be able to connect. The point is to check whether there's something screwy with the server setup, or whether the problem lies with the osql command. Jul 3, 2009 at 14:43
0

Did you make sure that you restarted the SQL Server service after you made the registry change?

1
  • Didn't need to change it because it had the correct value.
    – JAG
    Jul 3, 2009 at 12:33
0

Did somebody remove the database which was the default database for "sa"?

If that is the problem, try

EXEC sp_defaultdb 'sa', 'New default database'
3
  • In theory, there is only one database and it exists. I didn't run that command though.
    – JAG
    Jul 3, 2009 at 12:34
  • Normally it's 'master'. But I think that's not the problem...
    – splattne
    Jul 3, 2009 at 13:00
  • I've just tried it but made no difference. Thanks anyway.
    – JAG
    Jul 6, 2009 at 9:44
0

Sometimes login is disabled due to many incorrect passwords or any policy violation. So what we can do is just log in with window authentication, change the password and enable login again.

Log into the database with Windows Authentication

Run Query:

ALTER LOGIN [sa] WITH PASSWORD='newpassword', CHECK_POLICY=OFF 
GO
ALTER LOGIN [sa] ENABLE
GO

Use of CHECK_POLICY is required as it specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default is ON when we make OFF it will change the password without any difficulties. Later you can ON

NOTE: What I have observed that when CHECK_POLICY is on the GUI SSME solutions for changing password won't work every time. I don't the reason behind but that I have observed.

0

i resolved it as follows logon with your windows authentication goto properties of the server>>Security and change the server authenticati

1
  • 1
    Could you please spend more with formatting (and completing) your answer? As this is written, is not much of a help.
    – asdmin
    Nov 1, 2019 at 16:54
0

Thank you, this is the article I was looking for. Btw may I know what you use for your great and fast website? I'm also planning to create a simple website for my business, but I need a domain and hosting recommendation. I hear asphostportal has a good reputation for that. Can you recommend them or are there any alternatives?

1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .