JSON in SQL SERVER

JSON in SQL SERVER

JSON is very popular and currently the most commonly used data exchange format. Most modern web and mobile services return information formatted as JSON text, all database web services, web browsers return results formatted as JSON text or accept data formatted as JSON. Since external systems format information as JSON text, JSON is also stored in SQL Server 2016 as text. You can use standard NVARCHAR columns to store JSON data in SQL Server 2016.

With SQL Server 2016, built-in functions can parse JSON text to read or modify JSON values, transform JSON array of objects into table format, any Transact -SQL query can be run over the converted JSON objects, results of Transact-SQL queries can be formatted into JSON format.

‘$’ – references entire JSON object in the input text

Here, I am explaining JSON data related inbuilt functions in simple way

Let’s start with Examples:

 

Fetch specific value from JSON data with the help of “json_value”

Result: Los Angeles

 

Get array values from JSON data with help of “OPENJSON”

Result :

Value

sport

Water polo

 

 

Get JSON output from SQL query with help of “FOR JSON”

 

Save JSON data in table

You can user standard NVARCHAR column to save JSON data in text format.

 

Check valid JSON data in column with help of “ISJSON”

 

 

Get collection of values on any specific column in array formate by using “JSON_QUERY”

 

 

OPENJSON

It is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. In other words, OPENJSON provides a row set view over a JSON document. 

 

 

key	value	type
StringValue	John	1
IntValue	45	2
TrueValue	TRUE	3
FalseValue	FALSE	3
NullValue	NULL	0
ArrayValue	[“a”,”r”,”r”,”a”,”y”]	4
ObjectValue	{“obj”:”ect”}	5

 

 

You can also create Index on JSON data column, as we create

 

 

Always Encrypted

If you talk about security this one the best feature. Now, you are thinking what it means. So, It means that the data in the SQL SERVER reside always in encrypted format and SQL server can perform the operation on the encrypted data without decrypting it. The encryption key can be exist in some other system. With this, feature you can secure you ensure that your data is secure from the person like DBA / Developer as well. These guys also can’t see the actual data.

Encrypted is a way to send encrypted data over the pipeline and decrypt it only by users with access to valid certificates. So, even if the attacker gets the data, without a proper certificate stored on the client machine, the data would be useless.

How to use Always Encrypted:

1. Create the column master key definition
2. Create the column encryption key

The column master key is a certificate that is stored within a Windows certificate store.

The application that is encrypting the data uses the column master key to protect various column encryption keys that handle the encryption of data within the columns of a database table. Using certificate stores from SQL Server, which are sometimes referred to as Enterprise Key Manager, requires the use of SQL Server Enterprise Edition.

 

1. Creating and Using Column Master Keys

In Object Explorer, navigate first to the database, then to Security, and then expand the Always Encrypted Keys folder to display its two subfolders, as shown in the following figures:

 

Click on “New Column Master Key” and below new dialog would get open

 

Above step creates a self-signed certificate and loads it into the certificate store of the current user account running SSMS.

After creating the certificate and configuring it as a column master key, you must export and distribute it to all computers hosting clients requiring access to the data. If a client application is web-based, you must load the certificate on the web server. If it is an application installed on users’ computers, then you must deploy the certificate to each user’s computer individually.

Exporting certificates

• Windows 7 and Windows Server 2008 R2 (https://technet.microsoft.com/en-us/library/cc730988.aspx)
• Windows 8 and Windows Server 2012 (https://technet.microsoft.com/en-us/library/hh848628(v=wps.620).aspx)
• Windows 8.1 and Windows Server 2012 R2 (https://technet.microsoft.com/en-us/library/hh848628(v=wps.630).aspx)
• Windows 10 and Windows Server 2016
• Export Certificates using Powershell

 

Importing certificates

• Windows 7 and Windows Server 2008 R2 (https://technet.microsoft.com/en-us/library/cc754489.aspx)
• Windows 8 and Windows Server 2012 (https://technet.microsoft.com/en-us/library/hh848630(v=wps.620).aspx)
• Windows 8.1 and Windows Server 2012 R2 (https://technet.microsoft.com/en-us/library/hh848630(v=wps.630).aspx)
• Windows 10 and Windows Server 2016 (https://technet.microsoft.com/en-us/library/hh848630(v=wps.640).aspx)

 

When you import certificates into the certificate store on computers with an application that encrypts and decrypts the data, you must import the certificates into either the machine certificate store or the certificate store of the domain account running the application.

 

Column Encryption Key

SQL Server 2016 ADO.NET driver uses column encryption keys to encrypt the data before sending it to the SQL Server, and to decrypt the data after retrieving it from the SQL Server 2016 instance. As with the column master key, you can create column encryption keys by using T-SQL or SSMS.

Expand  #Security under selected database then expand #Always Encrypted Keys, right click on # Column Encryption Key   and click on #New Column Encryption Key .In the New Column Encryption Key dialog box, type a name for the new encryption key, select a Column Master Key Definition in the drop-down list and then click OK. You can now use the column encryption key in the definition of a new table.

Under #New Column Encryption Key Dialog , you will get already created Master Key like below

 

 

Now Create Table with Encrypted Values

You can create simply by using CREATE TABLE syntax with three extra parameters within ENCRYPTED WITH

1. ENCRYPTION_TYPE : – value can be RANDOMIZEDor DETERMINISTIC
2. ALGORITHM : – It can be only RAEAD_AES_256_CBC_HMAC_SHA_256
3. COLUMN_ENCRYPTION_KEY :- In our case it will be “MyColEncryptionKey”

 

 

 

Changes Required in Application

Always Encrypted work is the addition of a connection string attribute to the connection string of the application connecting to the database: Column Encryption Setting=enabled