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
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
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
Encryption in SQL Server 2012–X 509 Certificates Generation, Registering, Backup and Removing of Certificates
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
Once you have created the Password, it prompt you again to confirm and create the certificate
Once the certificate is created, you will get the “Succeeded” message
Also you can open the created Certificate file to view the details
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' );
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
3. To Enable TDE based Encryption for Database
4. To digitally sign data using SignByCert() function
Hope this helps !!!
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
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
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]
Hope this helps !!!
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
To view the current database master key in your database, you can use the following system security catalog view
Hope you find this information useful !!! In the upcoming articles, we will go through the different encryption options available in SQL Server 2012.
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
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
You can by default install all the products and try them for Free
or pick ApexSQL Complete 2012
Once the install is complete, Please launch SQL Server 2012 Management Studio,
ApexSQL Complete menu has the following options
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.
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
ApexSQL Complete also check your query as you type and shows them in “Error List” window
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
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.
ApexSQL Complete Options
The following are the different options available under General section, but I would like to highlight the “Decrypt Encrypted Objects”
The below view “dbo.vwAddress_Enc” is an encrypted view
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
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
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
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
When you select the Alias, it automatically fills the referenced object name
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
Using Snippet Manager, You can modify the existing Snippet and customize them or you can add new Snippets as well
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 !!!
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 !!!
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
2. Local or Network Folder
3. Drop Box
5. Amazon S3
7. Google Drive
Professional Version also supports SFTP and FTPS and AES Encryption of Backup files
Please follow the below steps for installation
The installation has completed successfully, Let us launch SQL Backup and FTP and create a Backup
Select the databases you want to Backup
Click on “Add backup destination” to specify the backup destination
For our demo, Let us select Drop box option
Click on “Authorize…” and Login to your Drop Box account
Click “Allow” to approve the Drop Box access
Once Drop Box access is authorized, Please click on “Finish” to proceed. You can configure automatic cleanup of backups as well.
Specify the Folder to which Backup has to be done
Click “Test” to make sure it works
Once Test is completed, Click “Ok” to proceed
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
If you want to enable Email notifications, Please select and specify the email address
Click on “Email Settings” icon to Test and configure, You can you use your own SMTP Server, if you prefer
Click “Send Test e-mail” to make sure the notifications are working and click “Ok” to proceed
Scheduling the Backups
If you want the backup to run automatically at a specific time, Please enable the “Schedule this job” option
Please click on “Schedule Settings” to customize the schedule based on your requirement, once finished, Please click on “Save & Close” to proceed
Please click “Run Now” to test the job manually
Once test is completed, Now click on “Save” button on the tool bar to save the job.
Hope this helps !!!