I recently needed to setup a restricted groups group policy for a client, and discovered that the instructions available online are incomplete at best, inaccurate at worst. After fiddling with the settings and trying various things I managed to get it working, so I thought I'd share what I found.
Restricted groups certainly aren't anything new, they've been available with Windows Server GPO's for several versions now. They allow you within a domain GPO to specify the membership of a specific group on the target machines, therefore saving you the hassle of doing it manually.
In this instance the aim was to grant local administrator permissions to a selection of users, but only to the client machines on the network, not the servers. Eg, those users could make changes, install software etc on the desktops and laptops within the companies network, but could not login to the servers, and therefore gain access to data they were not authorised to view.
A reasonable guide can be found here, however, the critical ommission is that the "Group Name" MUST be the name of the group that you want the users / groups to be a member of on the client machines. The instructions indicate that it's controlled by "Member Of", however from testing that appears not to the the case.
So to add MyAdminGroup to the Administrators group you would set :
Group Name = Administrators Members = MyAdminGroup, Administrator, \Domain Admins, \administrator Member of = empty
All the guides I've seen show the group name in their examples as being "Administrators", but make no reference to this being anything other than a simple name. When I first attempted it, I called the group "Local Admins", and then added "Administrators" (and a few other variations) to the Member of section, with no effect.
Note: When using Restricted groups you need to include the existing groups as well as the new ones, as this policy replaces rather than amends the existing group membership.
So continuing from my last post, after getting my monitoring script to work, I set about trying to introduce some semblance of security to at least partially hide the plain text password held within the script.
My initial success was through the use of the ConvertTo-SecureString and ConvertFrom-SecureString cmdlets. Essentially (or least as far as I understand it), the first of these converts some form of password string into some kind of system value, eg if you try simply outputting the contents of $password in the first script, it simply returns something like 'Secure-String', rather than a representation of the password itself. The second cmdlet converts that system value into an actual string value, though it's encrypted rather than the original ascii text as we'll see shortly.
So, my first task was to get hold of that encrypted version of the string, so I could then put that direct into the script, and use it in place of the plain text password. Using the following code I encrypted the password and then outputted it into a text file.
Extracting the resulting string from the text file, I added it into my script, and then adjusted the ConvertTo-SecureString line to receive an encrypted string rather than a plaintext one as you can see below.
To my relief this then worked, exactly like the original script! Filled with joy (ok, maybe not joy, but certainly pleased with myself), I updated the monitoring system to use the new script, set it running, and can you guess what happened? Yep, it failed. I went through all the settings, they were fine. I switched back to using the old script, it worked again.
After scratching my head for a while I had an idea, possibly a crazy one, but worth a try. I set the monitoring system to execute the script that generates the encrypted password so I could see what the output would look like. It was completely different to the one outputted direct on the server! I re-ran the server version, and sure enough, it produced the same output as it did the first time.
Clearly something about the encryption process used in ConvertTo-SecureString is dependant on where it is run. I updated the script with the new string, set the monitoring system going again, and finally it worked!
So the lesson here is that if you try this yourself, what ever system you are using to call your script, you must also get it to call the encryption script as well, and use THAT output to populate your monitoring script with its password.
Recently I've been trying to find a way to use a combination of PowerShell and our monitoring system IPMonitor to check connections between two sites.
Picture the scene, three different sites, A, B and C. At site A we have a monitoring system to check various aspects of the services we look after, sites B and C contain various servers, with VPN links joining A to B, A to C and B to C. From A I can check that the VPN links to B and C are up, but what about the link between B and C? That's where I figured PowerShell could come in handy.
After a bit of searching I found the test-command cmdlet which not only allows you to test a connection like you would with ping, but also to specify the source server to test from. After a little tinkering I came up with the following :
As you can see, running the script outputs either 1 or 2 depending on the result of the test. Within IPMonitor I then used an External Process Monitor to call the script, compare the script output with the test value (1 obviously), and then display if the connection is up.
The final piece to the puzzle was making the whole thing at least a bit more secure. For the terminally paranoid, having a script sat there containing a server password doesn't really fill you with joy, so I managed to encrypt the password and use that instead. As it turned out though, doing that in conjunction with IPMonitor turned out to be harder than expected, but I'll go into details on that later.
I recently had an interesting challenge at work. A 3rd party app we use had stopped working, and was generating an error when it tried to start. The error suggested a problem with its MSDE database, though unfortunately it didn't provide a lot of detail. As the resident SQL "expert" I was asked to look at it, and see if I could find a way to repair the database, since the alternative would be a re-installation, and a lot of work re-configuring everything.
Now I've never really played with MSDE before, just full fat SQL, but I soldiered on to see what I could come up with. After trying the obvious things using osql I'd quickly exhausted all the ideas I could think of, and found every article, post or blog relating to database repair made reference to using Enterprise Manager... not much use to me you might think! Well with a little searching I found that you can connect to an MSDE instance using EM, but it's not that straight forward, especially if you're connecting from a different computer. All the information is out there, but even after the fact I can't find anywhere that pulls it all together... so here goes!
There are two or three steps required, depending on whether you are connecting from a local or remote EM installation.
1) Enable TCP/IP on the MSDE database.
Run 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe' on the MSDE machine, and then select tcp/ip and move it to enabled protocol.
2) Create a sysadmin user in the database to allow you to login.
on the MSDE machine. Once connected enter the following commands (nothing will happen in osql until after the 'go' statement) :
1> use master 2> EXEC sp_addlogin 'YourSysAdminUserName','YourPasswd' 3> EXEC sp_addsrvrolemember 'YourSysAdminUserName', 'sysadmin' 4> go
Now on a local installation this may be enough, however on a remote installation you will likely receive an error :
"Login failed for user 'YourSysAdminUserName'. Reason: Not associated with a trusted SQL Server connection."
in which case you will need to change the authentication mode of your MSDE instance to Mixed Mode to allow you to connect with your new SQL login, so...
3) Change MSDE authentication mode from Windows Integrated to Mixed.
(The following is taken direct from MS KB285097)
--- To set this key to mixed-mode, follow these steps:
1. Stop SQL Server and all related services, such as SQLAgent, from the Services control panel. 2. Click Start, click Run, type regedt32, and then click OK. 3. Find the HKEY_LOCAL_MACHINE window on the local computer. 4. Navigate to the following registry key for the default instance: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer 5. Navigate to the following registry key for a named instance: HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode 6. On the right-hand pane, find the value LoginMode and double-click it. 7. In the DWORD Editor window, set the value to 0 or 2. Also, make sure that the Hex radio button is selected. 8. Click OK.
Restart SQL Server and SQL Server Agent services for this change to take effect.
Note When you change the default login authentication mode from Windows NT authentication to SQL authentication, we recommend that you change the sa password to a strong password. ---
So that was an interesting day in the office! Annoyingly after all that and many hours of fiddling I still manage to repair the problem myself. I was able to repair it with a 3rd party recovery tool, but it would have been so much more satisfying to have managed it myself.
Reading through various posts, articles and guides I've come across references to IDE's and ISE's, and had no idea what on earth they were. I think it's typical case of programming types assuming everyone knows these things, while forgetting that those of us from a non-programming sysadmin point of view (after all the main forcus of PowerShell) won't have heard of them before.
It took me a while to get my head around what they actually refer to, so I thought I'd share what I found. In simple terms :
ISE = Integrated Script Environment IDE = Integraded Development Environment
so essentially they're both fancy names for a script editor with some fancy bells and whistles!
There certainly seem to be a wide variety of editors out there to choose from, both free and commercial, with many apparently including support for other languages as well, which might be useful for some people. I've been a long term fan of EditPlus as my editor of choice for scripting, especially after discovering that you can download additional syntax lists for it, so it can do all the colour coding of your code even with new languages. For PowerShell though, especially seeing some of the alternatives out there, I think it might be time to move on!
The editor I've ended giving a try is PowerGUI, since it's free and seems to have some very handy features which will certainly help with learning the language. I'll admit I was a little hessitant about the whole Intellisense thing, since it's not something I've really used before, but the help in remembering which switches etc are available will certainly help. The other thing I particularly like is the mouse over help, which gives you a quick reminder of the complete syntax of the relevant command.
Of course now I just have to get my head around the language itself! It's weird to think this is the first time since I was a student where I've tried to learn a new language properly! :-) OK, so it's the slower method, but I think it'll be worth the effort to learn it properly, and therefore use it properly, rather than just try finding the new commends to do old methods, and not get the full benefit of the language. I've already seen so many posts from people talking about using old methods rather than making full use of the pipeline, so I'm determined to do it properly.
Written by Dr. Tobias Weltner, it's available either online as html pages so you can skip to the sections you need, or there's a free ebook download available of the entire book.
Something occured to me the other day... the collective wisdom of the internet isn't always a good thing, especially when dealing with something that has many people confused!
I had a moment of self doubt recently, brought on by the need to explain SQL backups to someone. I wanted to make sure that what I thought was correct really was, rather than simply soldier on and risk passing on a poor understanding to an unknowing recipient. What I found though were a lot of people who think they understand SQL backups, but who clearly don't!
So what is it they don't get you ask?
Well generally speaking the connection between different type of backup, and their effect on the transaction log. I saw people thinking that a full backup would cause old transaction log records to be cleared, and other people thinking that differential backups use the transaction log for its data... both of which are wrong. So to be clear...
Full Backups - Only the database (.mdf) itself is backed up*, the transaction log isn't touched, and it will continue to grow as always.
Differential Backups - Only the database (.mdf) itself is backed up, by backing up those extents within the database which are marked as having changed since the last Full backup. The transaction log isn't touched.
Transaction Log Backups - Finally the transaction log file is used, and the completed transactions marked as such so the space can be re-used / cleared, or more accurately, the inactive portion of the log is truncated.
So the critical thing here is that if you have a database in Full or Bulk-logged recovery mode, you must have a transaction log backup done at some point, even if you don't plan to use it. Otherwise the transaction log will never be truncated, and it will just continue to grow until it runs out of space. Or of course, consider simply changing the recover mode to simple.
* yes I know that the transactions made during the process of the backup are then backed up from the transaction log at the end, but lets not confuse matters here.
So I've been reading various bits of information about PowerShell recently, and I think perhaps the most important thing I've seen is the quality of the help system. After all, who wants to try memorising masses of cmdlet switches and options, when you might only use them once in a blue moon!
Checking out the wealth of information flowing forth from the get-help cmdlet was very satisfying, made me reminisce of my days with the Linux Man pages, in fact it's nice to see that not only has 'man' been setup as an alias for get-help, but it's even been aliased to use ' more', making it function more like the traditional man system.
I have got one complaint though which I've yet to find a resolve for (if there is one), and that's the inability to scroll back up the page. Great I can page down with Space, go down a line with Enter, but how do I go back up!?! It would have been nice to be able to use the up and down arrows as well like you can with man, so you can view the detail of an option, and then scroll back up to the syntax list or where ever without having to leave help and start again.
Hopefully I'm just missing something somewhere, but so far I don't think so.
By the way, if you're looking for a good guide to PowerShell then I can highly recommend the Windows PowerShell v2.0 Reference from the Microsoft scripting guys which you can find here http://blogs.technet.com/b/csps/p/psref.aspx. It covers all the basics you'll need to know, and points you in the right direction to find out more. Plus I've always liked the informal writing style of the scripting guys, it's a much nicer read than your average MS reference.
So what is it about PowerShell that interests me? Well as someone who started out in the server admin world with a hand full of Linux boxes, albeit in a fairly amateur capacity, I've always naturally been comfortable working at a command prompt. In a Windows world though it's always seemed that to automate anything via scripting there are so many ways to do things, depending on what you want to achieve, with none of them really covering all the bases. Traditional batchscripts, vbscript, wmi, adsi, no one scripting technology really covers everything, and you end up using a collection of things, with one method calling another method etc, since each method lends itself so well to some things more than others.
For instance, I in one script I wrote, you've got a batchscript handling the basic flow and logic of the script, that then calls a few of the GNU ported linux functions like grep, tail, wget etc, and uses vbscript to handle sending e-mail using the JMail component, and at one point calling WMI functions as well!
When I first heard about PowerShell it sounded like finally there might be a language I could invest some time into learning, safe in the knowledge that it could cover what ever I was trying to achieve. Of course like many things there was the question, 'is this too good to be true?', and at first it was. After all, it's great that you can control Windows 2008, Exchange 2007 and SQL Server 2008 etc via PowerShell, but when you're still predominantly using 2003, 2003 and 2005, that's not a lot of use. Now of course things have changed, and it's worth taking the time to learn the language, so that's what I'm determined to do. I think my first challenge will be to get my head about the basics of the language itself, and how the flow control etc is supposed to work, and then work on re-writing many of my more commonly used scripts to use PowerShell instead.
It's a funny thing, but throughout years of being a computer geek, and over ten years now working as a Sys Admin, I've never really had a go at blogging before now.
I suppose there are a couple of reasons behind this. Firstly, while I love technology of course, I've never been a massive fan of technology for its own sake. Sure, show me a shiny new toy and I'll drool like the next geek, but once the spittle has dried, the reality dawns that while pretty or simply cool in a geek way, the lustre far outweighs the substance. Secondly, and perhaps more specific to blogging, I've never seen the point of blogging for the sake of blogging. Don't get me wrong, there are a lot of fantastic blogs out there that I've enjoyed reading over the years, but unless you have something worthwhile to say, you're doomed to end up as yet another "for breakfast I had...", "same stuff different day...", "isn't 'stuff' great..." blogger.
So considering what I've said above, what made me decide to start this blog now? Well over the years there have been the odd things here and there that I've thought it might be interesting to write about, but never sufficient to bother starting a blog just for them. Recently though I've been reading more and more about PowerShell, and finally decided to take the plunge and learn it. I've read loads of articles, guides and blogs relating to using PowerShell, many of which are fantastically written, but one thing I've noticed is that they all seem to be from people who are already quite competent programmers. I've done my fair share of scripting over the years to automate various sysadmin tasks, but I'm far from proficient, so I thought it might be interesting to write about my experiences from that angle.
