Question

Authenticate to SQL Server with domain computer's local system account

Asked by: titansfan20

Using Group Policy, I have assigned a shutdown script to domain computers, that will let me know when computers are off or on, and if they need Windows Updates, so I can use WoL to power up the machines at night and install the patches, then shut them back down.

The VBScript checks the status of updates using a registry key, and if it has updates waiting to install, it connects to an SQL database (SQL Server 2000) and inserts a record. Later, a script will use the record in that database to wake up machines.

The VBScript works great as long as I run it as a domain user account (using Integrated Security=SSPI;Persist Security Info=True in the connection string). However, when I assign it to run as a Shutdown Script via Group Policy, I get an access denied error:

Error: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Computer Shutdown Scripts run as the LocalSystem account. I already knew this - I had to grant "Domain Computers" permissions to the share where the scripts are located so they could run them.

On my SQL server, I added the "Domain Computers" group and gave it permissions to write to the database, but adding the group to SQL Server this way doesn't seem to work like it does with normal NTFS file shares.

Does anyone know a way to allow domain computer accounts to authenticate with SQL? I need these to run as shutdown scripts - not log off scripts - and I REALLY don't want to put any domain credentials in the scripts in plain text.

Thanks in advance

(My vbscript code is below, in case it helps)

'Option Explicit
'On Error Resume Next
 
Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20
 
'Database connection string for SystemsInventory
Const strConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SystemsInventory;Data Source=NAS-LCLSYDB01"
'Create database connection
set adbConn = CreateObject("ADODB.Connection")
'Create Connection to the Record Set
set adbRS = CreateObject("ADODB.RecordSet")
 
'Collect and store the following info for inventory:
 
'ComputerName
set WshNetwork = WScript.CreateObject("WScript.Network")
strComputerName = WshNetwork.ComputerName
 
'IP and MAC Address
Set objWMIService = GetObject("winmgmts:\\" & strComputerName & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True", "WQL", _
                                          wbemFlagReturnImmediately + wbemFlagForwardOnly)
For Each objItem In colItems
	if not IsNull(objItem.IPAddress) Then
		strIPAddress = Join(objItem.IPAddress, ",")
		strNetwork = Left(strIPAddress,5)
		if strNetwork = "10.13" Then
			strRealIP = strIPAddress
			strMacAddress = objItem.MACAddress
		end if
	end if
Next
 
'Time of Shutdown
strShutDnTime = Date & " " & Time
 
'Open the Database Connection
adbConn.open strConnect
 
'Open the Record Set for modification
adbRS.Open "tbl_WoLInfo",adbConn,2,2
 
'Prepare records to insert
adbRS.AddNew
adbRS("ComputerName") = strComputerName
adbRS("ShutDownTime") = strShutDnTime
adbRS("IPAddress") = strRealIP
adbRS("MacAddress") = strMacAddress
 
 
'Insert records
adbRS.Update
 
'Close the Connections
adbRS.Close
adbConn.Close

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:

Select all Open in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation
Collaborate with the world's top tech experts
Unlimited access to our exclusive solution database
Never be left without tech help again

Subscribe Now

Asked On: 2009-04-03 at 11:12:46ID24293266
Tags: SQL
,; VBScript
,; NT Authentication
Topics: MS SQL Server
,; VB Script
,; Microsoft Development
,; Active Directory
Participating Experts: 3
Points: 0
Comments: 34

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Free Tech Articles

WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Watch More

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

by: NightmanPosted on 2009-04-03 at 11:55:37ID: 24063106

The reason is that LocalSystem is local to the machine on which it is running, and not a domain account. You either need to run it as a domain account (e.g. create a service account specifically for this purpose) or connect as a SQL user.

To do this, you would need to create an SQL login on the server, give it access to the database, and then persist the login credentials in your connection string:

Provider=SQLOLEDB.1;Data Source=NAS-LCLSYDB01;Initial Catalog=SystemsInventory;User Id=myUsername;Password=myPassword;

by: titansfan20Posted on 2009-04-03 at 12:06:37ID: 24063185

LocalSystem is the Computer account itself. Every computer on the domain has an account. My testing machine is named NAS-CR4737 -- it's domain account name is NAS-CR4737$.

If you grant the "Domain Computers" group permissions to files on a server, then my VBScript -- running as the LocalSystem account -- can access them just fine. It authenticates as "[DOMAIN]\NAS-CR4737$"
The computer accounts even have their own passwords that they automatically renew every 30 days. So the LocalSystem account is perfectly capable of authenticating across the network -- SQL seems to be the only thing that doesn't recognize it.

I prefer not to store a username and password in clear text in my script if I can help it - but I do realize that's an option.

Thanks

by: NightmanPosted on 2009-04-03 at 12:40:04ID: 24063456

I have never connected to a remote SQL Server using a LocalSystem account ... perhaps this is possible, but I don't know how. If the account is a domain account and visible to SQL Server, you should be able to create the login on SQL Server as well (note: while the login might be a valid domain account, it still has to be created on the SQL Server instance to allow access).

Alternatively, your options are as follows:

1. Connect using an SQL Login.
Perhaps VBS can support encryption/decryption much the way that .NET handles it. I've never tried, but this would allow you to store the credentials in an encrypted format.

2. Run the service as a regular domain account and grant it access on SQL Server.
I don't know if this is possible, or even practical - Group Policy is not my forté.

3. Create a SystemDSN and store the credentials there
Connect using the ODBC drivers and the system DSN

by: acperkinsPosted on 2009-04-03 at 19:11:23ID: 24065641

Nightman is of course correct. You cannot use a Local System account to access resource beyond the current server. I suspect that is why they call it Local. :)

by: titansfan20Posted on 2009-04-04 at 14:04:24ID: 24069014

I'm sorry, that is incorrect. As I said in my initial post, I am already doing exactly that (and have been for quite a while) - and the functionality is well-documented. SQL is the only thing that is having an issue with it.

If you would like more information on the matter, here is one description of the functionality on TechNet, just from a quick Google ( http://technet.microsoft.com/en-us/library/cc778824.aspx ):

##############################################################
System (or LocalSystem):

An identity that is used locally by the operating system and by services configured to log on as LocalSystem.

System is a hidden member of Administrators. That is, any process running as System has the SID for the built-in Administrators group in its access token.

When a process that is running locally as System accesses network resources, it does so by using the computer's domain identity. Its access token on the remote computer includes the SID for the local computer's domain account plus SIDs for security groups that the computer is a member of, such as Domain Computers and Authenticated Users.
##############################################################

Again, I appreciate any help.

by: titansfan20Posted on 2009-04-04 at 14:13:24ID: 24069035

Nightman, in response to your last post...

Thanks for the reply. BTW, I have already created an SQL Login to use for right now, so I can finish my scripts and verify the functionality. But I am hoping that I can find a way to resolve this, since I currently have the SQL credentials in clear-text in the script, which I would very much like to remedy.

Using encryption is a good idea - I will certainly see if I can find more information on doing this, if it's possible.

Unfortunately, this script is running as a shutdown script on 700+ PCs, so using ODBC/DSNs isn't practical.

Also, I'm still fairly new at posting questions here, and I only selected one Zone when I created this. Is there a chance someone could cross-post it in a Zone for VBScripting and/or network administration as well?

Thanks for the help.

by: acperkinsPosted on 2009-04-04 at 14:19:08ID: 24069059

>>SQL is the only thing that is having an issue with it.<<
I am afraid that is not true, at least is not strictly true. It is not only SQL Server but any function that makes use of a Windows service that is setup to use the Local System account.

by: acperkinsPosted on 2009-04-04 at 14:24:33ID: 24069081

But don't take our word for it, log on to the server as the (local) Adminstrator (and not the Domain Administrator) and you will get the same result. i.e. You will not be able to access any resources beyond the server. Period.

by: acperkinsPosted on 2009-04-04 at 14:25:40ID: 24069084

>>Also, I'm still fairly new at posting questions here, and I only selected one Zone when I created this. Is there a chance someone could cross-post it in a Zone for VBScripting and/or network administration as well?<<
Click on the Request Attention link and ask a Moderator to take care of this.

by: titansfan20Posted on 2009-04-05 at 01:07:03ID: 24070566

ac, I do not understand why we are laboring over this point, when a quick google shows that it is possible (and I went to the trouble to find documentation for it, posted the link and pasted the actual text from MS's website)?

If the Local System account cannot access network resources, then how is it running my script that is sitting on a network server?

Again, the long and short of the issue I am looking at:

1) I add the "MyDomain\Domain Computers" group to have permissions to files on a server, and this script can access & modify those files, running as the client computer's Local System account.
2) I create an SQL Login (non-domain), and put that in my script for its authentication, and the script works fine and creates records in the database.
3) I create a domain SQL Login for "MyDomain\Domain Computers" and give it the same permissions as the above SQL account, but I receive the error in my original post.

The "MyDomain\Domain Computers" security group functions as it is supposed to when using it to access Windows file shares on domain servers, but it does not function the same way on SQL Server.

Maybe it is a bug in SQL - or maybe it is by design? I don't know. But I was hoping that maybe there is just a COM component or something similar that I need to grant "Domain Computers" access to, and if so was hoping someone here could point me in the right direction.

by: itsmeandnobodyelsePosted on 2009-04-05 at 05:41:23ID: 24071286

>>>> If the Local System account cannot access network resources, then how is it running my script that is sitting on a network server?

You are mixing things up. Please refer to your initial question where you told

>>>> The VBScript works great as long as I run it as a domain user account

That is where it worked and if you run it without an active domain account it doesn't work. If so, it actually doesn't matter whether it was because of missing credentials or because of LocalSystem account was used which has no network access ( I personally think both is true).

To solve the issue yoe either could write a little service which was logged on with a valid domain account or - maybe easier - you use the 'at' service to invoke your script. Open an explorer and navigate to folder Windows\Tasks. There you could open a new task which was was invoked at shuttdown and where you can add the credentials of the account it should run under.

by: titansfan20Posted on 2009-04-05 at 08:09:05ID: 24071695

I apologize for any confusion, but I tried to clarify in my last post to avoid any more.

If I comment out the database parts of the script, it runs perfectly no matter what account I use - it can even read & write files on another server running as the System account.

When I shut down the client PC, it goes out to my server (as the PC's Local System account), and runs its shutdown script which resides on that server. This is because I granted the "Domain Computers" group permissions to read/execute in that directory.

This behavior can all be easily reproduced if you have the ability to create Group Policy objects in your organization. Just create a new test policy and a test container to try it on, and put a test PC in the container. Put my script on a server, and add the script to "Shutdown Scripts" in the Computer Configuration of GP (use the full UNC path to the script). Restart the computer so it picks up the GP changes.

The next time you shut down/restart the PC, the script will fail and you will receive an error, because the Local System account does not have permissions to that share/directory. NOW, go to that directory (where the script resides), and add the "Domain Computers" group to the permissions list for it, and grant this group at least Read and Execute permissions.

The next time the PC shuts down, the script will run without any errors (if you have commented out the SQL parts).

I do not know how to make it any plainer than this, but if my issue is still confusing, please ask and I will attempt to clarify.

Thanks again for any help
(and thanks Matt, for adding it to the other Zones)

by: acperkinsPosted on 2009-04-05 at 09:33:29ID: 24071966

>>Maybe it is a bug in SQL - or maybe it is by design? <<
This is not a bug in SQL Server. As I stated in my previous post, this is how all Windows service work, so you could say this is by design.

by: titansfan20Posted on 2009-04-05 at 09:50:14ID: 24072028

With all due respect, if at this point you still refuse to recognize that the Local System account can access network resources, then please do not bother replying, as it is not helpful.

I do not say this with any attitude -- I just prefer to avoid arguments, and would rather get to the bottom of the problem at hand.

Thanks

by: acperkinsPosted on 2009-04-05 at 13:57:44ID: 24072922

>>you still refuse to recognize that the Local System account can access network resources<<
That is exactly my point, by definition it cannot as stated in the link you posted and that everyone has been trying to tell you.

>>please do not bother replying, as it is not helpful.<<
Fair enough.

I wish you the very best of luck.

by: itsmeandnobodyelsePosted on 2009-04-05 at 23:18:06ID: 24074695

>>>> When I shut down the client PC, it goes out to my server (as the PC's Local System account),
No. It was still able to use the credentials of the last user logged-in.

LocalSystem comes into play when there is a service started prior to any user log-in, e. g. your client service to SQLServer DBMS.

by: titansfan20Posted on 2009-04-06 at 05:29:11ID: 24076592

*sigh*

PLEASE take the time to at least do a quick google before telling me I don't know what I'm talking about.

http://www.google.com/search?hl=en&q=%22group+policy%22+shutdown+script+%22domain+computers+group%22&btnG=Search

Seriously. I've posted a technet article, and even typed up instructions on how you can reproduce this functionality and verify for yourself that it actually works. Have you tried it, to verify your info?

This is the way computer startup and shutdown scripts are DESIGNED to work. If the Local System account couldn't access network resources, these scripts would be almost useless -- at the very least they couldn't be centrally managed.
In fact, one of the google results above is a Microsoft KB article instructing people to use this functionality, so computer startup scripts can write log files to a central server -- AS THE LOCAL SYSTEM ACCOUNT.

Do some research guys. I'm not just making this stuff up.

by: itsmeandnobodyelsePosted on 2009-04-06 at 06:46:04ID: 24077206

>>>> If the Local System account couldn't access network resources, these scripts would be almost useless

You are still making assumptions on Local System account which have no foundation.

Why do you mean that a shutdown script was based on Local System account when it was executed?

If you were actively shutting down the system it could operate on all known network paths already registered in registry *and* to the credentials of the last domain user logged-in. What has that ability to do with Local System Account which is a last resort account for a single workstation and is default account of services started from local system *prior to* any user-login.

The fact that a service started with Local System Account cannot access network paths using these credentials doesn't mean that an application invoked by an active user cannot use that service for operations on the network. But it has to pass its own credentials to the service in order to overcome the restrictions of the Local System Account.

>>>> Do some research guys. I'm not just making this stuff up.

I repeated your google search and didn't find any confirmation of your thesises. But even if there would be one that makes the same statement as you, do you think the expertise of these people is necessarily higher than that of EE experts? If so, you have chosen the wrong forum.

Note, I made a lot of projects with client-server architecture and integrated system services in the last 15 years. All services run with a (own) domain account because of making them able to connect to server DBMS. And I have tried it for DB2, ORACLE and SQL Server to do it with Local System Account (cause it is not easy to get an own Domain Account for such purposes from Domain administration) but it never worked.

Did you try my suggestion to add a task to the Task scheduler? Here you can add - in an absolute safe way - your login data, so that the task is running with that account.

by: titansfan20Posted on 2009-04-06 at 07:48:14ID: 24077797

>>>>You are still making assumptions on Local System account which have no foundation.

Why do you mean that a shutdown script was based on Local System account when it was executed?<<<<

Because Microsoft says so:
http://support.microsoft.com/kb/322241

Quote: "NOTE: Shutdown scripts are run on the Local System account."

>>>>I repeated your google search and didn't find any confirmation of your thesises. But even if there would be one that makes the same statement as you, do you think the expertise of these people is necessarily higher than that of EE experts? If so, you have chosen the wrong forum.<<<<

The word is "theses," and it's got nothing to do with whose expertise is higher. As I have said multiple times, I know this works because I am already doing it in several cases on my network, and have been for a while. I am simply providing documentation here so you guys will stop arguing with me and telling me that something I am already doing cannot be done. Maybe then someone will see their way to actually trying to research my issue. Sadly, I am beginning to become skeptical that this is going to happen.

>>>>Note, I made a lot of projects with client-server architecture and integrated system services in the last 15 years. All services run with a (own) domain account because of making them able to connect to server DBMS. And I have tried it for DB2, ORACLE and SQL Server to do it with Local System Account (cause it is not easy to get an own Domain Account for such purposes from Domain administration) but it never worked.<<<<

I've got an application running as a service on a dedicated computer right now, and the service logs on as the local System account. It writes log files to a central server hundreds of times a day, and the box it runs on is hardly ever logged into by any user. How does that work?

BUT, you do get right to the heart of my issue with that statement: namely, that it doesn't seem to work with SQL, even though it does with other network resources. That's why I sent this to the database Zone initially. I was hoping that an Expert in SQL Server might be able to help me figure out a way to make it work.

>>>>Did you try my suggestion to add a task to the Task scheduler? Here you can add - in an absolute safe way - your login data, so that the task is running with that account.<<<<

As I stated a few times earlier, this isn't a single task - it runs on every PC on the network - so creating a scheduled task on 700-some-odd computers not only isn't practical, it would also defeat the purpose of what I'm trying to do. I want to log when a computer turns off with updates pending, so I can turn it back on when the updates are scheduled to install. That can only be done with a Shutdown Script.

by: itsmeandnobodyelsePosted on 2009-04-06 at 09:20:29ID: 24078899

>>>> Quote: "NOTE: Shutdown scripts are run on the Local System account."

That is the Local System Account of the Server not of the client.

by: itsmeandnobodyelsePosted on 2009-04-06 at 09:22:38ID: 24078920

Look below at the article and you see

- APPLIES TO
Microsoft Windows 2000 Server
Microsoft Windows 2000 Advanced Server

by: itsmeandnobodyelsePosted on 2009-04-06 at 10:10:37ID: 24079397

>>>> "NOTE: Shutdown scripts are run on the Local System account."

I reread the article a few times and probably it is the client PC where the script runs on the Local System account.

Neverthelesss that isn't a contradiction to the statements made regarding the restrictions of that account.

The domain server which invokes the scripts doesn't run on Local System Account and is able to establish connections using the credentials stored in the domain database. So, when using group policies to invoke scripts the missing network capability of the client isn't a problem regarding the script itself cause all that was invoked by the server which of course has a network connection to each client PC. But if the script tries to use any protected resources - like the network paths defined at the client pc - it needs an account where the rights regarding that resource could be checked against. Local System Account has all rights at the local system but not beyond. So access to a share or to a non-local DBMS must fail because the security system (at the client) will not allow it. You can overcome that at the client side by using a service which uses or creates a valid account as it was with the Windows Task service. Or use your own service which was started with a valid account. Such a service would be able to connect to the DBMS and Local System Account is sufficient to communicate with such a service.

by: titansfan20Posted on 2009-04-06 at 11:33:22ID: 24080271

I'm not looking forward to explaining all this, but here goes......

A Windows domain has both User account objects and Computer account objects. For the practical purposes of what we are discussing here, they are essentially the same thing.
User accounts are associated with either humans, or with processes that need access to resources on the network.
Computer accounts are associated with either computers, or with processes that need access to resources on the network.

Humans know the password to their user account; this is how they verify who they are. Computers know the password to their computer account; this is how they verify who they are. (Computer account passwords have to be changed every 30 days by default, by the way - the computers do this automatically if they are on a domain.)

When a user or computer logs in to the domain with its password, it is granted an access token. This token contains the SIDs for every group that it is a member of.

So, if a user who is in the Domain Administrators group logs in to the domain, that group's SID (Security Identifier) is in his access token (he keeps this same token until he logs off, and gets a new one when he logs on again. This is why, if someone changes your permissions by adding you to a group, you usually have to log off and log back in before the changes take effect). By default, all user accounts are members of the "Domain Users" group, so they all have that SID in their token when they login. Therefore, if you grant the "Domain Users" group permissions to a network resource, then any user with an account in that domain will be able to access it.

Likewise, if a computer that is in the Domain Administrators group logs in to the domain (yes, you can put computer accounts in groups), then it will have that group's SID in its token as well. By default, all computer accounts are members of the "Domain Computers" group. Therefore, if you grant the "Domain Computers" group permissions to a network resource, then any computer with an account in that domain will be able to access it.

Fair enough. Here is the part that seems to be confusing some people.
As stated in the Technet article I posted earlier, the Local System account on a computer is different from other user accounts that exist on machines. First, it is a hidden member of that computer's Administrators group, meaning that it has full administrative rights on that computer. Second, when it access network resources, it accesses them under the guise of the domain account of that computer. When it does this, it has the full rights that the computer account has on the network -- that is, the SIDs of whatever groups the computer account is a member of are in its access token on the network. Therefore, if you grant the "Domain Computers" group Modify permissions to a share on the network, then any computer that is a member of the domain can create files on that share -- this also means that any service or application running under any domain computer's "Local System" account can also create files on that share.

I make use of this functionality regularly, so I am familiar with how it works.

Additionally, both the "Domain Users" and "Domain Computers" groups are in the BUILT-IN "Authenticated Users" group on the domain. Since the "Authenticated Users" group has read and execute permissions to the NETLOGON folder on domain controllers, that means that both User Accounts and Computer Accounts can access and run scripts from that location. Because of this, Logoff, Logon, Startup and Shutdown scripts all work without any modifications if they are placed in the NETLOGON directory (which is the default place for them).

I opted to place my scripts on a separate server, so in order for my PCs to access and run the scripts, I had to grant the "Domain Computers" group read and execute permissions to that folder. Now the Startup and Shutdown scripts run successfully (as long as I don't have any problems in my code, of course).

Computer Startup and Shutdown scripts are different from User Logon and Logoff scripts, because they run before the user logs on to the computer and after the user logs off the computer, respectively. Since this is the case, Microsoft designed them to be launched by the Local System account, so they could still have access to resources on the domain (namely so they would be able to access the scripts they were designed to run).

So when a Startup script is applied via Group Policy, when a PC that is a member of the domain boots up -- but before the "Press CTRL+ALT+DEL" screen comes up -- you see the message "Running Startup scripts." The Shutdown scripts are the same, except they run on shutdown/restart (not log off), and you see the "Running Shutdown scripts" message after the user is completely logged out.

When this happens, if you have security logging enabled on your file server where the scripts reside, you can view the logs, and you can see that the computer connected to the server with its domain computer account (NAS-CR4737$ in my case).

This is as expected. The Local System account is launching the script, and in order to access the script it is using the domain account of the computer that it is running on.

So, granting the "Domain Computers" group the ability to read/write to a share works fine, and enables the local system to access network resources.

This is the part that is throwing me on SQL:

When I add code in this script that tells the computer to connect to the database, the connection string tells it to connect with whatever domain account it is currently logged in as. Since it is running as Local System, this should mean that it connects as NAS-CR4737$, just like it does on my file server.

But for some reason, when I look at the logs on my SQL server, I see that the computer is connecting as "ANONYMOUS LOGON" instead of connecting as its computer account.

The question is, if it connects to a file share and authenticates as its domain computer account -- as it is supposed to -- then why does SQL server not recognize the domain computer account the same way?

by: itsmeandnobodyelsePosted on 2009-04-06 at 14:05:30ID: 24081715

>>>> Second, when it access network resources, it accesses them under the guise of the domain account of that computer.

Do you have any evidence for that beside a strong belief?

Your thesis stands and falls with that.

Why should a Local System Account change to NAS-CR4737$ when there is no settings which states to do so?

You should consider that the Local System Account has much more rights at the local system than NAS-CR4737$ (probably).

Also for security reasons local rights never could overrule domain rights.

by: NightmanPosted on 2009-04-07 at 07:06:00ID: 24087467

I've been away for a bit, and haven't been able to look at this. After spending quite a bit of time trying to find a solution to allow your Local System account to access SQL Server - nothing so far. So while it might indeed be possible (and your research indicates that it could be), I have no solution for you as yet.

HOWEVER, there is something else that you need to consider in terms of security:

You are using ADO to connect directly to the base table on the database - this is risky, as you are essentially providing open access to all of these PCs to access your SQL Server.

1. Create a stored procedure on the database that handles the insert.
2. Create an SQL user account that has permission to execute that stored procedure AND NOTHING ELSE - no access to the base tables, etc ...
3. Change your script to login with that specific account and execute the stored procedure, passing the computer name / shutdown time parameters.

While the password may be stored in clear text, the account does not have access to do ANYTHING other than that single stored procedure, so you are mitigating the risk of storing it by controlling the access at a more granular level on your server.

Hope this helps

by: titansfan20Posted on 2009-04-07 at 12:10:32ID: 24090765

I got it to work. The troubleshooting was quite involved, but now the Local System account on my test PC is inserting records into my database table using integrated security (no user/pw stored in the script).

When I have time -- maybe this afternoon or tomorrow -- I'll come back and explain what I had to do.

Nightman: That's actually an excellent idea. If I hadn't already spent so much time and energy on this, I would probably do it that way.

You've attempted to be very helpful in this thread from the beginning -- you'll certainly get my points when I close it.

by: titansfan20Posted on 2009-04-07 at 12:13:07ID: 24090793

Itsmeandnobodyelse wrote:
>>>>Do you have any evidence for that beside a strong belief?<<<<

Of course I do. I'm not a religious person, especially when it comes to machines.

In fact, I posted the evidence for it earlier in the thread, from the TechNet article.

by: NightmanPosted on 2009-04-07 at 12:16:33ID: 24090831

How did you get it to work? I would like to see the details for future reference.

by: titansfan20Posted on 2009-04-07 at 14:53:12ID: 24092354

Okay, here goes...

The problem turned out to be not so much an issue with SQL, but a few different factors that came into play. Let's just say that I got to learn a lot more about Kerberos than I had intended to.

After my last post (novel), I tried something that I hadn't already: using my script to copy a file to a share on the SQL server. It failed. So SQL was out of the equation for now - I had to figure out why my script would run and copy files to one of my servers, but not to this one.

When the file copy failed, the Security log on my server was showing that it had logged in as Anonymous. Since Anonymous didn't have permissions to write to the share, it was getting access denied. So why was it logging in as Anonymous to begin with, since it was logging in as NAS-CR4737$ on the other server? I noticed in the details of the event that when it was logging in as Anonymous, it was using the NTLM process instead of Kerberos. This was definitely odd, since everything should be using Kerberos to authenticate on the domain.

After a lot of research on Kerberos issues, I enabled Kerberos logging on my server ("HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters" - add a REG_DWORD value named LogLevel, and the data is "1"). This eventually started showing me some Kerberos errors, which confirmed my suspicion - NTLM was being used as a fallback because Kerberos wasn't working.

The first error I got showed as "KDC_ERR_BADOPTION" in the System log. After running a network capture on the client and the server, analyzing the packets in WireShark, and a little Googling, I found that this was being caused by DNS issues. Without going into too much detail, we have two DNS domains in my organization, and so we have two DNS Search Suffixes in the TCP/IP properties on all of our computers, so they look for computer names in both places. Since the DNS domain the server was on (along with nearly all our machines) was at the bottom of the search list, the DNS queries being returned by name lookups did not match the actual FQDN of the server in Active Directory.

I fixed this by switching order of the DNS Suffix Search entries, putting the AD Domain at the top of the list. That fixed it - my script copied files to the server's share and authenticated as NAS-CR4737$ without any errors. Good times.

So I uncommented the SQL code out of my script, ran it, and now I got a different error. =\

Now I was getting an error that said "Cannot generate SSPI context."

After more research and more packet sniffing, this turned out to be an SQL/Active Directory issue. When you authenticate with Kerberos, the client requests a ticket from the Domain Controller (DC), and the server requests one as well. If the two tickets don't match, you don't get to log in. The Kerberos ticket is generated (in part) based on the Service Principal Name (SPN) that you are trying to access. The SPN is an object property in Active Directory that consists of the name of the service you are using (MSSQLSvc), the name of the computer that runs the service (NAS-LCLSYDB01), and the port that the service listens on (1433).

When SQL Server is running as the Local System account on the server, then the SPN property of SQL server's domain account has this information in it. So, using ADSIEdit, I checked mine, and the MSSQLSvc was not listed in the computer account's SPNs, so I added it. Still didn't work.

Well, this SQL server has been a member of two different domains, and has had three different computer names in its lifetime (without reinstalling SQL), as well as having its service account changed a few times. After much headscratching and digging around, I found out a crucial bit of info that I had been missing: when the SQL service is using a Domain account as its service account (instead of Local System), then the SPN for this server doesn't belong on the computer account at all -- it has to be applied to the service account object. (Which makes sense in retrospect, since the process that queries the domain controller (SQL) for a ticket is the SQL account, it stands to reason that the ticket would be tied to that account).

So I cracked open ADSIEdit again, browsed through the AD configuration, removed the MSSQLSvc SPNs from the computer account and then added them to the user account that SQL was running under.

Lo and behold, all was right with the world and my script ran without a hiccup. I queried my database, and saw that the computer had written its computername and a timestamp into the table. I looked at the security log on the server, and saw that it had authenticated as NAS-CR4737$.

So it turned out to be a combination of DNS issues, Active Directory issues and Kerberos problems. Once I got those straightened out, the SQL stuff worked exactly as I said I thought it shoud. I got to learn a lot about Kerberos in the process, got to learn more about VBScript that I didn't know before, and found some useful tools along the way.

Next I get to work on the rest of my scripts and figure out why the Windows Update API is so slow - maybe find a way to keep it from taking two full minutes to shut down the computer.

But before that, I'm going to have a celebratory beer tonight. I think you guys should make me an honorary genius for this one.

by: itsmeandnobodyelsePosted on 2009-04-07 at 15:07:44ID: 24092453

>>>> But before that, I'm going to have a celebratory beer tonight.

Cheers. Great job.

by: NightmanPosted on 2009-04-07 at 22:56:49ID: 24094277

titansfan20, it sounds as if you really had your work cut out for you on this one ... I think that more than one beer is in order.

Of course, as with anything else, once you know what the problem is and what to look for, you can find reference material easily enough ...

http://support.microsoft.com/kb/319723
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

Glad you've got it working. BTW, I would strongly recommend going the stored procedure route and tying down the permissions of the domain computers group to execute only - currently it's a little insecure.

by: titansfan20Posted on 2009-04-08 at 07:50:35ID: 24097877

I wish you had a LOL emoticon for that one. Of course MSDN would have an article detailing exactly what the problem was, that you couldn't find without already knowing exactly what the problem was.

On the SP route: I may look into doing that, thanks. Currently the SQL Login "Domain Computers" only has access to this single table in my Inventory database, so it doesn't seem too insecure.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

For individual users
Instant access to solutions
Ask your tech questions
Start your 30-day Free Trial

Business Accounts

Perfect for organizations
Multiple users
Save over 36%
Create a Business Account

Answer for Membership

Earn free access
Showcase your knowledge
No credit card required
Sign Up to Answer

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...

Question

Authenticate to SQL Server with domain computer's local system account

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

SQL

VBScript

NT Authentication

MS SQL Server

VB Script

Microsoft Development

Active Directory

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press