Using Not Exists to get Noncompliant Servers

Sometimes you need to perform a check against all servers to verify settings are correct. There are many ways to do this, but in some cases, you just need an idea of how many servers have “fallen through the cracks”.  Probably the fastest way to check all servers is to use a multi-server query through a Central Management Server, and using NOT EXISTS with a subquery will get those wayward servers.

In this scenario, I wanted to verify every nonsystem database was using the Full Recovery Model. I knew there were a few out there that were in Simple, and I needed to provide a list of each of those. The tsql to get this isn’t too hard.

SELECT
     [DatabaseName] = name
    ,recovery_model_desc
FROM sys.databases
WHERE recovery_model_desc = 'SIMPLE' AND database_id > 4

The results include the Server Name since it was a multi-server query. (Told you it was easy)

GetRecoveryModel

Great, I have a list of the offending databases. The next thing I did was create a policy that’s evaluated daily to ensure that all the new and existing databases continued being in Full Recovery. It’s a very easy condition to make. Policy-Based Management can be a daunting feature to get into with SQL Server, as there is not much documentation out there, but my link above will explain some of the basics for setting up a policy to get you started.

FullRecoveryCondition

However, the next issue to tackle was that I needed a list of all the servers that were missing a log backup job. Those needed to be catalogued and subsequently fixed. This is where it got a bit tricky. Using a CMS, I could get all the servers with the job very quickly, but what a pain that would be to filter and sort manually. I thought about it for a few moments, and came up with a great solution, using the NOT EXISTS clause. I’ve probably only used this clause a handful of times in the past, but this seemed like a perfect application.

SELECT [LogBackupJob] = 'Missing'
WHERE NOT EXISTS
(SELECT *
FROM msdb.dbo.sysjobs
WHERE name LIKE 'BackupLog%'

LogBackupsMissing

Again, the CMS displays the Server Name in the results, so I just added a static column to the select clause so that when I copied the results into another file, it would still make sense.

One thing to mention about NOT EXISTS. The select statement inside doesn’t matter. You can do Select *, Select 1, Select 50/0. It doesnt’ matter. In short, the select is not evaluated, the statement is just checking for existing records. There are a few quality articles out there explaining the evaluation process, or lack thereof, but they can be hard to find.

The applications for NOT EXISTS in retrieving a list of servers missing a feature are limitless, and now that I realized how well it works, I’ll likely be using it a lot more in the future. Security compliance, maintenance, and more can be monitored quickly to find offending instances.

Advertisements

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.

Policy Based Management – Tricks & Tips

In continuation of my original post for TSQL Tuesday on the benefits and issues with Policy Based Management, I’d like to discuss options and suggestions for a basic, yet powerful, policy for evaluating the last backup date of each database. While this policy sounds simple enough, successfully evaluating it against every server in your environment may not be so easy. Each server might have special rules, and if you use high availability, you have an even bigger mess to deal with.

The first step is to create a blank policy, which I covered in detail in my original post. To verify a last backup, we need to use the Database Facet. This will cause the policy to evaluate against all databases by default, with some exceptions – see Targets below, but that can be altered per your whims, which I will address at the end.

Once the Create Policy dialogue box is open, we need to create a detailed condition for evaluation. This is going to require the easy-to-use but hard-to-figure-out-which-one-you-really-need built in fields, as well as some specialized ExecuteSql statements to make a robust policy.

Starting off, we only want to check the backups on databases that are in a normal state. We do this by selecting the Field, Operator, and Value of @Status = Normal. Simple enough, but now let’s dive into the trickier parts.

CreateCondition

The meat of the policy are the next two steps. Select AND @LastBackupDate = DATEADD(‘DAY’,-1,GETDATE()). Immediately create another line OR LastDifferentialBackupDate = DATEADD(‘DAY’,-1,GETDATE()). Note that unlike the normal sql script, you need single quotes around DAY. The Condition will error and remind you about this if you forget though. These expressions will check that a full or differential backup has been taken within the last 24 hours. Modify the date to fit your needs and remove the differential test if you never use them. You can then group the full and differential backup clauses together, and insert another clause to verify that a full backup has been taken within the last week. You can try using DATEADD(‘WEEK’) and that will not even error, however, it does not evaluate successfully. Stick with days and make your life easier.  Getting comfortable making grouped clauses is an imperative to successfully implementing complicated policies and this one ensures that some form of backup has been done every day, but still verifies that a full backup was done at least each week.

AddBackUpExpressions

If you need to apply different rules for specific databases or instances, you can do that too, but it won’t be fun. Create an ExecuteSQL statement that will test the Database name using @Name = ‘YourDatabase’ or create a custom field using ExecuteSql(‘String’, ‘SELECT SERVERPROPERTY(”InstanceName”)’) = ‘InstanceNotToBackup’. You can also evaluate based on database size if necessary. Perhaps you only perform full backups nightly on databases that are less than 100g in size, while larger databases only receive a differential backup. You can use the @Size Field (which is in megabytes) to check this value. Perform some grouping and you have a targeted policy.

Troubleshooting ExecuteSql If you have any problems with your ExecuteSql statements, test them by running the query in SSMS without the ExecuteSql wrapping. Get the query working first, then move onto evaluating the ExecuteSql string in the policy. When you write statements this way, don’t forget to add double quotes to your query since it will reside in a string when evaluated from the policy.

Mirroring By default, policies target Every Database. However, this is a lie. Policy Based Management actually targets every non system database that is accessible. This means that Mirroring takes care of itself. It will only evaulate against principal databases. The secondaries will not show up, and good luck trying to convince a policy to run against them. Even if you expicitly tell a database target to look at restoring or databases using mirroring, it will still skip them.

Availability Groups Since Avaibility Groups and their corresponding views did not exist prior to SQL2012, they provide a problem when you query a variety of SQL Server versions. In a multi-server query, you deal with this issue by first using the test: IF(SERVERPROPERTY('IsHadrEnabled')=1)

You’ll need to include that line in the condition to test AG queries successfully, so add it to your ExecuteSql string. If you forget, you will pay the price whenever you evaluate the policy against an older version of SQL.

Hadr

In verions prior to 2012, SQL cannot use sys.fn_hadr_backup_is_preferred_replica because it does not exist. Using an IF statement will let you bypass this error.

If you perform your backups on a preferred replica, you can use the following expression, evaluating to equal two. This passes the policy for non preferred replicas.

ExecuteSql('Numeric','
IF(SERVERPROPERTY(''IsHadrEnabled'')=1)
BEGIN
'SELECT sys.fn_hadr_backup_is_preferred_replica(DB_NAME())
END')

Perhaps you don’t care about preferred replicas though; you have no performance issues ever and live on the wild side – always performing your backups on the primary replica. You can allow all secondaries to automatically pass using the below condition, still checking for value two.

ExecuteSql('Numeric','
IF(SERVERPROPERTY(''IsHadrEnabled'')=1)
BEGIN
SELECT a.role FROM sys.dm_hadr_availability_replica_states a WHERE is_local = 1
END')

This all hinges on you having readable secondaries for your AGs, if you do not, the policy will skip over them regardless since the databases are not accessible.

AddAGExpression

Grouping the backup expressions again ensures they function properly

Arrays All the above conditions assume that you want to see every database, even if you are automatically passing them. Well, obviously TempDB is never going to be backed up, so we need to provide an expression for that, luckily this is simple and done via the @Name Field with the Value ‘Tempdb‘. If you want to exclude other databases, you can do so using the Operator IN, but if you think this works exactly like SQL and write (‘DB1′,’DB2’), it will never work. After much frustration, I realized that you need to set the value to Array(‘DB1′,’DB2’). Without the word Array, the IN clause will not evaluate correctly. Aren’t you glad I saved you all that frustration?

Test

I used an array to demonstrate how they are used, not because it was necessary for one value.

Targets Finally, you have the option of including all of these automatic passes not in the Condition you are checking, but in a custom Condition. Yes, it is very unfortunate that everything is a condition in PBM. So to make things easier, let’s call them Target Conditions and Evaluation Conditions. The same rules apply for creating an Evaluation Condition, but any databases that evaluate to True in the expression will be excluded from the evaluation results entirely. The main thing you need to be concious of is that when evaluating on the Database Facet, the default Every condition is false. It does not evaluate against nonaccessible databases (as I mentioned with Mirroring), and it does not target System databases. If you really want everything to be targeted, you need to create a Every Database Target Condition. This will seem rather redundant, but it works, and without explicitly stating you want both System and NonSystem objects, the target will try to do as little work as possible. It’s lazier than I am.

Don't even try making this a one line array, conditions hate that if you use standard fields.

Don’t even try making this a one line array, conditions hate that if you use standard fields.

Testing and Evaluation I’ve already mentiond that you should test ExecuteSql statements by running the query seperately. You’ll still have to do a lot of grouping testing and evaluating to get a complicated policy to work the way you want. When you evaluate a new policy, always view the results on a few databases and servers to verify that the actual results are in line with what your expected results are. If you get a bad statement, you might not be getting any actual results at all!

Test, test, test new policies!

Test, test, test new policies!

Automatic Passes vs. Targets For most policies I like to see every server and automatically pass the exceptions, hence why I presented conditions with so many automatic passes. Evaluating like this allows me to quickly determine that the server count is correct. It also quells the fear whenever anyone new evaluates a policy. Normally when someone notices a database is not evaluating, alarms go off in their head that the policy is broken and thus that it has never worked properly before. It can be quite a chore to calm them down and explain that was the intended result. That being said, all the Evaluation Conditions can be moved into the Target conditions instead, removing any unwanted results. You know TempDb is never going to be backed up, so moving it to the Target condition is of no consequence.

The main point to be stressed about Policy Management is that it is a powerful feature, but it does require you to learn how to use it through trial and error.

Remote Registry Read/Write

Eventually you will need to verify registry keys on a list of computers, whether for security reasons, or some project. I find navigating the registry editor very tedious because of the sheer number of files that must be waded through. The endless collapsing and expanding…it’s just dreadful. Getting lost in a huge subfolder is one of my biggest annoyances. Registry read issues are further compounded when you have to deal with multiple computers. I find it much easier, and faster, to read and set values across many computers all at once using TSQL, or better yet, PowerShell.

SQL Server has a way to query and set values in the registry, although you may not realize it. This isn’t something everyone has to every day, but when you do, you want an easy way to handle it. The TSQL commands are actually relatively simple. XP_REGREAD and XP_REGWRITE can solve your read/write woes, but with limitations.

For all examples, I’ll be reading and writing values for UAC, which can be located in the registry under HKEY_LOCAL_MACHINE at Software\Microsoft\Windows\CurrentVersion\Policies\System controlled by the value EnableLUA. A value of 1 means UAC is On, while a value of 0 means UAC is Off.

To read a registry value using TSQL, run the following command:

EXECUTE MASTER.dbo.xp_regread
'HKEY_LOCAL_MACHINE' --HKEY directory
,'Software\Microsoft\Windows\CurrentVersion\Policies\System' --Registry Path
,'EnableLUA' --Registry Key

To set UAC On using TSQL, run the following command: (Set the last value to ‘0’ to turn UAC Off)

EXECUTE MASTER.dbo.xp_regwrite
'HKEY_LOCAL_MACHINE'
,'Software\Microsoft\Windows\CurrentVersion\Policies\System'
,'EnableLUA'
,'reg_sz' --Other options include reg_binary and reg_dword
,'1' --Key Value to set, in this case it would be 0 or 1

You may notice that the Results Pane indicates that 0 rows are affected. It made me angry when I tried to build these examples, but SQL Server is a liar here. Read the value of the registry key once again, and you will see that the value did update to your desired value. Flip the value on and off if you really don’t believe me. The problem with using TSQL lies in the limitations of SQL Server. Obviously you cannot read/write registry values of computers that do not have SQL running on them, and this includes passive nodes. How about PowerShell as a better solution?

PowerShell is a skill that is becoming more necessary as environments grow. I would argue that anytime you need to touch more than one computer, you should consider a remote solution using PowerShell. Plus, reading and writing to the registry just feels right when working through PowerShell.

The simple way of querying a registry value in PowerShell is as follows:

$Path = 'HKLM:Software\Microsoft\Windows\CurrentVersion\Policies\System'
$Key = 'EnableLUA'
Get-ItemProperty -Path $Path -Name $Key

However, unlike most Cmdlets, this one does not accept a ComputerName Property. Probably because Microsoft hates us. You can use a slightly more complicated method to query a remote registry though.

$Computer = "YourComputerHere"
$Path = 'Software\Microsoft\Windows\CurrentVersion\Policies\System'
$Key = 'EnableLUA'
$Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine',$Computer)
$RegKey = $Reg.OpenSubKey($Path)
$Value = $RegKey.GetValue($Key)
Write-Host "$Computer $Value"

Please note that I did not include HKLM: in the $Path variable this time. If you leave that in, you’ll receive an error stating that “You cannot call a method on a null-valued expression.” Using this script does not require the HK as it only deals with Registry Paths. The original Cmdlet we used, Get-ItemProperty, can get the properties of files, so it needs the extra distinction.

Next, we will expand the script slightly to include a ForEach statement in order to query multiple computers at once.

$ComputerList = "Computer1","Computer2"
ForEach ($Computer in $ComputerList)
{
$Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine',$Computer)
$RegKey = $Reg.OpenSubKey($Path)
$Value = $RegKey.GetValue($Key)
Write-Host $Computer $Value
}

PowerShell includes an equally easy way to set the registry values, can you guess what the Cmdlet name is? Luckily that was a rhetorical question, and I will not make you answer in the comments. You can use Set-ItemProperty to update registry values. However, you are still limited to the local computer with this method. Let’s skip straight to the multi-server solution, as the baby steps are the same as those we took above with the Get statement.

$Newvalue = 1
ForEach ($Computer in $ComputerList)
{
$Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine',$Computer)
$RegKey= $Reg.OpenSubKey($Path,$True)
$RegKey.SetValue($Key,$NewValue,[Microsoft.Win32.RegistryValueKind]::String)
$Value = $RegKey.GetValue($Key)
Write-Host $Computer $Value
}

I keep these scripts in a repository so I can quickly verify Registry Keys across my environment. PowerShell is a perfect solution for reading and writing settings remotely. There’s no limit to the number of bells and whistles you can add to PowerShell scripts,(try adding a Try-Catch for unreachable servers) but the important thing is getting the work done without having to touch every computer, one at a time. Don’t work hard; work smart, so you can read more blogs.

Multi-Server Job Scripting Failure

Central Management Servers are probably the single most important thing in my SQL life right now. I manage a lot of servers, so having an organized way to keep track of them is paramount. Multi-Server Jobs are a great way to centralize jobs that need to be on all your boxes. If you are not familiar with the idea, I suggest you look into it if you have more than a handful of servers.

I was building a new CMS the other day and had to get dozens of Multi-Server Jobs transferred to the new instance. Some of the jobs have 30 steps, so using Object Explorer to script out the jobs was the obvious choice. However, I was immediately hit with an ambiguous error stating that SQL Server was “Unable to cast object of type ‘System.DBNull’ to type ‘System.String’.”

Test

My original error had another, equally unhelpful line.

Knowing that some object is Null doesn’t help very much. After browsing through the job steps, everything looked in order. Internet searches provided surprisingly little on this subject, and nothing I found really explained how it happens. I know I was going to be migrating a CMS again, and soon, so I wanted to know how to fix it. Surely it wasn’t just a bug in Management Studio? On to the test case!

First of all, you need to create a Multi-Server job. I’m assuming you already have a CMS set up, if not, you need one.
Right-click Multi-Server Jobs, create a new job, and then assign at least two targets for testing. I’m going to use the name TestJob for the rest of this scenario.

test

Make sure you have at least 2 target servers

Once TestJob is made, (it does not matter what it does; mine just performed SELECT @@VERSION) you need to run the job. Execute the following code:
msdb.dbo.sp_start_job 'TestJob' or just start the job using the GUI.

Test

Your job can fail, I don’t care. Just run it.

Now that you have some job history for 2 servers, let’s look at that history:

SELECT *
FROM msdb.dbo.sysjobservers js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE j.name = 'TestJob'

This will display one line per targeted server. This query is included for your own reference, we are concerned about the next one’s more targeted results. The query is checking your multi-server job history, specifically the server list, and comparing that to the servers that exist on the CMS. Right now, everything looks fine, and matches.

SELECT
[JobName] = j.Name
,[JobServerId] = js.Server_id
,[TargetServerId] = ts.Server_id
FROM msdb.dbo.sysjobservers js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.systargetservers ts ON ts.server_id = js.server_id
WHERE j.name = 'TestJob'

Test

Job History Server_Id matches Target Server_Id, awesome!

Again, at this point, all is well, and you can script out the multi-server job to your heart’s content. That would be fine if you saved a copy and never, ever changed the job while it sat on your CMS. Fast forward a few years though, and weird things may have happened. Let’s simulate a weird thing. I’m going to change the Server_Id in the history to an invalid value. That’s right, there are no constraints preventing me from doing this! This is going to simulate removing a server from the CMS after history already exists for it, or someone just going into your history and changing things because they hate you.

You will need to change the numeric values of the server_ids to match invalid and valid server_ids on your CMS, respectively.
UPDATE js
SET Server_id = 7
FROM msdb.dbo.sysjobservers js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE j.name = 'TestJob' and server_id = 5

Now we are going to rerun the script from earlier to verify the job history information.

SELECT
[JobName] = j.Name
,[JobServerId] = js.Server_id
,[TargetServerId] = ts.Server_id
FROM msdb.dbo.sysjobservers js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.systargetservers ts ON ts.server_id = js.server_id
WHERE j.name = 'TestJob'

Test

The history now has a server_id that doesn’t exist on the CMS

Great…now we have a null server. If you guessed this is a problem, you guessed correctly. If you try to script the job out now, you will receive a failure notification like the one at the top. Now that we have recreated the issue, we know how to fix it though!

Run the following code to find any null servers and just clean out that history. I’m going to assume if your server doesn’t exist, you don’t care about the history. I didn’t at least.

DELETE FROM js
FROM msdb.dbo.sysjobservers js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.systargetservers ts ON ts.server_id = js.server_id
WHERE j.name = 'TestJob' AND ts.server_id IS NULL

Finally, let’s look at the job history again, I’ve supplied the code for a third time, just in case you forgot it.

SELECT
[JobName] = j.Name
,[JobServerId] = js.Server_id
,[TargetServerId] = ts.Server_id
FROM msdb.dbo.sysjobservers js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.systargetservers ts ON ts.server_id = js.server_id
WHERE j.name = 'TestJob'

Test

Now we have clean history with no null values

If you try to script the job, it will work, and you can copy your multi-server job over to the new server.

Let’s take it a step further, and expand the script to check all the multi-server jobs. Notice that it is only looking at Category_Id=2, or Multi-Server jobs, and only those with invalid Server_Ids.

SELECT
[JobName] = j.Name
,[JobServerId] = js.Server_id
,[TargetServerId] = ts.Server_id
FROM msdb.dbo.sysjobservers js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.systargetservers ts ON ts.server_id = js.server_id
WHERE j.category_id = 2 AND ts.server_id IS NULL

Hopefully you got 0 results, and everything on your CMS is happy.
Even though I changed the Server_Id to recreate this scenario, the original problem occurred due to servers defecting from the CMS, but leaving behind their server history. Anytime a server defects, it needs to automatically remove the history from the sysjobservers table as well, otherwise people will continue to have this problem.

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.

SQL Version Mismatch

Quote

I came across a peculiar issue a few days ago while patching SQL Server. A security scan alleged that one of the SQL Servers was under patched, meaning it was missing the latest required hotfix. A colleague jumped on the problem and began researching.

Probably the first thing we do to verify that SQL Server is running the correct version is to do a simple

SELECT @@VERSION

This of course returns the current version that your server is patched to.

SQL 2014 Test Lab Query
InitialVersion
I apologize for the sparse lab screenshots, but I was unable to recreate this issue quickly in a lab.

Everything looked great from the query, it matched the version that all of the other boxes were running, and the server was reporting that it had the mandated hotfix. Obviously this was a case of a scan that has just failed to connect to the box. A network glitch causing a false positive? Not the first time, won’t be the last. However, we have to prove beyond a shadow of a doubt that everything is in order, so the next step was jumping on the box itself and investigating.

The hotfix was confirmed to be in the installed updates. For good measure, my colleague verified that the SQL Installation Manager agreed. Booting up the hotfix installation file revealed that the SQL Instance was not reporting that the hotfix was installed, nor would it let us install it, but the Shared Services were fine, showing the correct recent patch. Whoa, what’s going on here?

By this time, things were starting to look dicey, and my colleague’s shift was ending. There was talk of doing horrifying things the next day if nothing was resolved. Things like repairing the SQL Installation or even a full Reinstallation. I offered to take a look as a fresh pair of eyes, not expecting to find anything, but just to get up to date on what was going on and as a last-ditch effort before wiping the world clean.

I started poking around and realized that the SQL Shared Services were registering as a different version than the SQL Server Instance. Well, that sounded bad. I thought that if we at least matched those versions up, we could get somewhere after that. It was agreed that this couldn’t hurt anything, so I uninstalled the more recent patch on the Shared Services to match with what the SQL Instance should have been.

Still nothing.

Ok, obviously there is something very wrong now. There had to be something we were missing.

And there was. Something simple and obvious.

In defeat, I finally took the time to just stare at the Installation Manager. I took a few moments to really read the whole screen.

That’s when I saw it. There is a box detailing the installed version of each SQL component on the box, and somehow everyone who had touched it overlooked the message here. The SQL Instance was reporting an incomplete update. Of course it would not let us patch the hotfix because it did not have the Cumulative Update required! It refused to install the Cumulative Update because it was reporting that SQL Server RTM was installed. No hot fixes. No service pack. Nothing. What?? How did no one catch this!

SQL Update for SQL 2014 as reference
WindowsUpdate

Once I saw that, I immediately started a service pack installation, if only to verify that it would be accepted. The Installation Manager was more than happy to oblige now that I wasn’t skipping versions. The necessary cumulative updates and hot fixes went smoothly after that.

In summation, please remember to slow down and really assess the problem at hand. Don’t jump into a problem trying to fix it before you know what is going on and possibly make matters worse. SQL is very good at providing hints, or in this case, blatant answers to your problems. You just need to step back and know where to look. Yes, we all had done SQL installs before, and yes, we all knew about the version information. In the heat of the moment though, we all tried to fire first and ask questions later for something that appeared like a simple problem.

I do find it disconcerting that a SQL Instance would report a patch level of the Shared Instances instead of the instance itself through SELECT @@VERSION. I tried to recreate the scenario in a lab, but SQL responded with the correct version each time. Whatever caused the incomplete installation caused the version information to report back incorrectly through SQL.

Once Upon a Time I Tried to Blog…

Every good story starts with, “Once Upon a Time.” Thus, it makes sense that a blog would to too.  Isn’t a blog essentially just a collection of short stories? A good one should be entertaining at least, and short enough so people will read it.

I’ve been fighting with starting this thing for months, and I finally took the dive to create a site this week as a joint adventure with my friend who runs Olympic Admin. We are both in the process of getting twitters and blogs spun up. Should be a wild ride.

Two days of planning later, and I am ready to start. (This is actually very quick for me, as I plan everything out very carefully, from purchases, to conversations)  I would have been even quicker at this if I did not get home from work today with a migraine that drained all of my energy. Such is life though. Moving on.

I wanted to start a technical blog in order to force myself to research SQL heavily. It should also be a good way to improve my writing skills. I plan to throw in my own twisted sense of humor along with some ranting about general IT projects, and perhaps a few blogs about role playing games. Eventually I hope this builds up my confidence to try public speaking at the local user group, but that’s another goal for later.

Setting up this blog, however, has been a horrible pain of indecision. I’ll explain why.

I Want Power: I actually hosted my own website for a few years in high school and college, and I think that is precisely the reason why it has been so hard to work with the free version of WordPress. I’m not used to having limitations on site layout. I don’t like where those headers are, I’ll just rearrange them…except I cannot find any way to edit the page settings! Seems nothing but the basics are free on WordPress, and I really mean basics. I can change the text and it taunts me with the ability to change colors, only to then demand a hundred dollars in return for a predetermined set of colors. I can only imagine it would cost ten times more for the ability to actually customize my colors. While I appreciate the simplicity of creation, there is really nothing allowed for free. It almost feels like a “Pay to Win” game. If this blogging thing works out, I will have to upgrade quickly because I want the ability to determine the full layout of my site. I want all the power.

I’m Lazy: I’ve been procrastinating about creating a blog for months, if not a year. Although I understand the technical benefit of writing and having to deep dive topics in order to look semi competent, the time requirement has long been a major deterrence. I like my free time, and I’m horribly lazy, so having to keep to a schedule, make updates, and write did not seem like much fun. Hopefully, I can keep my posts light-hearted enough to keep myself entertained while writing. If I entertain anyone who happens to read this, that is just a bonus.

Blogging is for Emo Kids: I still have the lingering mentality that blogs are juvenile wastes of time. Originally most people had blogs to rant about how dreadful their lives were. Remember Xanga? I do, sadly. Blogs still leave a bad taste in my mouth because of that, even after reading professional blogs for so long. Although I know a technical blog shouldn’t be like that, I cannot help but worry that my “Everything Else” title will creep into that dangerous zone of whining and crying like a stuck up child about not having enough candy and the sun not shining bright enough and how the world just isn’t fair. I mean, I complain about everything already, but I’ll try to not sound Emo about it.

I’m Not an Expert: This is the other big issue that has kept me from blogging. I’ve been in SQL for a relatively short period of time. Although I learn quick and I am relatively smart, there are many more people out there much more skilled than I am, with dozens more years of experience. With all those SQL bloggers out there, what could I possibly contribute, and how can I do so without looking foolish when I am not a master in the subject? Well, I have to keep telling myself I am going to blog to help myself learn, and share my professional growth. I’m not doing it to provide amazing insights to others. If I happen to provide some useful information to someone out there, awesome. I’ll be very proud, but if my blog is just for me, that is ok too. I’m going to do my best to blog about less common topics though, things that I have had to work on that has had relatively little documentation while searching the internet for answers.

Summary: I’m apprehensive about starting a blog, but I think it could be a fun endeavor, and it will help me grow both as a writer, and as a SQL professional. Welcome to SQL Sanctum.