Wednesday, January 18, 2017
Friday, January 13, 2017
-- Create a database CREATE DATABASE Sales; GO -- Connect with newly cerated database USE Sales; GO -- Create a master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; GO -- Create a certificate for protecting the our key CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate'; GO -- Create the key for encrypting data -- Note that the created certificate protects it. CREATE SYMMETRIC KEY SalesKey WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE SalesCertificate; GO -- Creating a table that holds customers -- Note the Securiry Code Column, it is varbinary -- because code will be encrypted and stored CREATE TABLE dbo.Customer ( CustomerId int identity(1,1) PRIMARY KEY , Name varchar(100) NOT NULL , SecurityCode varbinary(256) NOT NULL ); GO
OPEN SYMMETRIC KEY SalesKey DECRYPTION BY CERTIFICATE SalesCertificate ; GO INSERT INTO dbo.Customer (Name, SecurityCode) VALUES ('Dinesh', ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS005XZ')) INSERT INTO dbo.Customer (Name, SecurityCode) VALUES ('Yeshan', ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS02ANX3'))
Once inserted, data will be looked like below;
And if I try to retrieve records, I need to decrypt encrypted values;
So far so good. Now let's try understand the usage of Authenticator. Assume that Yeshan needs to access some Securables that can be accessed only by Dinesh through an application. For that, all Yeshan needs is, log in to the application using Dinesh's Security Code. Since he does not know Dinesh's Security Code, one way of accessing the application using Dinesh's account is, replacing the Dinesh's code with his code. Let's see whether it is possible.
The following code updates Dinesh's account with Yeshan's code. And as you see, it gets updated and now Yeshan can use Dinesh account as he knows the code.
This should not be allowed and even if it is possible, what if we make sure that encrypted code cannot be replaced like that. That is what we can do with the Authenticator.
Look at the following code. It passes two additional values for encrypting. The third one which is 1 says that this has an Authenticator. The forth parameter is the data from which to derive an Authenticator.
OPEN SYMMETRIC KEY SalesKey DECRYPTION BY CERTIFICATE SalesCertificate ; GO -- Update Security codes with CustomerId as the Authenticator UPDATE dbo.Customer SET SecurityCode = ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS005XZ', 1, Convert(varbinary(256), CustomerId)) WHERE CustomerId = 1; UPDATE dbo.Customer SET SecurityCode = ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS02ANX3', 1, Convert(varbinary(256), CustomerId)) WHERE CustomerId = 2;
Just like the previous code, values are encrypted now. However, if Yeshan tried to do the same, see the result;
As you see, even though Dinesh's code has been replaced with Yeshan's code, when try to decrypt value of Dinesh, it results null because Authenticator is different. This is the usage of the Authenticator.
Note that we used CustomerId as the Authenticator but you can use something else, something uncommon as the Authenticator to make it more secured and avoid malicious activities like this.
Thursday, January 12, 2017
-- Create a database CREATE DATABASE Sales; GO -- Connect with newly cerated database USE Sales; GO -- Create a master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; GO -- Create a certificate for protecting the our key CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate'; GO -- Create the key for encrypting data -- Note that the created certificate protects it. -- However, this will throw an error CREATE SYMMETRIC KEY SalesKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE SalesCertificate; GO
USE master; GO -- Change the compatibility level to 120 ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 120; -- And check again USE Sales; GO -- This will work now without any error CREATE SYMMETRIC KEY SalesKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE SalesCertificate; GO
Sunday, January 8, 2017
- Read the attributes identified by the BA and understand the business requirement - yes, you can think that this not something we should concern but trust me, you need to know.
- Analyze and see whether this needs as a normalized table or denormalized table. Based on the requirement, decide the level like how far we need to go through normalization rules.
- Decided the data types of the attributes and size of them. Need to pick the best, thinking present and future too. Should we think about past data as well. Yes, we should if we need to load legacy data.
- Consider the datetime format and whether it needs to maintain Unicode characters.
- Need to analyze whether it needs partitions and partitions need to be distributed among multiple file groups (preferably multiple volumes).
- Constraints required. In addition to the Primary Key, we may add additional constraints such as Foreign Key, Unique Key and some rules.
- Whether Artificial or Surrogate Key is required.
- Number of indexes required, whether we need Columnstore or not, order of them, whether they need any filters.
- Whether compression is required.
Saturday, January 7, 2017
node as per the snippet you need to add. In this example, the required code is EXEC sp_set_firewall_rule and it needs three parameters: Rule name, Starting parameter and Ending parameter. Parameters have to be added as
node. Here is the way of adding this SP.
7. Done. Now the folder that contains the snippet has to be added to the Code Snippet Manager. Open Management Studio and select Code Snippet Manager menu item in the Tools menu.
8. Click on Add and add the Azure Folder (Or the folder you created).
9. Now the code snippet is available.
- Dinesh Priyankara
- Colombo, Sri Lanka
- Dinesh Priyankara (MSc IT) is an MVP – Data Platform (Microsoft Most Valuable Professional) in Sri Lanka with 16 years’ experience in various aspects of database technologies including business intelligence. He has done many local and foreign business intelligence implementations and has worked as a subject matter expert on various database and business intelligence related projects. He is the Founder/Principal Architect of dinesQL (Pvt) Ltd and he consults, teaches and runs training programs on data related solutions and subjects.
- SQL Server Identity value suddenly became 1000+
- SQL Server - Adding an Authenticator when encrypti...
- Incorrect syntax near 'TRIPLE_DES'. - SQL Server t...
- Table Design Considerations - SQL Server Brain Bas...
- SQL Server - Adding Code Snippet and Using Existin...
- SQL Server - Adding IF, BEGIN/END, WHILE statement...
- SQL Server - Rounding Off Decimal to 0.5
- SQL Server Job History is missing
- Should we use Compress function on string values t...
- Another new year for dinesql.blogsposts.com……… HAP...
- ▼ January (10)
- ► 2016 (171)
- ► 2015 (200)
- ► 2014 (56)
- ► 2011 (32)
- ► 2010 (79)
- ► 2009 (42)
Read new stuff..........
- SQL Server Sleeping Status and Connection Pooling
- Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I
- When was my SQL Server Database last accessed?
- Reporting Services 2008: Showing HTML content, and what it really renders
- Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part II
- Cannot login to SharePoint 2010 site when the Host Header is set
- Understanding GRANT, REVOKE and DENY T-SQL Commands
- Report Viewer Control: Exporting Reports to Word, PDF and Excel Programmatically
- OPENROWSET, OPENDATASOURCE - “Microsoft.ace.oledb.12.0” for linked server “(null)” reported error – Excel 2007, 2010
- Installing and Configuring PowerPivot for SharePoint 2010