This
is one of the critical and bad situations that any DBA can face, to find
yourself unable to start your SQL instance after disabling NT AUTHORITY login
“for any reason”.
In
my case it was disabled by the Network administrator guy by mistake and I did
not realize that till restarting my SQL production instance for a planned
maintenance operation!!
So,
the situation is very critical that you find your production instance failed to
be started with error message:
“Login failed for user 'NT AUTHORITY\SYSTEM'.
Reason: The account is disabled”.
The first positive point here is that the
error message is clear enough and then we can start troubleshooting in the
right track.
What
we need to do in this situation is simply grant “sysadmin” permission for NT
AUTHORITY system account again but the question is how can we connect to the
instance while it is stopped?, the answer is to start it in single user mode
and to do that we need to go through the following steps:
1- Open command prompt in administrator mode.
2- Navigate in SQL Server installation media till “Binn” folder.
3- Start SQL Server DB Engine service in single user mode using
command “sqlservr.exe -s SQL2012 –m” while SQL2012 is the instance name, here
is a complete example:
“C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -s SQL2012 –m”
while "–s SQL2012" is the instance name and "–m" is the option for starting the instance in single
user mode.
The
next step then is to grant the NT AUTHORITY system user "sysadmin" permission and to do that we need
to connect to the instance after starting it in single user mode via command
prompt, the command to do that is as below:
SQL
Authentication:
SQLCMD -S instance IP,port -U SQL account -P password
ALTER LOGIN [NT AUTHORITY\SYSTEM] ENABLE
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
Windows
Authentication:
SQLCMD -S instance IP,port –E
ALTER LOGIN [NT AUTHORITY\SYSTEM] ENABLE
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
After
that we can close the two opened command prompt and then restarting SQL Server
instance in the normal mode.
Extremely useful information which you have shared here. This is a great way to enhance knowledge for us, and also beneficial for us. Thank you for sharing an article like this.
ReplyDeleteSQL Server Load Soap Api