Oops! Deleted NT Authority\System SQL Account

[PTW]

The SQL account NT Authority\System was deleted by mistake. I re-created it, but I do not know the original password it was set-up with.

Under Current Activity I can see that there are still two processes running dated before the account was accidentally deleted--SQL Agent Alerter and SQL Agent Generic Refresher.

Aside from slapping myself aside the head for being so careless, is there anything I should be concerned about? Mostly I am worried about that password and what might happen if the Agent has to be restarted. Thanks.

 

[SQLBill]

What are you talking about? In SQL Server there isn't a login with that name. There IS a BUILTIN/Administrator login.

-SQLBill

Posting advice: FAQ481-4875

 

[PTW]

Hmm...I inherited these databases and they all had a NT Authority\System login in place for running the Agents/Services. Is that not a standard Windows security login to do this? Should I only be using the BUILTIN/Administrator login to do this?

I was curious as to why there were both setup, as it seemed redundant.

 

[MDXer]

If you are talking about running the services under a Domain account then you can recreate the account and reconfig the password. I would then look and see if SQL Server has a User for this account, if so you will need to delete and recreate the user as SQL Utilizes the NT SID which will change when the account is recreated. Once the account is back in place things should start running again.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Catadmin]

Is that not a standard Windows security login to do this? Should I only be using the BUILTIN/Administrator login to do this?

There actually is no standard Windows login for SQL Server. They are all made up individually based on the original installer's needs. Some people don't even map Windows domain accounts to the SQL admin or service accounts. Usually, you'll find that in a SOHO (small office / home office). Domain accounts for SQL Services are more common, but no means necessary, in larger environments.

As MDXer said, you'll need to remap the SQL Server and SQL Server Agent services to the new domain accounts because they will still be looking for the old ones. Be aware that any replication you have established, as well as jobs, are probably broken now that you've deleted the account. The reason for this is the unique identifier attached to every object in Active Directory called a Security Identifier (often referred to as a SID or SPid). Even though you have a domain login which has the same name, in order for AD to tell it apart from all other objects, it's assigned the new login a different SPid which means nothing is connected to it right now.

Go through SQL Server carefully, make sure to remap everything, and then check it regularly for the next couple of weeks to make sure you haven't inadvertantly forgotten anything. Also, watch your SQL Server/SQL Server Agent and Event Viewer logs which will tell you if something is broken even if you don't see it in the Enterprise Manager interface.

Last, check the Services on the OS to verify you've remapped all your SQL services and that there isn't another service dependent on the deleted NT login (there should NOT be, but it never hurts to check).



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Catadmin]

Domain accounts for SQL Services are more common, but no means necessary, in larger environments.

GAAAK! Let me rephrase that before someone bonks me over the head with a Pocky stick. @=) Domain accounts aren't necessary if your SQL Server only ever talks to itself and no other database tools. I.E., you don't use Log Shipping, Replication or hook up to other SQL/Oracle/Etc. servers.

They are good security tools, though. Google "SQL Server SA Account" and you'll come up with all sorts of interesting pages on the pros and cons of using the Local builtin Admin account verses a Domain Account.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

In SQL you can manually add the System Authority into the SQL Server so that things like Full Text indexing will run even after you remove the BUILTIN\Administrators login rights.

The code to put it back is:

EXEC sp_grantlogin 'NT Authority\System'
go
EXEC sp_addsrvrolemember 'NT Authority\System', 'sysadmin'
go

This can not be done via Enterprise Manager, it must be done via Query Analyser or another command line tool. If the System Authority doesn't need sysadmin rights remove the second command.

Once the account is granted it's login back in to the database you can manage it's permissions via Enterprise Manager.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)