July 1, 2016 at 3:28 pm
What would cause an account to temporarily show as Locked and then the lock appears to clear itself?
Can high server or network traffic cause a login error?
Any thoughts on how to debug what is happening?
Background:
We have a SQL Server 2014 database that has a SQL Server authentication account (not Windows authentication) that is used by our Tableau server for data extracts and live data connections for reports. I'll refer to that as the ReadOnlyAccount (not the real name).
Periodically the account has become locked or disabled which causes the Tableau reports to fail to update, yet we see no error in the Tableau Searver log file. We set up a job that runs every 15 minutes that checks the account using the following code (not showing the real account name):
[font="Courier New"]SELECT LOGINPROPERTY('ReadOnlyAccount', 'IsLocked') as Locked
,is_disabled
from sys.server_principals
WHERE name = 'ReadOnlyAccount'
[/font]
The job emails us if the account is locked or disabled. We've had the alert fire. However, when we have checked the account frequently it was not locked or disabled -- though sometimes it was -- perhaps only 1 in 4 times it was locked when we looked after receiving the email.
In the log file I see periodic login errors from the Tableau server interspersed with successful logins from the same server:
[font="Courier New"]07/01/2016 13:39:54,Logon,Unknown,Login succeeded for user 'ReadOnlyAccount'. Connection made using SQL Server authentication. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:54,Logon,Unknown,Login succeeded for user 'ReadOnlyAccount'. Connection made using SQL Server authentication. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:54,Logon,Unknown,Login succeeded for user 'ReadOnlyAccount'. Connection made using SQL Server authentication. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:54,Logon,Unknown,Login succeeded for user 'ReadOnlyAccount'. Connection made using SQL Server authentication. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:54,Logon,Unknown,Login succeeded for user 'ReadOnlyAccount'. Connection made using SQL Server authentication. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:54,Logon,Unknown,Login succeeded for user 'ReadOnlyAccount'. Connection made using SQL Server authentication. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:54,Logon,Unknown,Login succeeded for user 'ReadOnlyAccount'. Connection made using SQL Server authentication. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:53,Logon,Unknown,Login succeeded for user 'ReadOnlyAccount'. Connection made using SQL Server authentication. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:53,Logon,Unknown,Login failed for user 'ReadOnlyAccount'. Reason: Password did not match that for the login provided. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:53,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8.
07/01/2016 13:39:52,Logon,Unknown,Login succeeded for user 'ReadOnlyAccount'. Connection made using SQL Server authentication. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:52,Logon,Unknown,Login failed for user 'ReadOnlyAccount'. Reason: Password did not match that for the login provided. [CLIENT: XX.XXX.XXX.71]
07/01/2016 13:39:52,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8.
07/01/2016 13:35:30,Logon,Unknown,Login succeeded for user 'ReadOnlyAccount'. Connection made using SQL Server authentication. [CLIENT: XX.XXX.XXX.71]
[/font]
July 2, 2016 at 10:54 am
Is there more code for the alert? That will track status, but not alert for disabled.
It is possible that a wrong password can lock an account, check the policy on your local server. If the time to unlock was short enough, this could happen.
September 9, 2016 at 12:03 pm
The issue had escalated to the point where we were seeing this over 600 times a day.
We had hundreds of workbooks and data connections that all used the same SQL Server account.
We logically grouped out reports and assigned a different SQL Server account and password for each of the several dozen groups.
We have not seen that issue again in the past 24 hours.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply