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
{
[Cmdletbinding()]
Param(
  [string[]]$ComputerName = $Env:COMPUTERNAME,
 [Parameter(Mandatory=$true)]
  [string]$Folder,
  [string]$Rights
)
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
}#EndForEach
}#EndProcess
}#EndFunction

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
{
[Cmdletbinding()]
Param(
  [string[]]$ComputerName = $env:COMPUTERNAME,
 [Parameter(Mandatory=$true)]
  [string]$Folder,
 [Parameter(Mandatory=$true)]
  [string]$Account,
  [string]$Access = "Allow",
  [string]$Right = "FullControl"
)
Process {
  $ComputerName|
  ForEach-Object {
  $Server = "$_"
  $Acl = Get-Acl "\\$Server\$Folder"
  $Acl.SetAccessRuleProtection($True,$False)
  $Rule = New-Object System.Security.AccessControl.FileSystemAccessRule("$Account","$Right","ContainerInherit,ObjectInherit","None","$Access")
  $Acl.AddAccessRule($Rule)
  Set-Acl "\\$Server\$Folder" $Acl
  Write-Verbose "Permission Set for \\$Server\$Folder"
}#EndForEach
}#EndProcess
}#EndFunction
 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.

Error_TokenBasedAuthentication

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
DECLARE @DBCount INT,@File VARCHAR(250)
SELECT  @DBCount = COUNT(*) FROM sys.databases WHERE database_id <> 2
SELECT  @File = ''\\BACKUPSERVER\BACKUP\''+CONVERT(VARCHAR(100),SERVERPROPERTY(''MachineName''))
--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)
SELECT 1
ELSE
SELECT 0
')

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.

FileSharing

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…

Oh…

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.

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?

SELECT
name
,password_hash
,is_disabled
,[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.

SelectNullPassSA

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

CompareNull

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.

PWDCOMPARE 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)

WITH Test AS
(
SELECT [ID] = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
)
SELECT TOP 1000000
ID,[Pass]=NEWID()
INTO #Password
FROM Test

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.

SELECT
	 name
	,Pass
	,[IsMatch] = PWDCOMPARE(Pass,password_hash)
	,[ForceChange] = 'ALTER LOGIN ' + QUOTENAME(name) + '
	 WITH PASSWORD = ''' + Pass + ''' MUST_CHANGE,CHECK_POLICY=ON'
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.

Administrators:

  • 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

Users:

  • 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.

ServerManager

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.

Summary

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.

Mirroring with TDE

Recently I’ve had to set up a lot of database mirroring, and about half of those databases were encrypted. Setting up mirroring on unencrypted databases is normally a quick process, but encrypted mirroring requires a bit more work. While there are many examples of mirroring out there, it can be hard to find an example of encrypted mirroring that is not missing a step or two. You cannot use Object Explorer to do everything, so below are the necessary scripts to not only set up encrypted mirroring, but also how to setup Transparent Data Encryption.

1. Create and Backup Master Key and Certificate TDE requires a master key and a certificate, so those need to be created first and then immediately backed up. Backing these up is best practice, but the files will also need to be restored to the secondary server. If you already have TDE on the primary server, you can skip this step and go straight to Step 3.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/********** On Principal **********/

--Create Master Key
 USE MASTER
 GO
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StongP@ssword'
 GO


--Backup Master Key
 BACKUP MASTER KEY TO FILE = 'FilePath\MasterKey.KEY'
 ENCRYPTION BY PASSWORD = 'StrongP@ssword'
 GO

--Create Certificate
 CREATE CERTIFICATE CertName
 WITH SUBJECT = 'CertDesc', EXPIRY_DATE = '1/1/2900'
 GO

--Backup Cert; using a different key name than the original to avoid errors
 BACKUP CERTIFICATE CertName
 TO FILE = 'FilePath\CertName.CER'
 WITH PRIVATE KEY (FILE='FilePath\CertKey.KEY',
 ENCRYPTION BY PASSWORD = 'StrongP@ssword')
 GO
 CLOSE MASTER KEY
 GO


2. Enable Transparent Data Encryption
 The next step is to enable TDE using the new certificate, and then turn TDE on. If TDE has been enabled previously, you should have already skipped to Step 3.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
--Enable TDE on database
USE DbName
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CertName
GO

--Turn TDE On
ALTER DATABASE DbName
SET ENCRYPTION ON
GO


3. Restore Key and Certificate to Secondary
This is your first step if you already have TDE turned on. Just make sure you copy the backup files to the secondary server so you can restore them.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
/********** ON MIRROR **********/

--Restore Master key to Secondary server
USE MASTER
GO
RESTORE MASTER KEY
FROM FILE = 'FilePath\MasterKey.KEY'
DECRYPTION BY PASSWORD = 'StrongP@ssword'
ENCRYPTION BY PASSWORD = 'StrongP@ssword'
GO

--Restore the Certificate to Secondary server
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongP@ssword'
GO
CREATE CERTIFICATE CertName
FROM FILE = 'FilePath\CertName.CER'
WITH PRIVATE KEY ( FILE = 'FilePath\CertKey.key',
DECRYPTION BY PASSWORD = 'StrongP@ssword')
GO
CLOSE MASTER KEY
GO


4. Create Endpoints on Principal and Secondary
You can do this at any time that makes sense to you, but it makes sense to me to ensure all the settings are up to date before moving onto the mirroring setup. Encrypted mirroring requires an encrypted endpoint (seems logical enough) so notice the specified encryption algorithm in the script below. Many other mirroring examples leave this critical piece out.

1
2
3
4
5
6
7
8
/********** ON MIRROR & Principal **********/

--Create Mirroring Endpoint on Principal & Secondary
CREATE ENDPOINT Name
STATE = STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER,ENCRYPTION=SUPPORTED ALGORITHM AES)
GO


5. Backup Database on Principal
You will need to perform a full backup along with a Log backup on the database you plan to mirror. There is nothing special about the backup, just make sure that you have the latest log backups and that you finish before the next scheduled backup starts, otherwise you will have to restore another log. I hate having to do that.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
/********** On Principal **********/

--Backup Primary Database - Full
BACKUP DATABASE [DbName]
TO DISK = 'FilePath\DbNameFULL.BAK'
WITH NOFORMAT,INIT,COMPRESSION,SKIP,NOREWIND,NOUNLOAD,STATS=10,
NAME = 'DbFullBackup'
GO

--Backup Primary Database - Log
BACKUP LOG [DbName]
TO DISK = 'FilePath\DbNameLOG.TRN'
WITH NOFORMAT,INIT,COMPRESSION,SKIP,NOREWIND,NOUNLOAD,STATS=10,
NAME = 'DbLogBackup'
GO


6. Restore Backup to Secondary
Restoring the backups for an encrypted database is only slightly more involved than normal. You will have to Open the Master Key before the restore, otherwise it will fail.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
/********** ON MIRROR **********/

--Restore the Full Backup on the Secondary
USE MASTER
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongP@ssword' --Don't forget this!
RESTORE DATABASE [DbName]
FROM DISK = 'FilePath\DbNameFULL.BAK'
WITH NORECOVERY,NOUNLOAD,STATS=10
GO

--Restore the Log Backup on the Secondary
RESTORE LOG [DbName]
FROM DISK = 'FileName\DbNameLOG.TRN'
WITH NORECOVERY,NOUNLOAD,STATS=10
GO
CLOSE MASTER KEY --Remember to close the master key once complete
GO


7. Enable Mirroring on Secondary
 Once the database is in place, you may want to verify that the secondary database is in a Restoring state. This is required to mirror successfully. The scripts I’ve provided will restore correctly, but restoring the database improperly is one of the most common and embarrassing issues you’ll encounter while trying to turn on mirroring. Set the partner by using the fully qualified server name or by the IP address. Theoretically either will work, but it depends on your network.

The most important thing in the script below is to include the line to Add Encryption by Service. This can be done earlier and in a separate script, but I find it easiest to include while turning on mirroring. If you leave this step out, you’ll receive a misleading error after you run the script in Step 8 that will leave you depressed and frustrated because it won’t tell you anything useful. Something like:

Msg 1416, Level 16, State 31
Database "DbName" is not configured for database mirroring

So make sure you run the scripts with all the necessary lines!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
/********** ON MIRROR **********/

--Enable Encrypted Mirroring on Secondary
USE MASTER
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongP@ssword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY  --Most Important step!!
ALTER DATABASE DbName
SET PARTNER = 'TCP://PrincipalServer:5022'
GO
CLOSE MASTER KEY
GO


8. Enable Mirroring on Principal
This is the moment of truth. Did you follow all the steps correctly? If so, you should be fine. If you receive a database mirroring is not configured error, refer back to Step 7 and verify that encryption was added when you enabled mirroring. There is also a chance you will need to do another log restore to bring the log chain up to date. That’s purely based on how often you do log backups and how big the database is though.

1
2
3
4
5
6
7
8
/********** On Principal **********/

--Enable Encrypted Mirroring on Principal
USE MASTER
GO
ALTER DATABASE DbName
SET PARTNER = 'TCP://SecondaryServer:5022'
GO


Summary
Setting up Encrypted Mirroring is not overly difficult, but there are some quirks to it, and you can easily forget an integral step. I always refer to saved scripts to keep track of where I am in the process, and to make the overall setup faster.

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

TSQL2sDay150x150_388014A5

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.

Test

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.

Test

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.

Test

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!

Test

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!

Test

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.