SQL service agent Account

Question

SQL service agent Account

Viewing 15 posts - 31 through 45 (of 57 total)

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

sqlfriend SSC Guru Points: 52794 More actions · Answer 1

You are using configuration manager to change service account. But I'm asking when the firsttime you install it, is it a manual process?

For @SQLDCH answer about the group, he didn't make it clear if he sees the group NT SERVICE\SQLSERVERAGENT listed in a single group in windows local group, or he sees it is a member of SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER,

What I see is the latter.

Again what does that have relations to my domain account?

Any one can make the answer clearly and correctly?

Thanks

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

annasql (4/5/2011)
You are using configuration manager to change service account. But I'm asking when the firsttime you install it, is it a manual process?
For @SQLDCH answer about the group, he didn't make it clear if he sees the group NT SERVICE\SQLSERVERAGENT listed in a single group in windows local group, or he sees it is a member of SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER,
What I see is the latter.
Again what does that have relations to my domain account?
Any one can make the answer clearly and correctly?
Thanks

When you set the service account during the install - it is an automatic process - YOU NEED DO NOTHING MORE.

@SQLDCH is seeing the group NT SERVICE\SQLSERVERAGENT group inside the LOCAL Group called SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER.

And again the relation to the service account that is a domain account, you will NOT see it listed anywhere in those groups. YOU WILL ONLY see the SID for that account inside the group.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 3

How are you logged in now? Can you create the login under Security Logins?

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

Thanks.

"And again the relation to the service account that is a domain account, you will NOT see it listed anywhere in those groups. YOU WILL ONLY see the SID for that account inside the group."

All I can see in the group

SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER

is exactly like below:

NT SERVICE\SQLSERVERAGENT (S-1-5-80-344959196...).

So what 's the relationship between my domain account in my case is myorg\mysqlsvc and the NTservice account and its SID?

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

annasql (4/5/2011)
Thanks.
"And again the relation to the service account that is a domain account, you will NOT see it listed anywhere in those groups. YOU WILL ONLY see the SID for that account inside the group."
All I can see in the group
SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER
is exactly like below:
NT SERVICE\SQLSERVERAGENT (S-1-5-80-344959196...).
So what 's the relationship between my domain account in my case is myorg\mysqlsvc and the NTservice account and its SID?

The bolded section next to the NT Service is the SID.

Beyond that, you will not be able to see your service account from the local group nor from SQL Server.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52794 More actions · Answer 6

"The bolded section next to the NT Service is the SID.

Beyond that, you will not be able to see your service account from the local group nor from SQL Server. "

Thanks, I understand that string is SID, but it is SID of NT SERVICE\SQLSERVERAGENT, isn't it?

what has it to do with the sid of myorg\mysqlsvc which is used now as sql server agent account?

SQLRNNR SSC Guru Points: 281344 More actions · Answer 7

annasql (4/5/2011)
"The bolded section next to the NT Service is the SID.
Beyond that, you will not be able to see your service account from the local group nor from SQL Server. "
Thanks, I understand that string is SID, but it is SID of NT SERVICE\SQLSERVERAGENT, isn't it?
what has it to do with the sid of myorg\mysqlsvc which is used now as sql server agent account?

Then you should understand by now that you cannot see your userid from any screen to which you have access. Your user account is a part of that group and you can't do anything without browsing active directory.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52794 More actions · Answer 8

"Then you should understand by now that you cannot see your userid from any screen to which you have access. Your user account is a part of that group and you can't do anything without browsing active directory. "

Confused about above statement.

Do you mean the sid is not NT Service\SQLServerAgent's SID? It's the myorg\mysqlsvc's sid.

Thanks

TomThomson SSC Guru Points: 104768 More actions · Answer 9

If I understand this stuff correctly, installation will create three sql users: NT AUTHORITY\SYSTEM, NT SERVICE\MSSQLSERVER, and NT SERVICE\SQLSERVERAGENT all of whicy will be made sysadmins. These are individual users not groups. If you want to change either MSSQLSERVER or SQLSERVERAGENT so that the engine and the agent don't run under those accounts, first create the NT users you want to use and then use SQL Configuration Manager to change the system to use these users.

You may want to use the system security catalog view sys.server_role_members (and maybe alo sys.database_role_members) to see what roles exist and what their members are. Make sure the user you are logged into SQL as has VIEW DEFINITION permission at server level if you want useful results from sys.server_role_members (sys.database_role_members will work with that permission at the database level or at the server level).

Tom

sqlfriend SSC Guru Points: 52794 More actions · Answer 10

Thanks.

"If I understand this stuff correctly, installation will create three sql users: NT AUTHORITY\SYSTEM, NT SERVICE\MSSQLSERVER, and NT SERVICE\SQLSERVERAGENT all of whicy will be made sysadmins. "

Is it also a choice when you install you chose an domain account as service account?

sqlfriend SSC Guru Points: 52794 More actions · Answer 11

I did a check in AD, the SID of our domain account doesn't match the one in the group above mentioned.

So that didn't explain the relationship.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 12

annasql (4/6/2011)
I did a check in AD, the SID of our domain account doesn't match the one in the group above mentioned.
So that didn't explain the relationship.

While in AD did you bother to look in the group and find your service account?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52794 More actions · Answer 13

No, I had to ask our network people to do that. No access to AD. and I don't think the group NT SERVICE\SQLSERVERAGENT is in AD, it is a local account.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 14

Okay, when I first saw this message and thread - I started wondering about this myself. So, I looked around and figured out what is going on.

To explain, I will start on Windows Server 2003 first. When you install SQL Server 2005/2008 on that OS - there will be several local groups created (depends on what you install - and what version). Those groups will generally be something like:

SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER

This group will also be added to SQL Server and granted sysadmin rights. The service account that you are using will be added to this group - and that is how it will be granted the necessary ACL's and SQL Server permissions.

So far, so good...I think we all understand this.

Now, when you install on Windows Server 2008 - things change a bit. Well, actually they change a lot because on that OS we can now assign a SID to a service and grant permissions to that SID. That isolates the privileges required only to that service - and not necessarily to the service account that is running that service.

The same local groups are created - but, instead of adding the service account to the local group for ACL privileges the services SID is added, in this case the SID has a common name of NT SERVICE\SQLSERVERAGENT or NT SERVICE\MSSQLSERVER.

Additionally, instead of adding the local group to SQL Server and granting sysadmin rights to that group - the service SID is added and granted those rights.

There is no tie-in from the login used to run the service - and the service SID. The service itself is granted the permissions it needs to run (sysadmin in SQL Server).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sqlfriend SSC Guru Points: 52794 More actions · Answer 15

This makes a lot of sense.

I hope there is a microsoft article explains this in detail. Because we are planning upgrade all our sql 2005 to 2008 on windows 2008, this just caused a lot of confusion about the security.

So from what Jeff said, there is no tie-in from the login used to run the service and the service SID. So does that mean in this case, our domain account who is used to run the service account is NOT an admin, only the service itself is granted sysadmin, (I'm not sure how a service is granted sysadmin) but only know a login can be granted sysadmin.

So if we want that login to be a sysadmin, we have to explicitly add it to login list and grant it sysadmin, Correct?

Thanks