Get and Set Folder Permissions with PowerShell

Managing permissions for numerous servers is the theme today. Drilling down into a folder, right-clicking properties, then reviewing security on the same folder for potentially dozens of computers is time consuming and, with the capabilities of scripting, unnecessary.

PowerShell lets us do this very easily. The first script allows you to view each account and their corresponding read/write permissions on any number of computers. By default the script will only search the local computer. You can filter to only display a specific right. A full list and explanation of each right is available here.

Function Get-Permission
  [string[]]$ComputerName = $Env:COMPUTERNAME,
Process {
 $ComputerName |
 ForEach-Object {
 $Server = "$_"
 Write-Verbose "Getting Permissions for \\$Server\$Folder"
 (Get-Acl "\\$Server\$Folder").Access |
 Where { $_.FileSystemRights -LIKE "*$Rights*" } | Select IdentityReference, FileSystemRights, AccessControlType

Now for a simple example. Remember to supply a $ instead of a : after the drive letter, as this is designed to run remotely.

#Example of Get-Permission
Get-Permission -ComputerName "COMP1","COMP2" -Folder "C$\logs\SQL"
Now that you have verified the permissions list, you might need to make some adjustments. This set command will allow you to change $Access and $Rights for a specific $Account with minimal effort across your domain.
Function Set-Permission
  [string[]]$ComputerName = $env:COMPUTERNAME,
  [string]$Access = "Allow",
  [string]$Right = "FullControl"
Process {
  ForEach-Object {
  $Server = "$_"
  $Acl = Get-Acl "\\$Server\$Folder"
  $Rule = New-Object System.Security.AccessControl.FileSystemAccessRule("$Account","$Right","ContainerInherit,ObjectInherit","None","$Access")
  Set-Acl "\\$Server\$Folder" $Acl
  Write-Verbose "Permission Set for \\$Server\$Folder"
 And here is a quick example of how to execute the function. This can be used to allow or deny rights to the folder.
#Example Set-Permission
Set-Permission -ComputerName "Comp1","Comp2" -Folder "C$\logs\sql" -Account "Domain\ServiceUser" -Access "Allow" -Right "FullControl"

SQL Server Troubleshooting: Token-based Login Failure

In continuation of the forced mirroring failover procedure I posted last week, this post describes the another level of pain you may encounter.

After forcibly failing a mirroring session to the secondary server, users were unable to connect to the SQL Server. The SQL Error Log was full of the same error message.


Login failed for user


Our SQL Server uses Windows Based Authentication so that was a major hint. The solution was actually incredibly easy. Originally I assumed that an account was locked out or perhaps missing from the mirror server – who knew how long ago everything had been correctly synched.

There are two likely solutions to this issue.

UAC is blocking the connection attempts

I find this to be a less likely scenario, and since changing UAC settings require a server restart, I highly suggest testing the next method first. It will probably solve your problem without restart. If the issue is UAC, the server is probably a recent setup, otherwise I feel you would have noticed this.

Recreate the User Account

More likely, the account in question needs to be dropped and recreated in SQL Server. If the user connects though SQL using a group or service account, you’ll need to track down the appropriate account that they are a member of in Active Directory. Ask your AD administrator to look up the groups which the user is a member of if you don’t have access yourself, or intuitively already know the answer.

Once you have the correct account, you’ll need to take note of the existing permissions via screenshot, scripting the login as a create to statement, or just remembering everything. Delete the existing account in the server-wide Security as well as Database-level Security. Deleting an account in server Security does not cascade to the Database level. In fact, you’ll be warned of this by SQL Server when you attempt the delete.

Now you’ll just need to recreate the account providing the correct permissions and any relevant mappings. The token-based server access validation errors should be solved now.

Troubleshooting Policys and xp_dirtree

Today we had a policy failure on a newly built SQL server. The policy verifies that every SQL server has a backup file stored on a remote directory. Simple enough, but in the past, this policy has been prone to permissions errors.

In case you would like to implement something like this on your system, the dumbed-down condition of the policy is below:

ExecuteSql('NUMERIC', '
--Get DBs on server
SELECT  @DBCount = COUNT(*) FROM sys.databases WHERE database_id <> 2
--Insert DirTree results into a table variable to return a numeric result
DECLARE @Check TABLE (SubDirectory VARCHAR(250), Depth BIT, [File] BIT)
INSERT  @Check
EXEC master.dbo.xp_dirtree @File,1,1
--Numeric result for easy policy evaluation 1=Pass if( # files = # dbs )
IF((SELECT COUNT(*) FROM @Check) = @DBCount)

Policy Explanation

The dumbed-down policy checks a backup server, which has a folder named for each SQL server, to see if it has the correct count of backups.  Each folder should have a backup file for each database, except for TempDB. This isn’t exactly how we do it, but it gets my point across and it provides a good example of how a moderately complex policy works using ExecuteSQL. There aren’t a lot of examples on the web, so I try to provide as many as I can.

The Troubleshooting

The new server wasn’t processing the policy correctly. My first idea was, “Well, there’s no files!” Easy! Problem solved!  Or not…the files were in the folder, and the number was even correct. So it wasn’t the backups or copy failing. That meant that there was something wrong with the permissions, the problem was, what?

I verified the logins were all present in SQL by reviewing Security -> Logins through Management Studio. This was a new server, so I assumed it would be something simple like that. The account in question was the SQL Server service account.  Policy Based Management and xp_dirtree use this account. The account was in SQL and it had all the proper permissions. OK, next thing.

The folder where all the files are stored is a shared folder, so I went into the Properties of the folder and verified the account that was shared. Everything looked right there. Every time I’ve had an issue with the permissions of xp_dirtree reading servers, it’s been because the service account in the shared folder was wrong, so my next stop was checking out Active Directory.


Sharing a folder makes the file path easier. Just make sure your service account has permission here.

Drilling down through Active Directory, I found the account used for sharing the folder. This account had every SQL service account as a member. It looked fine in AD, it looked fine on the Shared folder. Everything worked perfectly except on this new server. What was wrong?!?

At this point, I just decided to throw a hammer at it and throw in extra service accounts until hopefully one worked. Eventually I added the SQL Agent account. Suddenly, the policy passed. The new server could read the backup directory! So I took the normal account out of the permissions. Now everything failed except this new server. Wait, what…?

I logged into the new server and loaded up SQL Configuration Manager…


When the server was setup, someone switched the accounts. SQL Server was running using the SQL Agent service account. SQL Agent was running using the SQL Server service account. I restarted each with the correctly corresponding service and account. I then fixed the permissions for the shared backup directory back to the original setup.

Voila! Everything worked perfectly!

The Moral of the Story

Make sure new servers and settings are configured properly before trying to fix things that weren’t broken before the new stuff existed. I’m sad to say it took me so long to understand the problem. Even with other DBAs chiming in. None of us thought about checking the Configuration Manager…that or they assumed I had already checked that.

Sometimes, it’s the little things that get you…OK, normally its the little things that get you.

T-SQL Tuesday #73 Naughty or Nice?

TSQL2sDay150x150_388014A5This month’s topic for T-SQL Tuesday is hosted by Bradley Ball (blog | twitter) with the subject of server environments.

As you work with SQL Server look around you. Is your environment Naughty or Nice? If it is Naughty what’s wrong with it? What would you do to fix it? Do you have a scrooge that is giving you the Christmas chills? Perhaps you have servers of past, present, and future haunting you. Maybe you are looking at SQL Server 2016 like some bright shining star in the east.

I won’t discuss my current environment, but I do have a frightening story of SQL past.

At a previous employer, I worked in a hastily built environment that never had the benefit of a DBA or Database Architect during setup.  The company started small, so originally they used Microsoft Access to house all their data. At some point, the tiny IT department migrated all the data over to a SQL Server database, but they still ran an Access front end.  The migration was done by people who were not concerned about normalization.  The goal was to move the data, structure was a tertiary concern at best. Many sins were committed in that database, and as the years went by and the data grew, the sins became more apparent.

Querying became a nightmare.  The number of nested functions, within nested functions, within nested stored procedures, was exhausting. I remember finding a procedure that had 14 levels of nested objects. I had to keep notes of object names as I delved in, trying to understand what was happening in that query. I’m still not certain I understood it all.

The real problem though, was that every user also had Excel reports that were direct connections to the database. The wait times were high from all the external hits to SQL Server, but the connection strings…oh the connections strings…

Every single user connected to the database via the SA account. Which was not renamed.  At least the password (which was from the beginning of time) was moderately complex.  However, each user could display the connection string to the database in plain text with a bit of poking around. There was no limit to the mayhem they could commit.

Since every user was connecting as SA, the only identification possible was by IP address. And since everyone always locks their computer in small company… it was not the best method for identifying a nefarious user.

I did try to get the SA password changed and the account locked down, but it was an arduous process. The old database was delicate, but even more unknown were all the individual reports and local connection interfaces created over the years. Interrupting business was frowned on more than the unlikely possibility of a user misusing the SA account. A few changes got made, but until the database and user interface was rebuilt from the ground up, nothing was actually solved.

Security is a major concern, and something like a global server admin account should always be kept tightly controlled, no matter the company size or trust level.

T-SQL Tuesday #68: Just Say No to Defaults

TSQL2sDay150x150_388014A5This month’s topic for T-SQL Tuesday is hosted by Andy Yun (blog | twitter) with the subject of defaults

This month, I’d like to ask everyone to blog about SQL Server Defaults. While SQL Server CAN be run with just the default settings, doing so is far from ideal. As we progress through our careers, many of us build lists of things we change. Let’s blog about those! And don’t think this month’s topic is constrained to just the SQL Server engine. SSIS & SSAS are absolutely fair game as well. SQL Server developer tools – sure! Want to take a different spin on “defaults,” go right ahead and be creative!

My job almost revolves around building out new SQL servers, so having an organized method is imperative. The list of security changes we make is a mile long. I may not even be exaggerating…However, there are a few changes from the default settings that I feel are the most important. I’m only going to touch on a few avoid an impossibly long read, and the likely redundant mentions.

Add TempDB Data Files

While Aaron Bertrand has some amazing ideas for how TempDB configuration should look during installation, at least SQL 2016 is making some improvements to encourage multiple data files. No one should be using a single data file by default. A good start is to have one data file per core, up to eight. Afterwards, it gets a bit more complicated.


If this doesn’t look wrong to you…well I’m sorry.

Adjust the Autogrowth Settings

One of my biggest pet peeves when I look at a new SQL Server and see default autogrowth settings. It just drives me crazy. Just yesterday, I found a database that was about a gigabyte in size, and yet the autogrowth was still set to 1MB. Can you imagine how many times it has to autogrow? That’s a resource hit every single time. Increase your autogrowth to a reasonable size so that it doesn’t have to autogrow every hour or more! Percent autogrowth is even more dangerous, especially for a log file. Logs can grow quickly, especially during something like index maintenance. While 10% of 100 MB may not seem like much, 10% of 1 TB can be temporarily devastating during high traffic times. Even worse, what do you do when your 10% growth triggers but your drive isn’t big enough to handle that?


This angers me so much!

Change Max Server Memory

There is a lot to be said about changing the maximum memory allowed by SQL Server. I’ve previously shared a way to get it for all your servers and some suggestions on what to set max memory to. The amount to limit it by varies, but the most important thing is to limit it, otherwise your OS may be memory starved and bring everything to its knees.

Change Cost Threshold for Parallelism & Max Degree

I listed these together because they go hand in hand. There are innumerable debates on what these should be, and it really depends on your server, but almost everyone agrees that the default settings are too relaxed. I’ve seen arguments for a Cost Threshold of 30-60 for a new default. Starting at a number in that range is a good idea. You can adjust it after testing your more problematic queries. However, as more people start using Columnstore indexes, it could mean you need lower thresholds or force serial plans. Perhaps someday we will need a specialized setting to set default cost thresholds for ColumnStores only?

As for the max degree, even Microsoft agrees the default is bad. Changing this appropriately requires knowing your system hardware and maybe doing a bit of math. It should be based on your number of CPUs, but you’ve got some wiggle room depending on how busy your system is. Many queries firing at once indicate a smaller Max DOP, while the inverse is also true.

Change the Default SQL Server Port

Everyone knows that the default port for SQL Server is 1433 and that the Browser uses 1434. You can change the default port. It’s a very easy way to prevent very simple hacking attempts. Sure, you can still be port scanned, but why skip an easy configuration change if it can help your security even by a little bit? You can even take it a step further and blacklist (or whitelist only what you want) any address that attempts to connect through the default port. That requires a bit more planning though.

Make Management Studio Yours

There are a lot more Server settings out there, but I want to mention something about Management Studio. Configure it so that it works for you. Personally, I’m OK with the color scheme, light on dark bothers my eyes, but some people love it.

I always dock my toolbars using AutoHide. I want to get to my registered servers, etc., quickly, but I still need full screen real estate when using the toolbars. Too much stuff.


Finally, my favorite change is to move the Status Bar to the top of the screen. It felt weird the first time, but now it’s distracting each time I open Management Studio for the first time and the bar is back on the bottom. It’s much more natural to see what is going on when the bar is at the top. The problem is, I have to change this so rarely, I normally spend 5 minutes hunting for the correct setting. You can find this under Tools -> Options -> Text Editor -> Editor Tab.

StatusBarI also change the defaults under Tab Text to only Include File Name. Everything else is just fluff that I can find on the status bar.


Security of PWDCOMPARE and SQL Hashing

Few people are as concerned about security as they should be, and fewer still are proficient in it. Even if you are, while you might be able to tackle the major security flaws, there’s always going to be something new that can get you, unless you implement an unhackable system – turned off and unplugged.

In SQL 2008, Microsoft added an interesting new function PWDCOMPARE. The idea was to provide DBAs with the ability to check if existing passwords were too simple, then require them to be changed. I was immediately concerned after reading about this. Researching the topic for this post actually alleviated most of my concerns, but there are a few things to know.

Function Explanation
PWDCOMPARE works by comparing a provided plain-text password against a password hash, both of which are provided as parameters to the function. If the result returns a value of 1, the password matches, while a value of 0 means the password is incorrect. You have to type the password exactly to get a match, that doesn’t seem so dangerous, right?

,[Pwd] = 'App!3'
,[IsMatch] = PWDCOMPARE('App!3',password_hash)
FROM sys.sql_logins

All users have access to query sys.sql_logins. Also, by default, they can see their own login as well as the SA account. We all (should) know that best practice is to disable and rename the SA account. (Just knowing it’s the right thing to do isn’t enough though, you should also be doing it!) Renaming the account doesn’t help here; users can still see the account with principal_id = 1, regardless of what it’s called and whether or not it’s disabled.

Now let me walk you through my original concern.

Curious User George logs in and performs a query on sys.sql_logins. He notices that the SA account is visible and someone accidentally left it enabled. Curious User George happens also to be Malicious User George. He sees a password_hash column in his query results. Here’s where SQL redeems some security though. If a user does not have the permission CONTROL SERVER, this field is left NULL. George cannot even see his own password hash.


When George attempts to use PWDCOMPARE, the result is also NULL. Crisis averted, basic users cannot decrypt passwords this way.


However, all those elevated users that should not exist, but do, in small companies? They have the power. They already have all the power, so this happens to be one of the smallest concerns.

MSDN explicitly states that “The PWDCOMPARE function is not a threat against the strength of password hashes because the same test could be performed by trying to log in using the password provided as the first parameter.”

However, if you try to login with a bad password, you should get an alert. I’m assuming you are auditing failed logins? If you try to do it repeatedly, you’ll be locked out of the account. Again, assuming routine security practices are in effect. If you compare a billion passwords using PWDCOMPARE, no alerts are fired, no one gets locked out. Even if it takes forever. How is that the same thing?

I created a temp table in six seconds that contained a million randomly generated passwords. It took 12-15 minutes to compare seven million rows and return the one password that matched. This really isn’t a viable method due to the time it takes for anything other than comparing some common passwords. But then again, all it took was a process running for a fraction of my day on a small development box. A targeted list will have significantly better results than using random GUIDs like in my below example.

Create a temp table with a million random passwords (no not reasonable passwords, just passwords)

FROM sys.all_columns a
CROSS JOIN sys.all_columns b
SELECT TOP 1000000
INTO #Password

Find weak passwords; provide script to force users to change
This script will provide you with scripts that will force users with discovered passwords to change their password when next logging in. It’s still up to you to educate them on better password policies though.

	,[IsMatch] = PWDCOMPARE(Pass,password_hash)
	,[ForceChange] = 'ALTER LOGIN ' + QUOTENAME(name) + '
FROM sys.sql_logins
	CROSS JOIN #Password
WHERE PWDCOMPARE(Pass,password_hash)=1

Hashing Concerns
My friend, integgroll, is a Penetration Tester. As suggestive as that title is, he is essentially an Ethical Hacker. When I asked him about cracking passwords, he provided me with a lot of interesting information. There are quality, free programs out there that will force a password in less time and with less effort than using PWDCOMPARE, and without using SQL Server to even test against. Such a test/attack is especially effective if you use a Mask Attack (targeted list) instead of just a simple Brute Force Attack (every possible combination).

SQL Server uses the twelve-year-old encryption method of SHA-512, which is slowly being broken through collision attacks. SQL appends a 32-bit salt to complicate the hashed password. However, my friend pointed out that salting does not mean that passwords are suddenly unable to be cracked, it just takes more time. With a salted hash, he estimated it would only increase the time required to force the password by around 20%. Not too bad when most users follow a password pattern and you can potentially test 414 million combinations a second if you have a quality graphics card to boost Hashcat’s performance.

With sufficient permission, one could viably steal any hashed password this way. Just another reason to stick to Windows Logins. Windows logins do not display at all when using sys.sql_logins. They only return by using sys.syslogins, which still has a password field, but this is left NULL for windows accounts, keeping the hash secure (at least from SQL).

I have yet to rationalize why we need to see a password hash at all. So long as there are password complexity requirements, I’d rather keep the chances for stealing and cracking passwords to a minimum, meaning that I’d rather not be able to see a plain-text or even a hashed password of any user.

EDIT: It was pointed out to me, and then I immediately remembered I’ve done it many times in the past, that password hashes let us script out logins without knowing the original password. I didn’t think about that while posting this, probably because I was so caught up in the disturbingly simple methods of password cracking.

The most important thing you can do here is improve password strength. Decrypting a hashed password is disturbingly easy and quick, but each digit you add to a password exponentially increases the cracking time required.


  • Implement a minimum password
    • Eight digits is too short for a reasonably secure password, more is better
  • Do not have a maximum length (or at least make it huge)
    • If users want a 75 digit password, let them. Their password will be more secure than your admin account.
  • Allow special characters and numbers
    • This substantially increases possibilities and thus increases cracking time, anytime I see policies which still only allow alpha character, I get angry or depressed – depending on how much energy I have.
  • Do not advertise your password policy on failed login or password creation
    • Displaying the policy is convenient for users, but it also lets hackers know how they can structure their Mask Attack
    • Controversial and may not really help vs. a determined Mask Attacker


  • Every digit added to your password makes it exponentially harder to crack
  • Do not follow common patterns!
    • Timothy1950 is a common password pattern. Most passwords only contain a capital letter for the first value. Mix things up, don’t end with a year. Even changing the password to 19Tim50othy makes a Mask Attack a lot more time-consuming.
  • Do not share passwords between accounts
    • If one is cracked, hackers normally test other popular websites
    • Even if you follow a pattern, they now have a very powerful Mask Attack to use against you – changing a number or two is easier than guessing 15 characters.

Windows Server 2012 Training Summary

Last week I attended a four-day training session titled Windows Server 2012 R2 Capabilities, Administration and Support. The class consisted of nine modules with nine labs. While this class was primarily directed at Windows Administrators to provide a general overview of the operating system, it did provide a few great snippets of information, but also a few terrible ones. I’ll try to keep this as brief and yet informative as possible…I’m sorry about the wall of text.

Day 1

The first day was an overview of new and exciting features in Server 2012 followed by an introduction to PowerShell administration.

This is the most important day of class for someone new to Server 2012. The first half of the day introduced the new layout of Server 2012. It’s the same design as Windows 8, so it is very daunting to the average person who hates 8. The instructor spent awhile explaining how to avoid the start screen and how to find programs and pin them to the desktop or taskbar.

A lot of time was spent looking at the new Server Manager. I want to use Server 2012 exclusively just for that dashboard. It’s that awesome. Besides looking cool, most important configurations are right there; no need to hunt all over the system.


Remote administration is great in Sever 2012. Now you can create Server Groups and add servers to those groups using Server Manager. You don’t have to log into every box or use complex PowerShell statements. It’s a bit like having a Central Management Server in SQL Server. I don’t know all the limitations yet, but I plan to explore soon.

Interesting note: If you promote a server to a Domain Controller using the GUI, everything works normally. If you promote via PowerShell, Server Manager will forever have a task assigned asking you to promote to a DC. Supposedly this is fixed for Windows 10, but it is a low priority fix never going to happen in Server 2012.

The PowerShell module was little more than an introduction on how to open PowerShell and use Get-Help. Throughout the rest of the class, scripts were provided to complete tasks as an alternative to the GUI, but learning PowerShell takes more than one or two rushed hours. I pitied anyone there who had never touched PowerShell; I’m sure all it did was reinforce their idea that PowerShell is too complicated to learn.

My one piece of advice is that you triple check that you are on the correct machine before you run a PowerShell script! To my own embarrassment, I admit that I had to troubleshoot and repair configurations more than once because I wasn’t paying attention to which Virtual Machine I was playing on.

Day 2

The next two days were mostly a blur of features that I will probably never touch again, except perhaps in lab scenarios. I’ll briefly mention a few things.

One module consisted of Hyper V setup and administration. All the virtual machines used Hyper V, and the class started feeling like a marketing ploy to convert your existing environment to Hyper V. The feature looks nice; I just wish my home processor supported SLAT so I could run it. I’ll stick with Virtual Box till I upgrade hardware.

Also mentioned today were ISCSI Storage, Data Deduplication – this sounds useful for reducing storage sizes, Work Folders, Dynamic Access Control, and Offload Data Transfer. The instructor skipped a section on DAC Management.

Labs consisted of joining to a domain, setting up a SCSI disk, preparing virtual machines for SAN storage using Storage Roles, creating an ISCSI target, and connecting to the ISCSI target. There was mention that two of the virtual machines will be clustered later in the week. Cool!

If you are interested in working with Server Core, but are afraid to make the jump, you can install the GUI, configure the server, and then uninstall the GUI. Optionally you can leave Server Manager available or go straight to the minimal core setup and just do your administration remotely. Sounds pretty cool, and I plan to play with this feature in home labs too.

Day 3

Active Directory & Networking were the topics for the third day of class. Another day of topics that were mostly over my head and that I would not have access to in my regular role.

A few notes:

  • No support for Domain controllers in a live environment for virtualization.
  • Prior to 2012, restarting or restoring a DC could cause failure when the RID pool would issue RIDs below the pool amount from the rest of the DCs. Server 2012 now validates the pool before issuing RIDs.
  • Recycle Bin must be enabled for recoverable objects in AD
  • New or upgraded features: DHCP Failover, Policy based admin, PowerShell DHCP module, DHCP integration with DNS
  • IPAM: new feature that doesn’t work that well. It works till it breaks, then you are doomed. Manages IP addresses, domain names, and device identities (instead of using something like Excel)
  • DCPromo is deprecated, but still available through PowerShell. Evidently the Microsoft instructor still prefers using deprecated features. Curious.
  • In 2012, you can restore an AD user using “Restore To” to eliminate the issue of having to restore the entire structure of an old user, this will let you put them anywhere without issue.

Now for the crazy scenario of Day 3…

While setting up new accounts using Active Directory Administrative Center, the instructor was apparently showing user passwords in clear text. Someone pointed this out, and the instructor began a VERY long discussion on why this is not a security issue. He argued that administrators should be able to see any account’s password if they created it. After pressing that this is a major security hole, he spoke with an AD Admin he knew. Finally he realized that the field in question was actually the user account field, he was incorrectly typing the password there. Crisis averted…but the fallout remains.

WHY would a Microsoft employee defend the idea that clear text passwords are not a security flaw? This still is very concerning to me. In this hypothetical situation, I don’t care if you audit logins; if the admins steal user passwords, that they can plainly see, it would be tracked, sure, but we know people share passwords with other accounts even if it’s a horrible security practice. Why would you make it so easy for an admin to possibly gain access to someone’s email, utility logins, or bank accounts? I could write a whole, very long blog about this, so I will move on.

Rushing to finish early on Friday, we covered three modules on Thursday, the last one being Failover Clustering. I had been waiting all week for this one! Too bad it was marred by marketing lies.

First, the cool aspects. We got to build a failover cluster using virtual machines. However, it went perfectly, so obviously this was not a real world scenario! Nothing much to report about the cluster creation, but if you have made few or no clusters, this part of the class is fun.

A few “new” features:

New in Server 2012! One node clusters! Yet in 2005…

New in Server 2012! Split Brain scenarios gone! Quorums have been around for a while.

Day 4

The last day was a blur of 80 slides smashed into about an hour, and then a lab that was demoed to us with the hopes that we would work along with the instructor. Lots of rush to get us out the door on the last day. Not much I can say about the day, because I did not have a lot of time to write notes or process the information.

Remote Desktop Services was the topic of the day. This can be installed via Server Manager, and then administered from the same spot. Weird caveat here is that everything grayed out is installed, while anything highlighted in green has not been deployed. The appearance is a bit misleading.


Major issues throughout the class were out-of-date lab documentation, duplicate labs, typos, and generally bad directions. For instance, the written instructions directed us to use the laptop has a host machine, but in reality we should have used one of the virtual machines. Another time, an earlier lab invalidated the directions for a later lab. We had to undo our earlier work (once the instructor realized the error) so that the later lab could be completed. The three huge hard copy manuals were even further out of date than the electronic copies. They were nothing more than a massive waste of paper.

The first day of the class is great for someone who is scared to work with Server 2012. It should dispel the fears of a new layout and excite you for all the new features at your fingertips. Beyond that, the benefit of the class is limited to those who would be dealing with every feature on a day-to-day basis. While I appreciate knowing a bit more of server administration, I feel that reading a few articles and then working in a 2012 virtual environment for a few hours would have been just as effective, if not more so.

T-SQL Tuesday #63: How do you Manage Security


This is my first attempt at contributing to T-SQL Tuesday. This month’s topic is hosted by Kenneth Fisher (blog | twitter) with the subject of security.



Security is one of those subjects that most DBAs have to deal with regardless of specialty. So as something we all have to work with at some point or another what are some tips you’d like to share? What’s the best security design? You’ve picked up a legacy system and the security is awful, how do you fix it? Any great tools out there you’d like to share? Hate it or love it I’m betting we all have something to say.

Security is an important subject that can be addressed on countless levels. I’m going to focus on an incredible way to monitor the security settings on any number of servers. This is a topic that I think is very poorly documented, very powerful, and largely ignored by Microsoft and the SQL Server Community. Today’s topic will be Policy Based Management.

Policy Based Management was introduced in SQL Server 2008, and since then has seen very little support. There is basically one book over the topic that only skims the surface over the myriad of options that it provides.  While the book explains the idea very well, there remains very little documentation over what exactly PBM can do.

Basic policy creation is very simple. The problem is wading through the dozens of facets for a condition (what piece of the server the policy is checking) and creating a valid target to evaluate against.


The Policy Creation Window appears simple at first

The difficult part of policy based management is deciding which facet you should use for your condition. Microsoft provides many facets, each with many drop down options. You can stack boolean operators to combine a few fields from one facet, but you cannot evaluate more than one facet in a single policy.


Creating a condition to check that each database is using the default SA account, please rename your account!

Once you have your condition created, you can create a specific target or just evaluate against everything within the facet. Target conditions are grouped together by facet, so if you have a dozen target conditions created for one facet, then create a policy using another facet and are suddenly distressed that your target conditions are missing, remember that targets are unique to a facet.


Policy creation with a valid condition and target selected

Once your policy is created, you can evaluate it against a single server, or use a CMS to evaluate against a selection of servers. That is the real purpose for policy based management. Create a handful of policies on your CMS, then evaluate them all targeting a group of servers. In moments you have verified that your security is correct for all your servers!


Policy evaluation against a single server on all databases

The way that most of my policies are created is through Advanced Edit. This allows you to perform an ExecuteSQL or WMI query targeting anything you can script out for evaluation. The downside of this is that SQL Server will not be able to automatically correct failed policies, but I rarely use that feature. By using Advanced Edit, you can essentially combine facets, and you don’t have to hunt through each facet for the one field you need. It’s much faster, and you can easily convert existing jobs or scripts into a policy by modifying them to return an expected numeric or string value. Below I’ve used an ExecuteSQL statement to verify that each database owner is the default SA account (which you should rename in your real environment) and that the account is disabled. Using existing facets, this would have taken two facets and thus two policies. If  you have figured out a way to do this in a single, built-in facet, please feel free to correct me, but even if you do, creating an ExecuteSQL statement was faster I bet!


Advanced Edit is faster and easier than guessing which facet to use

Currently we use over 700 security policies targeting over a hundred servers. It took forever to make, but audits are a breeze now since we have instant verification of security policies and a history of how often they have been met. Most of the policies are ExecuteSQL statements verifying SQL settings (including Availability Groups, Mirroring, Drive Space, etc.) and Windows settings. We also use Enterprise Policy Management Framework to provide easily read reports and statistics on all of our policies. I highly suggest you take the time to look into this project if you use Policy Management.