Archive

Archive for March, 2013

Encryption in SQL Server 2012–How to use EncryptByCert and DecryptByCert functions

March 31, 2013 1 comment

In the previous article, we saw how to create X 509 Certificate and register it in SQL Server,

Now we will see how we can use the registered certificates

SQL Server 2012 provides the following two functions to encrypt and decrypt data directly using X 509 Certificates

1. EncryptByCert

2. DecryptByCert

Please refer to the below sample on how we can use these functions

DECLARE @CertID INT
DECLARE @encryptedtext varbinary(300);
DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption'

-- Get the Certificate ID
SET @CertID = Cert_ID(N'Cert1_AdventureWorks2012')

-- Encrypt the data with the Certificate Registered
SET @encryptedtext = EncryptByCert(@CertID, @plaintext) 

-- Decrypt the data with the same Certificate
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText],
    CAST(DECRYPTBYCERT(@CertID, @encryptedtext) AS nvarchar(100)) [DecryptedText]

 

The result of the above are as follows

image

Please make sure to use the correct Certificate ID to decrypt the data and also make sure to document which certificate is used for Encryption, if you are planning to use multiple certificates for different tables.

Hope this helps !!!

In the upcoming articles, we will see How we can use Certificates to

1. Encrypt Symmetric Keys

2. To Enable TDE based Encryption for Database

3. To digitally sign data using SignByCert() function

Advertisements

Encryption in SQL Server 2012–X 509 Certificates Generation, Registering, Backup and Removing of Certificates

March 29, 2013 3 comments

Internal use X-509 Certificates can be generated using MakeCert.exe utility, Please find the below syntax

 

makecert.exe -sv "C:\Workspace\Cert1_AdventureWorks2012.pvk" 
    -pe 
    -a sha1 
    -b "01/01/2013" 
    -e "12/31/2013" 
    -len 2048 
    -r 
    -n "CN=SQL Server 2012 - Encryption Demo Certificate" 
    C:\Workspace\Cert1_AdventureWorks2012.cer


The details of the parameters are as follows

-sv        Private Key file
-pe        Private Key Exportable
-a         Signature Algorithm (md5 [Default] or sha1)
-b        Beginning Date 
-e        Expiring Date
-len      Length of Private Key
-r        Self-Signed Certificate
-n        Name of Certificate (Subject of Certificate)

 

You can run the above utility from Command Prompt, it will prompt you to create Password for accessing Private Key

image

Once you have created the Password, it prompt you again to confirm and create the certificate

image

Once the certificate is created, you will get the “Succeeded” message

image

Also you can open the created Certificate file to view the details

image

 

Register Certificates

Once we create the X-509 certificate, we can use this to Register Certificates in SQL Server using the below T-SQL command, Please make sure you have created the Service Master Key and Database Master Key before this step

 

USE AdventureWorks2012
-- Register Certificate in SQL Server

CREATE CERTIFICATE Cert1_AdventureWorks2012
FROM FILE = N'C:\Workspace\Cert1_AdventureWorks2012.cer'
WITH PRIVATE KEY
(
    FILE = N'C:\Workspace\Cert1_AdventureWorks2012.pvk',
    DECRYPTION BY PASSWORD = N'p@ssw0rd'
);

Backup Certificates

To Backup Certificate from SQL Server, Please use the below T-SQL Command

USE AdventureWorks2012

--Backup Certificates

BACKUP CERTIFICATE Cert1_AdventureWorks2012
TO FILE = N'C:\Workspace\Cert1_AdventureWorks2012_BACKUP.cer'
WITH PRIVATE KEY
(
    FILE = N'C:\Workspace\Cert1_AdventureWorks2012_BACKUP.pvk',
    ENCRYPTION BY PASSWORD = N'p@ssw0rd~123'
);

View Certificates registered in Database

You  can view the certificates registered in a database using the below query

SELECT 
    name, certificate_id, principal_id, pvt_key_encryption_type
    pvt_key_encryption_type_desc, is_active_for_begin_dialog, issuer_name
    cert_serial_number, sid, string_sid, subject, expiry_date, 
    start_date, thumbprint, attested_by, pvt_key_last_backup_date
FROM sys.certificates

 

Deleting Certificates registered in Database

To delete a certificate registered in database, please use the below query

-- Deleting Certicates

DROP CERTIFICATE Cert1_AdventureWorks2012

In SQL Server, Certificates can be used to

1. Encrypt data using EncryptByCert and DecryptByCert functions

2. Encrypt Symmetric Keys

3. To Enable TDE based Encryption for Database

4. To digitally sign data using SignByCert() function

Hope this helps !!!

Encryption in SQL Server 2012–Using PassPhrase

March 28, 2013 Leave a comment

SQL Server 2012 supports PassPhrase based encryption. PassPhrase method uses TRIPLE DES algorithm with a 128 key bit length.

The following are the 2 functions

1. EncryptByPassPhrase

2. DecryptByPassPhrase

Its very simple and easy to use as well

 

DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption';

-- Encrypt the data with a passphrase
DECLARE @encryptedtext varbinary(300);
SET @encryptedtext = EncryptByPassPhrase(N'p@ssw0rd', @plaintext);

-- Decrypt the data with the same passphrase
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText], 
    CAST(DecryptByPassPhrase(N'p@ssw0rd', @encryptedtext) AS nvarchar(100)) [DecryptedText]

The output will look as follows

image

Also you can use an Authenticator as well along with your PassPhrase

DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption';

-- Encrypt the data with a passphrase with Authenticator
DECLARE @encryptedtext varbinary(300);

SET @encryptedtext = EncryptByPassPhrase
            (
                N'p@ssw0rd', 
                @plaintext, 
                1, -- To Enable Authenticator Specify 1
                CONVERT(Varbinary, 'AuthenticatorText') -- Authenticator Text should be converted to VARBINARY
            );

-- Decrypt the data with the same passphrase with Authenticator
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText], 
    CAST(
        DecryptByPassPhrase(
                N'p@ssw0rd', 
                @encryptedtext, 
                1, 
                CONVERT(Varbinary, 'AuthenticatorText')
        ) AS nvarchar(100)) As [DecryptedText]

 

image

Hope this helps !!!

Encryption in SQL Server 2012–Creating Service Master Key and Database Master Key

March 23, 2013 1 comment

 

All Version of SQL Server 2012 including the Express Edition supports Encryption

The first steps to start Encrypting data in SQL Server 2012 are as follows.

 

1. Backup Service Master Key and keep it in a secure location

Use master

BACKUP SERVICE MASTER KEY TO FILE = N'C:\Workspace\MYServiceMasterKey.Key' 
ENCRYPTION BY PASSWORD = N'p@ssw0rd'

 

2. Create Database Master Key for Encryption for the databases you want to protect

Use AdventureWorks2012
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

 

3.  Backup the Database Master Key to a secure location

Use AdventureWorks2012
BACKUP MASTER KEY TO FILE = N'C:\Workspace\DatabaseMasterKey.Key' 
    ENCRYPTION BY PASSWORD = N'p@ssw0rd'

 

4. Open the Database Master Key and Test it

Use AdventureWorks2012
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssw0rd';

CLOSE MASTER KEY;

 

If you specify the wrong password to open the database master key, you will be getting the below error

image

 

To view the current database master key in your database, you can use the following system security catalog view

 

image

 

Hope you find this information useful !!! In the upcoming articles, we will go through the different encryption options available in SQL Server 2012.

ApexSQL Complete–Advanced SQL Server Intellisense for Free !!!

March 16, 2013 Leave a comment

SQL Server 2012 has built-in Intellisense, ApexSQL Complete takes Intellisense to next level.

It available as a Free Tool and its one of the best Free Tools available for SQL Server in market.

Its available for download in this below link

http://www.apexsql.com/sql_tools_complete.aspx

The download also includes all the tools from ApexSQL Developer Studio (15 Developer Tools, 3 of them are Freely available)

The installation process is quite simple

image

image

You can by default install all the products and try them for Free

image

or pick ApexSQL Complete 2012

image

 

image

Once the install is complete, Please launch SQL Server 2012 Management Studio,

ApexSQL Complete menu has the following options

image

Please click on “Enable Auto-complete” to use ApexSQL Complete instead of built-in Intellisense

You can open a new Query window and start typing the query and you can activate the Intellisense by pressing Ctrl + Space

You can choose the columns which you want to be included in Query and If you noticed, It automatically add Alias to the Table name automatically as well, which will be very helpful for complex queries and using in joins.

image

Also when you select a table, you can see the CREATE Script for the table as well and you have option to “Copy” the script as well

image

ApexSQL Complete also check your query as you type and shows them in “Error List” window

image

ApexSQL Complete Error List window works similar to Visual Studio Error List window, double clicking on the Error takes you to the exact Line and Column so that you can easily fix it

image

ApexSQL Code Structure

This is one of the most powerful feature I liked in ApexSQL which is not available in any of the other competitive production

It parses your SQL code and display the Code Structure as Tree, so that you can easily navigate the code and click and move to the preferred statement easily.

image

ApexSQL Complete Options

General Options

The following are the different options available under General section, but I would like to highlight the “Decrypt Encrypted Objects”

image

The below view “dbo.vwAddress_Enc” is an encrypted view

image

Since I have enabled the Decrypt option, ApexSQL Complete is able to show the definition of the Encrypted View, so that I can copy the script

image

Insert Options

image

Hints Options

If you don’t want to see System tables or views in the Intellisense list, you can deselect it using “System Tables” option, similarly you can customize the other hints too

image

Aliases Options

ApexSQL Complete by default create aliases for tables or views selected from Intellisense window, but if you want use your own custom defined Alias, you can define them here

image

I have created an alias named “Emp” for the Employee table, when I write the query, now the ApexSQL Complete Intellisense window displays the Alias on top

image

When you select the Alias, it automatically fills the referenced object name

image

Schema Cache Options

Here you can see the currently cached Database and its object counts, you can select them and right click and Delete or Refresh them

image

Snippet Manager

Using Snippet Manager, You can modify the existing Snippet and customize them or you can add new Snippets as well

image

image

image

Also after doing the customization, You can Export the options and share with your colleagues, and they can import and use the Snippets you created or use the same Aliases you have created.

ApexSQL Developer Studio also has the following products available for free as well

1. ApexSQL Search

2. ApexSQL Refactor

I hope you find this information useful !!! Have fun coding using ApexSQL Complete !!!

How to find First or Last Day of a Month ? SQL Server 2008 vs SQL Server 2012 compared

March 14, 2013 Leave a comment

SQL Server 2012 has a new Function called EOMONTH() which returns the last day of the month for the specified date in parameter and also has an option to define offset as well.

Let us first see How we can do this in SQL Server 2008

--Last Day of Last Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) AS [Last Day of Last Month]
--Last Day of This Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) AS [Last Day of This Month]
--Last Day of Next Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 2, 0)) AS [Last Day of Next Month]

Let us see How SQL Server 2012 simplifies the above code

--Last Day of Last Month
SELECT    EOMONTH(GETDATE(), -1) AS [Last Day of Last Month]
--Last Day of Current Month
SELECT    EOMONTH(GETDATE()) AS [Last Day of This Month]
--Last Day of Next Month
SELECT    EOMONTH(GETDATE(), 1) AS [Last Day of Next Month]

In SQL Server 2008 we had to make 4 function calls in order get the output, whereas SQL Server 2012 helps us do the same thing in 2 function calls

We can use the same function to get First Day of the Month as well

--First Day of Last Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)) AS [First Day of Last Month]
--First Day of This Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AS [First Day of This Month]
--First Day of Next Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE())) AS [First Day of Next Month]

Hope this helps !!!

How to backup your SQL Database and Upload to FTP or Cloud Automatically ?

March 12, 2013 Leave a comment

Its always a better idea to Keep Offsite backup of databases, “SQL Backup and FTP” allows you to do that freely

SQL Backup and FTP is available for download for free at http://sqlbackupandftp.com

It supports direct backup to

1. FTP

2. Local or Network Folder

3. Drop Box

4. Box

5. Amazon S3

6. SkyDrive

7. Google Drive

Professional Version also supports SFTP and FTPS and AES Encryption of Backup files

Please follow the below steps for installation

image

image

image

image

image

image

image

The installation has completed successfully, Let us launch SQL Backup and FTP and create a Backup

image

Select the databases you want to Backup

image

Click on “Add backup destination” to specify the backup destination

image

For our demo, Let us select Drop box option

image

Click on “Authorize…” and Login to your Drop Box account

image

Click “Allow” to approve the Drop Box access

image

Once Drop Box access is authorized, Please click on “Finish” to proceed. You can configure automatic cleanup of backups as well.

 

image

Specify the Folder to which Backup has to be done

image

Click “Test” to make sure it works

image

Once Test is completed, Click “Ok” to proceed

image

Let us now connect to SQL Server and do a backup, By default, if you have a SQL Server default instance running, it will automatically connect and display the databases, Please select the database which you want to backup

image

If you want to enable Email notifications, Please select and specify the email address

image

Click on “Email Settings” icon to Test and configure, You can you use your own SMTP Server, if you prefer

image

Click “Send Test e-mail” to make sure the notifications are working and click “Ok” to proceed

image

Scheduling the Backups

If you want the backup to run automatically at a specific time, Please enable the “Schedule this job” option

image

Please click on “Schedule Settings” to customize the schedule based on your requirement, once finished, Please click on “Save & Close” to proceed

image

Please click “Run Now” to test the job manually

image

image

image

Once test is completed, Now click on “Save” button on the tool bar to save the job.

Hope this helps !!!

%d bloggers like this: