May 26, 2011 at 8:30 am
A Service called 'Test Service' with (SVCTest as sid) is running on Server1 and is running as ‘local system account’. This service sid has been added to SQL Server on Server1 as “NT Service\SVCTest” with required access on SQL server. When an attempt is made to run a script via remote web based application interface, the script is designed to use the 'Test Service' to execute the task on Server1 fails due to a logon failure “unknown user name/anonymous login”. However, If 'Test Service' is run under a domain account (and if the domain account, not service sid, is added in SQL Server with required access), it works fine. The requirement is to grant minimal access to the 'Test Service'.
So the question is if this is even acheivable? The answer in the case of SQL Agent (NT Service\SVCAgent -default sql server sid for sql server agent) works.
May 26, 2011 at 9:04 am
I think you might be misunderstanding the concept of least privilege. Using a domain account which is just a user is perfectly fine. The problem you are describing (provided I read it right) is that you are trying to do a trusted connection to SQL from another box but use that other boxes local users. That isn't going to happen..
My recommendation:
1. Set the service to run as a domain account, this domain account will not belong to any domain level groups EXCEPT: Domain Users
2. On the machine that the service runs on that domain account will NOT be added to the local Administrators group. It will likely be granted the Log on as a service right.
Trusted connectivity requires a common trust element, in this case the domain. Think of it this way, if you just met someone you don't trust them, however, if a mutual friend setup the meeting you have a certain level of trust.. The domain is that friend..
Clear?
CEWII
May 26, 2011 at 9:24 am
I think Elliot has good advice here. I'm not sure what happens here with the Local System account. Typically you run that for various services, but not necessarily those that connect to SQL Server.
How did you add this to SQL Server? Do you have the code/steps?
May 26, 2011 at 9:44 am
Local users and local system have no security context outside of the local server (local, get it). Therefore when trying to connect to resources on other machines they can't provide a security context.
CEWII
May 26, 2011 at 10:07 am
I think that the OP said both SQL and the custom service are on the same machine (Server1) in this case. Your point is valid, and since services can move, it's why I'd recommend a domain user account as well.
May 26, 2011 at 10:44 am
I didn't get that from what he wrote Steve, could be...
CEWII
May 26, 2011 at 12:35 pm
"that you are trying to do a trusted connection to SQL from another box but use that other boxes local users."
No, the "Test Service" is on the same Server1 which has SQL Server as well.
Please ignore the last sentence "The requirement is to grant minimal access to the 'Test Service'."
I dont know if my example was clear enough!!
if you double click any service in the services.msc, Every Service has a Service Name, that was added to SQL Server using the normal 'add login' method in SSMS (the login was added as "NT Service\<ServiceName>").
FAILed:
A service was set to run under "local system account".
That service was fired (on Server1) via a script, to execute a task on SQL Server (on Server1) from Remote web app. prior to that, the "Service Name" was added to SQL Server by the above method. The script failed due to a logon failure.
Success:
A service was set to run under "domain account".
same method to fire the script on Server1 to execute a task on SQL Server on server1 from remote web app. prior to execute, the above 'domain account' was added to SQL server - this works.
Goal is to acheive this with local system account to avoid using a domain account, reasons are many, as to pwd policy, pwd in script, and other limitations...etc...
Thanks for your responses.
i have an attachment, see if you can access it. I know you guys already got what i am trying to acheive, but, sometimes it helps.
Zak
May 26, 2011 at 2:10 pm
you don't add the service name. You add the account under which the service runs. Those are separate things.
The service name is just meta data, like the full name for a user account. It has no security.
May 26, 2011 at 2:19 pm
Ok,
Can you tell me where you found information that said you could use NT SERVICE\Service Name to grant access to SQL, I haven't seen that before and my google search didn't immediately show it either.
While it is desireable to not have to worry about passwords I'm thinking you aren't going to get a choice. I did want to note, that if you use trusted security AND you log your service in as a domain account the password will only be contained in the service credentials, the script should never even need to know about the login credentials to the server.
As I see the question, "Is there a way to allow a service running as 'local system' to login as trusted to a local SQL Server?"
I found this article that kind of covers the rights that local system has:
http://msdn.microsoft.com/en-us/library/ms684190(v=vs.85).aspx
And this article that covers the differences between local system, local service, and network service:
http://msdn.microsoft.com/en-us/library/ms143691.aspx
I think all this comes down to the built-in accounts do not have identities that can be granted rights directly. Add a domain user with an insanely painful password and exempt it from the password change requirement. However, STILL change it periodically, 6-12 months should be fine.
Sorry, it may not be what you want, but I don't see any way around it.
CEWII
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply