Enumerate Distribution Groups Script Updated…

July 17th, 2009 4 comments

A reader sent in a question asking how to enumerate groups that have spaces in them and this lead me to realize I didn’t follow the specifications for valid distinguished names as well as I thought I had.  If you take a look at RFC 2253 and the Microsoft page that defines security group names, you will see what the allowed characters are for these names.  At this point the validation is a lot better, but it still isn’t perfect.

If you encounter a situation where you need to enumerate members of a group that my validation does not allow, you can scroll down to line 271 in the script and change the $rx variable to “.+” to make it simple which will allow you to pass any characters.  If you pass invalid characters you will get some funny errors happening, but it should work.  You may have to use quotes around the name if you are looking to use spaces or other allowed special characters.

Taking another look at the code, I also found a small bug that was causing the display of notes associated with a group to print out an error about null strings.  This should be fixed now.  If anyone notices any other problems, feel free to comment and let me know and I will try to fix it or add in the change as soon as possible.  I’ll be posting another update soon that goes the other direction of this script, one that enumerates group membership for a specific user.

Thanks again to Darren from Brisbane(?), Australia for pointing this out.

Here is the updated script download link: Download enumerate_groups.ps1Download enumerate_groups.ps1

Share/Save

sp_help_revlogin cleaned up and revised with roles

July 8th, 2009 No comments

When working with SQL Server there are often times where you will need to script your logins over to another server such as when you have database mirroring or log shipping configured for certain databases.   This is such a common thing that Microsoft provides in-depth instructions on how to do this as well as the T-SQL code required to do it, which you can read more about here: http://support.microsoft.com/kb/918992.  The script they provide is missing a few things so I took a little time to clean up the code, revise some of it, and add in a few of the missing pieces.

The three things that are overlooked in the Microsoft article that I have included are default language and default database (being mandatory) for logins as well as the scripting of roles.  The lack of the default language is pretty minor, but it’s something that could be important to people so I am including it in my script.  The lack of default databases being an option in the Microsoft script is not a big deal because it assumes you are scripting everything, but if you are only concerned with logins that are specific to a certain database you will get errors if you haven’t created that database on the target.  The lack of server roles is another important one that I have needed in previous configurations so it is also included here.  The resulting script is pretty long so you can check out the source of the revised procedure after the jump, which includes the sp_hexadecimal script from the Microsoft article.

Additionally, something just as important as the revlogin script itself is the question of “Well now that I have this script, how do I schedule this to happen automatically for me instead of just generating more code that I have to execute on the target server?” If you are asking that question then you might want to take a look at the next block of code. In order to automate sp_help_revlogin there are a few options, but the one that I prefer over all else is using osql and a SQL Agent job with a single T-SQL step because it is very simple to implement in most server configurations. The following script is relatively straight forward in that you only have one variable, the target server, to worry about. The only pre-requisites being that xp_cmdshell is enabled and that the Windows account that the job is being executed under has sysadmin rights to both servers. This script could easily be turned into a stored procedure but for the sake of brevity I have included it in its shortest form.

Disclaimer: Use these scripts at your own risk!!

-- Declare and set the target server...
DECLARE @TargetServer SYSNAME
SET @TargetServer = N'DB1.DOMAIN.LOCAL'

-- Define a temporary file to store the script output
DECLARE @guidfile VARCHAR(160)
SET @guidfile = '%TEMP%\' + CONVERT(SYSNAME, NEWID()) + '.txt'

-- execute sp_help_revlogin_roles and save the output to the temp file
DECLARE @cmd VARCHAR(8000)
SET @cmd = 'osql -E -n -h-1 -d master -w 8000 -Q "exec sp_help_revlogin_roles" -o "' + @guidfile + '"'
EXEC master.dbo.xp_cmdshell @cmd

-- execute the temp file on the target server
SET @cmd = 'osql -E -S ' + @TargetServer + ' -d master -w 8000 -i "' + @guidfile + '"'
EXEC master.dbo.xp_cmdshell @cmd

-- delete the temp file
SET @cmd = 'del "' + @guidfile + '"'
EXEC master.dbo.xp_cmdshell @cmd

Click here to view the code for sp_help_revlogin_roles

Categories: SQL Tags: , ,

Black Login Screen Showing Logo in Windows 2003

June 22nd, 2009 No comments

Have you ever had a login screen show up in Windows where everything is black except for the logo like the image below? This happens sometimes when the system drive on a server fills up, and a bug in windows causing all of the color settings to default to black. It is a pretty common problem, however for some reason Microsoft has not released a KB article about this even though I have seen this happen numerous times.

Black Login Screen With Logo in Windows 2003

Black Login Screen With Logo in Windows 2003


Read more…

Windows 2008 DNS Query Issues with Root Hints

June 9th, 2009 No comments

While browsing Newegg looking for some hardware, I came across an issue where the images on their site were not loading.  I couldn’t figure out exactly what was going on since it wasn’t a browser cache issue, and it didn’t seem to be a DNS issue, at least not on my workstation anyway.  After doing a little digging, I tried clearing the DNS cache on our DNS server and everything came up and the images started loading again fine.

Over the course of the last three months this happened a few times, not only for Newegg but for other sites as well, mostly in the .co.uk TLD though.  This happening once was easy enough to dismiss, even happening a second time, ok, but after a third time, there had to be some other underlying issue.  Fortunately, Microsoft released a KB article that explains what is going on and how to get around the problem.  You can read all about it here: http://support.microsoft.com/kb/968372

What is happening is basically that root hints are not updating on the DNS server and SERVFAIL is getting returned to the client requesting the DNS lookup.  This can cause pages to not load on certain domains, including but not limited to .co.uk, .cn, and .br, as well as certain .com’s that I have seen.  Fortunately the fix is easy enough; you can either configure forwarders or simply tweak the TTL of the root hints on your server.  I’m not going to get into the debate of which is better, root hints or forwarders; but for those of you using forwarders this problem will not affect you, and for those of you using root hints, below is a very easy set of commands you can run to fix the problem.

Launch an elevated command prompt and execute the following commands.  This will stop your DNS server, add the Microsoft recommended registry value, and start DNS back up again.

NET STOP DNS
REG ADD HKLM\SYSTEM\CurrentControlSet\Services\DNS\Parameters /v MaxCacheTTL /t REG_DWORD /d 0x0002a300
NET START DNS
Categories: DNS, Windows Tags: , ,

Enumerate Distribution Group Members

May 24th, 2009 4 comments

Note: Script updated on 2009/07/17 to fix two bugs.  Read more here: http://www.tinyint.com/index.php/2009/07/17/enumerate-distribution-groups-script-updated/


In my organization, we make use of many different groups to separate departments and sub-groups of each department, and many groups build off of this. We also make use of Dynamic Distribution Groups to make things a bit easier on the admin side of things. When tasked with cleaning up these distribution groups and making them easier to manage, it was a bit difficult determining who was supposed to receive mail for what group.

This is because the Get-DistributionGroupMember cmdlet doesn’t have a parameter like –expand which will give you all of the child groups and their members as well. If you have a group called “Engineering” which then has 4 child groups for each department and then each of those groups has each individual mailbox, when you perform “Get-DistributionGroupMember -Identity Engineering”, you will only see the four child groups, not each member of those as well. This became a big issue because of how much we rely on sub-groups in our organization, and after a lot of investigation it turned out there was no way to do this built in directly to any cmdlet, so I wrote a script that would do this for me.  If you need to recursively enumerate distribution group members you are unfortunately out of luck with built in cmdlets.

There are a number of scripts out there that serve a similar purpose as the one I have created, but most do not handle mail contacts or dynamic distribution groups, so I figured if I am going to have to add functionality, I might as well write it myself from the ground up. So now, if you are ever in need of getting child members of a distribution group you can use this script to help you out.

One nice feature here is if you specify “-showTree” as a parameter, it will display a treeview of all the groups. Without -showtree it will just grab all child members and display them in a flat view. The script is pretty long because I included help text that displays if you run the script without any parameters, and for that reason I am just posting it as a download link. Hope this helps a few people out there who went through the same trouble I did finding out that there is no built in way to do this!


Download Link: Download enumerate_groups.ps1Download enumerate_groups.ps1

Fix for a hanging server after shutdown in rdp session…

March 25th, 2009 No comments

One of my least favorite, and recurring, issues with Windows is one that pops up all the time where you try to reboot a server while you are in a remote desktop session; the RDP session will end but the server never reboots.  In my experience this only happens when you reboot within a normal RDP session, but if you are logged in with the /console or /admin switch it will work fine.  This fix is relatively old, but it is one that is not talked about very frequently.  This can also happen if you are logged into a regular RDP session and are trying to run Windows Updates.

This problem is a result of deadlocks occurring between the NTFS shutdown process and disk resource access.  Usually, your server will remain online responding to ping when this happens, and you can even get into computer management, remote registry, and other things remotely.  Getting back into the server through RDP however does not work because terminal services is already shutting down.  You can read more about this at the link below for the KB article.

Without this fix your options are limited to bring the server down gracefully, in fact many people would just do a hard reset from the power button to finish the reboot.  You can however issue a remote shutdown command from the command line.  Simply run “shutdown /r /t 5 /m \\computer_name” without the quotes to reboot the machine in question.  Make sure you don’t forget the /m switch otherwise you will end up rebooting your own machine.  /t is for the time to wait before shutting down and you can use 0 if you like to shutdown immediately, but this doesn’t give you a chance to send an abort (/a) if you enter the wrong server accidentally. You can also issue “shutdown /i” without the quotes to get the interactive dialog which will let you enter a list of servers to reboot and a few other options.

The Microsoft patch for this can be acquired from the following page: http://support.microsoft.com/kb/930045

Note: This does not solve the problem every single time.  I have seen the issue occur even after applying this patch, but it seems to fix the problem for the vast majority.

The problem with SQL Injection…

March 23rd, 2009 No comments

Oh yes, Little Bobby Tables we call him.

I know it’s an old comic, but XKCD really hit the nail on the head with this one.  It is something that I deal with on a regular basis and I think there are a lot of people out there who still don’t really understand the concept of SQL Injection, how it works, and how to get away from it.  While working with a client who fell victim to a SQL Injection attack on their website, I wrote some information for them to take and use as reference for what SQL Injection is, how to prevent it, and what to do about it when it happens.  I have generalized that info and hopefully it can help some others.
Read more…

Categories: SQL Tags: , , , , ,