Last week a customer of one of my applications (not dbdesc nor dtsdoc), reported the following error when the app performed a special task:

1

Login failed for user 'sa'

This app installs its own SQL Server instance with SQL Server authentication and it manages the users including the sa account. There’s no way to change the ‘sa’ password from the application, so probably someone might have been playing with my instance.

I logged in to this server and connect to SQL Server using the osql tool using windows authentication:

1

osql -E -S .\MYAPP

I then reset the sa password back to the original one:

1

sp_password NULL, 'mypassword', 'sa'

Unfortunately, this didn’t solve the problem. So the ‘sa’ password was intact after all.

Next thing I checked was that mixed-mode authentication was indeed enabled. It was unlikely that someone had changed this but.

I checked the registry key:

1

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

It had the correct value 2 which means mixed-mode.

What else to check? Ah, maybe the ‘sa’ login has been disabled. Let’s enable it again:

1
2
3
4

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

No luck, still can’t log in using the sa user. Here I started to be worried about this issue. I checked the system logs, nothing special there. Some errors reporting that ‘sa’ was unable to connect to SQL Server and nothing else. I also checked the SQL Server errorlog file. Everything seemed normal.

I have to say that this company has no IT department per se, but a guy who regularly helps them with their domain, active directory, etc. This guy was on vacation and unreachable.

I had no other option that to ask my customer if they had made any changes to their server, hoping to get some clue about what was going on. And he confessed. They were making changes to the Windows security policy editor adding and removing permissions a few days ago. He didn’t know exactly what they did, only that they “touch” different things (ouch!!).

Anyway, not my problem. Fortunately this gave me the clue I was looking for. The ‘sa’ user was being affected by a Windows policy setting. So I fixed it using this code:

1
2
3
4

ALTER LOGIN sa WITH PASSWORD='mypassword', CHECK_POLICY=OFF
GO
ALTER LOGIN sa ENABLE
GO

Note the CHECK_POLICY parameter. It makes the login immune to domain setup changes.

In summary:

4 things to check if you can’t log in to SQL Server using the sa account

1. Check the password and provide a new one if necessary

   sp_password NULL, 'mypassword', 'sa'

2. Check that mixed-mode authentication is enabled

   HKLM\Software\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer\LoginMode

   Should have the value 2. If not, change it and remember to restart the service.
3. Enable the sa account

   ALTER LOGIN sa ENABLE

4. Check that the sa account is not being affected by the domain security policy

   ALTER LOGIN [sa] WITH PASSWORD='mypassword', CHECK_POLICY=OFF