January 22, 2018 at 2:44 pm
Hi,
I am trying to execute below script to find available backup files for automate restore, but always the error is "The referenced account is currently locked out and may not be logged on to.". But the account is fine and its not locked out.
Please suggest the workaround.
--script:
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @backupPath = '\\xxx\SQL Backups\xxx\xxx\FULL'
-- 3 - get list of files
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
select * FROM @fileList
PRINT @cmd
--===========================================
Result:
backupFile
The referenced account is currently locked out and may not be logged on to.
NULL
January 22, 2018 at 3:59 pm
Does the MS SQL Server Agent service account has rights on that shared folder?
January 22, 2018 at 5:40 pm
adisql - Monday, January 22, 2018 2:44 PMHi,I am trying to execute below script to find available backup files for automate restore, but always the error is "The referenced account is currently locked out and may not be logged on to.". But the account is fine and its not locked out.
Please suggest the workaround.--script:
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @backupPath = '\\xxx\SQL Backups\xxx\xxx\FULL'
-- 3 - get list of files
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmdselect * FROM @fileList
PRINT @cmd
--===========================================
Result:
backupFile
The referenced account is currently locked out and may not be logged on to.
NULL
Are you checking the account when you hit the error or after the fact? The account could be locked out when you get the error and if you are checking it later it may have unlocked depending on how the account lockout policy is set up at your company.
Sue
January 23, 2018 at 11:42 am
Evgeny Garaev - Monday, January 22, 2018 3:59 PMDoes the MS SQL Server Agent service account has rights on that shared folder?
yes MS SQL Server Agent service account has rights on that shared folder.
January 23, 2018 at 11:44 am
Sue_H - Monday, January 22, 2018 5:40 PMadisql - Monday, January 22, 2018 2:44 PMHi,I am trying to execute below script to find available backup files for automate restore, but always the error is "The referenced account is currently locked out and may not be logged on to.". But the account is fine and its not locked out.
Please suggest the workaround.--script:
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @backupPath = '\\xxx\SQL Backups\xxx\xxx\FULL'
-- 3 - get list of files
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmdselect * FROM @fileList
PRINT @cmd
--===========================================
Result:
backupFile
The referenced account is currently locked out and may not be logged on to.
NULLAre you checking the account when you hit the error or after the fact? The account could be locked out when you get the error and if you are checking it later it may have unlocked depending on how the account lockout policy is set up at your company.
Sue
yes i tried with windows and sqlserver accounts aswell.
Windows accounts never locked.
SQL server accounts also never locked except SA account.
January 23, 2018 at 12:02 pm
adisql - Tuesday, January 23, 2018 11:44 AMSue_H - Monday, January 22, 2018 5:40 PMAre you checking the account when you hit the error or after the fact? The account could be locked out when you get the error and if you are checking it later it may have unlocked depending on how the account lockout policy is set up at your company.
Sue
yes i tried with windows and sqlserver accounts aswell.
Windows accounts never locked.
SQL server accounts also never locked except SA account.
I was referring to the lockout in AD, not the status in SQL Server. AD has an account lockout duration and wouldn't know anything about a SQL login.
I'm fairly sure you won't get an error about an account being locked out when it is not locked out. You may want to proceed by looking at what can cause this instead of approaching it from the view that the account is not locked out. I would check the SQL Server and Agent logs, the security event logs on the server
Sue
January 23, 2018 at 12:16 pm
Sue_H - Tuesday, January 23, 2018 12:02 PMadisql - Tuesday, January 23, 2018 11:44 AMSue_H - Monday, January 22, 2018 5:40 PMAre you checking the account when you hit the error or after the fact? The account could be locked out when you get the error and if you are checking it later it may have unlocked depending on how the account lockout policy is set up at your company.
Sue
yes i tried with windows and sqlserver accounts aswell.
Windows accounts never locked.
SQL server accounts also never locked except SA account.I was referring to the lockout in AD, not the status in SQL Server. AD has an account lockout duration and wouldn't know anything about a SQL login.
I'm fairly sure you won't get an error about an account being locked out when it is not locked out. You may want to proceed by looking at what can cause this instead of approaching it from the view that the account is not locked out. I would check the SQL Server and Agent logs, the security event logs on the serverSue
yes I checked the SQL Server and Agent logs, the security event logs on the server. But there is no entry for these account lockedout.
January 23, 2018 at 1:38 pm
adisql - Tuesday, January 23, 2018 11:42 AMyes MS SQL Server Agent service account has rights on that shared folder.
Can you logon on the server with that account and execute the script manually? Does that work?
January 23, 2018 at 1:49 pm
Evgeny Garaev - Tuesday, January 23, 2018 1:38 PMadisql - Tuesday, January 23, 2018 11:42 AMyes MS SQL Server Agent service account has rights on that shared folder.Can you logon on the server with that account and execute the script manually? Does that work?
yes i trying in server itself . but the error is same.
January 23, 2018 at 3:05 pm
adisql - Tuesday, January 23, 2018 1:49 PMyes i trying in server itself . but the error is same.
Check the security log on the box in order to find the root of this problem.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply