Tuesday, 24 January 2012

a little problem with DSquery & DSget

Just came across a knotty little issue with one of our domains, and it went something like this:

2nd line support engineer: Can you write me a script to give me a list of all the users whose accounts have expiry dates and whether those accounts are disabled or not

Me: no need. Just use

dsquery user -limit 0 | dsget user -samid -acctexpires -disabled

2nd line support engineer: that's only showing me users that don't have expiry dates - and i know for a fact that some of them do.

Me: (Assuming that the items wanted were simply being swamped by the vast number of accounts that didn't have an expiry date)

ok, then try this:
dsquery user -limit 0 | dsget user -samid -acctexpires -disabled | grep -v "never"


I supplied a copy of grep.exe from the gnu unix utils ports, as i couldn't make find work after two pipes for some reason
http://sourceforge.net/projects/unxutils/

2nd line support engineer: now i'm getting nothing at all

Me:
It's at this point I decide it's probably worth shadowing the engineer's TS session and having a look for myself what is going on

After deconstructing the pipe and using text files:

dsquery user -limit 0 > users.txt (Worked fine)
type users.txt | dsget user -samid (produced some results and then failed with the error:

dsget failed: Directory object not found.
type dsget /? for help.

followed by a PARTIAL list of user samid's (causing the error to scroll off-screen and making it difficult to find))

Looking at the last samid on the list, i searched for that user in users.txt - then looked at the next user in the list. Gotcha!!!!

The next user in the list had an apostrophe in his name! - very strange.... I'd have expected microsoft to sort that one out fairly quickly.

did a google - found http://www.rlmueller.net/CharactersEscaped.htm which (although suggesting that ' was a valid character) also suggested deconstructing the command using text files and manually editing the user DSNs. - not my favorite solution

With a little but of poking around in the help for the ds command, i found a couple of highly interesting switches

-uco output unicode
-uci input unicode
-uc both of the above

and so, with a few judicious switches on the initial syntax, the command ran through without errors:

Dsquery user –uc –limit 0 | dsget user –uci –samid –acctexpires –disabled | grep –v “never”

Problem solved.

Hope this can help someone else out there with a similar problem!

Thursday, 24 February 2011

TFS: restricting administrators to the admin console

Now, let's start with a caveat. I detest the way Microsoft have coded TFS. Because of some fundamentally flawed design decisions, the system is next to impossible to run securely.

The service account requires writeable access to the master database, & serveradmin & securityadmin - I think most people just give it sysadmin & live with it, but at my place of work the auditors deem this a serious issue

Full access is required the data sources on the analysis services database

The system requires a dedicated instance of reporting services

and finally, to run the administration tool, you require local admin rights to the application server!

Whilst i can't help with most of the above, I did figure a way to reduce the impact of giving end-users access to the admin tool.

1) Create local accounts on the server for each user - due to the number of rights allocated, this should be kept to a minimum. The name should match their domain username. These users should be placed in a local group called something like TFS_Admins, which we will use later

2) For each local account, set the following script as the start-up application (the environment tab of the user object)

(Example) "C:\Program Files\Microsoft Team Foundation Server 2010\Tools\runit.bat"

The script file should read as follows:

@echo off
runas /user:((DOMAIN))\%username% "c:\Program Files\Microsoft Team Foundation Server 2010\Tools\tfsmgmt.exe"

((DOMAIN)) is your domain name. If you installed TFS to a drive & folder other than c:\program files, alter the above accordingly.

You're now in a position where, if the user logs on to the server via RDP using their local server account, they will be prompted for their domain password, and (assuming they're configured as TFS admins, and their domain accounts have local admin rights) they will be thrown into the admin tool without any explorer start bar being run - Result. The RDP window will close 0-60 seconds

The next problem to tackle is that, since the users are domain admins, they can just rdp directly to the server & use their domain account.

3) Create a domain group containing the domain accounts of your TFS admins (DO NOT include those who are normal server admins!). Create a DENY ACCESS security entry on the file C:\Windows\Explorer.exe

This does 2 things. Firstly it stops the users from logging on remotely (which could also admittedly be done by adding them to the security policy "Deny log on through terminal services") but it ALSO prevents them from running explorer.exe via a backdoor, like the "open logs folder" link on the logs page of the administration console. - 2 birds, one stone. I'm sure you can see the reason for not adding server admins to this domain group!


One final issue to resolve: The locally logged-on user can still initiate the security dialog by using CTRL-ALT-END on the RDP session. This allows the ability to shut down & restart the system, and to run Task Manager from which all sorts of nefarious applications could be started - including explorer.exe - we didn't block the local users from running it.

4) DENY the TFS_Admins group created earlier access to c:\windows\system32\taskmgr.exe - again this should not contain normal local administrators

5) in the local security policy (or better still using a GPO) remove local administrators from the
"Shut down the system" and "Force shutdown from a remote system" rights. Replace them with domain admins, or potentially another local group to which your regular server operators can be added

6) Turn off default shares!

Hive: HKEY_LOCAL_MACHINE Key: SYSTEM\CurrentControlSet\Services\LanManServer\Parameters Name: AutoShareServer Data Type: REG_DWORD Value: 0

Idiot proof note: If you can't find the value in the registry under the exact location (i.e. it does not exist) - please right click in the right pane of the window and create it.

(Thanks Dan Petri)


And now you have a way to lock down the administration rights that need to be granted to TFS administrators to prevent your users mucking with the server config.



I'm sure it's not completely bulletproof, and someone somewhere will find a way in - for a start, I haven't looked at disabling remote WMI, disabling remote registry editing etc, However, it is a **** lot better than how it looks out of the box.

Enjoy.

Thursday, 20 August 2009

Excel evaluated functions

Yay, finally, something else obscure to blog about - evaluated functions in excel


What you say? use Real english? - well if you insist. I'm talking about the ability to treat a text string in an excel cell as if it were a formula, and to evaluate it (work out what the answer would be - happy?)


Suppose, for example, you had a spreadsheet set out as follows:





The boxes in yellow are those you have highlighted for user input. The aim of the spreadsheet is to allow the user to select a range of dates to add the values together for.


The value in E4 is the result of a formula which reads

=CONCATENATE("=sum(",E2,":",G2,")")


This creates a nice formula as seen in cell E4, but it's no use to us at present, because excel sees it as text and doesn't treat it as a formula. (Typically, you might put an intermediate result like this into a hidden column or row somewhere so as not to confuse the user)


The typical (but problematic) answer given to this conunderum on the net is as follows:

1) right-click on e4 and select "Name a range"
2) modify the "Refers to" field as shown below and press ok




3) in your target (answer) cell enter =Name (in our case the cell will read =Mysum.) You end up with a spreadsheet that looks something like this....


Which is exactly what you wanted, right? - errm it might look that way, but watch what happens if you change one of the source values - let's say we change a6 to 999



Oops - the data value has changed - but the calculated total has not. Why not? Well that's because when Excel decides whether a cell needs recalculating, it works out whether any of the cells that the formula depends upon have changed.

In this case E5 depends upon E4, E4 depends upon E2 and G2. However E2 and G2 are plain text, they don't depend on anything - they're not cell references (at least as far as excel knows). Because none of the prerequisites have changed, there's no need to recalculate.

There are 2 easy work-rounds for this. You can change either of the values in E2 or G2, or force a complete recalculation using ctrl-shift-F9.

But is there a more elegant solution that doesn't require user input? - use your noggin - i wouldn't have asked the question if there weren't :)

unfortunately, it requires the use of a (very small) macro, which means you need to use a macro-enabled workbook, but you can't have everything.

So the new way to implement is like this:

1) press ALT-F11 to bring up the VBA interface

2) Add a module to your spreadsheet as follows:






In the module we're going to create a very small UDF (user defined function)


Presented here for easy cut and paste:

Public Function MyEval(s)
Application.Volatile True ' forced recalc every time
MyEval = Evaluate(s)
End Function


3) save and close the VBA interface.

4) the results cell (E5 in our example) should now read =Myeval(CONCATENATE("=",E4)).

5) We also need to modify cell E4 to remove the "=" in front of the sum

=CONCATENATE("sum(",E2,":",G2,")")



Once you've set this up, you will find that the result updates automatically every time a data value changes.



Job done



So how does this little piece of magic work?



The key is in the line


Application.Volatile True



This tells excel that the function value cannot be depended upon, and that the value should be recalculated on every worksheet change. Without this line, the macro would function in exactly the same way as the earlier method using cell names. It's the same method Excel uses internally to ensure that functions like now() get update regularly.

Enjoy

Wednesday, 28 January 2009

Skippy's List

If you've never seen it then "Skippy's list" is a laugh a minute.

http://skippyslist.com/list/

213 things Skippy is no longer allowed to do in the U.S. Army.

Technical resources

Having one technical issue in this blog so far, it was with suprise that I was able to use it to assist in a technical query for someone on the activedir.org mailing list

If you don't know this list, it's an amazing resource for those involved in implementing and maintaining AD, run by a published author on AD and populated with the highest density of AD professionals I've ever encountered. If you work with AD, subscribe.

Friday, 6 July 2007

Remote service management

OK, first technical issue of the new blog - and welcome :)

I had an issue today where the application team responsible for the application support on a new implementation I'm managing wanted the rights to stop and start services.

As the application support team are also a subset of the users, and as the team as a whole are Windows developers, I am unwilling to give them full local admin rights - I need to ensure any OS changes go through the internal change control process and I get the distinct feeling that simply mandating this in the support policy wasn't going to cut it. :(

I used subinacl.exe from the resource kit to grant the necessary access to the services.

subinacl /service servicename /grant="domain\usergroup"=QSETOI

This grants the domain user group members the following permissions on the services:

Query Service Config
Query Service Status
Enumerate Dependent Services
StarT Service
StOp Service
Interrogate Service

All this is as documented in KB288129

I supplied the relevent people with the relevent command line tools (PSService, SC.exc, NetSVC) but none of them appeared to work - they were all getting access denied.

Some googling, KBing and head-scratching later I found details of some changes that were made in W2K3 SP1 to the default DACL of the service controller (the rights given to remote users had beed reduced by default, and an API had been added that allowed the security on the service manager to be changed. (See http://msdn2.microsoft.com/en-us/library/ms684323.aspx)

So, to grant the users access to the service controller, it was as simple as using

sc sdset SCMANAGER D:(A;;CCLCRPRC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)

(The important part of this sddl code is A;;CCLCRPRC;;;AU which gives authenticated users the same rights as interactive users - In W2K3 SP1 these rights are changed to A;;CC;;;AU)

Or so I thought.

With this solution in place, users were able to remotely query the services, but any attempt to stop or start them resulted in an access denied message

Checking the security event log, I found the following alert:


Object Open:
Object Server: SC Manager
Object Type: SC_MANAGER OBJECT
Object Name: ServicesActive
Handle ID: -
Operation ID: {0,90876741}
Process ID: 524
Image File Name: C:\WINDOWS\system32\services.exe
Primary User Name: SERVERNAME$
Primary Domain: DOMAINNAME
Primary Logon ID: (0x0,0x3F7)
Client User Name:
Client Domain:
Client Logon ID: (0x0,0xC657EA)

Accesses: DELETE
READ_CONTROL
WRITE_DAC
WRITE_OWNER
Connect to service controller
Create a new service
Enumerate services
Lock service database for exclusive access
Query service database lock state
Set last-known-good state of service database
Privileges: -
Restricted Sid Count: 0
Access Mask: 0xF003F

The info highlighted in red indicates that instead of just requesting the permissions required to enumerate, start and stop services, the command line tools were actually requesting FULL CONTROL access - which hadn't been given.

WARNING: If I had granted full control permissions, the users would have had the right to add a new service or modify one of the existing services. This service could be configured to point to any exe placed on the server. This could include an exe specifically written to add the users into the local administrators group.

So now my problem was with command line tools which were not coded to support this function.
To my surprise, however, when we tried to use the services.msc tool remotely, this worked exactly as designed - only requesting the exact permissions it needed to start and stop the services remotely. This was a better solution than I originally anticipated, and more in line with how the users actually wanted to work.

Hello and welcome

I've been meaning to set up a blog for some time, it seems I'm somewhat behind the wave.

My purpose in this blog is to document the little bits and twiddles of system design and configuration that I stumble across in everyday life, along with a few bits and pieces I find particularly humourous.

Take this as a warning, I have a somewhat corny sense of humour <:)