Look for Server “Ouches” – By Collecting Server Logs

At some point, most parents have asked their child to “show me where it hurts”. Perhaps a parent asks this question because their child has just yelled “ouch” or maybe the child seems to not be himself (i.e. lethargic). And the reason parents do this is because they want to help their child get better and catch any problem as early as possible.

In a similar way, we need to know when our servers are yelling “ouch” or don’t seem to be themselves. We can do this by examining the event logs, which basically is like the server talking to us, and by baselining the performance of our server.

Now, the easiest way to do both of these things is if we have some sort of monitoring software. Typically, monitoring software will examine server logs and baseline servers, and will alert us to any issues. This is the ideal situation …

However, what if we don’t have any monitoring software or we don’t have the monitoring software on all our servers. Well, then we may have to build our own process. And in this post, I am going to examine how to look for “ouches” in our server’s event logs.

How to read a server’s event logs?

First, I am going to acknowledge that you could go to each server and examine the event logs, but that seems like a horrible idea to me. Although, I have heard of some DBA’s doing that, the problem is that this method is not scalable. If you have one or two servers, ok, it could work. But, if you have hundreds of servers, it will never work.

So, that is where automation comes in and using PowerShell is my tool of choice. I am going to demonstrate some ways to read a server’s event logs.

Method 1: Use Get-EventLog (https://technet.microsoft.com/en-us/library/hh849834.aspx)

Get-EventLog -ComputerName $server -LogName “Application” -EntryType Error,Warning -After $logdate

Method 2: Use Get-WinEvent (https://technet.microsoft.com/en-us/library/hh849682.aspx)

Get-WinEvent -FilterHashtable @{LogName=’Application’; Level=1,2,3; StartTime=$logdate} -ComputerName $server

 As you can see, it is pretty simple to read a server’s event logs. Now, all you have to do is read the data into a table and report on it daily. This will also help you to keep a history of the “ouches”, in case you need it. But, there are some things to keep in mind:

  • For the best performance, filter the amount of records returned by the command by using the parameters EntryType when using Get-EventLog and/or Level when using Get-WinEvent. This will perform much better than piping the command to an output, which will read all the records, and then using a where clause for filtering. In my deployment, since I am looking for “ouches”, I only care about messages that are: critical, error, and warnings.
  • Get-EventLog is the older command and can’t read some of the newer event logs from Windows Vista and later. So, it is recommended to use Get-WinEvent for newer operating systems.
  • Get-WinEvent also lists event logs and event log providers. You can get events from selected logs or from logs generated by selected event providers. And, you can combine events from multiple sources in a single command. Get-WinEvent allows you to filter events by using XPath queries, structured XML queries, and simplified hash-table queries.

 

So, although there are multiple ways to look for “ouches” on a server, I always recommend a monitoring software solution or an automated process that can easily be built with PowerShell.

Posted in DBA | Tagged | 1 Comment

SQL PASS 2015 – Watch Portions Online

I just wanted to give a reminder that portions of last week’s PASS Summit 2015 can be watched online (http://www.sqlpass.org/summit/2015/Live.aspx).  Personally, I found the Day 2 keynote on the Internet of Things (IOT, https://en.wikipedia.org/wiki/Internet_of_Things) very interesting.  It is one of those buzz words we are hearing everywhere now, and it is good as a data professionals to know how that term fits in or will affect us.

Posted in Professional Networking, Training | Tagged | Leave a comment

Monitoring AlwaysOn

Just recently, I violated one of my guiding work principles as a DBA … Be Proactive, Not Reactive … What this principle means to me, is that I want to try to anticipate problems arising and fix them, rather than not noticing them until they bloom into a full-fledged issue that has a greater impact on my users.

Here was the situation … It was just after a maintenance window, and there were some infrastructure issues. I probably could stop the story there, but I will continue … On one of my servers, I am using AlwaysOn (https://msdn.microsoft.com/en-us/library/ff877884.aspx) and my disk space monitoring process noticed rapid growth on the primary replica with one of the database’s log file to the point of almost filling the drive. As I checked the log backups, everything was working fine. So I queried the log_reuse_wait_desc column of sys.databases (https://msdn.microsoft.com/en-us/library/ms178534.aspx), which will give a description of why the log file can’t be reused. And as you might have guessed, it pointed to AlwaysOn (AVAILABILITY_REPLICA). I immediately realized that the problem was that the data was not moving to the replica and I resumed the data movement once the underlying issue was fixed. And for those wondering, the issue was caused because the secondary replica, which was our DR server, had a networking issue. Although the Hyper-V host was up and the virtual server appeared up, it was not allowing any traffic in or out.

At this point, I realized that I had no monitoring in place to determine whether or not data is moving between the replicas or if a replica is falling behind. So, I needed to setup something and I went with Policy Based Management (https://msdn.microsoft.com/en-us/library/bb510667.aspx). The basic idea is that you define a condition that specifies the state of a management facet. A management facet is defined by as “a set of logical properties that are related to an area of management interest”. Then, you define a policy that references that condition and specify how and when to evaluate it.

Pict56

How to use Policy Based Management to monitor AlwaysOn?

There is a great post, which I feel is a must read, about monitoring performance for AlwaysOn at: https://msdn.microsoft.com/en-us/library/dn135338.aspx.

In that post, it explains how data is synchronized, how to estimate recovery time, how to estimate how much data will be loss, and how to setup monitoring. As you can see in the picture above, the monitoring consists of setting up 3 conditions and 2 policies. The policies evaluate the current situation to see if the failover time will exceed 10 minutes or if the potential for data loss is greater than 1 hour.

So, there you have it, a simple way to monitor your AlwaysOn instances. Of course, some of the SQL Server monitoring solutions have built in functionality to do something similar. But, if you aren’t using one of them on that server, this is an easy way to implement something.

In my case, if I had setup this policy based management, I would have been alerted and the log drive would not nearly had grown so large. And I would have been proactive, instead of reactive.

Posted in DBA | Tagged , , | Leave a comment

Investigating Job Schedules

One of the very first blog posts I made was on knowing what happened last night (https://sqlbadboy.wordpress.com/2012/10/12/what-happened-last-night/). In that post, I mentioned various ways that we could find out the statuses of jobs that had run. And this is an essential task for a DBA.

However, we should also know what is supposed to happen and when. Although that may be easier said than done, it is not impossible. Generally, this type of knowledge will come with having experience within an organization. But, there are some things we can do to help speed up that process.

Why is it important to know when things are scheduled to run on your SQL Server?

  • If you know when things are scheduled to run, it can help you identify busy times for your SQL Servers. This is valuable information so that you can schedule your database maintenance at less busy times.
  • If you know when things are scheduled to run, it can help you to monitor business processes. For example, if you have a process that is supposed to run every morning and send a report by a certain time and the report is not sent. Then, you can assume there was a problem.
  • If you know when things are scheduled to run, it can help you with baselining servers and planning for hardware resources.

How to find when things are scheduled to run on your SQL Server?

  • Use the Microsoft system stored procedures:
  • Use T-SQL against the system tables (sysjobs, sysjobschedules, sysschedules) in the msdb database:
SELECT 
       sql_server_name = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)),
       job_name = j.name,
       schedule_name = s.name,
       is_scheduled_enabled = s.enabled,
       schedule_description =
              CASE s.freq_type
              WHEN 1 THEN 'Once on ' + CONVERT(VARCHAR, s.active_start_date) + ' at ' + CONVERT(VARCHAR, s.active_start_time)  
              WHEN 4 THEN 'Every day ' 
              WHEN 8 THEN 'Every ' + CONVERT(NVARCHAR, s.freq_recurrence_factor) + ' week(s) on ' + 
                     CASE WHEN s.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END 
              WHEN 16 THEN 'Every ' + CONVERT(VARCHAR, s.freq_recurrence_factor) + ' months(s) on day ' + CONVERT(VARCHAR, s.freq_interval) + ' of that month '  
              WHEN 32 THEN 'Every ' + CONVERT(VARCHAR, s.freq_recurrence_factor) + ' months(s) on the ' + 
                     CASE s.freq_relative_interval  
                     WHEN 1 THEN 'first '  
                     WHEN 2 THEN 'second '  
                     WHEN 4 THEN 'third '  
                     WHEN 8 THEN 'fourth '  
                     WHEN 16 THEN 'last '  
                     END +  
                     CASE  
                     WHEN (s.freq_interval > 00) AND (s.freq_interval < 08) 
                           THEN DATENAME(dw, '1996120' + CONVERT(VARCHAR, s.freq_interval))  
                     WHEN (s.freq_interval = 08) THEN 'day'  
                     WHEN (s.freq_interval = 09) THEN 'week day'  
                     WHEN (s.freq_interval = 10) THEN 'weekend day'  
                     END + ' of that month '  
              WHEN 64 THEN 'Automatically starts when SQLServerAgent starts.'
              WHEN 128 THEN 'Runs when the computer is idle.'
              END +
              CASE s.freq_subday_type  
              WHEN 1 THEN 'at ' + CONVERT(VARCHAR, s.active_start_time)  
              WHEN 2 THEN 'every ' + CONVERT(VARCHAR, s.freq_subday_interval) + ' second(s)'  
              WHEN 4 THEN 'every ' + CONVERT(VARCHAR, s.freq_subday_interval) + ' minute(s)'  
              WHEN 8 THEN 'every ' + CONVERT(VARCHAR, s.freq_subday_interval) + ' hour(s)'  
              ELSE ''
              END + 
              CASE 
              WHEN s.freq_subday_type IN (2, 4, 8)  
                     THEN ' between ' + CONVERT(VARCHAR, s.active_start_time) + ' and ' + CONVERT(VARCHAR, s.active_end_time)  
              ELSE ''
              END
FROM msdb.dbo.sysjobs j
       INNER JOIN msdb.dbo.sysjobschedules js
              ON j.job_id = js.job_id
       INNER JOIN msdb.dbo.sysschedules s
              ON js.schedule_id = s.schedule_id
ORDER BY j.name

So, hopefully this post will make it just a little bit easier to find out what is going to happen, when it comes to our SQL Servers.

Posted in DBA, TSQL | Tagged , | Leave a comment

Encrypted Database Objects … How to Guide

Time and again, I have talked about security as being a high concern for DBA’s. So, that is why it is good to familiarize ourselves with the various types of security within SQL Server. In a previous post, I discussed encryption at the database level (https://sqlbadboy.wordpress.com/2015/03/02/how-sql-server-can-make-things-more-secure-use-encryption/). Today, I am going to focus on encrypting and decrypting SQL Server objects, such as stored procedures, views and user defined functions.

*** PLEASE NOTE: All my examples will focus on encryption with stored procedures.***

Why might we see database object encryption? Well, sometimes a software vendor or even someone in our own company may want to obfuscate the text of a database object so that it is not readily available in any of the catalog views within SQL Server. This object may contain sensitive logic that handles business operations.

In these cases, the object will appear with a little lock in SQL Server Management Studio (SSMS):

1

If you try to get the objects definition or text, it will not work by standard methods, even if you are a sysadmin on that SQL Server instance.

  • Using SSMS to script out the text

2

3

4

How to encrypt a database object

This is extremely simple to do! All that is needed is to include the WITH ENCRYPTION keywords.


CREATE PROCEDURE dbo.spTest_Encrypted

WITH ENCRYPTION

AS

SELECT name FROM dbo.test

GO

*** NOTE: CLR procedures can NOT be encrypted. And any procedure created with this option can NOT be published as part of SQL Server replication.***

How to decrypt a database object

Unfortunately, decrypting a database object is not that difficult. In fact, a database object can be decrypted by either using a privileged user who can either access system tables over the dedicated administrator connection (DAC) or by attaching a debugger to the server process that can retrieve the decrypted procedure from memory at runtime, which is what many third party tools do.

Method 1: Using the Dedicated Administrator Connection (DAC) (https://msdn.microsoft.com/en-us/library/ms189595.aspx) with a sysadmin account. You can connect to the DAC via SSMS by prefixing the server name with ADMIN:

5

By default, this connection is only allowed from a client running on a server. If you don’t enable the remote admin connection option (https://msdn.microsoft.com/en-us/library/ms190468.aspx) of sp_configure, you will get the following error:

6

Once you are connected, follow these steps:

  • Get the encrypted text for the original object from sysobjvalues, which is a base system table
  • Create an encrypted fake object with dummy text
  • Get the encrypted text for the fake object
  • XOR the original with the fake object

The following posts contain sample code for the steps above:

Method 2: Using a third party tool, such as RedGate’s SQL Prompt (http://www.red-gate.com/products/sql-development/sql-prompt/).

7

So, hopefully this post helps give a basic understanding of database object encryption. It is yet another simple way that many use to provide a level of security for database objects.

Posted in DBA, Security | Tagged | Leave a comment

Security Cleanup … Identifying Invalid Logins and Orphaned Users

Imagine that we own a large apartment building. And naturally, over time different tenants will come and go. But, what if we never bothered to change the keys to the apartments and we never attempted to get the keys back as a tenant leaves. Do you think this would pose a security risk? Well, undoubtedly so!

Similarly, when it comes to database servers, we should have a standard security process to check who has rights to a SQL Server (https://sqlbadboy.wordpress.com/2013/03/22/is-it-safe-is-it-secure/). And as people come and go from our organization, we should have a process to change certain passwords and cleanup the security on our servers. However, there are many companies that do not do this. And this becomes especially evident whenever you are starting a new job or taking over a new server.

In this post, I am NOT going to focus on changing passwords for domain and/or SQL Server logins. That should be straightforward. My focus for this post will be how to identify invalid Windows logins and orphaned users in databases.

*** PLEASE NOTE: If your standard is to use only domain groups as security principals, then you will generally not have the same type of mess to cleanup. The thoughts in this post are targeted for environments where domain user accounts and SQL Server logins are used. Also, I am discussing the situation where contained databases are NOT being used.

Identifying Invalid Windows Logins

The code below uses sp_validatelogins (https://msdn.microsoft.com/en-us/library/ms181728.aspx) to identify the Windows users and groups that no longer exist in the Windows environment, and then simply generates DROP LOGIN statements (https://msdn.microsoft.com/en-us/library/ms188012.aspx).

DECLARE @InvalidLogins TABLE (
  SID VARBINARY(85) NOT NULL,
  LoginName NVARCHAR(128) NULL
)

INSERT INTO @InvalidLogins
EXEC sp_validatelogins

SELECT
  LoginName,
  DropCmd = 'DROP LOGIN ' + QUOTENAME(LoginName)
FROM @InvalidLogins

Identifying Orphaned Database Users

An orphaned database user is a user (database principal) that does NOT have a corresponding server login (server principal). I have listed code that uses two different sets of tables. One way uses the system tables, sysusers and syslogins, and the second way uses catalog views, database_principals and server_principals. Also, this code is database specific. So, if you want to find all the orphaned database users on the entire server, you will need to run this code in each database.

1) Using system tables

SELECT
  DatabaseName = DB_NAME(),
  UserName = u.name,
  DropCmd = 'DROP USER ' + QUOTENAME(u.name)
FROM sys.sysusers u
  LEFT OUTER JOIN sys.syslogins l
    ON u.sid = l.sid
WHERE l.sid IS NULL
  AND u.islogin = 1
  AND u.hasdbaccess = 1
  AND u.name NOT IN ('dbo', 'public', 'guest')

2) Using catalog views

SELECT
  DatabaseName = DB_NAME(),
  UserName = d.name,
  DropCmd = 'DROP USER ' + QUOTENAME(d.name)
FROM sys.database_principals d
  LEFT OUTER JOIN sys.server_principals s
    ON d.sid = s.sid
WHERE s.sid IS NULL
  AND d.authentication_type_desc IN('INSTANCE', 'WINDOWS')

So, the real takeaway from this post is to make sure that you have a security processes in place to keep your security clean.

Posted in Security | Tagged | Leave a comment

Where Can I Live? What Address is Available?

Probably, most of us have shopped for a new place to live at some time in our life. Regardless of the type of place we wanted to buy (i.e. home versus condo), the first thing we needed to know was what addresses or properties were available. And we basically had two options: either ask a realtor what places are available or find them ourselves (i.e. search the internet or drive around our desired neighborhood).

In a similar sense, sometimes when we are working with SQL Server, we need an (IP) address. We may need this address when we are building a windows cluster or when we are adding a listener for an Availability Group.

So, the question becomes how do we go about finding what is available? Well, we can ask someone on the infrastructure team what IP address is available or we could find one ourselves. This latter option is what I am going to demonstrate in this post using PowerShell.

PowerShell code to examine a certain IP range and resolve the host names

 The code is based around GetHostEntry (https://msdn.microsoft.com/en-us/library/ms143997.aspx), which will query a DNS server for the IP address that is associated with a host name or IP address.


$ip_address_base = "123.456.789."

# address parameters: $i is start, $j is ending

$i = 1

$j = 32

while ($i -lt $j){

$ip_address = $ip_address_base + $I

[System.Net.Dns]::GetHostEntry($ip_address)

trap{$ip_address; continue}

$i = $i + 1

}

So, with this simple piece of code, you can very quickly find what IP addresses appear to be available. However, even when I use this code, I still normally ask the infrastructure team if I could use a certain address. The reason I do this is because they may have a design to where things should be or certain addresses reserved for reasons I don’t know about.

Posted in PowerShell | Leave a comment

Getting to Know Your Hardware

Should a DBA know what kind of hardware they are running on? And better yet, should they care?

My answer to both questions would be a firm … YES!

Here are two reasons why a DBA should know and care about their hardware:

  1. Investigating performance issues on a virtual server can be a little different than on a physical server. With a virtual server, you have another layer of software (Hyper V or VMWare), which could impact your performance. So, you will need to know how to investigate performance issues at that layer too.
  2. Since a lot of DBA’s handle SQL Server licensing, knowing CPU information (i.e. sockets, cores) of your hardware is crucial to figuring out SQL Server licensing.

With those reasons in mind, I am going to use this post to show how to obtain some basic CPU information through T-SQL and PowerShell.

Determine server type – virtual or physical

  • T-SQL – With SQL Server versions of 2008 R2 and newer, the dynamic management view, dm_os_sys_info, contains columns called virtual_machine_type and virtual_machine_type_desc which will help you determine if the server is virtual. (https://msdn.microsoft.com/en-us/library/ms175048.aspx)
SELECT
  @@SERVERNAME AS server_name,
  virtual_machine_type_desc
FROM sys.dm_os_sys_info
  • PowerShell – By using the Win32_ComputerSystem class, you can determine whether the server is virtual or physical. For virtual servers, the Manufacturer will say “Microsoft Corporation” or “VMWare”, and the Model will say “Virtual Machine” or “VMWare Virtual Platform”. (https://msdn.microsoft.com/en-us/library/aa394102.aspx)
Get-WmiObject Win32_ComputerSystem -ComputerName "ServerName" | 
Select Name, Manufacturer, Model

Determine CPU information – sockets and cores

  • T-SQL – You can get some basic CPU information by looking at the dynamic management view, dm_os_sys_info. The column cpu_count will give the total amount of logical CPU cores, and the column hyperthread_ratio will give the number of logical or physical cores that are exposed by one processor. However, SQL Server can NOT tell the difference between when hyper-threading is enabled and multi-core processors with those columns. (https://msdn.microsoft.com/en-us/library/ms175048.aspx)
SELECT
  @@SERVERNAME AS server_name,
  cpu_count AS logical_CPU_cores,
  cpu_count/hyperthread_ratio AS physical_CPU_sockets
FROM sys.dm_os_sys_info

The output of the following code gives how many CPUs (processors or sockets), the number of cores per processor, and the number of logical processors. If the number of cores and the number of logical processors is not equal, then hyper-threading is enabled.

Get-WmiObject Win32_Processor -ComputerName "ServerName" | 
Select SocketDesignation, NumberOfCores, NumberOfLogicalProcessors

So, hopefully this post will be helpful for DBA’s to get to know their hardware

Posted in DBA, PowerShell, TSQL | Tagged , , | Leave a comment

SQL Server Takeover

Imagine this … you have just been given the responsibility to manage another SQL Server that you know nothing about …

Ok, maybe we don’t really have to use our imagination in this scenario, since this happens all the time to DBA’s. For example, perhaps you have started a new job (which was recently my case) … or maybe your company acquired another company and its IT resources … or possibly there was a SQL Server instance under someone’s desk that now IT has to take care of.

What should a DBA do in those situations? Well, in this post, I am going to try to explain some of the first steps that I normally do when I take over a SQL Server.

Check for proper database maintenance running

  • Maintenance plans or solution – Determine if there is a comprehensive strategy to handle the database maintenance. This could be a set of maintenance plans or something like Ola Hallengren’s maintenance solution (https://ola.hallengren.com/). Whatever the case, it should include backups (see previous point), indexing optimizations, integrity checks, and cleanup (database and server).

Check for some standard configurations on the databases

Check for some standard configurations on the SQL Server instance

Well, these are some of the basic first steps I take when I inherit a SQL Server. Of course, more tuning and adjustments will need to be made as you come to understand the load and usage of the server. But, this is just a start.

Posted in DBA | Tagged , , | Leave a comment

How SQL Server Can Make Things More Secure … Use Encryption!

One of the hottest topics in the technology world is security.  This topic is important because there are few things that can hurt a company more than stolen or misused data.  So, it is easy to see why security should always be an area of highest concern to DBA’s.  And one of the keys to having a secure environment is to understand the layers of security that can be involved with databases.  For example, security can be applied at the server level, to each SQL Server instance, to individual databases, to individual objects within the database, backups, etc.

In a previous post, I addressed some server and database level security (https://sqlbadboy.wordpress.com/2013/03/22/is-it-safe-is-it-secure/).  In this post, I am going to focus on how SQL Server uses encryption as a security measure.  Encryption is the process of obfuscating data, or purposely scrambling or masking it, by the use of keys and/or passwords.  Just to be clear, encryption does NOT solve access control issues, but rather adds another layer of security if those controls are bypassed.

*** NOTE: Please give thought as to when encryption needs to be used, since there is a performance overhead.  It should not be blindly applied to everything.  In some cases, it may be necessary to use encryption such as when data is moving over a public network.  On the other hand, it may not be necessary for data on an internal network.

How does SQL Server use encryption?

Here are the basics of SQL Server encryption, and the diagram is taken from: (Encryption Hierarchy: https://msdn.microsoft.com/en-us/library/ms189586.aspx):

50_Encryption

How to setup Transparent Data Encryption (TDE)?

Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files.  The encryption of the database files is performed at the page level.  The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory.  Also, using TDE does not increase the size of the encrypted database.

Steps to enable TDE on a database:

  1. Create a master key (https://msdn.microsoft.com/en-us/library/aa337551.aspx)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
  1. Create or obtain a certificate protected by the master key (https://msdn.microsoft.com/en-us/library/ms187798.aspx)
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate'
  1. Create a database encryption key and protect it by the certificate (https://msdn.microsoft.com/en-us/library/bb677241.aspx)
USE DatabaseName
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert
  1. Set the database to use encryption (https://msdn.microsoft.com/en-us/library/bb522682.aspx)
ALTER DATABASE DatabaseName SET ENCRYPTION ON

*** NOTE: Make sure that when you enable TDE that you should immediately back up the certificate and the private key associated with the certificate.  If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.  

Backup the database master key (https://msdn.microsoft.com/en-us/library/aa337546.aspx):

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'
BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey'
  ENCRYPTION BY PASSWORD = 'P@ssw0rd'

Backup the certificate (https://msdn.microsoft.com/en-us/library/ms178578.aspx):

BACKUP CERTIFICATE TDECert 
TO FILE = 'C:\temp\dbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\dbcert_Key.pvk',
ENCRYPTION BY PASSWORD = 'P@ssw0rd')

Moving a TDE enabled database (https://msdn.microsoft.com/en-us/library/ff773063.aspx):

If a database with TDE enabled needs to be moved to another server, you will have to use code like the following to restore the database master key and certificate.

RESTORE MASTER KEY
  FROM FILE = 'c:\temp\exportedmasterkey'
  DECRYPTION BY PASSWORD = 'P@ssw0rd'
  ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd';
CREATE CERTIFICATE TDECert
FROM FILE = 'D:\BACKUP\dbCerts\dbcert.cer'
WITH PRIVATE KEY (FILE = 'D:\BACKUP\dbCerts\dbcert_Key.pvk',
DECRYPTION BY PASSWORD = 'P@ssw0rd')

*** NOTE: At times, I have seen an issue with databases being restored with the following error, “Please create a master key in the database or open the master key in the session before performing this operation.”  In order to the master key to be used with all sessions, it must be encrypted by the service master key as follows:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Useful DMV’s to use with encryption:

Hopefully, this post gave enough information for a DBA to have a basic understanding of encryption in SQL Server and how to use it.

Posted in DBA, Security | Tagged , | 1 Comment