Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,893,387 hits
  • Select GETDATE()

    April 2014
    M T W T F S S
     123456
    78910111213
    14151617181920
    21222324252627
    282930  

Archive for April, 2014

SQL 2014 Learning Series # 3 – New Feature – Backup Encryption

Posted by blakhani on April 3, 2014


Before we jump into the new feature, lets have a look at behavior in previous version. Same simulation can be done on SQL Server 2014 as well.

  1. Create Database
  2. Create table in the database and insert some rows.
  3. Take backup of the database and check if we are able to see those rows in the backup.

Here are the commands which we can run to achieve this.

Create database [SQLServerHelp]
go
use [SQLServerHelp]
go
Create Table MyData (i int, j char(100));
go
insert into MyData values (1, 'See this data')
go
backup database SQLServerHelp to Disk = 'E:\Tools\SQLServerHelp.bak'
go


I have opened the backup file with one of the text editor and I can search for the row which I inserted. Note that opening big file in text editors may take a lot of time and file may become unusable for SQL Server. PLEASE DON’T TRY THIS ON PRODCUTION DATABASE BACKUP. I have highlighted the row which was inserted via above script. (1, ‘See this data’)

image

The way to avoid above situation is that encrypt the database use TDE or encrypt the column using column encryption. Please read below

http://technet.microsoft.com/en-us/library/cc278098(v=SQL.100).aspx (Database Encryption in SQL Server 2008 Enterprise Edition)

There are some overhead involved with above techniques and you may want to make sure that only backup is encrypted and you are OK if MDF file has data in readable format. To solve this, SQL Server 2014 has new feature called backup encryption. You can read more on books online

Let’s look at steps involved.

Create database master key for the master database.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLServer-Help@123';
GO

Create a Backup Certificate

Use Master
GO
CREATE CERTIFICATE BackupEncryptCert
   WITH SUBJECT = 'SQL Backup Encryption Certificate';
GO

 

Take a backup: This can be done by SSMS UI or T-SQL

image

T-SQL

BACKUP DATABASE [SQLServerHelp] TO  DISK = N'E:\Tools\Encrypted_SQLServerHelp.bak' WITH NOFORMAT, NOINIT,  
NAME = N'SQLServerHelp-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [BackupEncryptCert])
GO

Here is the message which we can see as output in SSMS when we run T-SQL.

image

Here is the complete text of highlighted warning.

“Warning: The certificate used for encrypting the database encryption key has not been backed up. 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.”

It’s clear that to restore this backup, we need to backup certificate. Going back to original problem, I tried searching for the text in new backup file but can’t find it. If you look at screenshot, it’s clear that it’s more unreadable.

image

As Vinod (b|t) mentioned in his blog, we can see complete message in ERRORLOG (this is also an enhancement in SQL Server 2014)

2014-04-03 06:36:42.780 Backup       Database backed up. Database: SQLServerHelp, creation date(time): 2014/04/01(10:15:50), pages dumped: 307, first LSN: 34:24:37, last LSN: 34:56:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\Tools\SQLServerHelp.bak’}). This is a

2014-04-03 06:36:42.810 Backup       BACKUP DATABASE successfully processed 298 pages in 0.212 seconds (10.981 MB/sec).

2014-04-03 07:25:49.220 Backup       Database backed up. Database: SQLServerHelp, creation date(time): 2014/04/01(10:15:50), pages dumped: 307, first LSN: 34:168:37, last LSN: 34:200:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\Tools\Encrypted_SQLServerHelp.bak’}

2014-04-03 07:25:49.240 Backup       BACKUP DATABASE successfully processed 298 pages in 0.228 seconds (10.211 MB/sec).

It’s clear that second one took little more time. I am guessing this is because of encryption as all other factors are same.

Hope you have learned something new today!

Edit (9 April 2014) – Vinod (b|t) wrote a blog on restoring such backups. http://blogs.extremeexperts.com/2014/04/09/sql-server-2014-restoring-encrypted-backups/

 

 

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , , , , | 1 Comment »

    SQL 2014 Learning Series # 2 – New Permission – Select All User Securables

    Posted by blakhani on April 1, 2014


    In my last post of SQL 2014 Learning series I have explained “Connect any database” permission. As I mentioned, this permission does not grant any permission in any database beyond connect. Someone might think, what’s the use if they are just able to expand the database but not see any database object? Here is the screenshot (which I showed in earlier blog)

    As we can see above, database can be expanded but tables are not visible. The real usage of the “Connect any database” permission can be seen if it’s combined with “Select All User Securables”. You may ask, what are the situations when DBA need to give those two permissions? Imagine a situation where an auditor has been appointed to audit the databases. If you have to created auditor account (Balmukund in my example), a long way would be to create login, give permission to database and then permission to select individual tables. This permission “SELECT ALL USERS SECURABLES” is designed  to allow a user to view data in database where he can connect.

    Let me give this permission to Balmukund login. I can either use Management Studio Interface or T-SQL.

    image

    use [master]
    GO
    GRANT SELECT ALL USER SECURABLES TO [Balmukund]
    GO

    As soon as permissions are given to Balmukund, he would be able to select data from all table in database.

    image

    If you notice closely, there is a lock icon on dbo.v1 under “Views”. This means that “Balmukund” would not be able to see the definition of the view. His permissions are limited to perform Select on tables and views.

    Good part about this permission is that even if we create new tables in the database or even a new database, the permissions are available on new objects as well. This means that DBA need not keep giving permission on newly created table and he can have sound sleep. After taking above screenshot, I created new database (yeah, that’s the name as well) and a new table. Select permissions are automatically available to Balmukund.

    image

    Hope this post was helpful.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , | 3 Comments »