Solved

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

Posted on 2009-04-03
34
5,768 Views
Last Modified: 2012-08-14
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

Open in new window

0
Comment
Question by:titansfan20
  • 13
  • 8
  • 6
  • +1
34 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 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;
0
 

Author Comment

by:titansfan20
ID: 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
0
 
LVL 29

Expert Comment

by:Nightman
ID: 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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 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. :)
0
 

Author Comment

by:titansfan20
ID: 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.
0
 

Author Comment

by:titansfan20
ID: 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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 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.
0
 

Author Comment

by:titansfan20
ID: 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.
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 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.



0
 

Author Comment

by:titansfan20
ID: 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)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 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.
0
 

Author Comment

by:titansfan20
ID: 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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 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.
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 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.
0
 

Author Comment

by:titansfan20
ID: 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.
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 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.


0
 

Author Comment

by:titansfan20
ID: 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.
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 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.
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 24078920
Look below at the article and you see

- APPLIES TO
  Microsoft Windows 2000 Server
  Microsoft Windows 2000 Advanced Server
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 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.
0
 

Author Comment

by:titansfan20
ID: 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?
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 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.




0
 
LVL 29

Expert Comment

by:Nightman
ID: 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
0
 

Author Comment

by:titansfan20
ID: 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.
0
 

Author Comment

by:titansfan20
ID: 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.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 24090831
How did you get it to work? I would like to see the details for future reference.
0
 

Accepted Solution

by:
titansfan20 earned 0 total points
ID: 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.
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 24092453
>>>> But before that, I'm going to have a celebratory beer tonight.

Cheers. Great job.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 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.
0
 

Author Comment

by:titansfan20
ID: 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.
0

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now