sa password fault - Possibly OK after restart

Question

sa password fault - Possibly OK after restart

as_1234

SSCrazy

Points: 2842
More actions
September 27, 2017 at 10:56 am

#402536

All,
Setup is as follows;
Two Server 2012 Datacenter servers
Availability group running on SQL 2016 Standard.
The first server started to refuse the SA password as invalid. Existing connections were OK, only new connections were refused.
I restarted the server and initially the same issue existed. After a few minutes it cleared.
Before the restart the first server also refused to connect to the cluster using the Windows Failover cluster manager (Access denied). After the restart it was OK.
If possible I wouldn't mind some thoughts on:
Has anyone seen this issue before?
Finding the possible cause. My thought is that it was a Windows issue affecting SQL rather than a SQL issue?
Can I trust that the data failed over to the second server OK? SSMS tells me that no data loss occurred but I'm new to AG groups so I'm concerned that it 'thinks' no data loss occured when actually there was some?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

I can't really help with the AG aspect of this, but it would seem strange that a Windows issue would affect a SQL Authenticated login. Have you looked at the properties of the login? You can try to investigate with a query like this:
SELECT name, principal_id, sid, is_disabled, create_date, modify_date, is_policy_checked, is_expiration_checked, LOGINPROPERTY (name, 'PasswordLastSetTime') AS PasswordLastSetTime, LOGINPROPERTY (name, 'BadPasswordCount') AS BadPasswordCount, LOGINPROPERTY (name, 'BadPasswordTime') AS BadPasswordTime, LOGINPROPERTY (name, 'DaysUntilExpiration') AS DaysUntilExpiration, LOGINPROPERTY (name, 'IsExpired') AS IsExpired, LOGINPROPERTY (name, 'IsLocked') AS IsLocked, LOGINPROPERTY (name, 'IsMustChange') AS IsMustChange, LOGINPROPERTY (name, 'LockoutTime') AS LockoutTime, default_database_name, default_language_name, credential_id FROM sys.sql_logins WHERE name NOT LIKE '##%' ORDER BY name;

as_1234 SSCrazy Points: 2842 More actions · Answer 2

Hello,

Thanks for your help.

That query shows a bad password time that matches the issue I had but lockout time of 1900-01-01 01:00:00.000. Weird.
I found one thing since my post. The SQL logs are showing a bad login every 15 minutes. There is a transaction backup and clean up job running every 15 minutes so it's probably that. Strangely it seems to complete OK. I will take a look at it.

At the moment I'm thinking that the regular invalid logins triggered some kind of lockout but the query seems to suggest otherwise and I checked the local security policy and the lock out seems to be disabled.

On the AG side - It seems to have worked OK. It looks like existing connections were running OK. Only new ones were being refused.

Thanks

as_1234 SSCrazy Points: 2842 More actions · Answer 3

All,

The issue with an invalid login showing in the sql logs when the maintenance plan runs looks like it might be a bug https://social.technet.microsoft.com/Forums/en-US/83b3d647-7c66-4b9b-a63d-bdc2c39385f6/maintenance-plan-login-failed-for-user-sa-on-master-database?forum=sqlsecurity

Just looking at the post

"in the maintenance plan go to Manage Connections -> Local server connection -> select "Use Windows NT Integrated security""

If I try that then it tells me the 'couldn't connect to server'. I can't find an explanation as to which Windows account it's trying to use? Does anyone know?

Thanks

Andrew

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

With that setting it will use the Windows account that the SQL Agent job tells it. If you look at the step in the SQL Agent job, there will be something like:
Step name:
subplan_1
Type:
SQL Server Integration Services Package
Run As:
SQL Server Agent Service Account (or some other specified proxy)

You can find out about setting up such a proxy in articles online like:
https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Of bigger concern... why do you have the SA login enabled? The general recommendation is to always disable the SA login to help prevent getting hacked.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

as_1234 SSCrazy Points: 2842 More actions · Answer 6

Jeff: Thanks.

Chris: Sorry I didn't see the notification for your post. Thanks, that's the account I thought it would use. Having re-checked the permissions that user doesn't have permissions to the database so that would explain the error.

I was getting confused by the 'run as' column showing 'sql server agent account' and thinking that if it's running the job successfully it must have access to the database. So the options in 'manage connections' override the 'run as' parameter on the subplan?

Thanks

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

as1981 - Monday, October 2, 2017 2:15 PM
Jeff: Thanks.
Chris: Sorry I didn't see the notification for your post. Thanks, that's the account I thought it would use. Having re-checked the permissions that user doesn't have permissions to the database so that would explain the error.
I was getting confused by the 'run as' column showing 'sql server agent account' and thinking that if it's running the job successfully it must have access to the database. So the options in 'manage connections' override the 'run as' parameter on the subplan?
Thanks

Heh... you're welcome but you didn't actually answer the question, which probably also means that you may be in deep KimChi security-wise.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

as_1234 SSCrazy Points: 2842 More actions · Answer 8

Hello,

Sorry, that wasn't intentional. I hadn't seen previous advice to disable SA so hadn't done it. I can see why it makes sense though so I will do so asap. I don't plan to run anything as SA hence meaning to run the SQL agent jobs using the SQL agent account.

I've just had to upgrade from 2000 to 2016 so I have quite a lot to figure out.

Thanks

Andrew

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 9

as1981 - Monday, October 2, 2017 2:15 PM
I was getting confused by the 'run as' column showing 'sql server agent account' and thinking that if it's running the job successfully it must have access to the database. So the options in 'manage connections' override the 'run as' parameter on the subplan?

Not exactly. If Manage Connections is set to Integrated Security, that means that it will use the credentials of whoever is running the maintenance plan, which in this case would be configured in the SQL Server Agent job step. If you had set a SQL Authenticated user in Manage Connections, then yes that would take precedence over how SQL Server Agent job step is configured.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

as1981 - Monday, October 2, 2017 2:33 PM
Hello,
Sorry, that wasn't intentional. I hadn't seen previous advice to disable SA so hadn't done it. I can see why it makes sense though so I will do so asap. I don't plan to run anything as SA hence meaning to run the SQL agent jobs using the SQL agent account.
I've just had to upgrade from 2000 to 2016 so I have quite a lot to figure out.
Thanks
Andrew

Ah... understood.

There's a pretty good Microsoft document on security. It's a bit dated but it does cover a whole lot quite nicely. The stuff on "Surface Area" is a bit useless because if anyone that shouldn't gets in with SysAdmin privs, having all that stuff turned off won't amount to even a speed bump to attack software. That's the real key... no one get's SysAdmin privs except DBAs and maybe the occasional Windows Admin. Everyone else get's limited privs. I extend that to "db_owner" privs, as well. YMMV if you have a multi-tenant server. Long read but it has a ton of good info that will keep you and the company you work for out of hot water.
http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx

You should also hire a pro to do penetration testing of your apps because SQL Injection is still one of most common ways the bad guys get in. That also means that you should never give an app more than read, write, and execute (existing) stored procedures. It I had my way, no app would have read or write. Everything would be done through a stored procedure. Of course, that's a battle that is virtually impossible to win

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)